用于PDO的AJAX升级（$ qry_result）

Question

I learned the basics of PDO queries just recently, and now I'm tackling AJAX. I have a working AJAX script from a tutorial, but it's written with "old-fashioned" DB queries, rather than PDO.

I've been trying to modify it, but it isn't working yet. I think I've fixed everything for this line, which I don't understand:

$qry_result = mysql_query($query) or die(mysql_error());

How do I modify that line?

This was the original query:

$query = "SELECT * FROM people_bios WHERE Gender = '$Gender'";

I pasted the entire script below - not the original, but my upgrade.

$dsn = "mysql:host=localhost;dbname=db_new;charset=utf8";
$opt = array(
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);

$pdo = new PDO($dsn,'UserMe','aBCs804hG24LME', $opt);

// Retrieve data from Query String
$age = $_GET['age'];
$Gender = $_GET['Gender'];
$wpm = $_GET['wpm'];
// Escape User Input to help prevent SQL Injection
// NOTE: Since I've converted this to PDO, I assume I can delete the next three lines:
// $age = mysql_real_escape_string($age);
// $Gender = mysql_real_escape_string($Gender);
// $wpm = mysql_real_escape_string($wpm);

$sql= "SELECT * FROM people_bios WHERE Gender = '$Gender'";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':Gender',$Gender,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

if(is_numeric($age))
$query .= " AND age <= $age";
if(is_numeric($wpm))
$query .= " AND wpm <= $wpm";

$qry_result = mysql_query($query) or die(mysql_error());

//Build Result String
$display_string = "<table>";
$display_string .= "<tr>";
$display_string .= "<th>Name</th>";
$display_string .= "<th>Age</th>";
$display_string .= "<th>Gender</th>";
$display_string .= "<th>WPM</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
while ($row = $stm->fetch())
{
$display_string .= "<tr>";
$display_string .= "<td>$row[Common]</td>";
$display_string .= "<td>$row[age]</td>";
$display_string .= "<td>$row[Gender]</td>";
$display_string .= "<td>$row[wpm]</td>";
$display_string .= "</tr>";

}
echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;

Answer 1

First off, this is the wrong way to use prepared queries:

$sql= "SELECT * FROM people_bios WHERE Gender = '$Gender'";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':Gender',$Gender,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

In the query line, you're still concatenating in the value, and are still wide open to SQL injection attacks. Change your query to this:

$sql= 'SELECT * FROM people_bios WHERE Gender = :Gender';

That way when you bind the parameter, there is something to actually bind it to.

Now, back to your original question on what this line does:

mysql_query($query) or die(mysql_error());

The result of mysql_query() will be false (or falsy? I don't remember) if there is an error in running your query. That's where the or die(mysql_error()) comes in. This half of the line won't execute if the result of mysql_query() is true. So in the event of an error, die() (halt everything) and display the string returned from mysql_error(), which is the last error the MySQL client ran into.

PDO makes things a bit more helpful. You have already enabled exceptions for PDO, so you can use a try/catch block:

try {
    $stmt->execute();
} catch (Exception $e) {
    print_r($e); // Do something more useful here, like log.
}

Note that you should avoid showing the database errors as is. At best, they provide some insight into your database backend and at worse important data like credentials could be revealed. Handle the error and show the user a more generic message. Log the full exception though!