PHP changing old mysql_query to PDO

Question

I have some old mysql_query queries in my code which i want to convert in to PDO but am struggling to get to work.

my original code was:

mysql_query("UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username' ")
or die(mysql_error()); 

now i am trying:

$sql = "UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username'";
$q   = $conn->query($sql) or die("failed!");

but can't seem to get it to work, any ideas?

UPDATED CODE:

$conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);


 // check if the form has been submitted. If it has, process the form and save it to the   database
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['id']))
  {
 // get form data, making sure it is valid
 $id = $_POST['id'];
 $fname = mysql_real_escape_string(htmlspecialchars($_POST['fname']));
 $lname = mysql_real_escape_string(htmlspecialchars($_POST['lname']));
 $contact = mysql_real_escape_string(htmlspecialchars($_POST['contact']));
 $price = mysql_real_escape_string(htmlspecialchars($_POST['price']));


 // check that firstname/lastname fields are both filled in
 if ($fname == '' || $lname == '' || $contact == '' || $price == '' )
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 //error, display form
 renderForm($id, $fname, $lname, $contact, $price, $error);
 }
 else
 {
 // save the data to the database
 $username = $_SESSION['username'];

 $query = "UPDATE people 
         SET price=?, 
             contact=?, 
             fname=?, 
             lname=? 
          WHERE id=? AND 
                username=?";
$stmt = $db->prepare($query);
$stmt->bindParam(1, $price);
$stmt->bindParam(2, $contact);
$stmt->bindParam(3, $fname);
$stmt->bindParam(4, $lname);
$stmt->bindParam(5, $id);
$stmt->bindParam(6, $username);    
$stmt->execute();


 // once saved, redirect back to the view page
header("Location: view.php"); 
}

Answer 1

For more information visit this link: PHP PDO

based on your example,

<?php

    $query = "UPDATE people 
             SET price=?, 
                 contact=?, 
                 fname=?, 
                 lname=? 
              WHERE id=? AND 
                    username=?";
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $price);
    $stmt->bindParam(2, $contact);
    $stmt->bindParam(3, $fname);
    $stmt->bindParam(4, $lname);
    $stmt->bindParam(5, $id);
    $stmt->bindParam(6, $username);    
    $stmt->execute();

?>

PDO Prepared statements and stored procedures

Answer 2

Note that when working with the mysql driver for PDO you always have to disable emulated prepared statements:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = 'UPDATE people SET';
$sql.= ' price = :price,';
$sql.= ' contact = :contact,';
$sql.= ' fname = :fname,';
$sq;.= ' lname = :lname';
$sql.= ' WHERE id= :id AND username = :username';

$stmt = $pdo->prepare($sql);

$stmt->execute(array(
    ':price' => $price,
    ':contact' => $contact,
    ':fname' => $fname,
    ':lname' => $lname,
    ':id' => $id,
    ':username' => $username,
));

As you can see I have used named parameters, because when you have lots of them it is imho way clearer what you are doing.

Note: that ircmaxell is currently working on getting the default to always use real prepared statements, but until that (which may take some while) you always have to disable them for mysql.

Answer 3

If you're going to use PDO, you need to look at prepare() and execute otherwise you're losing the security that PDO is offering and retaining the SQL Injections. So, given your example:

$conn = new PDO(/*connection info*/);

$query = $conn->prepare("UPDATE people "
                      . "SET    price    = :price, "
                      . "       contact  = :contact, "
                      . "       fname    = :fname, "
                      . "       lname    = :lname "
                      . "WHERE  id       = :id "
                      . "  AND  username = :username");
$result = $query->execute(array(
  ':price'    => $price,
  ':contact'  => $contact,
  ':fname'    => $fname,
  ':lname'    => $lname,
  ':id'       => $id,
  ':username' => $username
));

That's more the lax way, but you can also bindParam and be explicit as to the data type it's expecting.

Answer 4

Few things you have to be clear while using PDO extension is that there are multiple ways to get things done.

The way you are currently using being one of them including few more. However it is always a good idea to bind parameters separately, because this prevents many problems like SQL Injection and many more.

Other important things to look at are statement, prepare and execute.

$conn = new PDO("...."); //Creating the handler

//Create the statement
$stmt = $conn -> prepare("UPDATE people SET price = :price, contact = :contact, fname = :fname, lname = :lname WHERE id= :id AND username = :username");

// Bind the params
$stml -> bindParam(":contact", $contact, PDO::PARAM_STR); //This way you can also define the DATATYPE of the parameter

//Execute
$stmt -> execute(array(
   ":price" => $price, //another way of binding the params
   ":fname" => $fname, 
   ":lname" => $lname,
   ":id" => $id, 
   ":username" => $username));