Extending variable scope? (Related post at alt.php.mysql)

[strawberry]

In the function below, I'd like to extend the scope of the $table
variable such that, once assigned it would become available to other
parts of the function. I thought 'global $table;' would solve this but
it's clear that I'm misunderstanding $variable persistence. I posted a
similar enquiry over at alt.php.mysql, but I guess this is a more
appropriate forum because the problems I'm having relate to PHP.

Any help appreciated.

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

Apologies if the formatting gets stuffed up in the posting. TIA

[Jerry Stuckle]

strawberry wrote:

In the function below, I'd like to extend the scope of the $table
variable such that, once assigned it would become available to other
parts of the function. I thought 'global $table;' would solve this but
it's clear that I'm misunderstanding $variable persistence. I posted a
similar enquiry over at alt.php.mysql, but I guess this is a more
appropriate forum because the problems I'm having relate to PHP.

Any help appreciated.

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

Apologies if the formatting gets stuffed up in the posting. TIA

Just define $table at the start of our function, i.e.

function array_to_mysql($array) {
$table = null;

....

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[strawberry]

Jerry Stuckle wrote:

strawberry wrote:

In the function below, I'd like to extend the scope of the $table
variable such that, once assigned it would become available to other
parts of the function. I thought 'global $table;' would solve this but
it's clear that I'm misunderstanding $variable persistence. I posted a
similar enquiry over at alt.php.mysql, but I guess this is a more
appropriate forum because the problems I'm having relate to PHP.

Any help appreciated.

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

Apologies if the formatting gets stuffed up in the posting. TIA

Just define $table at the start of our function, i.e.

function array_to_mysql($array) {
$table = null;

...

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Thanks Jerry - but I'm still a long way off

Here's a snippet from the array:
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 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 the 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);

Again, any more nudges in the right direction greatly appreciated.

---
For continuity, here's the current incarnation of the function:

function array_to_mysql($array) { //1 define the function
$table = null; //2 define a persistent variable
if(is_array($array)){ //3 if $array is indeed 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;
$condition = "WHERE ID = $value";} //12 then assign that
value to $parent

echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
UPDATE <BR>\n";
if(!is_null($parent)){ //14 Finally, if $parent is
assigned issue the following
echo "INSERT INTO $table (ID) VALUES ('$parent') $condition<BR>\n";
}
}
}
}
}

[Jerry Stuckle]

strawberry wrote:

Jerry Stuckle wrote:

>>strawberry wrote:

>>>In the function below, I'd like to extend the scope of the $table
variable such that, once assigned it would become available to other
parts of the function. I thought 'global $table;' would solve this but
it's clear that I'm misunderstanding $variable persistence. I posted a
similar enquiry over at alt.php.mysql, but I guess this is a more
appropriate forum because the problems I'm having relate to PHP.

Any help appreciated.

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

Apologies if the formatting gets stuffed up in the posting. TIA

Just define $table at the start of our function, i.e.

function array_to_mysql($array) {
$table = null;

...

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Thanks Jerry - but I'm still a long way off

Here's a snippet from the array:
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 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 the 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);

Again, any more nudges in the right direction greatly appreciated.

---
For continuity, here's the current incarnation of the function:

function array_to_mysql($array) { //1 define the function
$table = null; //2 define a persistent variable
if(is_array($array)){ //3 if $array is indeed 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;
$condition = "WHERE ID = $value";} //12 then assign that
value to $parent

echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
UPDATE <BR>\n";
if(!is_null($parent)){ //14 Finally, if $parent is
assigned issue the following
echo "INSERT INTO $table (ID) VALUES ('$parent') $condition<BR>\n";
}
}
}
}
}

Hi, Strawberry,

Sorry, it took a while for me to get back to you. I wanted to have some
time to think about this.

Yes, I can see where you're getting confused. It's not an easy job to
solve, but it can be done.

First of all, since you're using the same ID in both cases, you only
need one insert statement:

INSERT INTO TASK (ID,NAME,EXPAND,PARENT)
VALUES ('14','Task 2.4','true', 11)
ON DUPLICATE KEY UPDATE

If you don't have a parent, you could use 0 or null (depending on your
database schema) for PARENT.

Alternatively, you should be using and UPDATE for the second one:

