How can you update a record with a prepared statement?

@TopkatDec 26.2008

I’ve looked at the PHP.net manual, searched Google and I can’t find any examples of updating records with prepared statements. Everything is on select and insert…

How is it done?

I tried the following, but it says I have a syntax error from the first line of code…

[code=php]$q = “UPDATE table1 SET title=?, description=? date=? WHERE id=?”; $stmt = mysqli_prepare($dbc, $q); mysqli_stmt_bind_param($stmt, ‘sssi’, $title, $descrip, $date1, $id); mysqli_stmt_execute($stmt);[/code]

to post a comment

PHP

10 Comments(s) _↴

@NogDogDec 26.2008 — #Missing comma between setting the description and the date in the SQL.

@TopkatauthorDec 26.2008 — #Thanks for your reply. I edited out some columns because I wanted the code more simple. I checked my original code and there is a comma between description and date.

$q = "UPDATE table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id=?";

I get this 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 '?, description=?, price=?, location=?, email=?, date=? WHERE id=?' at line 1

Query: UPDATE table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id=?

I think I would be able to figure it out if I could find an example of updating a record with a prepared statement, but I can't find anything on Google :-

@chazzyDec 26.2008 — #what version of mysql are you using? are you really using mysqli or is it mysql extensions?

can you just copy and paste your entire code? Rather than retyping it?

@TopkatauthorDec 26.2008 — #Hey. I'm using mysql 5.0.67 and PHP 5.2.6.

I don't even know if I'm using the right syntax for updating the query. I'm supprised no one has said "YOU DON'T UPDATE LIKE THAT...", because i've just been guessing how it's done and I'm pretty sure it's gotta be wrong, hence the errors.

I can update perfectly using "mysqli_query", but it's not safe and it's performance isn't as good as prepared statements.

Do you have any example code of updating with a prepared statement? It would be great help if you did.

Here is a cut and paste of my broken code:

[code=php]
 $q = "UPDATE table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id=$id";
 $stmt = mysqli_prepare($dbc, $q);
 mysqli_stmt_bind_param($stmt, 'ssisss', $title, $descrip, $pri, $loc, $e, $date1);
 mysqli_stmt_execute($stmt);
 [/code]

If anyone could post an example of an update prepared statement, it would be PERFECT! :-)

@chazzyDec 26.2008 — #any difference if you do this:

[code=php]
 $q = "UPDATE table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id=?";
 $stmt = mysqli_prepare($dbc, $q);
 mysqli_stmt_bind_param($stmt, 'ssisssi', $title, $descrip, $pri, $loc, $e, $date1, $id);
 mysqli_stmt_execute($stmt); 
 [/code]

is price in fact an integer? is it not a double? what does the date look like?

@TopkatauthorDec 26.2008 — #Yeah price is int (i dont want double) and the date is

$date1 = "2008-12-26 09:36:22";

I would prefer to use NOW(), but I want to get it to work first...

Am I using the right syntax? Do you have any examples of updating a record with prepare?

Thanks for your help mate.

@chazzyDec 26.2008 — #and..

any difference if you do this:[/quote]

No reason why this wouldn't be working. i can see possible reasons why what you were using wasn't working.

@TopkatauthorDec 26.2008 — #Hey thank you very much for helping me. I originally wrote the code how you wrote it, then I changed it to have

WHERE= $id

Are you sure the query syntax is right? All the variables are fine...

I get this 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 '?, description=?, price=?, location=?, email=?, date=? WHERE id=?' at line 1

Query: UPDATE table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id=?

This problem is really driving me crazy. Am I the only person in the world who has ever wanted to use a prepared statement for update and then had a problem doing it?? I've searched under so many keywords to find an example. I've had plenty of results for Java, even though I only searched for PHP prepared statement :-

@chazzyDec 26.2008 — #yeah, syntax looks fine. though i'm more inclined to lean towards the OOP format, fewer errors. try wrapping the column names in backticks (

).
 
 <CODE lang="php">[code=php]<i>
 </i>$dbh = new mysqli('host','user','password','database');
 $stmt = $dbh-&gt;prepare("UPDATE

table1 SET title=?, description=?, price=?, location=?, email=?, date=? WHERE id`=?");
$stmt->bind_param("ssisssi", $title, $descrip, $pri, $loc, $e, $date1, $id);
stmt->execute();
[/code]

None of the columns stick out as reserved words but who knows.

@TopkatauthorDec 27.2008 — #Hey thank you so much Chazzy!!! Super hero for sure!!! :-)))

You were TOTALLY right!!! The code was right!!! I was going insane yesterday, I spent half a day trying to get it to work... then today I woke up and wrote a very basic script and made a new database and it worked!!!

So I went back to my script and noticed some old code i didn't comment out. I feel bloody stupid, lol :-))

I will never have doubts about anything you say ever again :-)))

Also in #PHP _↴

[RESOLVED] CROPPING an image to a certain height and width [RESOLVED] simpleXML and Namespaces Help table aligning by variables and array....

Success!

Help @Topkat spread the word by sharing this article on Twitter...

Tweet This

How can you update a record with a prepared statement?

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

How can you update a record with a prepared statement?

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