...

View Full Version : 2Dim Array inserted into MySQL table?



Hayyel
03-09-2009, 04:40 AM
Hello,

I have a 2 dimensional php array I need to insert into a MySQL table. I can currently get the info I need out of the array using:


foreach ($finalarr as $firstDimKey => $firstDimVal) {
foreach ($firstDimVal as $secondDimKey => $secondDimVal) {
echo "KEY: $secondDimKey -- VALUE $secondDimVal<br>";
}
or


foreach ($finalarr as $firstDimKey => $firstDimVal) {
echo "Member ID for key $firstDimKey is {$firstDimVal['memberID']}<br>";
}

This is my first attempt at using MySQL so any help is appreciated.

oesxyl
03-09-2009, 05:04 AM
what is the question/problem?

best regards

Hayyel
03-09-2009, 12:42 PM
The problem is I have no idea how to get the data contained in the array into a MySQL table.

I can connect to the DB. I just have no clue how to write the query to get the data from the array to the database table.

djm0219
03-09-2009, 01:08 PM
Serialize (http://www.php.net/manual/en/function.serialize.php) the array so it becomes a simple variable that you may store then use unserialize (http://www.php.net/manual/en/function.unserialize.php) to restore the array to its original state after you retrieve it from the database.

Hayyel
03-09-2009, 02:02 PM
So there is no way then to extract the individual vlaues for the keys and populate the table with them?

daemonkin
03-09-2009, 02:10 PM
Are you wanting to run through the 2-d array and insert into your db?

Like this:



Array
(
[22] => Array
(
[description] => Black Bag
[image] => bag_black.jpg
[sizes] => Array
(
[one_size] => 0
)

)

[23] => Array
(
[description] => Green Bag
[image] => bag_green.jpg
[sizes] => Array
(
[one_size] => 0
)

)
)


You can run through 2 for loops to gather up the data and write an insert statement for each:



foreach($a2D as $key=>$value){
foreach ($value as $key2=>$value2){
$sql = "INSERT INTO db (...) VALUES ('{$VALUE2['...']}')";
}
}


Does this help?

Don't forget to use the '{' and '}' separators around each value that you are inserting as SQL will not be able to parse the information. The curly brackets help to define the values that should be entered.

D.

Hayyel
03-09-2009, 02:49 PM
So I have:


include 'dbconnect.php';
include 'opendb.php';
$finalarr = unserialize(file_get_contents("../../subpages/uploads/guild.txt"));
foreach ($finalarr as $firstDimKey => $firstDimVal) {
foreach ($firstDimVal as $secondDimKey => $secondDimVal) {

$sql = "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,0) VALUES ('{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$second DimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}',' {$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDi mVal}','{$secondDimVal}','{$secondDimVal}')";
}
}

What am I missing/doing wrong?

oesxyl
03-09-2009, 05:22 PM
So I have:


include 'dbconnect.php';
include 'opendb.php';
$finalarr = unserialize(file_get_contents("../../subpages/uploads/guild.txt"));
foreach ($finalarr as $firstDimKey => $firstDimVal) {
foreach ($firstDimVal as $secondDimKey => $secondDimVal) {

$sql = "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,0) VALUES ('{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$second DimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}',' {$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDi mVal}','{$secondDimVal}','{$secondDimVal}')";
}
}

What am I missing/doing wrong?
you have a 0 after rank in the fields name part of the query and I'm not sure that the way you build the values part give what you expect.
try to outut the query to see what you have like this:


echo '<pre>'.$sql.'</pre>';


best regards

Hayyel
03-09-2009, 06:24 PM
Yep no where close to the results I expected.

While I see what it is doing I am failing to understand how to get it to iterate through the Field Names and Values. Currently it just repeats the same one over and over.

Seems like it needs a counter or something.

oesxyl
03-09-2009, 06:38 PM
Yep no where close to the results I expected.

While I see what it is doing I am failing to understand how to get it to iterate through the Field Names and Values. Currently it just repeats the same one over and over.

Seems like it needs a counter or something.
you can build the query form your array using join or a loop with foreach or for.
something like that:


$sql = "insert into mytable (fields list) values ";
$rows = array();
foreach($myarray as $myrowarray){
$row = array();
foreach($myrowarray as $filedname => $value){
// do some checking here to be sure that fields are in same order you
// and are same as in sql
$row[] = $value;
}
$sqlfrag = '('. join(',',$row) . ')';
}
$sql .= join(',',$sqlfrag);

I didn't tested, could be wrong, it's only to have a idea how to do.

best regards

Hayyel
03-10-2009, 12:56 AM
So it all finally looks good using this code:


include 'dbconnect.php';
include 'opendb.php';
foreach($finalarr as $firstDimKey => $firstDimValue){
foreach($firstDimValue as $secondDimKey => $secondDimValue){
$sql = "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,0) VALUES ('{$secondDimValue}')";
}
}
$result = mysql_query($sql) or die(mysql_error()); // execute the query

Only issue is now I receive a error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES ('')' at line 1

It is caused by the 0 in the fields list.

If I remove it I get an error:

Column count doesn't match value count at row 1

I have tried removing the field in the table and query to no avail. It is actually in the array like that. How could I get rid of it?\


Array
(
[22] => Array
(
[description] => Black Bag
[image] => bag_black.jpg
[sizes] => Array
[0] =>
)

[23] => Array
(
[description] => Green Bag
[image] => bag_green.jpg
[sizes] => Array
[0] =>
)
)

oesxyl
03-10-2009, 01:08 AM
the number of values between '(' and ')' must be same with the number of fields you want to insert. Use again the part with echo and pre to see what's wrong.

best regards

Hayyel
03-10-2009, 03:57 AM
I had the code totally wrong. I was misinterpreting what the sql echo was telling me. I had it set to put one value in each row instead of all 19 values.

My new code is as follows and works perfectly. Is there any optimization I should look into?


include 'dbconnect.php';
include 'opendb.php';
foreach($finalarr as $firstDimKey => $firstDimValue){

$sql = "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,unknown) VALUES ('{$firstDimValue['note']}','{$firstDimValue['careerID']}','{$firstDimValue['careerString']}','{$firstDimValue['rankInAlliance']}','{$firstDimValue['lastLogin']}','{$firstDimValue['bearerStatus']}','{$firstDimValue['LastLoginNumberHour24']}','{$firstDimValue['zoneID']}','{$firstDimValue['lastLoginNumberDay']}','{$firstDimValue['statusNumber']}','{$firstDimValue['lastLoginnumberMonth']}','{$firstDimValue['lastNumberLoginYear']}','{$firstDimValue['memberID']}','{$firstDimValue['onote']}','{$firstDimValue['founder']}','{$firstDimValue['name']}','{$firstDimValue['titleString']}','{$firstDimValue['rank']}','{$firstDimValue['unknown']}')";
echo '<pre>'.$sql.'</pre>';

$result = mysql_query($sql) or die(mysql_error()); // execute the query

}
echo "SQL update successful!";

THANK YOU all for your help!

oesxyl
03-10-2009, 04:13 AM
So I guess my code is wrong still. The error is because it is doing the following:


INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,unknown) VALUES ('')
INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,unknown) VALUES ('62')

One Value for each row instead of 19 per row.
$firstDimValue must be a array and must have 19 items in your case.
If the items in $firstDimValue correspond to fields note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus, lastLoginNumberHour24, zoneID, lastLoginNumberDay, statusNumber, lastLoginNumberMonth, lastLoginNumberYear, memberID, onote, founder, name, titleString, rank, in that order, you can do something like that:


$sql = "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,
lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,
statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,
founder,name,titleString,rank,unknown) VALUES (". join(',',$firstDimValue) .")";

and you don't need the inner foreach.
Before that check if $firstDimValue is what you expect using print_r or var_dump.

best regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum