PDO not returning results from SELECT on more than 1 column

The server is running PHP 5.2.8. PDO has mysql 5.1.30 drivers installed.

Alright, so I开发者_如何学Go am trying to figure out some PDO ( and this is just killing me. When I run the code below, I get the expected results, no problem.

However, whenever I try to add more than one column (or *) to the SELECT, there is no reply from the query - no results whatsoever. I have tried everything - I know it must be something simple. Any suggestions as to why more than one column fails to return any rows?

$hostname = "localhost";
$dbname = "dbname";
$username = "username";
$password = "password";
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';

    /*** The SQL SELECT statement ***/
    $sql = "SELECT LastName FROM staff";
    foreach ($dbh->query($sql) as $row) {
        echo $row['LastName'] . '<br />';
    }

    /*** close the database connection ***/
    $dbh = null;
} catch(PDOException $e) {
    echo $e->getMessage();
}

Again, if I try to add columns in the statement stored in $sql to anything other than a single column, I get bupkis. For example:

SELECT FirstName, LastName FROM staff

returns zero results. Both columns exist - if requested separately, they return expected results. When combined, the query takes quite some time, then returns nothing.

No exception is caught by the catch block.

I think you have a number of issues here, mostly in your code that handles reading the values returned by the query. I have taken the liberty of changing a few things and rewriting this to use prepare statements, which is a function that PDO provides that you should take advantage of.

On prepare statements:
Why use them: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
PHP PDO doc: http://php.net/manual/en/pdo.prepare.php

Here is the core code:

try {
  //open database
  $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);  

  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

  //define sql query
  $sql = "SELECT LastName FROM staff";

  //prepare the query for execution 
  $qresult = $dbh->prepare($sql);

  //insert code below to handle parameters to the sql query here

  //execute the query
  $qresult->execute();

  //fetch the results
  foreach ($qresult->fetch(PDO::FETCH_ASSOC) as $row)
    {
    echo $row['LastName'] . '<br />';
    }


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

$qresult = null;  //close the result set
$dbh = null;  //close the database          

Note, that I have replaced the call to query() with a couple of lines that call prepare() then execute(). You can then easily insert the following lines in between the prepare() and execute() calls to handle passing parameterized queries. This will help reduce chances of sql injection.

I have also changed the way you are accessing the retirned valued by specifying that I want them returned as and associative array, PDO::FETCH_ASSOC. This will get you a result set that you can iterate through like you would have using the old mysql interfaces.

If your query was a parameterized query like:

$sql="SELECT LastName FROM staff WHERE LastName=':lastname'";

where :lastname is the parameter.

Here is the code you would insert at the comment to handle this, (this code will handle multiple parameters. Simply add additional elements to the $param array):

//bind parameters to the prepared statement
$param = array(':lastname'=>'Jones');
foreach ($param as $key => $value) {
  $qresult->bindValue($key,$value);
  }

Make sure you separate the columns in the SELECT with a comma (space on either side of the comma is okay, but not required). If you want to select all columns, have only a * with no other characters.