MYSQL loop update row value separately

Question

i have the following php:

<?php
$connection=mysqli_connect("host","user","pass","db");
if (mysqli_connect_errno())
    {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
$result = mysqli_query($connection,"SELECT ID FROM tbname");
while($row = mysqli_fetch_array($result))
   {
    mysqli_query($connection,"UPDATE tbname SET amount= (amount+ 1) WHERE ID='$row[ID]' ");
   }
mysqli_close($connection);
echo 'OK';   ?>

I want to 'corelate' the pressing of a button to update the associated row value from the table but when i use this code i get all my values updated. Can anyone help me ?

Your identifier here `$row{ID]`, needs quotes like this `$row['ID']` — Jay Blanchard, Jan 28 '16 at 21:19
@JayBlanchard if i do so, i get **Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING ** error on that line — Alex Resiga, Jan 28 '16 at 21:21
Your current code is effectively the same as removing the `SELECT` query and the `while` loop and just doing `UPDATE tbname SET amount= (amount+ 1);` (notice no `WHERE` clause). Both will update all rows in the table. — Patrick Q, Jan 28 '16 at 21:23
@PatrickQ i realised.. any idea how i could update the values separately? — Alex Resiga, Jan 28 '16 at 21:24
You need to capture/pass some identifier unique to the row that you want to update. — Patrick Q, Jan 28 '16 at 21:25
@PatrickQ well that ID is a primary key, and this php code is part of a ajax request, do i have to mention something about the ID from there or only in this PHP? — Alex Resiga, Jan 28 '16 at 21:27

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

You have to properly identify the variable in the array and concatenate the variable in the query:

mysqli_query($connection,"UPDATE tbname SET amount = amount+ 1 WHERE ID='" . $row['ID']. "' ");

you also do not need the parentheses around the calculation in the SET clause.

Since you're selecting all of the rows in your table and then looping through all of the rows and changing the value, which is not what you want, you have to select with a filter:

SELECT ID FROM tbname WHERE *some condition is met*

Once you do that you'll be able to update a subset of your records as you desire.

Since you're using MySQLi you should learn about prepared statements for MySQLi to guard yourself from potential SQL Injection Attacks.

in addition you should employ error checking, such as or die(mysqli_error()) to your connection and queries. If not you'll have to look in your error logs to fish out any problems that you could have with these.

edited May 23 '17 at 11:59

Community

1
1

answered Jan 28 '16 at 21:29

Jay Blanchard

34,243
16
77
119

and get rid of the loop or is it any good? – Alex Resiga Jan 28 '16 at 21:30
You'll need to loop if you select query contains a subset of all of the records. If you just want to update one record you can specify an ID rather than using `$row['ID']` – Jay Blanchard Jan 28 '16 at 21:32
i dont want to update only one value. i have a table with 30 rows which consist of an id which is a primary key, a name, a td where the amount is shown and a td where i have a picture that has the class .press_me which triggers the ajax.and when i press the nth button i would want to update only the nth value in the table – Alex Resiga Jan 28 '16 at 21:35
nth value or nth row? – Jay Blanchard Jan 28 '16 at 21:36
well only the value since it's the only thing that changes in the table – Alex Resiga Jan 28 '16 at 21:37
If you going to change a certain value in all of the rows you do not need to query the table and then loop, you just need to perform an `UPDATE` query. `UPDATE tbname SET amount = amount+ 1 ` should be all you need. – Jay Blanchard Jan 28 '16 at 21:39
no..they are individual values..the table is made out of children and the values are the amount of homework they have not done, and this table is for the teacher who presses the button on a specific row to increment the amount of that child.. i hope you understand – Alex Resiga Jan 28 '16 at 21:42
Then you will need to have a single ID in the UPDATE statement, based on the teacher's selection. When you load the buttons for each student you should supply the ID for each of those forms, then when the button is clicked it passes the ID to the UPDATE query. – Jay Blanchard Jan 28 '16 at 21:45
ok..i ll try to do this somehow.. but as a conclusion i get rid of the loop right? – Alex Resiga Jan 28 '16 at 21:48
You're absolutely right. – Jay Blanchard Jan 28 '16 at 21:49
The query string in the original code [is just fine](http://php.net/manual/en/language.types.string.php#language.types.string.parsing) by the way. I'm all for getting rid of unnecessary string concats! `"UPDATE tbname SET amount=(amount+ 1) WHERE ID='$row[ID]'"` will parse without problems. – miken32 Jan 28 '16 at 23:41
I agree that getting rid of unnecessary string concats is a good thing @miken32 but leaving the identifier in the array variable unquoted will not work and should throw an error. – Jay Blanchard Jan 29 '16 at 12:57

Patrick Q · Accepted Answer · 2016-01-28T21:43:49.347

1

This assumes that your ajax request is passing an 'id' parameter. Note that this code is open to SQL injection attacks. I am assuming that you know how to properly sanitize your inputs and parameterize your queries to protect yourself. If you don't, Jay's answer includes some good links that you should check.

<?php
if(!empty($_POST["id"]))
{
    $id = $_POST["id"];

    $connection=mysqli_connect("host","user","pass","db");
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
    }

    mysqli_query($connection,"UPDATE tbname SET amount= (amount+ 1) WHERE ID = '" . $id . "'");

    mysqli_close($connection);
    echo 'OK';
}
else
{
    echo 'NO ID PASSED';
}
?>

edited Jan 28 '16 at 21:43

answered Jan 28 '16 at 21:33

Patrick Q

6,373
2
25
34

and in the ajax, how could i get the id? – Alex Resiga Jan 28 '16 at 21:49
i mean i have the var id = $(this).closest('tr').attr('id'); line, could this work? – Alex Resiga Jan 28 '16 at 21:51
That's really impossible to say since you didn't include any HTML and Javascript in your question. If you don't already have this ID specified in your page, then you really have a lot more work to do than the scope of the original question. – Patrick Q Jan 28 '16 at 21:52
i'm not sure how to retrieve the ID from the mysql not the id from the table, since i have no id's in it as it's generated within a php loop – Alex Resiga Jan 28 '16 at 21:57

MYSQL loop update row value separately

2 Answers2