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')
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')
Loganathan Natarajan
Your INSERT query statement is wrong. Try to write individual statement.
ASKER
Yes I know it is wrong. The reason it is wrong is the values coming from the php array.
ASKER
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.
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?
If you're new to PHP and MySQL, this article can help steer you toward some useful learning resources:
https://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.
https://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.
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
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
ASKER
the outputs "print_r ($ComboArray)" and "print_r($values)" was in the file I attached
the file I attached??
ASKER
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')
)
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')
)
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
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);ASKER
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/ComboTa
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')
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/ComboTa
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
It works like charm.
Its 1.15am in Sydney, Australia. I need to catch some sleep now.
ASKER
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.
Accepted answer: 0 points for adokli's comment #a40197184
for the following reason:
The response rate very quick.
@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?
ASKER
Sorry Ray, I have not used the E-E extensively that is why. I am more than happy to aware points to the solution.
No apology needed at all. I just figured you were sleepy :-)
ASKER
Ray, please walk me through the process to award point?
ASKER
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
Accepted answer: 0 points for adokli's comment #a40198531
for the following reason:
The services rendered by expert was first class
ASKER
Please do not close as I have requested. I want award 500 points for the solution.
ASKER
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
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
ASKER
A very quick answer was given to my problem.
Thanks for using E-E and thanks for the points! All the best, Ray