INSERT INTO TASK (ID,NAME,EXPAND)
VALUES ('14','Task 2.4','true')
ON DUPLICATE KEY UPDATE
UPDATE TASK SET PARENT=11 WHERE ID = '14'

But your problem is a lot more difficult because of the way you have
your arrays. I think you would be a lot better off not trying to use a
recursive function here because your children are of different types.

Recursion works much better if the children are of the same type. For
instance, parts to a car:

Car =Array
Engine =Array
Piston
Crankshaft
Camshaft
Valve
Body =Array
Door
Hood
Bumper

And so on.

As it is, you have different types as your children. This setup is
going to be much more difficult to process. You'll probably be better
off processing each type of array separately, i.e. the TASK function
would look for attributes and children. The attributes function would
process attributes such as NAME, ID and expand, while the children
function would handle notes, attributes (calling the attributes function
above) and tagdata.

You may still have some recursions - that's fine. But you'll be
separating the various types of arrays into different functions, making
them easier to handle.

Returning values to the caller could be handled with references.

Another way would be to go to an object oriented method, with each type
of array being a class. Put the array elements into class variables and
when you're done you can process the class to create your SQL
statements. If you're familiar with OO techniques, I think this would
be the better way to go.

But this is going to be a bit complicated because of the different data
types you have.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[strawberry]

Jerry Stuckle wrote:

strawberry wrote:

Jerry Stuckle wrote:

>strawberry wrote:

In the function below, I'd like to extend the scope of the $table
variable such that, once assigned it would become available to other
parts of the function. I thought 'global $table;' would solve this but
it's clear that I'm misunderstanding $variable persistence. I posted a
similar enquiry over at alt.php.mysql, but I guess this is a more
appropriate forum because the problems I'm having relate to PHP.

Any help appreciated.

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

Apologies if the formatting gets stuffed up in the posting. TIA
Just define $table at the start of our function, i.e.

function array_to_mysql($array) {
$table = null;

...

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Thanks Jerry - but I'm still a long way off

Here's a snippet from the array:
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 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 the 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);

Again, any more nudges in the right direction greatly appreciated.

---
For continuity, here's the current incarnation of the function:

function array_to_mysql($array) { //1 define the function
$table = null; //2 define a persistent variable
if(is_array($array)){ //3 if $array is indeed 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;
$condition = "WHERE ID = $value";} //12 then assign that
value to $parent

echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
UPDATE <BR>\n";
if(!is_null($parent)){ //14 Finally, if $parent is
assigned issue the following
echo "INSERT INTO $table (ID) VALUES ('$parent') $condition<BR>\n";
}
}
}
}
}

Hi, Strawberry,

Sorry, it took a while for me to get back to you. I wanted to have some
time to think about this.

Yes, I can see where you're getting confused. It's not an easy job to
solve, but it can be done.

First of all, since you're using the same ID in both cases, you only
need one insert statement:

INSERT INTO TASK (ID,NAME,EXPAND,PARENT)
VALUES ('14','Task 2.4','true', 11)
ON DUPLICATE KEY UPDATE

If you don't have a parent, you could use 0 or null (depending on your
database schema) for PARENT.

Alternatively, you should be using and UPDATE for the second one:

INSERT INTO TASK (ID,NAME,EXPAND)
VALUES ('14','Task 2.4','true')
ON DUPLICATE KEY UPDATE
UPDATE TASK SET PARENT=11 WHERE ID = '14'

But your problem is a lot more difficult because of the way you have
your arrays. I think you would be a lot better off not trying to use a
recursive function here because your children are of different types.

Recursion works much better if the children are of the same type. For
instance, parts to a car:

Car =Array
Engine =Array
Piston
Crankshaft
Camshaft
Valve
Body =Array
Door
Hood
Bumper

And so on.

As it is, you have different types as your children. This setup is
going to be much more difficult to process. You'll probably be better
off processing each type of array separately, i.e. the TASK function
would look for attributes and children. The attributes function would
process attributes such as NAME, ID and expand, while the children
function would handle notes, attributes (calling the attributes function
above) and tagdata.

You may still have some recursions - that's fine. But you'll be
separating the various types of arrays into different functions, making
them easier to handle.

Returning values to the caller could be handled with references.

Another way would be to go to an object oriented method, with each type
of array being a class. Put the array elements into class variables and
when you're done you can process the class to create your SQL
statements. If you're familiar with OO techniques, I think this would
be the better way to go.

