CODEWITHSUNDEEP
phppdo
berzerk
berzerk

Reputation: 65

PHP PDO - can connect but query not working

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

Upvotes: 2

Views: 9067

Answers (3)

Robert Parham
Robert Parham

Reputation: 704

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

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

Upvotes: 1

outis
outis

Reputation: 77450

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.

Upvotes: 2

Francis Avila
Francis Avila

Reputation: 31651

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

Upvotes: 5

Related Questions