'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
– Dave, Commented Aug 6, 2018 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
– Michael Berkowski, Commented Aug 6, 2018 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 for examples. — Michael Berkowski
– Michael Berkowski, Commented Aug 6, 2018 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
– DonQuery, Commented Aug 6, 2018 at 21:12
yes i can echo each element i want on the page... only for the first property though... no loop — DonQuery
– DonQuery, Commented Aug 6, 2018 at 22:01

dWinder · Accepted Answer · 2018-08-07 06:27:46Z

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 7, 2018 at 6:27

dWinder

11.7k3 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

DonQuery Over a year ago

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..?

dWinder Over a year ago

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

DonQuery Over a year ago

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 Over a year ago

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?

dWinder Over a year ago

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

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related