PHP - XML 到 MySQL

Question

我正在编写一个 PHP 代码，它可以从 XML 读取数据并将其存储在 MySQL 中。到目前为止，我已经从 XML 文件中读取数据并在网站上回显它了。这是代码:

<?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());
}
?>

我的 mysql_query 做错了什么？我收到此消息:

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

感谢您的帮助!

Answer 1

这是一个很好的例子，说明为什么您应该使用 prepared statement去做这个。它不仅比一遍又一遍地运行相同的 INSERT 语句更快，而且可以避免转义问题并让您摆脱过时的 mysql_query 函数。

我必须猜测 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();
}