'simplexml' php to mysql database loop return only first element

Question

Be grateful for some help on this. I have a very long xml form with over 80 items to insert into my db.

I've researched this and for some reason my foreach loop doesn't work.

I've reduced the insert here to give an idea of what I am trying to do.

I can insert the first 'property / item into the database so I know I have no issues with the inserts etc.

For some reason the loop won't show the other 79 items in the db.

$affectedRow = 0;

$xml =  simplexml_load_file('properties2.xml') or die("can not find it");

foreach($xml->children()  as $row) {    
    $reference = $row->branch->properties->property['reference'];
    $instructedDate = $row->branch->properties->property->instructedDate;
    $price_text = $row->branch->properties->property->price_text;

    $sql = "INSERT INTO test( reference, instructedDate, price_text) VALUES ('". $reference ."','". $instructedDate ."','". $price_text ."')";

    $result = mysqli_query($conn, $sql);

    if (! empty($result )) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    }
}
?>

eg xml file

-<agency branchName="billies Estate Agents " name="billie ea" xsi:noNamespaceSchemaLocation="http://www.feedcompany.co.uk/xmlexport/feedcompanyXMLSchema.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    -<branches>

        -<branch name="billies Estate Agents ">

            -<properties>

                -<property reference="00000477">

                    <instructedDate>26/02/2018 15:11:56</instructedDate>

                    <price_text>Offers in Excess of £200,000</price_text>

                 </property

               -<property reference="00000478">

                    <instructedDate>26/02/2018 15:11:56</instructedDate>

                    <price_text>Offers in Excess of £200,000</price_text>

                </property>

If you just try to display the values in your loop do you see all of them? Could you add a short example of your XML file to your question so others may test (don't need or want all 80 items)? — Dave, Aug 06 '18 at 20:07
Always when developing and testing code, enable error display in PHP. If the XML references are not found (because `children()` does not return exactly what you expect) you may have a fatal error in the loop. At the top of your script `error_reporting(E_ALL); ini_set('display_errors', 1);` — Michael Berkowski, Aug 06 '18 at 20:08
Note that this is vulnerable to a secondary SQL injection. With mysqli, you should be using `prepare()/bind_param(),execute()`. See [How can I prevent SQL injection in php](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php#60496) for examples. — Michael Berkowski, Aug 06 '18 at 20:10
dave,,how do i display the values? you mean echo them? michael, nothing happens when i paste that .. i use error_message which shows me when there is an issue i.e no variable match or typo etc — DonQuery, Aug 06 '18 at 21:12
yes i can echo each element i want on the page... only for the first property though... no loop — DonQuery, Aug 06 '18 at 22:01
For decent MySQLi error reporting, add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script — Phil, Aug 07 '18 at 05:15
@MichaelBerkowski not to mention that a prepared statement executing in a loop will be **much more** efficient than this — Phil, Aug 07 '18 at 05:16

score 1 · Accepted Answer · answered Aug 07 '18 at 06:27

1

You only gets the first element into the DB because $xml->children() is not what you expected.

Notice that your XML starts with <agency> and after it you have <branches> tag. I guess your full XML is something like this:

<agency>
    <branches>
        <branch>
            <properties>
                <property>
                    ...
                </property>
                <property>
                    ...
                </property>
            </properties>
        </branch> 
    </branches>
</agency>

You want to get all the properties -> so you need to use $xml->branches->branch->properties->children().

Something like:

foreach($xml->branches->branch->properties->children() as $property) {
    $sql = "INSERT INTO test( reference, instructedDate, price_text) VALUES ('". $property['reference']."','". $property->instructedDate ."','". $property->price_text."')";
 ...
 }

When you execute $xml->children() as in 3th line you gets the branches tag as the only element in the array - that why you have only one element insert to your DB.

answered Aug 07 '18 at 06:27

dWinder

11,359
3
19
33

Hi that worked for the 87 elements but they were the same entry.. i.e not 87 individul properties..should i have an i++ counter somewhere to loop through each one..? – DonQuery Aug 07 '18 at 10:20
The fooreach code is pseudo. You need to get the array of the properties tag from your xml and run the loop on him. If you do that then each element in the array is single property - now you can access the property data directly. No need for i counter -just get the array with xpath – dWinder Aug 07 '18 at 10:21
i have now tested properly and didnt just try and force your code into my already poor effort; . It worked . amazing i couldnt find one tuts onlline that came close and yours is 10x more simple logic.... many thanks. – DonQuery Aug 07 '18 at 10:24
hi david.. sorry to be padantic but your code worked fine... what are you talking about in the last comment? pseudo? are you talking as if thats what id do if i hadnt already done it? so because i have used your code and it worked, i should read in a past tense not future? – DonQuery Aug 07 '18 at 10:27
What I meant by pseudo was that the array in my foreach `$xml->branches->branch->properties->children()` was not tested and by this line I mean to extract array of properties. If that works - fine, if not then you can extract this array by using xpath – dWinder Aug 07 '18 at 10:39
great thanks... just photos table and a room table to bolt on now and also a lesson in a prepared statement... do you guys always work these problems out by using your theory knowledge or have you been through the pain of a particular problem before and remember how to do it? crazy – DonQuery Aug 07 '18 at 10:48
Both theory knowledge and experience. Learning the hard-way is good - as longer as you stuck on issue the longer you will remember to avoid it in the future. Good luck! – dWinder Aug 07 '18 at 11:03
1 week of pain.. shouldnt forget that... i blame the xml feed ! do i need pdo etc as this code wont ever get near a user? its an internal feed until i echo the tables to a page.. – DonQuery Aug 07 '18 at 11:20
I believe PDO is better practice. It is up to you to decide if it worth it (I personally believe it is) – dWinder Aug 07 '18 at 11:27

'simplexml' php to mysql database loop return only first element

1 Answers1