INSERT into XML contents into mysql DB

Question

Basically I have a php script which calls an external XML file

it then inserts certain data from the xml file into the mysql db inside a foreach loop

However when I check the database its empty

What am I doing wrong

The xml contains the following structure

<jobs>
    <job>
        <jobref>987654321</jobref>
        <title>TITLE HERE</title>
        <url>
        URL HERE
        </url>
        <salary>&pound;85 - &pound;105/day benifits</salary>
        <location>LOCATION</location>
        <description>desc</description>
        <category>CAT HERE</category>
    </job>
    <job>
        <jobref>123456789</jobref>
        <titleTITLE HERE</title>
        <url>
        URL HERE
        </url>
        <salary>&pound;85 - &pound;105/day benifits</salary>
        <location>LOCATION</location>
        <description>desc</description>
        <category>CAT</category>
    </job>
</jobs>

Below is the PHP code

<?php
    $dbu = 'user';
    $dbp = 'pass';
    $dbh = 'localhost';
    $dbn = 'dbname';

    $dbc = mysql_pconnect($dbh,$dbu,$dbp);

    // Get XML Feed
    $startPage = $_GET['page'];
    $perPage = 3000;
    $currentRecord = 0;
    $xml = simplexml_load_file("urlhere");
    $jobs = $xml->xpath("/jobs/job");

    foreach ($jobs as $job)
    {
        mysql_select_db($dbn,$dbc);
        $query = mysql_query("INSERT INTO `listings` (`jobref`,`title`,`url`,`salary`,`location`,`description`,`category`) VALUES ('$job->jobref','$job->title','$job->salary','$job->location','$job->description','$job->category')",$dbc);

        echo "$job->jobref Added <br>";
    }
?>

It displays :

120239811 Added 
201909016 Added 
202482907 Added

when i run the script so its parsing the xml file but its not actually inserting the data

Any help would be great because I'm on a very tight timeframe to finish this Thanks in advance

** EDIT BELOW ** NEW code below

<?php
        $startPage = 1;
        $perPage = 3000;
        $currentRecord = 0;
        $xml = simplexml_load_file("urlhere");
        $jobs = $xml->xpath("/jobs/job");
        mysql_select_db($dbn,$dbc);
        foreach ($jobs as $job)
        {
        $currentRecord += 1;
         if($currentRecord > ($startPage * $perPage) && $currentRecord < ($startPage * $perPage + $perPage)){ 
        $query = mysql_query("INSERT IGNORE INTO `listings` (jobref,title,url,salary,location,description,category) VALUES ('$job->jobref','$job->title','$job->url','$job->salary','$job->location','$job->description','$job->category')",$dbc);
        if($query){
        echo "$job->jobref Added <br>";
        } else {
        echo mysql_error();
        }
        }
        }
        ?>

Its now just adding the 1st entry in the XML file

Removed IGNORE from query and added ON DUPLICATE KEY UPDATE jobref =$job->jobref+1 still same problem

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Mar 14 '17 at 12:31
Try testing `$query` like `if (! $query) { echo mysql_error(); }` then you may see something useful — RiggsFolly, Mar 14 '17 at 12:32
Also MOVE the `mysql_select_db($dbn,$dbc);` OUTSIDE the foreach loop it needs to be done only once — RiggsFolly, Mar 14 '17 at 12:34
upper comments are good statements and you should do those suggestions, also put this two things on top of your script `error_reporting(E_ALL);` and `ini_set('display_errors', 1);` This will give you more details about what errors occur in your script — codtex, Mar 14 '17 at 12:40
Ive just amended the script and checked column count in db & script and both match up — Chris Yates, Mar 14 '17 at 12:43
no i've moved the mysql_select_db outside of the foreach loop — Chris Yates, Mar 14 '17 at 12:56
Nothing being displayed from mysql_error or error_reporting so pulling hair out at the minute — Chris Yates, Mar 14 '17 at 13:52
Also seeing "INSERT **IGNORE** INTO", could that be the problem? (Duplicate keys on unique indexes) — Balazs Vago, Mar 14 '17 at 14:16
removed it still same problem added update on duplicate keys to query (see above ) — Chris Yates, Mar 14 '17 at 14:19
Try echoing the INSERTS and see if they render correctly for MySQL (even try one line in console). — Parfait, Mar 14 '17 at 14:26

score -1 · Answer 1 · edited May 23 '17 at 11:46

did you sure the xml loaded succesfully? :) but i guess from your sample the xml node <salary> has illegal ampersand char "&" here the link about it.

but you can deal with it with a little hack like this.

i suggest you to use mysqli, so according to your xml sample

...

$conn = mysqli_connect($dbh, $dbu, $dbp);
if(!$conn) die('connect fail: '. mysqli_error($conn));

$db = mysqli_select_db($conn, $dbn);
if(!$db) die('db fail: '. mysqli_error($conn));

... 

$file = file_get_contents(url_here);

// replace '&' to '&amp;'
$file = str_replace('&', '&amp;', $file);
$xml = simplexml_load_string($file);

$jobs = $xml->xpath("/jobs/job");

foreach ($jobs as $job)
{
    ...

    // just get back those ampersand
    $job->salary = str_replace('&amp;', '&', $job->salary);

    $s = "INSERT IGNORE INTO `listings` (`jobref`,`title`,`url`,`salary`,`location`,`description`,`category`) VALUES('$job->jobref','$job->title','$job->url','$job->salary','$job->location','$job->description','$job->category')";

    $q = mysqli_query($conn, $s);

    if(!$q) {
        die(mysqli_error($conn));
    }else {
        echo "$job->jobref Added <br>";
    }
}

INSERT into XML contents into mysql DB

1 Answers1