• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Insert php multidimentional array into mysql

I am trying to populate a mysql table from a multidimensional array in php.
I am having error during insert and when I tried display the insert string I realized that the values it tried to insert are wrong hence the error. I have attached a segment of the code I am using. Also below is the insert string from a foreach loop I am using the generate the insert string.
INSERT INTO ComboTable (RptDate, CSA_Name) VALUES ('0', 'Array'), ('1', 'Array'), ('2', 'Array'), ('3', 'Array'), ('4', 'Array'), ('5', 'Array'), ('6', 'Array'), ('7', 'Array'), ('8', 'Array'), ('9', 'Array'), ('10', 'Array'), ('11', 'Array')
0
adokli
Asked:
adokli
  • 13
  • 8
  • 2
1 Solution
 
Loganathan NatarajanLAMP DeveloperCommented:
Your INSERT query statement is wrong. Try to write individual statement.
0
 
adokliAuthor Commented:
Yes I know it is wrong. The reason it is wrong is the values coming from the php array.
0
 
adokliAuthor Commented:
Please ignore the insert statement for now. My concern is more to do with the outputs "print_r ($ComboArray)" and "print_r($values)". The output from print_r($values) is the values to be used for the insert, but they are wrong.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Loganathan NatarajanLAMP DeveloperCommented:
why do you want to store the array values into the db? instead you store the values and construct the array structure where you need?
0
 
Ray PaseurCommented:
If you're new to PHP and MySQL, this article can help steer you toward some useful learning resources:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

In order to help you here, we need to see the data you have, and the CREATE TABLE statement(s) for the data base tables.
the outputs "print_r ($ComboArray)" and "print_r($values)".
would be very helpful.
0
 
Ray PaseurCommented:
The probable reason for something like this...

VALUES ('0', 'Array'), ('1', 'Array'), ('2', 'Array')...

... is that when a PHP script uses an array as if it were a string, PHP returns the word "Array" instead of returning any of the data.  It's a problem because PHP also raises a Notice-level condition for array-to-string conversions, but the standard PHP installation suppresses the Notice messages, making the problem hard to find.  You can only get to see things like this if you use error_reporting(E_ALL) and you should use that in all of your PHP scripts.

More information on type juggling:
http://php.net/manual/en/language.types.string.php#language.types.string.casting

More information on how to work with PHP arrays is available here:
http://php.net/manual/en/language.types.array.php
0
 
adokliAuthor Commented:
the outputs "print_r ($ComboArray)" and "print_r($values)" was in the file I attached
0
 
Ray PaseurCommented:
the file I attached
??
0
 
adokliAuthor Commented:
Below the print_r($ComboArray) output
Array (
[ReportDate] => 2014-01 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[1] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Albury 2 CSA )
[2] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Applecross 2 CSA )
[3] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Applecross CSA )
[4] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[5] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Albury 2 CSA )
[6] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Applecross 2 CSA )
[7] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Applecross CSA )
[8] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[9] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Albury 2 CSA )
[10] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Applecross 2 CSA )
[11] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Applecross CSA )
)

Below is the print_r($values) output
Array (
[0] => ('0', 'Array')
[1] => ('1', 'Array')
[2] => ('2', 'Array')
[3] => ('3', 'Array')
[4] => ('4', 'Array')
[5] => ('5', 'Array')
[6] => ('6', 'Array')
[7] => ('7', 'Array')
[8] => ('8', 'Array')
[9] => ('9', 'Array')
[10] => ('10', 'Array')
[11] => ('11', 'Array')
)
0
 
Ray PaseurCommented:
I have not tested this query string, but the PHP code demonstrates the principles correctly.  Please read it over and post back if you have any questions, thanks. ~Ray
http://iconoun.com/demo/temp_adokli.php

<?php // demo/temp_adokli.php
error_reporting(E_ALL);
echo '<pre>';

/**
 * SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28475904.html#a40196762
 *
Below the print_r($ComboArray) output
Array (
[ReportDate] => 2014-01 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[1] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Albury 2 CSA )
[2] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Applecross 2 CSA )
[3] => Array ( [ReportDate] => 2014-01 [Csa_Name] => Applecross CSA )
[4] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[5] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Albury 2 CSA )
[6] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Applecross 2 CSA )
[7] => Array ( [ReportDate] => 2014-02 [Csa_Name] => Applecross CSA )
[8] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Acacia Ridge Depot 2 CSA )
[9] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Albury 2 CSA )
[10] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Applecross 2 CSA )
[11] => Array ( [ReportDate] => 2014-03 [Csa_Name] => Applecross CSA )
)
*/

// SET UP THE TEST DATA
$combo_array = array
( array('ReportDate' => '2014-01', 'Csa_Name' => 'Acacia Ridge Depot 2 CSA' )
, array('ReportDate' => '2014-01', 'Csa_Name' => 'Albury 2 CSA' )
, array('ReportDate' => '2014-01', 'Csa_Name' => 'Applecross 2 CSA' )
, array('ReportDate' => '2014-01', 'Csa_Name' => 'Applecross CSA' )
, array('ReportDate' => '2014-02', 'Csa_Name' => 'Acacia Ridge Depot 2 CSA' )
, array('ReportDate' => '2014-02', 'Csa_Name' => 'Albury 2 CSA' )
, array('ReportDate' => '2014-02', 'Csa_Name' => 'Applecross 2 CSA' )
, array('ReportDate' => '2014-02', 'Csa_Name' => 'Applecross CSA' )
, array('ReportDate' => '2014-03', 'Csa_Name' => 'Acacia Ridge Depot 2 CSA' )
, array('ReportDate' => '2014-03', 'Csa_Name' => 'Albury 2 CSA' )
, array('ReportDate' => '2014-03', 'Csa_Name' => 'Applecross 2 CSA' )
, array('ReportDate' => '2014-03', 'Csa_Name' => 'Applecross CSA' )
)
;

