PHP - XML to MySQL

Question

I am working on a PHP code that would read data from XML store it in MySQL. So far I came to point where I read data from XML file and echo it on website. Here is the code:

<?php

//mysql connection
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("bet_sql") or die(mysql_error());

$xml = simplexml_load_file('http://cachepricefeeds.williamhill.com/openbet_cdn?action=template&template=getHierarchyByMarketType&classId=46&marketSort=MR&filterBIR=N');

foreach ($xml->response->williamhill->class->type as $type) {
  $type_attrib = $type->attributes();
  $type_attrib['id'];
  $type_attrib['name'];
  foreach ($type->market as $event) {
    $event_attrib = $event->attributes();
    $event_attrib['id'];
    $event_attrib['name'];
    $event_attrib['date'];
    $event_attrib['url'];
    foreach ($event->participant as $participant) {
        $participant_attrib = $participant->attributes();
          $participant_attrib['name'];
          $participant_attrib['oddsDecimal'];
    }
  }

  mysql_query("INSERT INTO games (type_id, type_name, event_id, event_name, event_url, participant_name, participant_odds) 
          VALUES ($type_attrib[id], $type_attrib[name], $event_attrib[id], $event_attrib[name], $event_attrib[url], $participant_attrib[name], $participant_attrib[oddsDecimal]) ")
          or die(mysql_error());
}
?>

What am I doing wrong with mysql_query? I am geting this message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jupiler League, 134465843, Zulte-Waregem v Genk - 75 Minutes Betting, ' at line 2

Thanks for help!

Answer 1

Problems with insert query are

• Array keys are given without single quotes which will cause warnings

• Values are not properly escaped. it may be the main reason of syntax error.

  mysql_query("INSERT INTO games (type_id, type_name, event_id, event_name, 
  event_url, participant_name, participant_odds) 
  VALUES ($type_attrib['id'], $type_attrib['name'], $event_attrib['id'], 
  $event_attrib['name'], $event_attrib['url'], $participant_attrib['name'],
  $participant_attrib['oddsDecimal'])"
  ) or die(mysql_error());
  

Answer 2

This is a prime example of why you should use a prepared statement to do this. Not only is it faster than running the same INSERT statement over and over, it would avoid the escaping problems and gets you off the obsolete mysql_query function.

I had to guess what datatypes were for bind_param

$msyqli = new mysqli('localhost'...); //Your connection credentials here
$sql = 'INSERT INTO games (type_id, type_name, event_id, event_name, event_url, participant_name, participant_odds) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
$prep = $mysqli->prepare($sql);
foreach ($xml->response->williamhill->class->type as $type) {
    //Truncated the other code out for example    
    $prep->bind_param('isissss', $type_attrib[id], $type_attrib[name], $event_attrib[id], 
    $event_attrib[name], $event_attrib[url], $participant_attrib[name], $participant_attrib[oddsDecimal]);
    $prep->execute();
}