|
|
Posted by strawberry on 11/06/06 14:53
Captain Paralytic wrote:
> strawberry wrote:
> > 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
>
> To make this a bit easier, could you supply a var_export($array) for us?
So here's a snippet from the array [print_r($array)]. I'm not sure
you'd thank me if I stuck the whole thing on here!:
Note: This is one of 4 children of a parent task which has ID = 11
[3] =>
Array ( [name] => TASK
[attrs] =>
Array ( [ID] => 14
[NAME] => Task 2.4
[EXPAND] => true )
[children] =>
Array ( [0] =>
Array ( [name] => NOTES
[attrs] => Array ( )
[tagData] => Embedded devices, etc ) ) )
The statements echoed by the function (see below) look something like
this:
INSERT INTO TASK (name) VALUES ('TASK') ON DUPLICATE KEY UPDATE
INSERT INTO (ID) VALUES ('14') ON DUPLICATE KEY UPDATE
INSERT INTO (ID) VALUES ('14') WHERE ID = 14
INSERT INTO (NAME) VALUES ('Task 2.4') ON DUPLICATE KEY UPDATE
INSERT INTO (ID) VALUES ('14') WHERE ID = 14
INSERT INTO (EXPAND) VALUES ('true') ON DUPLICATE KEY UPDATE
INSERT INTO (ID) VALUES ('14') WHERE ID = 14
INSERT INTO NOTES (name) VALUES ('NOTES') ON DUPLICATE KEY UPDATE
INSERT INTO NOTES (tagData) VALUES ('Embedded devices, etc') ON
DUPLICATE KEY UPDATE
and here's how I suppose it should look
INSERT INTO TASK (ID,NAME,EXPAND) VALUES ('14','Task 2.4','true') ON
DUPLICATE KEY UPDATE
INSERT INTO TASK (PARENT) VALUES ('11') WHERE ID = '14'
Even if we ignore the NOTES bit for now (which I guess should be a
field within the TASK table) you can see there's a lot wrong with my
function!
So, I can see that to do it this way I'll need to sort out my logic,
and also implode the keys and values, something like:
foreach ($array as $key => $value)
$array[$key] = "'".str_replace("'", "\'",
stripslashes($value))."'";
$values = implode(", ", $value);
but I can't quite figure this bit out either. :-( MySQL is such a
breeze compared with this stuff. Once again, any nudges in the right
direction greatly appreciated.
[Back to original message]
|