PHP PDO - can connect but query not working

Question

I want to wean myself from the teat of the old mysql extension and am just doing a test PDO connection and simple query on a table in a database. I seem to be able to connect, ('connection successful' echoes out) but that's where the good times end. I have spent way too much time now just trying to get started with PDO.

<?php
$host = 'localhost';
$port = '3306'; 
$username = 'user';
$password = 'blabla';
$database = 'workslist';

try {
    $db = new PDO("mysql:host=$host; port = $port; dbname = $database", $username, $password);
    echo 'connection successful<br />';

    $query = 'SELECT * FROM main';
    $statement = $db->prepare($query);
    $statement->execute();
    $results = $statement->fetchAll();
    $statement->closeCursor();      

    foreach($results as $r){
            echo $r['work'] . '<br />';
    }

} catch (PDOException $e) {
    echo 'Error!: ' . $e->getMessage() . '<br />';
    die();
} 
?>

Is there anything wrong with the above?

The database name is 'workslist', the table name is 'main', and 'work' is one of the columns in that table. The PHP version I'm using is 5.3.4, and am using wamp on win7. I ran phpinfo() and under the PDO heading, the PDO drivers mysql, sqlite are enabled. To be sure the database and table actually exist I've tried it with MySQL and can return rows with the old mysql_fetch_array() method. I've checked the php.ini file to make sure the "extension=php_pdo..." lines are all uncommented.

cheers

Answer 1

This should work.

Please double-check that you actually have a table named "main" in that database.

Note that this error will not be discovered by PDO until you execute() the query, and if there is a problem with your query the default behavior is to return an empty result, not throw an exception.

To make PDO noisier, add the PDO::ERRMODE_EXCEPTION option when constructing PDO:

$db = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password, 
              array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION)
);

Now check if you see the following:

Error!: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'workslist.main' doesn't exist

Answer 2

The particular problem is that spaces aren't allowed in the DSN string. With the spaces, the "dbname" directive isn't processed, so there's no default database. Besides removing the spaces, explicitly specifying the database in the statement can help prevent this sort of problem:

SELECT `work` FROM `workslist`.`main`

That way, should there not be a default database for some reason, the query will still succeed.

Answer 3

PDO won't throw an error unless you configure it to:

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );