xml data to mariadb

[system]

good day guys hopefully you can help me out.

I am trying to write a little application that grabs price data from a website in xml format and store it in a database for later use. the thing is that i keep getting syntax errors.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '35)' at line 1

So hopefully you can help me out and point me in the right direction.

best regards

Joery

here is the code :

[php]<?php
$typeids=array(34,35);
$url=“http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=”.join(’&typeid=’,$typeids);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HEADER, 0);
$data = curl_exec($ch);
if($data === false)
{
echo 'Curl error: ’ . curl_error($ch);
}
else
{
curl_close($ch);
}

$xml =new SimpleXMLElement($data);

foreach($typeids as $typeid){
$item=$xml->xpath(’/evec_api/marketstat/type[@id=’.$typeid.’]’);
$price= (float) $item[0]->sell->percentile;
$price=round($price,2);
echo $typeid." “.$price.”\n";
}

$types = implode(" ",$typeids);

// perform sql query

$con=mysql_connect(“localhost”,“root”,"");
mysql_select_db(“bui”, $con) or die (mysql_error());

$sql = “INSERT INTO item_prices (item-id, price)”
. “VALUES (’.$types.’, ‘$price’)”
. “ON DUPLICATE KEY UPDATE item-id=VALUES ($typeid)”;

$result = mysql_query($sql);
if (!$result) {
die (mysql_error());
} else {
echo ’ SUCCES’;
}
?>
[/php]

[JimL]

The punctuations around $types shouldn’t be there.

You can write that query like this, to clean up the clutter a little:
[php]$sql = “INSERT INTO item_prices (item-id, price)
VALUES (’$types’, ‘$price’)
ON DUPLICATE KEY UPDATE item-id=VALUES ($typeid)”;[/php]

[hr]

mysql_* is deprecated and will no longer work when upgrading to PHP7. Please use PDO or Mysqli instead.

You should never insert parameters directly into the query like that. Please read up on prepared/parameterized queries. Which would make your query look something like this:
[php]$sql = “INSERT INTO item_prices (item-id, price)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE item-id= VALUES (?)”;[/php]

[azakir]

thank you JimL you got me going into the right direction. And as you advised switched to mysqli.

Also decided that since i might need a bit more help might aswell create a account here.

Now with the prepared statements and mysqli i get the following error:

Fatal error: Call to a member function bind_param() on boolean on line 44

These prepared statements are pretty new for me so if you could help me out that be really great

Here is the code:

[php]<?php

$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “bui”;

$typeids=array(34,35);
$url=“http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=”.join(’&typeid=’, $typeids);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HEADER, 0);
$data = curl_exec($ch);
if($data === false)
{
echo 'Curl error: ’ . curl_error($ch);
}
else
{
curl_close($ch);
}

$xml =new SimpleXMLElement($data);

foreach($typeids as $typeid){
$item=$xml->xpath(’/evec_api/marketstat/type[@id=’.$typeid.’]’);
$price= (float) $item[0]->sell->percentile;
$price=round($price,2);
echo $typeid." “.$price.”\n";
}

$types = implode(" ",$typeids);

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare(“INSERT INTO item_prices (itemid, price) VALUES (?, ?) ON DUPLICATE KEY UPDATE itemid= VALUES (?)”);

$stmt->bind_param(‘ds’, $types, $price);
$stmt->execute();

echo “New records created successfully”;

$stmt->close();
$conn->close();

?>
[/php]

[JimL]

You need to check for errors on the query preparation and execution as well (now you’re just checking for connection errors).

I think the easiest and best solution is to wrap the database code in a try/catch block. This means you can remove the if ($conn->connect_error) stuff

[php]try {
// code that may fail here
} catch (\Exception $e) {
// You will end up here if any exception is thrown in the try block
// $e contains error stuff. like $e->getMessage() and $e->getCode()
}[/php]

[hr]

Seems you need to tell mysqli to throw exceptions (PDO does by default)

[php]mysqli_report(MYSQLI_REPORT_ALL); // Traps all mysqli error [/php]http://stackoverflow.com/a/28965706

[azakir]

good day jim

I have made adjustment to the code as described by you.

it now throws the following message at me:

exception 'mysqli_sql_exception' with message 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1' in G:\Xampp\htdocs\db.php:45 Stack trace: #0 G:\Xampp\htdocs\db.php(45): mysqli->prepare('INSERT INTO `it...') #1 {main}

Also thought i’d use pastebin instead of posting the full code here:

http://pastebin.com/nkxfa0Gy

would you say it would be better to move to PDO or stick with mysqli?

[JimL]

I like PDO way more than Mysqli, this problem is with your SQL query / statement though.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

[azakir]

okay i see. so i removed the ON DUPLICATE KEY UPDATE itemid= VALUES (?)") and it ran “almost perfectly”

I have a array of item id’s that need their prices collected and stored in the database but it only stores the first of the 2 not the entire group.

$typeids=array(34,35);

only 34 gets its id and price stored in the database. Is there a way i can change this?

[JimL]

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

You either need to generate that kind of query syntax, or run multiple queries (loop over the rows you want to insert). Since you in mysqli/pdo can prepare queries the db server will not have to parse the query for every execution, so I’d just run multiple queries in this case.

[php]$stmt = $conn->prepare(“INSERT INTO item_prices (itemid, price) VALUES (?,?)”);

foreach ($typeids as $id) {
$stmt->bind_param(‘sd’, $id, $price);
$stmt->execute();
}[/php]

[azakir]

awesome jim everything is working as it should now.

You can’t believe how much i appreciate your help