How to properly update MySQL data from PHP file?

Question

I'd like to update a field in my database using PHP. I wrote this code and works fine. The value of var2 is 0, after the update its value is 1. I'd like to use the field var2 as a PHP variable, thus I can use it as an if condition. With the if conditional I want to update var2 if its value is 0, and redirect to other php file if var2's value is 1.

<?php
session_start();
$con=mysql_connect("localhost","root","");
mysql_select_db("database",$con);
$sql = "SET SQL_MODE = ''";
mysql_query($sql,$con);
$sql = "UPDATE table SET var2=1 WHERE var1 =    '".$_SESSION['user']."'";

if (){}


mysql_query($sql,$con);
if(mysql_error() != ""){
    echo $sql." ".mysql_error();
}
mysql_close($con);
?>

I have tried with

$abc = $_POST['var2'];
$sql = "UPDATE table SET "$abc"=1 WHERE var1 =    '".$_SESSION['user']."'";

but it doesn't work for me, I got the next message "Parse error: syntax error, unexpected T_VARIABLE in C:\xampp\htdocs\egresadoslunes\finalizar.php on line 10"

First you [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). You're trying to execute the query before you actually write the query. You have an empty `if` statement. — Jay Blanchard, Apr 23 '15 at 16:53
check your quoting method, plus what's this supposed to do `$sql = "SET SQL_MODE = ''";` and `if (){}` that does nothing; remove it. — Funk Forty Niner, Apr 23 '15 at 16:55

score 1 · Answer 1 · answered Apr 23 '15 at 17:00

1

You can't just put a variable after a string literal. You should either explicitly concat it with the . operator:

$sql = "UPDATE table SET " . $abc . "=1 WHERE var1 = '".$_SESSION['user']."'";

Or have it inline in the string:

$sql = "UPDATE table SET ${abc}=1 WHERE var1 = '".$_SESSION['user']."'";

Obligatory comment:
Using such string manipulation techniques leaves your code wide open to sql-injection attacks. You should probably consider using prepared statements instead.

answered Apr 23 '15 at 17:00

Mureinik

297,002
52
306
350

This presumes `$abc` is not a reserved word or something malicious. It really needs to be white-listed against known-good column names and possibly escaped with back-ticks. – tadman Apr 23 '15 at 18:36

score 0 · Answer 2 · answered Apr 23 '15 at 17:06

0

Check if row was affected.

$sql = "UPDATE table SET var2=1 WHERE var2=0 and  var1 =    '".$_SESSION['user']."'";
$result=(mysql_query);
if( mssql_rows_affected($result) > 0){
     //var 2 == 0
}
else{
     //var 2 != 0
}

answered Apr 23 '15 at 17:06

Misunderstood

5,534
1
18
25

How to properly update MySQL data from PHP file?

2 Answers2