// DOES IT LOOK RIGHT?  YES
print_r($combo_array);

// CONSTRUCT THE INSERT QUERY
$sql = "INSERT INTO ComboTable (RptDate, CSA_Name) VALUES ";

// ADDING EACH OF THE ROWS, SEPARATED BY COMMAS
foreach ($combo_array as $arr)
{
    $sql .= PHP_EOL
    . '('
    . "'"
    . $arr['ReportDate']
    . "', '"
    . $arr['Csa_Name']
    . "'"
    . '),'
    ;
}
// REMOVE THE TRAILING COMMA
$sql = rtrim($sql, ',');

// SHOW THE WORK PRODUCT
print_r($sql);

Open in new window

0
 
adokliAuthor Commented:
Hi Ray, I am going mad here.
I am getting Parse error I do not understand. i am running the insert with line below which is the line 166.
However, when I copy and paste the insert string generated at mysql prompt it works fine. I do not why.

mysql_query($sql) or die(mysql_error() ;

Parse error: syntax error, unexpected ';' in /cns/var/www/Sales/ComboTable_v3.php on line 166

INSERT INTO ComboTable (RptDate, Csa_Name) VALUES ('2013-07', 'Acacia Ridge Depot 2 CSA'), ('2013-07', 'Albury 2 CSA'), ('2013-07', 'Applecross 2 CSA'), ('2013-07', 'Applecross CSA'), ('2013-08', 'Acacia Ridge Depot 2 CSA'), ('2013-08', 'Albury 2 CSA'), ('2013-08', 'Applecross 2 CSA'), ('2013-08', 'Applecross CSA'), ('2013-09', 'Acacia Ridge Depot 2 CSA'), ('2013-09', 'Albury 2 CSA'), ('2013-09', 'Applecross 2 CSA'), ('2013-09', 'Applecross CSA'), ('2013-10', 'Acacia Ridge Depot 2 CSA'), ('2013-10', 'Albury 2 CSA'), ('2013-10', 'Applecross 2 CSA'), ('2013-10', 'Applecross CSA'), ('2013-11', 'Acacia Ridge Depot 2 CSA'), ('2013-11', 'Albury 2 CSA'), ('2013-11', 'Applecross 2 CSA'), ('2013-11', 'Applecross CSA'), ('2013-12', 'Acacia Ridge Depot 2 CSA'), ('2013-12', 'Albury 2 CSA'), ('2013-12', 'Applecross 2 CSA'), ('2013-12', 'Applecross CSA'), ('2014-01', 'Acacia Ridge Depot 2 CSA'), ('2014-01', 'Albury 2 CSA'), ('2014-01', 'Applecross 2 CSA'), ('2014-01', 'Applecross CSA'), ('2014-02', 'Acacia Ridge Depot 2 CSA'), ('2014-02', 'Albury 2 CSA'), ('2014-02', 'Applecross 2 CSA'), ('2014-02', 'Applecross CSA'), ('2014-03', 'Acacia Ridge Depot 2 CSA'), ('2014-03', 'Albury 2 CSA'), ('2014-03', 'Applecross 2 CSA'), ('2014-03', 'Applecross CSA')
0
 
Ray PaseurCommented:
This is missing the parenthesis that closes the call to die()
mysql_query($sql) or die(mysql_error() ;

Open in new window

Try it like this:
mysql_query($sql) or die(mysql_error() );

Open in new window

But that said, you really should use trigger_error() instead of die().  There are a lot of reasons for this that are too lengthy to explain now, and you'll need to understand them some day, but for now, you just want to cultivate good habits, so get out of the habit of using die().  It made sense in PHP3, back around 1999.  Times have changed!
0
 
adokliAuthor Commented:
Wow Ray, you are a legend. How did I miss that. thank so much.
It works like charm.

Its 1.15am in Sydney, Australia. I need to catch some sleep now.
0
 
adokliAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for adokli's comment #a40197184

for the following reason:

The response rate very quick.
0
 
Ray PaseurCommented:
@adokli: It's traditional at E-E that you award points to the solutions that meet your needs.  Did you really mean to close this without accepting any comment as the solution?
0
 
adokliAuthor Commented:
Sorry Ray, I have not used the E-E extensively that is why. I am more than happy to aware points to the solution.
0
 
Ray PaseurCommented:
No apology needed at all.  I just figured you were sleepy :-)
0
 
adokliAuthor Commented:
Ray, please walk me through the process to award point?
0
 
adokliAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for adokli's comment #a40198531

for the following reason:

The services rendered by expert was first class
0
 
adokliAuthor Commented:
Please do not close as I have requested. I want award 500 points for the solution.
0
 
adokliAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for adokli's comment #a40201582

for the following reason:

The expert provided a lot information to help me understand the problem I was having
0
 
adokliAuthor Commented:
A very quick answer was given to my problem.
0
 
Ray PaseurCommented:
Thanks for using E-E and thanks for the points!  All the best, Ray
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now