php Foreach Loop only extracting last record

Question

This is my php code to extract data from xml and insert into my mysql table. Why does this code extract only last xml record? This code extracts and inserts
100 CAPITAL TOWERS,MS ,Harare,JACKSON,MS,39201,100 CAPITAL TOWERs,100 CAPITAL TOWERS into table which is the last record of xml.
We need help to insert all records from top to bottom of xml .

<?php
$con = mysql_connect("localhost","root","pass");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("db_name", $con);
if(!$vals=simplexml_load_file('local1.xml'))
{
    trigger_error('Error reading XML file',E_USER_ERROR);
}
foreach ($vals as $record) {    
    $country = $record->country;
    $state = $record -> state ;
    $district = $record->district;
    $city = $record->city;   
    $post_office = $record->post_office;
    $postal_code = $record->postal_code;
    $wings = $record->wings;
    $house_no = $record->house_no;
}
$sql="INSERT INTO address(country, state,district,city,post_office,postal_code,wings,house_no)
VALUES
('$country','$state','$district','$city','$post_office','$postal_code','$wings','$house_no')";
if (!mysql_query($sql,$con))
{
    die('Error: ' . mysql_error());
}
echo "Records added";
mysql_close($con)
?>

local1.XML:

<?xml version="1.0" encoding="UTF-8" ?>
<records>
    <record>
        <country>100 OLD MILL CREEK RD</country>
        <state>NC  </state>
        <district>Harare</district>
        <city>FRANKLIN</city>
        <post_office>NC  </post_office>
        <postal_code>28734</postal_code>
        <wings>100 OLD MILL CREEK RD</wings>
        <house_no>100 OLD MILL CREEK RD</house_no>
    </record>
    <record>
        <country>100 CAPITAL TOWERS</country>
        <state>MS  </state>
        <district>Harare</district>
        <city>JACKSON</city>
        <post_office>MS  </post_office>
        <postal_code>39201</postal_code>
        <wings>100 CAPITAL TOWERS</wings>
        <house_no>100 CAPITAL TOWERS</house_no>
    </record>
</records>

see this example http://stackoverflow.com/questions/19561657/loop-through-an-xml-object-with-simplexml — Bhavika, Jan 19 '17 at 06:49

score -1 · Accepted Answer · answered Jan 19 '17 at 06:48

Try to add your sql query inside foreach loop like this:

foreach ($vals as $record) {    
 $country = $record->country;
 $state = $record -> state ;
 $district = $record->district;
 $city = $record->city;   
 $post_office = $record->post_office;
 $postal_code = $record->postal_code;
 $wings = $record->wings;
 $house_no = $record->house_no;

$sql="INSERT INTO address(country, state,district,city,post_office,postal_code,wings,house_no)
   VALUES
    ('$country','$state','$district','$city','$post_office','$postal_code','$wings','$house_no')";

  if (!mysql_query($sql,$con))
 {
  die('Error: ' . mysql_error());
  }
 }

With one important caveat, a good rule of thumb is "never execute a query inside a loop-". — Strawberry, Jan 19 '17 at 08:10

php Foreach Loop only extracting last record

1 Answers1