Update only 1 database field from XML

General PHP Help
#1

Please can anyone shed any light on why this code does not work correctly

It reports ‘Record updated successfully’ but does not insert any data into the quantity column.

I am presuming that it has something to do with the WHERE item = “.$item.” as when I remove it it works but only inserts the quantity from the very last xml record into all fields.

<CREATED value="Mon Jan 27 21:48:22 UTC 2020"> <PRODUCT ITEM="13726"> <QUANTITY>4467</QUANTITY> </PRODUCT> </CREATED>

[code]foreach ($xml->CREATED->PRODUCT as $row) {
print_r($row);
$item = mysqli_escape_string($conn, $row->attributes()[‘ITEM’]);
$quantity = mysqli_escape_string($conn, $row->QUANTITY);

  $sql2 = "UPDATE tbl_temp_products SET quantity = ".$quantity." WHERE item = ".$item."";

 }

if (mysqli_query($conn, $sql2)) {
echo “Record updated successfully”;
} else {
echo "Error updating record: " . mysqli_error($conn);
}

[/code]

#2

It’s returning true, so either it worked, or there is nothing to update…

Why aren’t you using prepared statements?

#3

Please forgive my ignorance but I am still learning and not sure what you mean by prepared statements.

Yes it works but it does not :frowning: every record in the database is being updated with the final value in the xml feed and not each individual products quantity from the respective line in the feed (if that makes sense).

The full code I have is

<?php
// Turn off all error reporting
error_reporting(0);

$conn = mysqli_connect("localhost", "root", "", "tbl_temp_products");

// Product Import Begin
$context  = stream_context_create(array('http' => array('header' => 'Accept: application/xml')));
$url = 'products.xml';

$xml = file_get_contents($url, false, $context);
if($xml === false) {
    echo '<div class="pt-2 container"><div class="row justify-content-around"><div class="col-11 alert alert-danger">' . 'could not read xml file!' . ' <i class="fas fa-times-circle"></i></div></div></div>';
    exit;
}

$xml = simplexml_load_string($xml);
if($xml === false) {
    echo '<div class="pt-2 container"><div class="row justify-content-around"><div class="col-11 alert alert-danger">' . 'could not convert xml file to a php object!' . ' <i class="fas fa-times-circle"></i></div></div></div>';
    exit;
}

foreach ($xml->CREATED->PRODUCT as $row) {
    print_r($row);
    $item = mysqli_escape_string($conn, $row->attributes()['ITEM']);
    $quantity = mysqli_escape_string($conn, $row->QUANTITY);

    $sql = "SELECT item, quantity from tbl_temp_products WHERE item = '$item'";
    $result = mysqli_query($conn, $sql);
     if(mysqli_num_rows($result) > 0) {
      echo $item . ' already exists<br/>';
     }else {
      $sql2 = "UPDATE tbl_temp_products SET quantity = ".$quantity." WHERE item = ".$item."";
      $result2 = mysqli_query($conn, $sql2);
     }
}

if (mysqli_query($conn, $sql2)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

$conn->close();

?>
#4

Do you have a sample of the xml file?

<?php

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO("mysql:host=localhost;dbname=tbl_temp_products", "root", "12user", $options);


// Product Import Begin
$context  = stream_context_create(array('http' => array('header' => 'Accept: application/xml')));
$url = 'products.xml';

$xml = file_get_contents($url, false, $context);
if($xml === false) {
    echo '<div class="pt-2 container"><div class="row justify-content-around"><div class="col-11 alert alert-danger">' . 'could not read xml file!' . ' <i class="fas fa-times-circle"></i></div></div></div>';
    exit;
}

$xml = simplexml_load_string($xml);
if($xml === false) {
    echo '<div class="pt-2 container"><div class="row justify-content-around"><div class="col-11 alert alert-danger">' . 'could not convert xml file to a php object!' . ' <i class="fas fa-times-circle"></i></div></div></div>';
    exit;
}

$inserted_records = 0;
$updated_records = 0;
$insert_sql = "INSERT INTO tbl_temp_products (item, quantity) VALUES (?, ?)";
$insert_stmt = $pdo->prepare($insert_sql);

$update_sql = "UPDATE tbl_temp_products SET quantity = ? WHERE item = ?";
$update_stmt = $pdo->prepare($update_sql);
foreach ($xml->PRODUCT as $row) {
    print_r($row);
	$update_stmt->execute([$row->QUANTITY, $row['ITEM']]);
	if($update_stmt->rowCount() == 0) {
		$insert_stmt->execute([$row['ITEM'], $row->QUANTITY]);
		$inserted_records +=1;
	} else {
		$updated_records +=1;
	}    
}
echo "<p>Inserted $inserted_records records</p>";
echo "<p>Updated $updated_records records</p>";

This may work. I would need the xml file for more testing however.

#5

I have just tested your code and it returns

Inserted 0 records

Updated 0 records

#6

Change the foreach loop to the following.

foreach ($xml->CREATED->PRODUCT as $row) {

Testing the file you sent, after I corrected the malformation, I get the following to display from my debugging script.

Success 0.01s 24632KB
Item: 13726 Qty: 4394
Item: 13725 Qty: 2112
Item: 13724 Qty: 1286
Item: 9700 Qty: 231
Item: 9701 Qty: 1848

#7

Yes that updates all the values correctly but throws this error upon completion

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1364 Field ‘model’ doesn’t have a default value in C:\wamp64\www\puckator\sync.php on line 38
PDOException: SQLSTATE[HY000]: General error: 1364 Field ‘model’ doesn’t have a default value in C:\wamp64\www\puckator\sync.php on line 38

this is line 38

$insert_stmt->execute([$row['ITEM'], $row->QUANTITY]);
#8

I didn’t know what your database structure was, so I didn’t add them for the insert statement.

#9

I also don’t see anything in the XML file that alludes to a model.

#10

thats because I can filter the xml feed and was only calling the stock quantity and the item number.

There are quite a few other fields in the xml but did not think it would cause an issue database side if only calling the item and quantity.

#11

Then you may want a report of what was not updated, or figure out how you want to deal with the differences.

I would be doing this as a nightly process that runs automatically and would fill in everything possible, but that doesn’t appear to be how you are structuring it

#12

the other data will not change unless I run the full product import again. The only thing that will change daily is the quantity.

#13

The failure is because it was attempting to add a product not in the database. So if you keep a record of those, maybe add it to another list. Unless you don’t care about those and just want to update what you have in your currently. Either works, just different approaches

#14

I have just changed this line

$insert_stmt->execute([$row['ITEM'], $row->QUANTITY);

to this

$insert_stmt->execute([$row['ITEM'], $row->QUANTITY, $row->MODEL]);

and the script finishes without any errors

Inserted 879 records

Updated 1354 records

#15

And now you have an ETL script

#16

sorry whats ETL script??? (I must be a real dumb dumb)

#17

Extract Transform Load. It is basically just importing data

#18

well I am learning something new every day :smile:

Thank you so much for taking time to help

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service