Insert into MySQL db occurs twice in a loop

Question

I'm currently building a small site to retrieve player stats for a computer game from a mysql db and display them online.

I get player statistics for a list of players from a third party API and am trying to insert them into a table in the db. However my code is performing the inserts twice - the primary key restriction on the table stops duplicate entries, but I don't want to accept this.

Something is amiss with my looping logic, but I'm going in my own loop trying to figure this one out.

Sequence of events is:

I query my db to get the player ID's needed for the API calls
I put these in an array
I query the third party api in a loop to get all the player stats

I want to insert the stats (1 row per player) to my db (I plan to escape the strings etc, it's a work in progress)

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {
    $member_data[] = $row;
}

foreach ($member_data as $id) {

$endpoint =  "http://www.bungie.net/Platform/Destiny/Stats/2/".$id[destiny_membership_id]."/".$id[destiny_character_id]."/";

$bungie_result = hitBungie($endpoint);
$response = json_decode($bungie_result, true);

$destiny_membership_id = $id[destiny_membership_id];
$destiny_character_id = $id[destiny_character_id];
$kills_deaths_ratio = $response[Response][allPvP][allTime][killsDeathsRatio][basic][displayValue];

// DB insert
$sql = "INSERT INTO xax_pvp_stats (destiny_membership_id,destiny_character_id,kills_deaths_ratio) ";
$sql .= "VALUES ('$destiny_membership_id','$destiny_character_id','$kills_deaths_ratio') ";
$result = $conn->query($sql);

if ($conn->query($sql) === FALSE) {
    echo "<br />Error: " . $sql . "<br />" . $conn->error;
} else {
    echo $destiny_character_id." is in the DB";
}
}

} else {
    echo "0 results";
}

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Aug 03 '15 at 12:34
`$response[Response][allPvP][allTime][killsDeathsRatio][basic][displayValue]` <-- that should've given you all sorts of errors, and you're calling `$conn->query($sql);` twice (`$result = $conn->query($sql);` + `if ($conn->query($sql) === FALSE)`) — Elias Van Ootegem, Aug 03 '15 at 12:34
Thanks Jay - I'm going to sort that, it's still in early stages at the minute — Luke Wallwin, Aug 03 '15 at 13:05

score 3 · Accepted Answer · answered Aug 03 '15 at 12:35

3

You're executing the query twice. Once here:

$result = $conn->query($sql);

and once here:

if ($conn->query($sql) === FALSE) {

I'm guessing you meant to examine the result in the second line:

if ($result === FALSE) {

answered Aug 03 '15 at 12:35

David

208,112
36
198
279

Couldn't see the wood for the trees! Thanks, a definite blonde moment – Luke Wallwin Aug 03 '15 at 13:06
@LukeWallwin: It happens :) Glad we could help, and welcome to Stack Overflow! – David Aug 03 '15 at 13:07

score 2 · Answer 2 · answered Aug 03 '15 at 12:36

Several issues, starting with the one you're worried about (insert happening twice):

1) You're calling $conn->query twice, which, of course, executes the INSERT query twice:

Here:

$result = $conn->query($sql);//insert1

if ($conn->query($sql) === FALSE) {//insert 2
    echo "<br />Error: " . $sql . "<br />" . $conn->error;
} else {
    echo $destiny_character_id." is in the DB";
}

2) Your code is vulnerable to injection, learn about prepared statements and use them

3) accessing values in an associative array requires the keys to be quoted: $response[Response][allPvP][allTime][killsDeathsRatio][basic][displayValue] issues notices. When developing, always use display_errors, and set the error level as strict as you can E_STRICT|E_ALL is recommended.

Thanks Elias, I'll look to incorporate all of this! – Luke Wallwin Aug 03 '15 at 13:20 — Luke Wallwin, Aug 03 '15 at 13:20

score 0 · Answer 3 · answered Aug 03 '15 at 12:35

0

You're running the query twice: once when you set $result then again in the following if statement. Remove the $result line (you're not using that variable anyway) and you'll be good to go.

answered Aug 03 '15 at 12:35

Jeffwa

1,143
10
12

Insert into MySQL db occurs twice in a loop

3 Answers3