But this is going to be a bit complicated because of the different data
types you have.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Thanks Jerry, plenty of food for thought.

I'm reluctant to take an OO approach simply because my experience with
programming generally, and this method in particular, is so limited.

I know that the structure of the array is somewhat inconsistent.
Unfortunately, that's not an aspect I have much control over, but
that's why I took an approach that 'simply' said 'for every occurrence
of the key 'name', let its value be that of a table and let every
subsequent key be a field within that table - until, of course, you
find another occurrence of the key 'name'. Oh, and record the fact that
nested arrays are children of the parent array.'

Although in reality I'm really only interested in a small part of the
whole array, I thought it would be simpler to have a script that
processed the whole thing with ambivalence. The XML from which the
array is generated is still in development. There may, in due course,
be more objects added to it, some of which I too would be interested in
including in my db. This way, I'd only have to add the missing tables -
I could even code for the creation of a table if one was missing.

As I mentioned, an earlier version of this function was posted over at
alt.php.mysql where Paul's been giving me some useful pointers. I know
that you both frequent both NGs so I didn't worry about it, but perhaps
I should consider closing one or other of the threads and consolidating
everything in one place - probably here, as the problem is more
specifically a PHP one rather than MySQL.

[strawberry]

strawberry wrote:

Jerry Stuckle wrote:

strawberry wrote:

Jerry Stuckle wrote:
>
>>strawberry wrote:
>>
>>>In the function below, I'd like to extend the scope of the $table
>>>variable such that, once assigned it would become available to other
>>>parts of the function. I thought 'global $table;' would solve this but
>>>it's clear that I'm misunderstanding $variable persistence. I posted a
>>>similar enquiry over at alt.php.mysql, but I guess this is a more
>>>appropriate forum because the problems I'm having relate to PHP.
>>>
>>>Any help appreciated.
>>>
>>>$function array_to_mysql($array) { //1 define the function
>> if(is_array($array)){ //2 if $array is indeed an array
>> foreach($array as $key=>$value){ //3 foreach key/value pair
>>>within the array
>> if(is_array($value)){ //4 either the current value
>>>is itself an array
>> array_to_mysql($value); //5 (so call this function again
>>>to process that array)
>> } else { //6 or it isn't
>> if($key == 'name'){ //7 (in which case, if the key
>>>paired with that value = 'name'
>> $table = $value; //8 then assign that value to
>>>$table)
>> } elseif //9 or else
>> ($key == 'ID'){ //10 (if the key paired with
>>>that value = 'ID'
>> $parent = $value;}else{ //11 then assign that value to
>>>$parent
>>>echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
>>>UPDATE <BR>\n";}
>> if(!is_null($parent)){ //13 Finally, if $parent is
>>>assigned issue the following
>>>echo "INSERT INTO $table (ID) VALUES ('$parent') ON DUPLICATE KEY
>>>UPDATE<BR>\n";
>>>}
>>>}
>>>}
>>>}
>>>}
>>>
>>>Apologies if the formatting gets stuffed up in the posting. TIA
>>>
>>
>>Just define $table at the start of our function, i.e.
>>
> function array_to_mysql($array) {
> $table = null;
>>
>>...
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>js*******@attglobal.net
>>==================
>
>
Thanks Jerry - but I'm still a long way off
>
Here's a snippet from the array:
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 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 the 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);
>
Again, any more nudges in the right direction greatly appreciated.
>
---
For continuity, here's the current incarnation of the function:
>
function array_to_mysql($array) { //1 define the function
$table = null; //2 define a persistent variable
if(is_array($array)){ //3 if $array is indeed 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;
$condition = "WHERE ID = $value";} //12 then assign that
value to $parent
>
echo "INSERT INTO $table ($key) VALUES ('$value') ON DUPLICATE KEY
UPDATE <BR>\n";
if(!is_null($parent)){ //14 Finally, if $parent is
assigned issue the following
echo "INSERT INTO $table (ID) VALUES ('$parent') $condition<BR>\n";
}
}
}
}
}
>

Hi, Strawberry,

Sorry, it took a while for me to get back to you. I wanted to have some
time to think about this.

Yes, I can see where you're getting confused. It's not an easy job to
solve, but it can be done.

