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!

Just a word of caution since I see you are messing with sports book data. If you're having issues with this type of error you must be fairly new to MySQL and should probably not be handling this data in a production environment without someone overseeing the process. — Jasper
– Jasper, Commented Jan 14, 2014 at 18:21
You aren't quoting the data in your query, byt you should be using PDO or MySQLi, which allow you to prepare statements. — Jon
– Jon, Commented Jan 14, 2014 at 18:22

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

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

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Jan 14, 2014 at 18:27

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jon Over a year ago

You should give an example to make this a useful answer ^^

bundyal Over a year ago

Could you please help me a bit more with prepared statement? How to do it in my case? If not, thanks for resources!

Machavity Over a year ago

Added an example. It's abbreviated for the sake of demonstration

Hrishi · Accepted Answer · 2019-02-19 08:37:27Z

0

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

edited Feb 19, 2019 at 8:37

answered Jan 14, 2014 at 20:18

Hrishi

4544 silver badges11 bronze badges

1 Comment

Hrishi Over a year ago

I did not put any code, It is the same query as specified in question. It is just for reference and for supporting above 2 points.

Collectives™ on Stack Overflow

PHP - XML to MySQL

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related