[SOLVED] Adding 10 on to each value in a mysql query

Adding 10 on to each value in a mysql query

I want to create a query that will add 10 to all the values, e.g. take the table below

name   values
A      10
B      20
C      30

$qry = mysqli_query($con,"SELECT * FROM table")

I could do it through php, but that could create a lot of extra sql calls

while($row = mysqli_fetch_assoc($qry) {
   $new_value = $row['values']+10;
   $name = $row['name'];
   $qry2 = mysqli_query($con,"UPDATE table SET values=$new_value WHERE name='$name'");
 }

But that could be a lot of sql queries. How can we just update all the values in the table in one query?

Solution

You can just update the value with a single query:

UPDATE table SET values = values + 10;

And wrapped in PHP:

mysqli_query($con,"UPDATE table SET values = values + 10");

This will update all rows in the table. If you want to limit the query, just add a where clause for the fields that should be included in the subset:

mysqli_query($con,"UPDATE table SET values = values + 10 WHERE name='$name'");

However, in this solution as well as your approach, it's open for SQL Injection. You should use prepared statements to avoid this type of unwanted behavior.