Insert xml into sql with sql with foreach?

Question

First thing first, I have an xml feed from unibet.com that looks like this:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<betOfferResponse>
    <events>
        <name>Red Star Belgrade - CSKA Moscow</name>
        <homeName>Red Star Belgrade</homeName>
        <awayName>CSKA Moscow</awayName>
        <start>2018-02-13T17:00Z</start>
        <group>Europa League</group>
        <type>ET_MATCH</type>
        <sport>FOOTBALL</sport>
        <state>NOT_STARTED</state>
        <liveBetOffers>true</liveBetOffers>
        <openForLiveBetting>false</openForLiveBetting>
        <id>1004394826</id>
        <groupId>2000051195</groupId>
        <path>
            <id>1000093190</id>
            <name>Football</name>
            <englishName>Football</englishName>
        </path>
        <path>
            <id>2000051195</id>
            <name>Europa League</name>
            <englishName>Europa League</englishName>
        </path>
    </events>
    <events>
        <name>FC Astana - Sporting Lisbon</name>
        <homeName>FC Astana</homeName>
        <awayName>Sporting Lisbon</awayName>
        <start>2018-02-15T16:00Z</start>
        <group>Europa League</group>
        <type>ET_MATCH</type>
        <sport>FOOTBALL</sport>
        <state>NOT_STARTED</state>
        <liveBetOffers>true</liveBetOffers>
        <openForLiveBetting>false</openForLiveBetting>
        <id>1004394832</id>
        <groupId>2000051195</groupId>
        <path>
            <id>1000093190</id>
            <name>Football</name>
            <englishName>Football</englishName>
        </path>
        <path>
            <id>2000051195</id>
            <name>Europa League</name>
            <englishName>Europa League</englishName>
        </path>
    </events>

but when I use my script for reading and putting the xml data into my db, it does not work, because of these:

 <betOfferResponse>

But when I create my own xml with only the data underneath, it works. But it cant loop the file and get the second entry and so on..

Here is my php code that:

    <?php


$xml=simplexml_load_file("test.xml") or die("Error: Cannot create object");
$connection = mysqli_connect("localhost", "root", "password", "tipstr") or die ("ERROR: Cannot connect");

/* Assumes that the number of IDs = number of customers */
$size = sizeOf($xml->id);
$i = 0; //index

/* Add each customer to the database, See how we reference it as    $xml->ENTITY[INDEX] */
while($i != $size) 
{
    print_r($xml);
    echo $xml->id[$i]; //Test

    $sql = "INSERT INTO xf_nflj_sportsbook_event (event_id, category_id, user_id, username, title, description, event_status, date_create, date_open, date_close, date_settle, date_edit, event_timezone, wagers_placed, amount_staked, amount_paidout, likes, like_users, view_count, outcome_count, comment_count, thread_id, prefix_id, last_comment_date, limit_wagers_single_outcome) VALUES ('$xml->id',2,1,'tipstr', '$xml->name', '$xml->sport', 'open', 1517755596,1517755596,1517761200,1517761200,0,'Europe/London', 0, 0,0,0,0,0,0,0,0,0,0,0)";
    mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");

    $i++; //increment index
}


mysqli_close($connection);

What am I doing wrong? How can I loop/foreach the insert query, so that it can get the whole file and not only the first one, and how can I make it read the xml from unibet?

edit: Sorry about not adding the xml I use when I succeed. But here it is:

 <events>
        <name>Marseille - S.C. Braga</name>
        <homeName>Marseille</homeName>
        <awayName>S.C. Braga</awayName>
        <start>2018-02-15T18:00Z</start>
        <group>Europa League</group>
        <type>ET_MATCH</type>
        <sport>FOOTBALL</sport>
        <state>NOT_STARTED</state>
        <liveBetOffers>true</liveBetOffers>
        <openForLiveBetting>false</openForLiveBetting>
        <id>1004394835</id>
        <groupId>2000051195</groupId>
        <path>
            <id>1000093190</id>
            <name>Football</name>
            <englishName>Football</englishName>
        </path>
        <path>
            <id>2000051195</id>
            <name>Europa League</name>
            <englishName>Europa League</englishName>
        </path>
    </events>
     <betoffers>
        <id>2086105579</id>
        <eventId>1004044027</eventId>
        <closed>2018-05-16T18:45Z</closed>
        <live>false</live>
        <startingPrice>false</startingPrice>
        <criterion>
            <id>1001221607</id>
            <label>Winner</label>
        </criterion>
        <betOfferType>
            <id>4</id>
            <name>Winner</name>
        </betOfferType>
        <outcomes>
            <id>2307553711</id>
            <odds>15000</odds>
            <label>AC Milan</label>
            <type>OT_UNTYPED</type>
            <changedDate>2018-02-05T22:19:04Z</changedDate>
            <oddsFractional>14/1</oddsFractional>
            <oddsAmerican>1400</oddsAmerican>
        </outcomes>
        <outcomes>
            <id>2307553715</id>
            <odds>7000</odds>
            <label>Arsenal</label>
            <type>OT_UNTYPED</type>
            <changedDate>2018-02-05T22:19:04Z</changedDate>
            <oddsFractional>6/1</oddsFractional>
            <oddsAmerican>600</oddsAmerican>
        </outcomes>