First of all, since you're using the same ID in both cases, you only
need one insert statement:

INSERT INTO TASK (ID,NAME,EXPAND,PARENT)
VALUES ('14','Task 2.4','true', 11)
ON DUPLICATE KEY UPDATE

If you don't have a parent, you could use 0 or null (depending on your
database schema) for PARENT.

Alternatively, you should be using and UPDATE for the second one:

INSERT INTO TASK (ID,NAME,EXPAND)
VALUES ('14','Task 2.4','true')
ON DUPLICATE KEY UPDATE
UPDATE TASK SET PARENT=11 WHERE ID = '14'

But your problem is a lot more difficult because of the way you have
your arrays. I think you would be a lot better off not trying to use a
recursive function here because your children are of different types.

Recursion works much better if the children are of the same type. For
instance, parts to a car:

Car =Array
Engine =Array
Piston
Crankshaft
Camshaft
Valve
Body =Array
Door
Hood
Bumper

And so on.

As it is, you have different types as your children. This setup is
going to be much more difficult to process. You'll probably be better
off processing each type of array separately, i.e. the TASK function
would look for attributes and children. The attributes function would
process attributes such as NAME, ID and expand, while the children
function would handle notes, attributes (calling the attributes function
above) and tagdata.

You may still have some recursions - that's fine. But you'll be
separating the various types of arrays into different functions, making
them easier to handle.

Returning values to the caller could be handled with references.

Another way would be to go to an object oriented method, with each type
of array being a class. Put the array elements into class variables and
when you're done you can process the class to create your SQL
statements. If you're familiar with OO techniques, I think this would
be the better way to go.

But this is going to be a bit complicated because of the different data
types you have.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Thanks Jerry, plenty of food for thought.

I'm reluctant to take an OO approach simply because my experience with
programming generally, and this method in particular, is so limited.

I know that the structure of the array is somewhat inconsistent.
Unfortunately, that's not an aspect I have much control over, but
that's why I took an approach that 'simply' said 'for every occurrence
of the key 'name', let its value be that of a table and let every
subsequent key be a field within that table - until, of course, you
find another occurrence of the key 'name'. Oh, and record the fact that
nested arrays are children of the parent array.'

Although in reality I'm really only interested in a small part of the
whole array, I thought it would be simpler to have a script that
processed the whole thing with ambivalence. The XML from which the
array is generated is still in development. There may, in due course,
be more objects added to it, some of which I too would be interested in
including in my db. This way, I'd only have to add the missing tables -
I could even code for the creation of a table if one was missing.

As I mentioned, an earlier version of this function was posted over at
alt.php.mysql where Paul's been giving me some useful pointers. I know
that you both frequent both NGs so I didn't worry about it, but perhaps
I should consider closing one or other of the threads and consolidating
everything in one place - probably here, as the problem is more
specifically a PHP one rather than MySQL.

Well, I think this very nearly works...

function array_to_mysql($array) { // define the function
static $table = null; // define a persistent
variable
static $parent = null; // and another
static $id = null; // and another
if(is_array($array)){ // if $array is indeed 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
if ($key == 'children'){// (so see if it's a
child...
$parent = $id; // ...and if so, record the id of
its parent
} // and then
array_to_mysql($value); // so call this function
again to process that array);
} else { // or it isn't
if($key == 'name') { // If $key = 'name' then its
associated
$table = $value; // value is the name of a table within
my db
} elseif // otherwise
($key == 'ID'){ // If $key = 'ID' then its associated
value
$id = $value; // is a primary key (and, possibly, a
parent) within my db
if (!is_null($parent)){
echo "INSERT INTO $table (ID,PARENT) VALUES
('$id','$parent') UPDATE ON DUPLICATE KEY<BR>\n";
}
} else {
echo "INSERT INTO $table (ID,$key) VALUES
('$id','$value') UPDATE ON DUPLICATE KEY<BR>\n";

}
}
}
}
}

it doesn't implode anything so I've got to issue a separate INSERT
statement each time and there's a flaw whereby it inappropriately
assigns a value of '1' to the $parent variable so creating a whole
bunch of meaningless INSERT statements but, having come this far, this
must simple enough to straighten out :-) I'm going to replace the
echoes with real statements now and see what happens...

Jerry & Paul, thanks for all your help.