6

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"); }
Improve this question
4
  • 2
    If you're going to switch to PDO, which is a great idea, be sure to use SQL placeholders. Your old example is typical of mysql_query where it's likely vulnerable to severe SQL injection problems. As always, what errors are you getting? "Can't get it to work" is not a useful diagnostic. Commented Sep 7, 2012 at 0:32
  • Despite the improvements, your method should work. Can you show the full code, from where you start initiating $conn? Commented Sep 7, 2012 at 0:36
  • sorry i should have been more specific, i have an edit form which allows users to edit adverts they have posted, when they click on an ad to edit it comes up with the form and the previous values are already echo'ed in to the text boxes from my database. This still works fine (it uses another query), now when I try and sumbit the form it comes up with "mysql_real_escape_string" error which to me suggests that the form is sumbiting blank data so to speak? so i'm not sure whether my PDO query is at fault as it was working fine with the old mysql_query code, thanks for your help!! Commented Sep 7, 2012 at 0:37
  • yes will include the full code in the question :) Commented Sep 7, 2012 at 0:38

4 Answers 4

Reset to default
6

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thankyou for your reply! i have used this method but i am still getting the mysql_real_escape_string error? could this be because i have defined my variables as "$fname = mysql_real_escape_string(htmlspecialchars($_POST['fname']));" is this necessary when using PDO or do you think it is something else?
@NeilKumar try removing that, PDO will automatically do the escaping for you. $fname = $_POST['fname'];
okay now i get Fatal error: Call to a member function prepare() on a non-object in... any ideas what this means?
@NeilKumar i think it's because you are using $db instead of $conn. change $db->prepare($query); to $conn->prepare($query);
5

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.

Improve this answer

Comments

4

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.

Improve this answer

Comments

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));
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.