|
|
Posted by strawberry on 11/07/06 11:01
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.
> >>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
> ==================
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.
[Back to original message]
|