PHP loop through array to update SQL database

Question

I am trying to loop through an array ($lineup_selected) that corresponds to a player row in a database. For each player I would like to execute an UPDATE query to the database that adds the value of $submissions_selected to the total_picks column. I am struggling with the code as it fails to execute the query. Any help please?!

// Select team & formation
$team_selected = "team1";
$lineup_selected = array("player1", "player2", "player3");
$submissions_selected = 4000;

    // Loop through and update total_picks for each player in database present in lineup_selected array
    $player_picks_query = "SELECT full_name, total_picks FROM table WHERE team=$team_selected";
    $result = mysqli_query($conn, $player_picks_query);

    while($row = mysqli_fetch_assoc($result)) {

        $player = mysql_real_escape_string($row["full_name"]);

        $add_player_picks = "UPDATE table 
                    SET total_picks = total_picks + $submissions_selected
                    WHERE full_name = '$player'";

    }

Actually looks like the error is in the first query!!! `WHERE team=$team_selected";` should have quotes around `$team_selected` like this `WHERE team='$team_selected'";` — RiggsFolly, Jun 28 '17 at 13:47
I've tried adding the quotes but it made no difference. I'd like the $add_player_picks to execute once per loop. So that the loop goes through each of the players in the $lineup_selected array one-by-one, finds that player in the database and adds the $submissions_selected into the total_picks column — Dylan Wood, Jun 28 '17 at 14:02
@DylanWood Ivo P's answer executes your query using a safer approach (mysqli prepared statements) and improves the speed of the query by allowing the database to do the looping — ctwheels, Jun 28 '17 at 14:21

Benjamin Brasseur · Answer 1 · 2017-06-28T13:50:04.970

1

In your select request you have a team that is obviously a String. So, maybe you can try your request like : "SELECT full_name, total_picks FROM table WHERE team='$team_selected'" I don't know if PHP is smart enough to put the quotes.

edited Jun 28 '17 at 13:50

answered Jun 28 '17 at 13:38

Benjamin Brasseur

558
2
17

I understand you cannot make a comment but this is really not an answer. – nerdlyist Jun 28 '17 at 13:38
3

I know you are short of reps, but this is really just a comment. This type of answer attracts downvotes or gets flagged ___I did not DV___ and if that happens you will loose rep points and take longer to get to the 50 reps you need to comment on any question. Until then, stick to questions that are well asked and therefore easily answered without needing clarification. http://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead – RiggsFolly Jun 28 '17 at 13:39
Oh thank you for this information @RiggsFolly. I edit my answer. – Benjamin Brasseur Jun 28 '17 at 13:44

Ivo P · Accepted Answer · 2017-06-28T14:09:33.433

1

why not:

UPDATE table
SET total_picks = total_picks + $submissions_selected
WHERE team = '$team_selected'

this way you have only one query to execute and let your database do the looping. Else you would first select some records and then have your database update each one of them to update the record.

I assume the fullname is unique. If not, it would mean your version can have the update-query modify multiple records each time and so my approach is invalid

-- and I seem to repeat a lot of the comments when stating to sanitize and escape your input to be save(r).

edit: combined it should come to:

// set team & formation
$team_selected = "team1";
$lineup_selected = array("player1", "player2", "player3");
$submissions_selected = 4000;



$updatequery = "UPDATE table
                SET total_picks = total_picks + ?
                WHERE team= ?";

$stmt = mysqli_prepare($updatequery);
mysqli_stmt_bind_param($stmt, "is", $submissions_selected, $team_selected);

/* execute prepared statement */
mysqli_stmt_execute($stmt);

Myself I am more into the pdo approach, but syntax should be like this.

edited Jun 28 '17 at 14:09

answered Jun 28 '17 at 13:40

Ivo P

1,722
1
7
18

1

This doesn't answer the question and you fail to explain your answer. Your answer also contains a syntax error that would cause the SQL query to error out (`$team_selected` should be in quotes). Your answer also contains security flaws (SQL injection), although so does the original question. You should point out such security flaws to the OP and the rest of the community for everyone to practice safer coding techniques. – ctwheels Jun 28 '17 at 13:52
I've removed my downvote. Your answer looks better. If you add an alternate way to update the table (preventing SQL injection), I will consider an upvote. Look at [PDO](http://php.net/manual/en/book.pdo.php) or [mysqli_*](http://php.net/manual/en/book.mysqli.php) if you are unsure. Prepared statements are a great line of defense for many attacks that can be performed on databases. – ctwheels Jun 28 '17 at 13:59
Much better, I've upvoted your answer as it does solve the OP's question and improves the original code to add security against SQL injection attacks. This should be the accepted answer for this question (in my opinion) – ctwheels Jun 28 '17 at 14:20

score -1 · Answer 3 · answered Jun 28 '17 at 13:45

-1

I think it will be better if you use only an update statement.

First of all you implode your array

$lineup_selected = array("player1", "player2", "player3");

$players='".implode("','",$lineup_selected )."';

Now you can update the table

$updateStmt="UPDATE table 
SET total_picks = total_picks + $submissions_selected
WHERE full_name in (".$players.") and team=".$team_selected.";

answered Jun 28 '17 at 13:45

enido

44
2

Is it just me or does this line: `$players='".implode("','",$lineup_selected )."';` look a little broken? Also your answer allows SQL injection. Straight up. Let's allow [Bobby Tables](http://bobby-tables.com/) to let you know how – ctwheels Jun 28 '17 at 13:56

PHP loop through array to update SQL database

3 Answers3