AJAX / PDO冲突（PHP / MySQL）

I got my first working AJAX script from a tutorial a couple days ago. The only problem is that it was written with an "old-fashioned" database query. I had to upgrade the code to make it compatible with PDO - but I didn't finish the job.

It works to a point; I can display a list of people based on gender. However, when I try to refine my list by age or wpm, it doesn't work.

This is the original query:

$query = "SELECT * FROM ajax_example WHERE sex = '$sex'";

So the obvious problem is the three $query variables left in my code:

$query .= " AND age <= $age";
$query .= " AND wpm <= $wpm";
echo "Query: " . $query . "<br />";

I replaced each instance of $query with $stmt, but it didn't work. So I replaced them with $row, $sql and even $Total, but nothing works. In most cases, it still works to a point - I can display every name in the table based on gender. But when I enter a value for age or wpm, it doesn't work (and some variables produce an error message).

Can anyone tell me what to replace $query with?

$age = $_GET['age'];
$sex = $_GET['sex'];
$wpm = $_GET['wpm'];

//build query
$sql= "SELECT * FROM ajax_example WHERE sex = :sex";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':sex',$sex,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

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

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

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

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

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

You have to build the entire query string in $sql first, before you prepare it, bind parameters, and execute.

//build query
$sql= "SELECT * FROM ajax_example WHERE sex = :sex";
if(is_numeric($age)) {
    $sql .= " AND age <= :age";
}
if(is_numeric($wpm)) {
    $sql .= " AND wpm <= :wpm";
}
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':sex',$sex,PDO::PARAM_STR);
if (is_numeric($age)) {
    $stmt->bindParam(':age', $age, PDO::PARAM_INT);
}
if(is_numeric($wpm)) {
    $stmt->bindParam(':wpm', $wpm, PDO::PARAM_INT);
}
$stmt->execute();

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

It is easier to use unnamed parameters for dynamic queries in PDO and passing them as an array in execute(). The statement will not be executed unless at least $_GET['sex'] parameter is passed. I have left in the echo of the derived statement and the dump of the array.

$age = isset($_GET['age']) ? $_GET['age'] : NULL;
$wpm = isset($_GET['wpm']) ? $_GET['wpm'] : NULL;
if(isset($_GET['sex'])){
    $params =array($_GET['sex']);
    //build query
    $sql= "SELECT * FROM xajax_example WHERE sex = ?";
    if(is_numeric($age)){
        $sql .= " AND age ?";
        array_push($params,$age); 
    }   
    if(is_numeric($wpm)){
        $sql .= " AND wpm ?";
        array_push($params,$wpm); 
    }
    echo $sql;//Test
    print_r($params);//Test
    $stmt = $dbh->prepare($sql);
    $stmt->execute($params);
}else{
    echo "No Sex";//Default message for no $_GET['sex']
}