|
|
Posted by strawberry on 11/08/06 01:37
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.
> > >>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.
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.
Navigation:
[Reply to this message]
|