PDO - Update is not working

Question

Does anybody have an idea why this is not working?

$sql = $dbh->prepare('UPDATE notes SET title=:title, content=:content WHERE id=:id');
$title = $_POST['title'];
$content = $_POST['editor_edit'];
$id = $r2['id'];
$sql->bindValue(':title',$title);
$sql->bindValue(':content',$content);
$sql->bindValue(':id',$id);
$sql->execute();

What I tried is doing the same without bindValue, but with putting the content directly into the execute command, which wasn't working. When I tried to put simple values into the statement (like 'UPDATE notes SET title="test", content="hello" WHERE id=20') it worked, so the connection to the database is working. Anybody sees a mistake?

What does that mean it is not working? What errors are there? — B001ᛦ, Aug 27 '15 at 09:48
If you're not getting any errors, it's possibly not a syntax issue - it might simply be that the value in `$r2['id']` doesn't actually match the id of any record in the table - try: `var_dump($r2['id'])` to see what it actually holds. — CD001, Aug 27 '15 at 10:02
I think the update syntax is incorrect, please check your update syntax. SET `title` = : title, `content` = : content — Dean Gite, Aug 27 '15 at 10:04
It would be good to see the connection code as well, to be sure its ok and to see if you are using `setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` or not — RiggsFolly, Aug 27 '15 at 10:05
@CD001, that really was the mistake... used a variable, that i hadn't declared in the case the code i posted is running... thanks for that, it works now, so everything else was working.. — nameless, Aug 27 '15 at 10:24

score 1 · Answer 1 · answered Aug 27 '15 at 10:00

for database connection, Try out these codes..these code will show any error if there is any

database.php

   <?php
try{
    $db = new PDO('mysql:host=localhost;dbname=your database name', "root" , "");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}catch(PDOException $e){
    print "error in connection" . $e->getMessage();
}

And for updating..try these codes

    <?php
require_once 'database.php';

$title = $_POST['title'];
$content = $_POST['editor_edit'];
$id = $r2['id'];

    $stmt = $db->prepare("UPDATE notes SET title  = ?, content =? WHERE id = ?");
    $stmt->execute(array($title,$content,$id));
    echo "updated Successfully";

hope it will help you!!

bg17aw · Answer 2 · 2015-08-27T10:30:44.327

You are using PDO and you tested the query using simple values and it is working. That means the problem happens at the parameter binding faze.

So you would like to see the SQL query PDO is generating AFTER binding all the parameters but you don't know how to. You need to find a way to do this otherwise you will end up asking here for help on every query that is not working, as you have no idea what PDO is actually generating.

Two ways to do this (I am assuming you are using WAMP, but you can adapt this to work on LAMP as well):

Method 1: We can capture every query that MySQL runs into the mysql.general_log table. By running this command:

SET GLOBAL general_log = 'ON';

SET GLOBAL log_output = 'TABLE';

To get output relevant to certain user/app from mysql.general_log table:

SELECT * FROM mysql.general_log WHEREuser_hostNOT LIKE "%root%" ORDER BY event_time DESC

OR

SELECT * FROM mysql.general_log WHEREuser_hostLIKE "%username%" ORDER BY event_time DESC

Method 2: Edit my.ini file in WAMP to see sql query log

[wampmysqld]
general-log=1
general-log-file=c:/mysql_general.log

Also, set your connection like this to get errors thrown for both the PREPARE faze as well the EXECUTE faze:

try{
    $db = new PDO('mysql:host=localhost;dbname=dbname', "username" , "password");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

}catch(PDOException $e){
    print "error in connection" . $e->getMessage();
}

Why is this downvoted? If the original poster would use this, he would easily be able to see where the issue was. Also, he needed this for the future, I know from experience how frustrating is to not be able to see the final sql query generated by PDO. — bg17aw, Aug 27 '15 at 10:51

score 0 · Answer 3 · answered Aug 27 '15 at 10:01

You are not getting any errors because you are not looking for any!

Try this with some error processing included, it will likely throw you an error so you can fix it yourself.

if ( ! isset( $_POST['title'],$_POST['editor_edit'], $r2['id']) ) {
    echo 'Parameters Missing';
    exit;
}

$title = $_POST['title'];
$content = $_POST['editor_edit'];
$id = $r2['id'];

$stmt = $dbh->prepare('UPDATE notes SET title=:title, content=:content WHERE id=:id');
if ( ! $stmt ) {
    print_r( $dbh->errorInfo() );
}

$stmt->bindValue(':title',$title, PDO::PARAM_STR);
$stmt->bindValue(':content',$content, PDO::PARAM_STR);
$stmt->bindValue(':id',$id, PDO::PARAM_INT);
$result = $stmt->execute();
if ( ! $result ) {
    print_r( $stmt->errorInfo() );
    exit;
}

score -1 · Answer 4 · answered Aug 27 '15 at 09:54

-1

The line $r2['id']; might be returning a string. So, $id will be holding a string value. Your id is of int so, convert it to int before like:

$id = $r2['id'];
$id = intval($id);

answered Aug 27 '15 at 09:54

Prerak Sola

9,517
7
36
67

PDO - Update is not working

4 Answers4