AJAX vs PDO (messy script)

PHP
1

I learned how to convert PHP/MySQL queries to PDO just recently. Today, I started learning about AJAX. My first working example works fine, but the script uses an “old-fashioned” database query.

I started upgrading it but ran into several things that confuse me. Can anyone tell me how to clean it up? I pasted the script below, along with the changes I’ve made and the questions I have (numbered 1-5). Thanks.


/*
$dbhost = "localhost";
$dbuser = "(Username)";
$dbpass = "(Pasword)";
$dbname = "test";
//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());
*/

// 1. I replaced the above database connection with this...
$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,'(Username)','(Password)', $opt);

//////////

// Retrieve data from Query String
$age = $_GET['age'];
$sex = $_GET['sex'];
$wpm = $_GET['wpm'];

// Escape User Input to help prevent SQL Injection
// 2. I'm not sure what to do with the next three lines...
$age = mysql_real_escape_string($age);
$sex = mysql_real_escape_string($sex);
$wpm = mysql_real_escape_string($wpm);
//build query

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

// 3. I replaced the above query with this...
$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

// 4. What do I do with this line?
$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>Sex</th>";
$display_string .= "<th>WPM</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
// 5. I assume I can replace the next line with... while ($row = $stm->fetch())
while($row = mysql_fetch_array($qry_result)){
$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;
2

That SitePoint article describes and explains how to convert from using the old mysql_* extension over to PDO, including how to use prepared statements.

3

OK, the last piece to the puzzle just fell in place. Thanks.