how to put json array into database via for or while?

Question

I'm taking Webhook from the FACEIT platform and I'm getting something like this

And I'm trying to update database row "Game" to 1 if the player with id is in that game

so I tried with this, I'm not getting any error but, my Game row is not updating.

<?php
include "connect_db.php";
header('Content-Type: application/json');
$request = file_get_contents('php://input');
$data = json_decode($request);

if(!empty($data)){
    $req_dump = print_r($data, true);
    $fp = file_put_contents('request.log', $req_dump);
    if($data->event == 'match_status_ready')
    {
            for($i=0; $i<count($data->payload->teams); $i++){
                for($j=0; $j<count($data->payload->teams[$i]->roster); $j++){
                    $sqlupdate[$j] = "UPDATE Korisnici SET Game='1' WHERE faceit_playerid =".$data->payload->teams[$i]->roster[$j]->id;
                    $conn->query($sqlupdate[$j]);
                    /*$message = "Tim[". $i ."] Igrač[". $j ."] - ". $data->payload->teams[$i]->roster[$j]->id ."\n";
                    file_put_contents('status.log', $message, FILE_APPEND);*/
                }
            }
            $curl = curl_init("discord-webhook-url");
            curl_setopt($curl, CURLOPT_HTTPHEADER, array('Content-type: application/json'));
            curl_setopt($curl, CURLOPT_POST, 1);
            curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
            curl_setopt($curl, CURLOPT_HEADER, 0);
            curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode(array("content" => "!matchid ". $data->payload->id ." ".$data->event)));
            echo curl_exec($curl);   
    }
    
    else if($data->event == 'match_status_finished' || $data->event == 'match_status_cancelled')
    {
            for($i=0; $i<count($data->payload->teams); $i++){
                for($j=0; $j<count($data->payload->teams[$i]->roster); $j++){
                    $sqlupdate = "UPDATE Korisnici SET Game='0' WHERE faceit_playerid =".$data->payload->teams[$i]->roster[$j]->id;
                    $conn->query($sqlupdate);
                }
            }
        $curl = curl_init("discord-webhook-url");
        curl_setopt($curl, CURLOPT_HTTPHEADER, array('Content-type: application/json'));
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
        curl_setopt($curl, CURLOPT_HEADER, 0);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode(array("content" => "!matchid ". $data->payload->id ." ".$data->event)));
        echo curl_exec($curl);
    }
    sleep(3);
}
else { echo "Link je prazan !"; }
?>

When I put it into log I'm getting a right thing

Tim[0] Igrač[0] - c8d41fb0-df78-4b53-a1ef-1b007f7d8f7b
Tim[0] Igrač[1] - edfeb779-89e7-485d-bbc8-64d99e3356ba
Tim[0] Igrač[2] - 13144f2a-e8b3-4469-816c-9af61caedb58
Tim[0] Igrač[3] - 268b7ac8-48dc-4bcf-995a-de00244e8cf7
Tim[0] Igrač[4] - ab056dc5-3cab-4568-9d2e-ddb405904500
Tim[1] Igrač[0] - 7196b459-4b4c-4a1b-8f46-9bb37a243c65
Tim[1] Igrač[1] - d8fe30bb-2a72-4169-9d30-1a6c82c6d0af
Tim[1] Igrač[2] - 7b4d7693-3646-4010-931c-e6f7c2790206
Tim[1] Igrač[3] - 4f789940-e122-4470-9347-82f21611132e
Tim[1] Igrač[4] - 4ccc9f0f-3536-4a1d-8386-a630cf2be337

Can anyone help me? I'm really disappointed...

(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Feb 08 '21 at 20:59

score 1 · Accepted Answer · answered Feb 08 '21 at 22:28

Thanks sir, i finnaly got it on another way, is it allright ?

$stmt = $conn->prepare("UPDATE Korisnici SET Game='1' WHERE faceit_playerid=?");
        $stmt->bind_param('s', $faceitid);
        foreach ($data->payload->teams as $team){
            foreach ($team->roster as $player){
                /*$sqlupdate = "UPDATE Korisnici SET Game='1' WHERE faceit_playerid=" . $player->id;
                $conn->query($sqlupdate);*/
                $faceitid = $player->id;
                $stmt->execute();
            }
        }
        $stmt->close();

EDIT: How can i bypass too many connections with this ?

score 0 · Answer 2 · answered Feb 08 '21 at 21:50

0

Try logging the SQL query, instead of the values. This should help you see what you're actually telling the DBMS to do. You may find that you're sending it the wrong values.

Also, instead of a couple of numerical for loops, try this:

foreach ($data->payload->teams as $team){
    foreach ($team->roster as $player){
        $sqlupdate = "UPDATE Korisnici SET Game='1' WHERE faceit_playerid =" . $player->id;
        $conn->query($sqlupdate);
    }
}

answered Feb 08 '21 at 21:50

Andrew Rothman

25
5

Make sure to use bind_param _after_ you define the variable. $faceitid = $player->id; THEN $stmt->bind_param('s', $faceitid); THEN $stmt->execute(); – Andrew Rothman Feb 11 '21 at 18:45

how to put json array into database via for or while?

2 Answers2