September 7, 2012
PHP changing old mysql_query to PDO
Question by neeko
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 by John Woo
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 by Starx
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));
