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" and "How can I prevent SQL injection in PHP?". — sticky bit
– sticky bit, Commented Feb 8, 2021 at 20:59

musoviich · Accepted Answer · 2021-02-08 22:28:03Z

1

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 ?

answered Feb 8, 2021 at 22:28

musoviich

114 bronze badges

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

Comments

Andrew Rothman · Accepted Answer · 2021-02-08 21:50:51Z

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 8, 2021 at 21:50

Andrew Rothman

256 bronze badges

1 Comment

Andrew Rothman Over a year ago

Make sure to use bind_param after you define the variable. $faceitid = $player->id; THEN $stmt->bind_param('s', $faceitid); THEN $stmt->execute();

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related