I am not getting any special error, when I add more items in the xml, only the php error I have written in. So Tried checking the network tab in the consol, but nothing there either.

"it does not work" is not a problem description. Why doesn't it work? Do you get an error? Unexpected results? — HoneyBadger, Feb 08 '18 at 12:19
_"But when I create my own xml with only the data underneath"_ - I'm curious about what the XML you build yourself looks like if it actually works with that code? For starters: `id` are several levels down, so I don't see how `$xml->id` would work at all, unless you've change the XML structure completely. You're also fetching `$xml->name` and `$xml->sport`, which are on different levels. — M. Eriksson, Feb 08 '18 at 12:26
Here's an example of how you iterate through a XML object properly: https://stackoverflow.com/questions/871422/looping-through-a-simplexml-object-or-turning-the-whole-thing-into-an-array — M. Eriksson, Feb 08 '18 at 12:28
MySQL supports mass inserts. Here is no need to execute an statement for each insert. Use the loop to build an large insert statement or a file, then use that to insert the data. (It is much faster) — ThW, Feb 08 '18 at 14:45

Nigel Ren · Answer 1 · 2018-02-08T17:20:24.277

You should change your loop to make it easier...

<?php
$xml=simplexml_load_file("xml url") or die("Error: Cannot create object");
$connection = mysqli_connect("localhost", "root", "password", "db_name") or die ("ERROR: Cannot connect");

foreach ( $xml->events as $event )  {
    $sql = "INSERT INTO xf_nflj_sportsbook_event
                (event_id, category_id, user_id, username, title,
                description, event_status, date_create, date_open,
                date_close, date_settle, date_edit, event_timezone,
                wagers_placed, amount_staked, amount_paidout, likes,
                like_users, view_count, outcome_count, comment_count, thread_id,
                prefix_id, last_comment_date, limit_wagers_single_outcome)
        VALUES ('{$event->id}',2,1,'tipstr', '{$event->name}', '{$event->sport}',
                'open',
                1517755596,1517755596,1517761200,1517761200,0,'Europe/London',
                0, 0,0,0,0,0,0,0,0,0,0,0)";
     mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");
}

Note that I've changed how the data is embedded into the string, as each element is now called $event I've changed them to {$event->id} etc.

You also should look into prepared statements as you can prepare this statement once and then execute multiple times with each row of data.

This doesn't produce any output, just inserts data into the database from the file.

You were almost correct, just missed a closing } and one more line, så I will add the correct solution as an answer :) But if it wasn't for you, I would not have figured it out :) — Tipstr, Feb 08 '18 at 17:11

score 0 · Answer 2 · answered Feb 08 '18 at 17:12

Thanks to Nigel Ren who gave me the correct formula, here is the correct answer with all the missing data..

Now I can finally fetch data from unibet`s xml :)

 <?php


$xml=simplexml_load_file("xml url") or die("Error: Cannot create object");
$connection = mysqli_connect("localhost", "root", "password", "db_name") or die ("ERROR: Cannot connect");

/* Assumes that the number of IDs = number of customers */
$size = sizeOf($xml->id);
$i = 2; //index

/* Add each customer to the database, See how we reference it as    $xml->ENTITY[INDEX] */
while($i != $size) 
{
    print_r($xml);
    echo $xml->id[$i]; //Test
    foreach($xml->events as $event) //Extract the Array Values by using Foreach Loop
    {
    $sql = "INSERT INTO xf_nflj_sportsbook_event (event_id, category_id, user_id, username, title, description, event_status, date_create, date_open, date_close, date_settle, date_edit, event_timezone, wagers_placed, amount_staked, amount_paidout, likes, like_users, view_count, outcome_count, comment_count, thread_id, prefix_id, last_comment_date, limit_wagers_single_outcome) VALUES ('$event->id',2,1,'tipstr', '$event->name', '$event->sport', 'pending', 1517755596,1517755596,1517761200,1517761200,0,'Europe/London', 0, 0,0,0,0,0,0,0,0,0,0,0)";
    mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");

    $i++; //increment index
    }
}


mysqli_close($connection);

Your `while` loop isn't needed, `$i` isn't needed either. – Nigel Ren Feb 08 '18 at 17:16 — Nigel Ren, Feb 08 '18 at 17:16

Insert xml into sql with sql with foreach?

2 Answers2