How to set my query for a PDO prepared statement

Question

I have built a PDO connection and query, now I need it to be safe from SQL Injection

Here is my code

User Input

<?php $search=$_GET["Search"];?>

SQL that querys DB

<?php
// Issue the query
$Recordset1 = $dbh->query("SELECT * FROM catelogue 
WHERE catelogue.TITLE LIKE '$search'");
$Recordset1->setFetchMode(PDO::FETCH_ASSOC);
?>

Fetch The rows

<?php $row_Recordset1 = $Recordset1-> fetch(); ?>

After this there is a table with a do-while loop to display everything that was returned

How do I make a prepared statement for my query?

Thanks

EDIT:

Ok That last bit of code that DavdRew posted helped to get my search working again. Now I have 2 new problems.

Problem 1: After doing a few querys I get this message

mysql_pconnect() [function.mysql-pconnect]: MySQL server has gone away

It still shows the rest of my page with what I searched for. How is this fixed?

Then Problem 2:

With every search the first record returned is empty, a blank record. It never did this before, Why is it doing it now?

Many Thanks DavdRew

EDIT: ADDED MORE CODE

THIS IS THE ENTIRE PDO CODE

<?php 

$hostname_EchosPDO = "localhost";
$username_EchosPDO = "echos";
$password_EchosPDO = "echos";
$database_EchosPDO = "full catelogue";
$connStr = 'mysql:host=localhost;dbname=full catelogue';

try {
    $dbh = new PDO($connStr, $username_EchosPDO, $password_EchosPDO);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    } 
?>


<?php


$q = $dbh->prepare("SELECT * FROM catelogue WHERE catelogue.TITLE LIKE ?"); // prepare statement
if ($q->execute(array('sharpay%'))) // execute wirh passed params array($search)
{
    $row_Recordset1 = $q->fetchAll(PDO::FETCH_ASSOC); // store fetched result into $rows;

}
else
{
    $error = $dbh->errorInfo();
    throw new Exception("SQLSTATE: {$error[0]}. {$error[1]} {$error[2]}");
}


?>
<?php /* $row_Recordset1 = $Recordset1-> fetch(); */ ?>


<?php do { ?>
<table width="800" border="0">
          <tr>
          <form action="/Echos Online/Detail.php" method="get"><input value='<?php echo $row_Recordset1['CAT NO.']; ?>' name="detail" type="hidden"/><td width='100' rowspan='4'><input type="image" img src="<?php echo $row_Recordset1['IMAGE PATH']; ?>" width="<?php if ($row_Recordset1['FORMAT']=='DVD') {echo "70";} else if ($row_Recordset1['FORMAT']=='DVD+CD') {echo "70";} else if($row_Recordset1['FORMAT']=='BLURAY+CD') {echo "81";}else if($row_Recordset1['FORMAT']=='BLURAY+DVD') {echo "81";} else if($row_Recordset1['FORMAT']=='BLURAY') {echo "81";} else {echo "100";} ?>" height="100"></td></form>
            <td width="100">Artist:</td>
            <td><?php echo $row_Recordset1['ARTIST']; ?></td>
          </tr>
          <tr>
            <td width="100">Title</td>
            <td><?php echo $row_Recordset1['TITLE']; ?></td>
          </tr>
          <tr>
            <td width="100">Format</td>
            <td><?php echo $row_Recordset1['FORMAT']; ?></td>
          </tr>
          <tr>
            <td width="100">Cat. No.</td>
            <td><?php echo $row_Recordset1['CAT NO.']; ?></td>
          </tr>
          <hr background-color="#e4a566" color="#e4a566"; width="100%"/>
  <?php }  while ($row_Recordset1 = $q-> fetch());  ?>
</table>

Honestly now I'm at the piont of going back to preg_replace and mysql_real_escape_string

Thanks for the help

Answer 1

Like this:

$q = $this->pdo->prepare($query);

$data = array();
if ($q->execute($params)) // params is the array of values for each '?' in your prepared statement.
    $data = $q->fetchAll($fetch);
else
{
    $error = $this->pdo->errorInfo();
    throw new \Exception("SQLSTATE: {$error[0]}. {$error[1]} {$error[2]}");
}

return $data;

Keep in mind that WHERE IN works bad, you need to put as much ? into you imploded by , and wrapped with IN ( from left and ) right. Example:

$statement = $pdo->prepare('SELECT * FROM my_table AS m WHERE m.id = ?');
if ($statement->execute(array(24))) // here you can pass values too.
     $data = $q->fetchAll(\PDO::FETCH_ASSOC);
else
     exit('Shit happens');

This search for record with id = 24;

For your code, that would be:

$q = $dbh->prepare("SELECT * FROM catelogue WHERE catelogue.TITLE LIKE ?"); // prepare statement
if ($q->execute(array($search))) // execute wirh passed params array($search)
{
    $rows = $q->fetchAll(PDO::FETCH_ASSOC); // store fetched result into $rows;
}
else
{
    $error = $dbh->errorInfo();
    throw new Exception("SQLSTATE: {$error[0]}. {$error[1]} {$error[2]}");
}

Output:

<table width="800" border="0">
    <?php foreach ($rows as $row): ?>
        <tr>
            <form action="/Echos Online/Detail.php" method="get"><input value='<?php echo $row['CAT NO.']; ?>' name="detail" type="hidden"/><td width='100' rowspan='4'><input type="image" img src="<?php echo $row['IMAGE PATH']; ?>" width="<?php if ($row['FORMAT']=='DVD') {echo "70";} else if ($row['FORMAT']=='DVD+CD') {echo "70";} else if($row['FORMAT']=='BLURAY+CD') {echo "81";}else if($row['FORMAT']=='BLURAY+DVD') {echo "81";} else if($row['FORMAT']=='BLURAY') {echo "81";} else {echo "100";} ?>" height="100"></td></form>
            <td width="100">Artist:</td>
            <td><?php echo $row['ARTIST']; ?></td>
        </tr>
        <tr>
            <td width="100">Title</td>
            <td><?php echo $row['TITLE']; ?></td>
        </tr>
        <tr>
            <td width="100">Format</td>
            <td><?php echo $row['FORMAT']; ?></td>
        </tr>
        <tr>
            <td width="100">Cat. No.</td>
            <td><?php echo $row['CAT NO.']; ?></td>
        </tr>
        <hr background-color="#e4a566" color="#e4a566"; width="100%"/>
    <?php endforeach; ?>
</table>

Answer 2

$pdo = new PDO('mysql:host=localhost;dbname=mydb', $username, $password);

$stmt = $pdo->prepare('SELECT * FROM catelogue WHERE catelogue.TITLE LIKE :search');
$stmt->bindValue('search', $search);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

OR 

$stmt = $pdo->prepare('SELECT * FROM catelogue WHERE catelogue.TITLE LIKE :search');
$stmt->execute(array('search' => $search);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

note that you probably want to have your $search string wrapped with wildcard characters, like

$stmt->bindValue('search', '%'.$search.'%');

also note that searching using like wont use index if you have wildcard on left side of like criteria

$stmt->bindValue('search', '%'.$search.'%'); //will not use index
$stmt->bindValue('search', $search.'%'); //will use index