0

This code prints out a list of column names. But I would expect it to print out the information stored in the UserName table for the user with the LoginName of JSmith.

 $loginname = "JSmith";
 $stmt = $dbh->prepare("SELECT * FROM UserName WHERE LoginName=:login_name");
 $stmt->bindValue(":login_name", $login_name, PDO::PARAM_STR);
 $stmt->execute();

echo "<table><tr>";
$result = $stmt->fetch(PDO::FETCH_ASSOC);
 print_r($result);
foreach ($result as $key => $val){
    echo "<th>" . $key . "</th>";   
}
echo "</tr>";
echo "</table>";

Why doesn't this return the row with the users name? Looking at echo gettype($result) shows me that they are strings and integers; not like each piece of the returned array is itself an array. I'm sure it's a simple thing, but any help is appreciated. Thanks!

1252748
  • 14,597
  • 32
  • 109
  • 229

1 Answers1

1

You're printing the keys, not the values. The keys are the column names, and the values are the corresponding column values.

print_r($result) shows that $stmt->fetch() returns an array with the information you expect, and the corresponding HTML simply doesn't reflect that.

http://php.net/manual/en/pdostatement.fetch.php#example-1018

Let's fix it.

$result = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);
foreach ($result as $key => $val){
    echo "<th>" . $key . "</th>";
    echo "<td>" . $val . "</td>";
}
echo "</tr>";
echo "</table>";
Matt Ball
  • 354,903
  • 100
  • 647
  • 710
  • Yes, that's true. However something still isn't right though. Because when I print `$val` I get the values from the first row of records. Not the record with user JSmith. Which is why I thought there was something wrong with loop. But it must be with the query. Is there some way to print out the query I've prepared so I can see if its syntax has been interpreted properly? – 1252748 Feb 05 '13 at 03:53
  • The query looks fine to me. You can run a similar query in your database shell to see what's actually in there: `SELECT * FROM UserName WHERE LoginName = 'JSmith'`. – Matt Ball Feb 05 '13 at 03:57
  • This post http://stackoverflow.com/a/2243512/1252748 recommends `echo $stmt->queryString` to see the query that was actually made. However it shows me `SELECT * FROM UserName WHERE LoginName=:login_name`. Even though I've placed it after the bindValue() line. Should I expect to see it like this, or should I expect to see it with the bound value JSmith? – 1252748 Feb 05 '13 at 03:58
  • You should expect to see it as you do. The parameter substitution is done by the database itself - that's the whole point of a prepared statement API such as PDO. – Matt Ball Feb 05 '13 at 03:59
  • yeah, it must be a problem with the query though; inputting it manually retrieves the correct row. – 1252748 Feb 05 '13 at 03:59
  • I just noticed: you're mixing `$loginname` and `$login_name`. Assuming your real code does that too, there's your problem. – Matt Ball Feb 05 '13 at 04:00