|
|
Posted by Jerry Stuckle on 11/06/06 17:41
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.
>>jstucklex@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.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|