CODEWITHSUNDEEP
phppdo
Nick
Nick

Reputation: 25

Creating a search script with PHP PDO

I've spent quite a bit of time researching how to do this but just can't get it working :(

I have a simple 'contacts' MySQL database set up with table (id, first_name, last_name, email) containing a few records.

I want to allow users to pick their search terms, i.e. search 'first_name' column for a particular name, or search 'last_name' column etc.

I have a simple HTML form that sends user data to script.

Anyhow, my PHP script is shown below. When I run the PHP script I don't get any errors ... it's just that I don't get anything ... nothing happens.

I would greatly appreciate any help you can offer.

<?php
try{
    $conn = new PDO('mysql:host=localhost; dbname=mydbname', 'user',   'pass');

}catch (PDOException $e){
    echo "Cannot connect to database";
    exit;
}

  $sql = 'SELECT first_name, last_name, email FROM mycontacts WHERE :searchtype LIKE   :searchterm';
  $searchtype = '%'. $_POST['searchtype'] .'%';
  $searchterm = '%'. $_POST['searchterm'] .'%';
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':searchtype', $searchtype, PDO::PARAM_STR);
  $stmt->bindParam(':searchterm', $searchterm, PDO::PARAM_STR);
  $stmt->bindColumn(1, $first_name);
  $stmt->bindColumn(2, $last_name);
  $stmt->bindColumn(3, $email);

   $stmt->execute();
  $numRows = $stmt->rowCount();

  echo "<p>Number of rows:".$numRows."</p>";

  if (isset($numRows)) { ?>
  <table>
  <tr>
  <th scope="col">First Name</th>
  <th scope="col">Last Name</th>
  <th scope="col">Email</th>
  </tr>
  <?php while ($stmt->fetch()) { ?>
  <tr>
   <td><?php echo $first_name; ?></td>
   <td><?php echo $last_name; ?></td>
   <td><?php echo $email; ?></td>
   </tr>
   <?php } ?>
   </table>
   <?php } ?>

Upvotes: 1

Views: 3677

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157885

You need to implement a white list to pick from allowed fields.
Also note that search have to be implemented using GET method, not POST

$terms = array("last_name","first_name","etc");
if (in_array($_GET['searchtype'], $terms) {
    $field = $_GET['searchtype'];
}
$sql = "SELECT * FROM mycontacts WHERE `$field` LIKE :searchterm";

Upvotes: 3

Related Questions