PDO/Prepare/Update failed array 0000

[4xjbh]

I am changing my test code from mysqli to PDO. It worked for one of my forms but the other I racking my brain as to why it isn't. I have stripped it down, found a few typos but array 0000 error is occurring and I am unsure how to resolve it. Can you help? 

 

James

 

connection

	$dbtype     = "mysql";
	$dbhost     = "localhost";
	$dbname     = "mydb1";
	$dbuser     = "myusr1";
	$dbpass     = "mp1";
try {	
	// database connection
 	$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
	$conn -> exec('SET CHARACTER SET utf8');		
} catch(PDOException $e) {
	echo 'There was a problem';
}

php

    $sql = "UPDATE catalogue
            SET title=:title,
                manufacturer=:manufacturer   
            WHERE id=:id";    
    $q = $conn->prepare($sql);
    $q->bindParam(':id',$_POST['id'], PDO::PARAM_INT);
    $q->bindParam(':title',$_POST['title'], PDO::PARAM_STR);
    $q->bindParam(':manufacturer',$_POST['manufacturer'], PDO::PARAM_INT);
    if ($q->execute()){ 
            echo "Saved successfully";
    } else {
        echo "<br/> Crap, something went wrong <br/>";
        //just for testing
        echo $sql." <br/>";
        print_r($_POST);
        print_r($conn->errorCode());  echo "<br/>";
        print_r($conn->errorInfo());  echo "<br/>";
    } 

output

Crap, something went wrong 
UPDATE catalogue SET title=:title, manufacturer=:manufacturer WHERE id=:id 
Array ( [title] => Madinoz Pty Ltd [manufacturer] => 71 [media_id] => 1 [category_id] => 17 [subcategory_id] => Please Select [notes] => This is some text. [keywords] => Hardware, Handles, Hooks, Rails, Fittings, [save] => Save [id] => 2 ) 00000
Array ( [0] => 00000 [1] => [2] => ) 

[4xjbh]

#$%^$@#....

 

This has been resolved.Might help if I put the 's' on 'catalogue'

 

What a waste of time! Time for bed.

[mac_gyver]

if you had tested if the ->prepare() worked, you would have likely gotten an error about an unknown table name...

 

you need to test at each step that can fail or in the case of PDO you can configure it to throw an exeption at any step that fails and it will goto the catch statment upon the first statement that fails.

[AbraCadaver]

if you had tested if the ->prepare() worked, you would have likely gotten an error about an unknown table name...

 

you need to test at each step that can fail or in the case of PDO you can configure it to throw an exeption at any step that fails and it will goto the catch statment upon the first statement that fails.

What are you talking about?  Look at the code:

echo 'There was a problem';

echo "<br/> Crap, something went wrong <br/>";

They have done at least 2 tests and generated debugging messages.

Edited July 15, 2013 by AbraCadaver

[kicken]

What are you talking about?  Look at the code:

 

    $sql = "UPDATE catalogue
            SET title=:title,
                manufacturer=:manufacturer   
            WHERE id=:id";    
    $q = $conn->prepare($sql);

 

There is no test there, that is what he is talking about. The prepare will generally fail if the query is invalid, syntax wise at least. Considering he didn't get an PHP errors about trying to call bindParam on a non-object though, the prepare likely did not fail since syntactically it is fine, it just references an unknown object.

 

The reason there was no useful error reported is because these lines:

        print_r($conn->errorCode());  echo "<br/>";
        print_r($conn->errorInfo());  echo "<br/>";

Need to instead be

        print_r($q->errorCode());  echo "<br/>";
        print_r($q->errorInfo());  echo "<br/>";

If you prepare a query, then you have to get your error information from the resulting PDOStatement object. Errors relating to prepare'd queries are not available on the PDO connection object (except if the prepare itself has failed).

[AbraCadaver]

Sorry, that was attempted sarcasm 

 

Also, good catch on the other stuff.  I skimmed right past it seeing the other error messages.