Posted by strawberry on 11/03/06 15:57

Hi,

Here's my (latest) attempt at building MySQL statements from a
multidimensional array. It doesn't quite work, of course, but I
can't figure out why. I suspect there's lots of reasons. Any
pointers would be helpful!

function array_to_mysql($array) //1 define the function
{
if(is_array($array)){ //3 if $array is an array
foreach($array as $key=>$value){ //4 foreach key/value pair
within the array
if(is_array($value)){ //5 either the current value is itself
an array
array_to_mysql($value); //6 (so call this function
again to process that array)
} else { //7 or it isn't
if($key == 'name'){ //8 (in which case, if the key paired
with that value = 'name'
$table = $value; //9 then assign that value to $table)
} elseif //10 or else
($key == 'ID'){ //11 (if the key paired with that value =
'ID'
$parent = $value;} //12 then asign that value to $parent
//13 and also issue the following statement
echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
UPDATE <BR>\n";
if(!is_null($parent)){ //Finally, if $parent is assigned issue
the following
echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
UPDATE<BR>\n";
}
}
}
}
}

The $table variable is supposed to be assigned whenever the function
encounters a key called 'name' (it should be case sensitive). It should
retain that key's associated value until it encounters another key
called 'name'. But it doesn't. :-(

So here's some example output:

INSERT INTO PROJECT (name) VALUES ('PROJECT') UPDATE ON DUPLICATE KEY
INSERT INTO (COMPANY) VALUES ('Myself LLC') UPDATE ON DUPLICATE KEY
INSERT INTO (WEBLINK) VALUES ('www.myselfllc.net') UPDATE ON DUPLICATE
KEY
INSERT INTO (VIEW-DATE) VALUES ('2005-12-26') UPDATE ON DUPLICATE KEY

and this is how it should look:

INSERT INTO PROJECT (COMPANY) VALUES ('Myself LLC') UPDATE ON DUPLICATE
KEY
INSERT INTO PROJECT (WEBLINK) VALUES ('www.myselfllc.net') UPDATE ON
DUPLICATE KEY
INSERT INTO PROJECT (VIEW-DATE) VALUES ('2005-12-26') UPDATE ON
DUPLICATE KEY

Note the first INSERT statement should not have happened. It should
have realised that PROJECT was the 'name' of the table and assigned
$table accordingly. $table did get assigned but the assignment was
apparently lost by the time it reached COMPANY.

TIA

[Back to original message]