PHP PDO code to insert in MySQL db does not work

Question

I am having difficulties troubleshooting some simple PHP code to insert a record in a MySQL table.

This code entered directly into WAMP works fine:

INSERT INTO `users` (`userName`,`userEmail`) VALUES ('orange','orange@gmail.com')

This PHP code doesn't work:

<?php
    $dbHost="localhost";
    $dbName="project";
    $dbUser="admin";
    $dbPassword="abcd";
    $dbh=new PDO("mysql:host=$dbHost;dbName=$dbName", $dbUser, $dbPassword);
    print_r($dbh);
    echo "</br>";
    print_r($dbh->errorInfo());

    $query=$dbh->prepare("INSERT INTO users (userName, userEmail) VALUES (?,?)");
    echo "</br>";
    print_r(var_dump($query->errorInfo()));
    echo "</br>";
    print_r($query->errorCode());
    echo "</br>";
    print_r($dbh->errorInfo());

    $query->bindValue(1, 'apple');
    echo "</br>";
    print_r(var_dump($query->errorInfo()));
    echo "</br>";
    print_r($query->errorCode());
    echo "</br>";
    print_r($dbh->errorInfo());

    $query->bindValue(2, 'apple@gmail.com');
    echo "</br>";
    print_r(var_dump($query->errorInfo()));
    echo "</br>";
    print_r($query->errorCode());
    echo "</br>";
    print_r($dbh->errorInfo());

    $inserted=$query->execute(); //True if succesful, False if not.
    echo "</br>";
    print_r(var_dump($query->errorInfo()));
    echo "</br>";
    print_r($query->errorCode());
    echo "</br>";
    print_r($dbh->errorInfo());
    echo "</br>";
    if ($inserted){print_r("true");}else{print_r("false");};
?>

What I get when I execute the page is the following printout:

PDO Object ( )
Array ( [0] => [1] => [2] => )
array(3) { [0]=> string(0) "" [1]=> NULL [2]=> NULL }

Array ( [0] => 00000 [1] => [2] => )
array(3) { [0]=> string(0) "" [1]=> NULL [2]=> NULL }

Array ( [0] => 00000 [1] => [2] => )
array(3) { [0]=> string(0) "" [1]=> NULL [2]=> NULL }

Array ( [0] => 00000 [1] => [2] => )
array(3) { [0]=> string(5) "3D000" [1]=> int(1046) [2]=> string(20) "No database selected" }
3D000
Array ( [0] => 00000 [1] => [2] => )
false

The record isn't inserted in the db. What I am doing wrong? I am not sure what I should see in the print_r's, I am providing them as help for responders.

Thank you,

JDelage

edited - I added the print_r's recommended in the comments.

Here is what I see in WAMP:

Could you try running `print_r($dbh->errorInfo());` after the connection and the query? This should give some useful debugging information. — lonesomeday, Jun 09 '11 at 20:51
What you need to do here rather than all the print statements is to check the error status of `$query` do `var_dump($query->errorInfo());` and check `$query->errorCode()` — Michael Berkowski, Jun 09 '11 at 20:52
If your database name really is `project`, the only thing I see that seems strange is `mysql:host=$dbHost;dbName=$dbName`. I can´t imagine it´s the problem, but do you have the same result if you change it to `mysql:host=$dbHost;dbname=$dbName` (note that dbname is all lowercase)? — jeroen, Jun 09 '11 at 21:13
Weird. Maybe [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) helps get some more error messages out of PDO — Pekka, Jun 09 '11 at 21:34
Could you try to call `$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after connecting to the database? The script then will throw an exception immediately whenever an error occurs. You can then `try { .. }` and `catch (Exception $e) { echo $e->getMessage(); }` and see what happens. — Dan Soap, Jun 09 '11 at 21:39
Do you get the same results when using named parameters? Try setting your PDO connection to throw exceptions to see if you get anything then. — Nev Stokes, Jun 09 '11 at 21:40
All - thank you. For some reason, replacing "INSERT INTO users..." with "INSERT INTO **project.users**..." works. I am still not clear on what causes the problem.... — JDelage, Jun 09 '11 at 22:52
Entered follow up question here: http://stackoverflow.com/questions/6300446/write-php-pdo-queries-as-dbname-tablename-as-opposed-to-tablename-why — JDelage, Jun 09 '11 at 23:30
@jeroen - I must have done something wrong previously. I tried that again in another example, and that worked fine. If you enter that as an answer, I'll make it my accepted one. — JDelage, Jun 10 '11 at 00:17

score 4 · Accepted Answer · answered Jun 09 '11 at 22:31

The error message seems to indicate that you've connected to the DB fine, but that the project database hasn't been selected.

To be sure it's trying to correct with the right DSN, I'd try changing the connection string to contain values directly, rather than variables, i.e.:

'mysql:host=localhost;dbname=project'

This shouldn't make a difference, but it's worth checking.

If that doesn't work, and since you appear to be able to connect to MySQL, a workaround could be to include the database name as part of the query. So your query above would become:

$query=$dbh->prepare("INSERT INTO project.users (userName, userEmail) VALUES (?,?)");

Replacing "INSERT INTO users..." with "INSERT INTO project.users..." works. Thank you - but I have to say I still don't understand the problem.... — JDelage, Jun 09 '11 at 22:50

score 1 · Answer 2 · answered Jun 10 '11 at 01:23

1

Very strange problem, it seems that you need to enter dbname in lowercase letters for it to correctly connect to the database.

So it should be:

mysql:host=$dbHost;dbname=$dbName

answered Jun 10 '11 at 01:23

jeroen

91,079
21
114
132

PHP PDO code to insert in MySQL db does not work

2 Answers2

Linked