Hi folks,

I am parsing XML in PHP using xpath within a foreach loop. At the bottom of the script, I am running a MySQL query to update the database with parsed array. In this, I am using a MySQL field reference created from a PHP variable...

(... , field1, field2, $phpfieldname,... )

... and it's working fine except for one problem - it appears the MySQL query holds the first $phpfieldname in memory got the entire parsing sequence, rather than refreshing it on each foreach loop. Is there any way to refresh it on each cycle?

Cheers,

NDF

    Is it a prepared statement you're talking about? You're probably going to have to show us some code so we can understand how you're building the query.

      Hoping this gives an understandable example: -

      $phpfieldname = $newstring.$newinteger;
      
      $writemaster = mysql_query("INSERT INTO maintable (field1, field2, $phpfieldname) VALUES ('$value1', '$value1','$value1')");

      That is directly below the array created by xpath. Does that make it cleaer? I hope so. If not, I will try again.

        The sample code looks fine, so you'll probably have to show us more of the actual code you're using.

          $ParsedArray = array(
          	$value1 = $newxml->item1;
          	$value2 = $newxml->item2;
                  $value2 = $newxml->item3;
          	$newstring = $newxml->item4;
          	$newinteger = $newxml->item5;
          
          $phpfieldname = $newstring.$newinteger;)
          
          $writemaster = mysql_query("INSERT INTO maintable (field1, field2, $phpfieldname) VALUES ('$value1', '$value2','$value3')"); 
          
          ?>

          I hope that explains it a bit better. I am reasonably new to PHP and I cant post the exact script for privacy reasons, but that is the same model as what I am working on. It's a fairly simple script.

          Thanks for the reply,

          NDF

            Well there's not much we can do to help you if we can't see the original code. Ignoring the fact that your new sample code has a parse error (or two or three or... well, several), it doesn't even contain a loop structure as your real code apparently does.

            If you aren't using prepared statements, then MySQL doesn't care what previous query you gave it - whatever you send it in the query string is what it does. Thus, if you're getting the same data repeated, then you're apparently sending MySQL the same data in the query string. Make sure you didn't misplace a variable declaration or function call outside of the loop structure.

              Thanks. I will have a look at it again tomorrow (I've been at it all day today).

              And sorry about not posting all the code, I was just trying to throw something together there. If I can't sort it, I will try to get back with a fuller version of what it is I am trying to do.

              And I think those parse errors are typos BTW.

              It's really frustrating because it's ECHOing fine, but not sending it all to the database...

              Thanks again. It's very much appreciated.

                NDF wrote:

                It's really frustrating because it's ECHOing fine, but not sending it all to the database...

                How/what are you echo'ing to ensure that you're using the correct values? Try storing the SQL query string in a variable and echo'ing that out each time you call mysql_query() - that way you can have a visual log of the queries that are being generated and sent to MySQL.

                Also, if the "privacy concerns" aren't in any way tied to legal obligations (e.g. company policy, CDA/NDA, etc.), you can feel free to PM me a snippet of the actual code.

                  OK, I REALLY hope this makes more sense as I dont want to waste your time and you are being very generous in trying to help me fathom this.

                  Basically, this is a more complete version of what I am working with. I have two scripts. The first one does a switch on the booktype, and call the hardback script if it finds case as "Hardback".

                  <?php
                  
                  require("connect.php");	
                  
                  $newbooks =  simplexml_load_file('books.xml');
                  
                  foreach ($newbooks->catalogue->xpath('//books') as $bookitem) {
                  
                  $booktype = (string) $bookitem->type;
                  
                  switch ($booktype) {
                  					case "Hardback":
                  					include 'Hardback.php';
                  					break;
                  					}
                  					}
                  ?>
                  
                  
                  <?php
                  
                  $ParsedArray = array($booktitle = $bookitem->title,
                  				$numbofpages = $bookitem->pagecount,
                  				$Author = $bookitem->author,
                  				$AuthorCode = $bookitem->authorrefnumber);
                  
                  $writemaster = mysql_query("INSERT INTO maintable (booktitle, numberofpages, $Author) VALUES ('$booktitle', '$numbofpages','$AuthorCode')");
                  
                  ?>

                  Lets says one of the authors is called "David L Smith", and that author is the first one encountered in the loop. The problem I am having is that it appears to be accepting "David L Smith" in the query when it sets the field to populate to $author but getting stuck there. i.e. it is not populating any other authors even though there are literally dozens more. When it gets to the next "David L Smith" book, it populates that and so on... but no others.

                  So it seems that the SQL query is holding onto the first $author value for the entire iteration with the foreach statement. However, if I echo all the PHP values right after the parsedarray coding (and before the MySQL query), it returns all the values as required - every book for all authors and all the information required. However, it does seem that the MySQL query looks at it and says 'does this array have "David L Smith" as the author? If it doesn't, it doesnt get sent to the table". The MySQL query doesnt seem to be refreshing the $author value for the field reference.

                  I really don't have a clue what to do. I have encountered problems like this in the past where PHP itself didn't like a nested array in the foreach cycle and retained the original nested array for the entire sequence but the error checking I have done here suggests it is a problem with the MySQL query itself as PHP is ECHOing the correct values.

                  Again, thanks so much for any help you could give me. This is the last part of the task but I just cant seem to get it functional.

                    Did I make the problem clearer?

                      Is there really a separate column in your table for every author? If you have 20 books you could have 20 different column for all records and each column would be wasted except for the one record that has that authors name.

                      Based on your insert query your table looks to have columns something like such

                      booktitle | numberofpages | David L Smith | Jane Doe | Another Author | Some Other Author | ect

                      Your table columns should probably be more like

                      booktitle | numberofpages | author | authorcode

                      and your insert query more like

                      $writemaster = mysql_query("INSERT INTO maintable (booktitle, numberofpages, author, authorcode) VALUES ('$booktitle', '$numbofpages','$Author','$AuthorCode')"); 
                      

                      I suspect you don't have a column in your table for each author so you will need to do it similar to above.

                        Thanks for the help.

                        Sadly, that is actually how it is designed!

                          Hi Folks,

                          Just been able to solve this by myself.

                          It turns out that MySQL WAS indeed updating the reference as per the php variable. However, there was decimal numbers and some other characters in the field names, and it seems that MySQL doesn't like this, but didnt warn me. Anyway, I have changed the field names to alphanumeric and it seems to be working fine now.

                          Thanks to all that helped and I am sorry if I wasted your time (I realise now that being totally specific with examples is ssooo important).

                          Thanks again folks!

                          NDF.

                            Write a Reply...