wandering in dis- array

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
\n";
if(!is_null($parent)){ //Finally, if $parent is assigned issue
the following
echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
UPDATE
\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

Re: wandering in dis- array

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
\n";
> if(!is_null($parent)){ //Finally, if $parent is assigned issue
> the following
> echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
> UPDATE
\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?

Re: wandering in dis- array

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
\n";
> if(!is_null($parent)){ //Finally, if $parent is assigned issue
> the following
> echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
> UPDATE
\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

One thing that ocurs to me is that the element called 'name' may not be
the first element in the array. So when you hit the first field/value
pair, there may not yet be a name.

Re: wandering in dis- array

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
\n";
> > if(!is_null($parent)){ //Finally, if $parent is assigned issue
> > the following
> > echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
> > UPDATE
\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.

Re: wandering in dis- array

strawberry wrote:

blah, blah, blah...

Sniped all the stuff, 'cos it was getting too big!

I can see what is wrong, but I'm not sure exactly what you want to
achieve.

The reason for getting the first INSERT that has a field called "name"
is that the original code will output an INSERT statement for every
array element that is processed, since the echo is outside of the "if"
statement that identifies a table "name" field.

Now in your first example you said that the required output was for
every INSERT to have a single key/value pair. In the most recent one
you are suggesting that they are collected together.

In either case you need to ensure that the first element of the array
is a "name" element.

Also, your statement about keeping the table name till you encounter
another "name" element would not happen, as any array can only have one
element with a particular value of "key". In your processing you are
calling the function recursively whenever you encounter a "sub-array",
so that all the variables will start off empty as you are not using any
"static" variables.

If your sub-arrays are supposed to have the same table value as was
previously encountered, then you need to make the $table variable
accessable to the new instance of the function call.

Take a look at http://uk.php.net/static for pointers on what I think
you need to do.

There isn't quite enough of the var_export shown fo me to be 100% sure
that this'll fix it, but I am 95%.

HTH
Regards
Paul

Re: wandering in dis- array

Captain Paralytic wrote:
> strawberry wrote:
>
> blah, blah, blah...
>
> Sniped all the stuff, 'cos it was getting too big!
>
> I can see what is wrong, but I'm not sure exactly what you want to
> achieve.
>
> The reason for getting the first INSERT that has a field called "name"
> is that the original code will output an INSERT statement for every
> array element that is processed, since the echo is outside of the "if"
> statement that identifies a table "name" field.
>
> Now in your first example you said that the required output was for
> every INSERT to have a single key/value pair. In the most recent one
> you are suggesting that they are collected together.
>
> In either case you need to ensure that the first element of the array
> is a "name" element.
>
> Also, your statement about keeping the table name till you encounter
> another "name" element would not happen, as any array can only have one
> element with a particular value of "key". In your processing you are
> calling the function recursively whenever you encounter a "sub-array",
> so that all the variables will start off empty as you are not using any
> "static" variables.
>
> If your sub-arrays are supposed to have the same table value as was
> previously encountered, then you need to make the $table variable
> accessable to the new instance of the function call.
>
> Take a look at http://uk.php.net/static for pointers on what I think
> you need to do.
>
> There isn't quite enough of the var_export shown fo me to be 100% sure
> that this'll fix it, but I am 95%.
>
> HTH
> Regards
> Paul

Thanks a lot for the pointer CP. I'll take a look at it right away.

Re: wandering in dis- array

strawberry wrote:
> Captain Paralytic wrote:
> > strawberry wrote:
> >
> > blah, blah, blah...
> >
> > Sniped all the stuff, 'cos it was getting too big!
> >
> > I can see what is wrong, but I'm not sure exactly what you want to
> > achieve.
> >
> > The reason for getting the first INSERT that has a field called "name"
> > is that the original code will output an INSERT statement for every
> > array element that is processed, since the echo is outside of the "if"
> > statement that identifies a table "name" field.
> >
> > Now in your first example you said that the required output was for
> > every INSERT to have a single key/value pair. In the most recent one
> > you are suggesting that they are collected together.
> >
> > In either case you need to ensure that the first element of the array
> > is a "name" element.
> >
> > Also, your statement about keeping the table name till you encounter
> > another "name" element would not happen, as any array can only have one
> > element with a particular value of "key". In your processing you are
> > calling the function recursively whenever you encounter a "sub-array",
> > so that all the variables will start off empty as you are not using any
> > "static" variables.
> >
> > If your sub-arrays are supposed to have the same table value as was
> > previously encountered, then you need to make the $table variable
> > accessable to the new instance of the function call.
> >
> > Take a look at http://uk.php.net/static for pointers on what I think
> > you need to do.
> >
> > There isn't quite enough of the var_export shown fo me to be 100% sure
> > that this'll fix it, but I am 95%.
> >
> > HTH
> > Regards
> > Paul
>
> Thanks a lot for the pointer CP. I'll take a look at it right away.

Yep, excellent. That seems to have sorted out that problem.

function array_to_mysql($array) //define the function
{
static $table = null;

etc, etc...

1 down, maybe 9 to go?

Anyway, thanks again.

Re: wandering in dis- array

strawberry wrote:
> strawberry wrote:
> > Captain Paralytic wrote:
> > > strawberry wrote:

> > > The reason for getting the first INSERT that has a field called "name"
> > > is that the original code will output an INSERT statement for every
> > > array element that is processed, since the echo is outside of the "if"
> > > statement that identifies a table "name" field.

> >
> > Thanks a lot for the pointer CP. I'll take a look at it right away.
>
> Yep, excellent. That seems to have sorted out that problem.
>
> function array_to_mysql($array) //define the function
> {
> static $table = null;
>
> etc, etc...
>
> 1 down, maybe 9 to go?
>
> Anyway, thanks again.

Surely nearly 2 down, when you look at (and fix) the the "if" statement
that I mentioned ;-)

Re: wandering in dis- array

Captain Paralytic wrote:
> strawberry wrote:
> > strawberry wrote:
> > > Captain Paralytic wrote:
> > > > strawberry wrote:
>
> > > > The reason for getting the first INSERT that has a field called "name"
> > > > is that the original code will output an INSERT statement for every
> > > > array element that is processed, since the echo is outside of the "if"
> > > > statement that identifies a table "name" field.
>
> > >
> > > Thanks a lot for the pointer CP. I'll take a look at it right away.
> >
> > Yep, excellent. That seems to have sorted out that problem.
> >
> > function array_to_mysql($array) //define the function
> > {
> > static $table = null;
> >
> > etc, etc...
> >
> > 1 down, maybe 9 to go?
> >
> > Anyway, thanks again.
>
> Surely nearly 2 down, when you look at (and fix) the the "if" statement
> that I mentioned ;-)

You're quite right, of course! Call me a dummie if you like but the
only way I could think of resolving that was to ask again whether or
not $key = 'name'
This doesn't look very elegant but it looks like it might work. I'll
turn the echoes into real mysql statements and see what happens. Maybe
I don't have to bother with the implosion
I described. I guess it just means more INSERT statements - but so
what, right?

Cheers.

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

Re: wandering in dis- array

strawberry wrote:
> You're quite right, of course! Call me a dummie if you like but the
> only way I could think of resolving that was to ask again whether or
> not $key = 'name'
> This doesn't look very elegant but it looks like it might work. I'll
> turn the echoes into real mysql statements and see what happens. Maybe
> I don't have to bother with the implosion
> I described. I guess it just means more INSERT statements - but so
> what, right?

Nah, I won't call you a dumie, but I will suggest that what you may
want to do is to change the innermost if statement to something like
this:

if ($key == 'name') {
$table = $value;
} elseif {
($key == 'ID') {
echo "INSERT INTO $table (ID) VALUES ('$value')
\n";
} else {
echo "INSERT INTO $table ($key) VALUES ('$value') UPDATE ON
DUPLICATE KEY
\n";
}

Re: wandering in dis- array

Captain Paralytic wrote:
> strawberry wrote:
> > You're quite right, of course! Call me a dummie if you like but the
> > only way I could think of resolving that was to ask again whether or
> > not $key = 'name'
> > This doesn't look very elegant but it looks like it might work. I'll
> > turn the echoes into real mysql statements and see what happens. Maybe
> > I don't have to bother with the implosion
> > I described. I guess it just means more INSERT statements - but so
> > what, right?
>
> Nah, I won't call you a dumie, but I will suggest that what you may
> want to do is to change the innermost if statement to something like
> this:
>
> if ($key == 'name') {
> $table = $value;
> } elseif {
> ($key == 'ID') {
> echo "INSERT INTO $table (ID) VALUES ('$value')
\n";
> } else {
> echo "INSERT INTO $table ($key) VALUES ('$value') UPDATE ON
> DUPLICATE KEY
\n";
> }

Thanks, Paul. That definitely feels like a step forward. Now I've just
got to resolve the tips you've given me with those proposed by Jerry. I
don't know if there's a correct protocol for closing a thread so I'll
just say that I'd like to close this thread - before I get flamed again
;-) - and continue it over at

http://groups.google.com/group/comp.lang.php/browse_thread/t hread/f2a8ce8e295c9e66/a189013650ff79a5#a189013650ff79a5

I'll try and post the latest version of the function, incorporating
everyone's comments, later today so that, if you (or anyone) wishes to
continue to contribute we can be sure we're talking about the same
thing.