How to write update query using some {$variable} with example

Question

How to write update query using some {$variable} with example eg:

$query="update subjects set values username='{$name}', hash_password='{$pass}' where id=1";

but i have error like this "Query Error..!!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values username='Veerendra Kakumanu', hash_password='veeru' where id=1' at line 1" sample code $name=$_POST["name"]; $pass=$_POST["pass"]; $query="update subjects set values username='{$name}', password='{$pass}' where id=1"; $result=mysql_query($query,$connection); — Veerendra K, Mar 22 '13 at 21:03
Rather google for a tuturial on PDO and prepared statements; in the long run that's less cumbersome than interpolating variables into sql queries. — mario, Mar 22 '13 at 21:04
He should have Googled, but I have a decent code base of examples which I can easily modify for users like this. — Jonast92, Mar 22 '13 at 21:15

score 2 · Answer 1 · answered Mar 22 '13 at 21:10

Create a PDO connection:

// Usage:   $db = connectToDatabase($dbHost, $dbName, $dbUsername, $dbPassword);
// Pre:     $dbHost is the database hostname, 
//          $dbName is the name of the database itself,
//          $dbUsername is the username to access the database,
//          $dbPassword is the password for the user of the database.
// Post:    $db is an PDO connection to the database, based on the input parameters.
function connectToDatabase($dbHost, $dbName, $dbUsername, $dbPassword)
{
   try
    {
        return new PDO("mysql:host=$dbHost;dbname=$dbName;charset=UTF-8", $dbUsername, $dbPassword);
    }
    catch(PDOException $PDOexception)
    {
        exit("<p>An error ocurred: Can't connect to database. </p><p>More preciesly: ". $PDOexception->getMessage(). "</p>");
    }
}

Init it like this:

$host = 'localhost';
$user = 'root';
$databaseName = 'databaseName';
$pass = '';

And call it like this:

$db = connectToDatabase($host, $databaseName, $user, $pass);

And use a function like this:

function update($db, $username, $password, $id)
{
    $query = "UPDATE subjects SET username = :username, hash_password = :password WHERE id = :id;";
    $statement = $db->prepare($query); // Prepare the query.
    $result = $statement->execute(array(
        ':username' => $username,
        ':password' => $password,
        ':id' => $id
    ));
    if($result)
    {
        return true;
    }
    return false
}

Now finally, you can do something like:

$username = "john";
$password = "aefasdfasdfasrfe";
$id = 1;

$success = update($db, $username, $password, $id);

You also avoid sql injection by doing it like this (preparing the statements, and executing the variables into the statement).

score 1 · Accepted Answer · answered Mar 22 '13 at 21:08

1

You cannot use values there, it should be:

$query="update subjects set username='{$name}', hash_password='{$pass}' where id=1";

But I would recommend using a prepared statement instead of dumping variables straight into your query.

answered Mar 22 '13 at 21:08

jeroen

91,079
21
114
132

thank you sir..can u tell me how to write prepared statement plzz – Veerendra K Mar 22 '13 at 21:12
Good point with the prepared statements and the values (+1) since they are for insert. I gave an example on how to use them in my answer. – Jonast92 Mar 22 '13 at 21:13

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

If you don't wanna read up on context/database escaping, this is how you avoid such problems using PDO for example:

$pdo = new PDO('mysql:host=localhost;dbname=db', 'user', 'pw');

$pdo->prepare("update subjects set values username=?, hash_password=? where id=?")
    ->execute(array($user, $pass, 1));

How to write update query using some {$variable} with example

3 Answers3

Linked