0

I have a php script that stores match data (and other data), to a database. I had previously had it running through the xml file with a foreach, and storing/updating each record 1 at a time.

Seeing as there are over 3,000 match records, when updating, it would take over 45 seconds to complete. So I am trying to do it by using one query, rather than thousands of individual queries.

$updateQuery = "";
$data = simplexml_load_file('$url');

foreach($data->match as $match) {
    $matchId = intval($match->id);
    $homeTeamName = $match->homeTeamName;
    $homeTeamScore = intval($match->homeTeamScore);
    $awayTeamScore = intval($match->awayTeamScore);
    $awayTeamName = $match->awayTeamName;

    // Check if record exists
    $query = "SELECT `matchId` FROM `matches` WHERE `matchId` = " . $matchId;
    $result = mysqli_query($mysqli,$query); 
    if(mysqli_num_rows($result) == 1 ){
        $updateQuery .= "UPDATE `matches` 
                    SET
                    `homeTeamName`='" . $homeTeamName . "',
                    `homeTeamScore`=" . $homeTeamScore . ",
                    `awayTeamScore`=" . $awayTeamScore . ",
                    `awayTeamName`='" . $awayTeamName . "'
                    WHERE `matchId`=" . $matchId . "; ";
    } else {
        $sql_statement="INSERT INTO `matches` 
                (`matchId`, `homeTeamName`, `homeTeamScore`, `awayTeamScore`, `awayTeamName`) 
                VALUES 
                (" . $matchId . ",
                '" . $homeTeamName . "', 
                " . $homeTeamScore . ", 
                " . $awayTeamScore . ", 
                '" . $awayTeamName . "')";
        mysqli_query($mysqli,$sql_statement);
    }
}
mysqli_query($mysqli,$updateQuery);

Please ignore the two types of queries (update/insert), once I get the update working, I will be changing the insert query to work similarly.

What I am finding is that when I view the result of $updateQuery, I can manually select it, place it into the phpmyadmin sql entry, and it works, it updates thge records, so the query being created isn't the issue (snippet below). But when I run the php script, the database doesn't get updated.

Snippet of the query generated: UPDATE matches SET homeTeamName='Manchester_United', homeTeamScore=2, awayTeamScore=1, awayTeamName='Leicester_City' WHERE matchId=111981; UPDATE matches SET homeTeamName='AFC_Bournemouth', homeTeamScore=2, awayTeamScore=0, awayTeamName='Cardiff_City' WHERE matchId=108933;

What am I missing?

Dean84
  • 39
  • 5
  • `mysqli_query` only executes 1 query. You should parameterize your query and just execute it in the loop 1 at a time. Look into `insert into... on duplicate update`. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html – user3783243 Oct 04 '18 at 16:59
  • 1
    You cannot submit more than one statement unless you're using mysqli_multi_query. – aynber Oct 04 '18 at 16:59
  • You can make matchId a unique field, if it isn't already, then use `INSERT INTO ... ON DUPLICATE KEY UPDATE`, then you can build all of the rows into one query. – aynber Oct 04 '18 at 17:00
  • Thanks guys, changed it to 'mysqli_multi_query', and now it works fine :) – Dean84 Oct 04 '18 at 17:03
  • Possible duplicate of [php/mysql with multiple queries](https://stackoverflow.com/questions/10610675/php-mysql-with-multiple-queries) – random_user_name Oct 04 '18 at 17:16
  • Well that fixed one problem, and gave me another :p I have multiple xml files being read. Now when it moves onto the next select query (checking for record), I get the 'mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given'. Works fine for the first check, but not the ones after it. – Dean84 Oct 04 '18 at 17:32
  • I'm not sure why you are using `$updateQuery .=` in this code **.=** don't use `.` if you not already have same var. – Deepak Saini Oct 04 '18 at 18:10
  • There are some security implications to using `mysqli_multi_query` and you are injecting values into this query, `'" . $awayTeamName . "')`. Multi queries allow a attacker to do things like this `$awayTeamName = "'); CREATE USER 'haxor'@'localhost' IDENTIFIED BY 'password'; --";` Where they can hi-jack your query and run a completely different one. Even if this comes from an XML file are you willing to take that risk? – ArtisticPhoenix Oct 04 '18 at 19:38
  • @Deepak, I am fairly noob at PHP and I used `.=` as it runs through a foreach loop, and runs the query at the end, so thought that was the only way to add on to the end of that variable rather than overwrite it which `=` does. Is there a better way? – Dean84 Oct 11 '18 at 14:37
  • @ArtisticPhoenix - Its on a localhost, so not that worried about it. In the end I went with just truncating the table each time, and doing away with the update query. I found just inserting each time took a few seconds to run, where as individually updating was taking minutes. The data does come from an XML file. Just for future reference what way would you have done it? I am still learning, and any advice is much appreciated. – Dean84 Oct 11 '18 at 14:40
  • @Dean84 Use can use array for taking value. – Deepak Saini Mar 13 '19 at 12:26

0 Answers0