Select Statements with PDO

Creating a Simple SELECT Query

Once you have created a PDO you can begin querying the database. There are two ways queries can be created – firstly through the query() method and secondly through the prepare() method.

The prepare() method allows for prepare statements with all the security benefits that entails.

However, for our first simple SELECT we'll use the query() method as no parameter are been sent into it. To keep things really simple we'll also only extract one record.

require_once('includes/conn.inc.php'); 
$sql= "SELECT filmName, filmDescription FROM movies WHERE filmID = 10";
$stmt = $pdo->query($sql); 
$row =$stmt->fetchObject();
echo $row->filmName;
echo $row->filmDescription;

The above uses the PDO 'object' style syntax by calling the PDO fetchObject() method. Each field value retrieved from the database can be referenced by the $row variable using PHP object arrow syntax (->)

If you prefer you can use associate array syntax which you may be familiar with from older database connection techniques in PHP.

To use an associate array we would change the above to:

require_once('includes/conn.inc.php');  
$sql= "SELECT filmName, filmDescription FROM movies WHERE filmID = 10"; 
$stmt = $pdo->query($sql); 
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['filmName'];
echo $row[filmDescription];

In the above the values are retrieved using the square brackets of the associate array.

Queries that extract multiple rows

The above queries use the primary key of our test data table to only extract one record. If we need more that one record then we could do the following:

require_once('includes/conn.inc.php'); 
$sql = "SELECT * FROM movies";

Here we use the query() method of the PDO to create an array which is looped to extract the data as follows:

foreach($pdo->query($sql) as $row){
	echo "<li>{$row['filmName']}</li>";
}

This is a cheap and cheerful approach for various reasons - for example it makes it more difficult to ascertain how many records have been retrieved.

Using prepare()

The PDO prepare() and execute() methods are considered a more secure approach especially with queries using values from forms or querystrings. You will be familiar with this approach if you have read the section on mysqli.

$sql= "SELECT filmID, filmName, filmDescription, filmImage, filmPrice, filmReview FROM movies WHERE filmID = :filmID"; 
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $filmID, PDO::PARAM_INT); 
$stmt->execute();

Look on line 1 above (you may need to scroll to the right). Notice the use of the : (colon) in the SQL statement before :filmID. This equates to the parameter created and bound to that value via the PDO bindParam() method. The colon performs the same role as using the ? (question mark) position placeholders with mysqli.

The bindParam() takes three values:

Syntax
Parameter name, Parameter value, Parameter type

In the example above an integer value was used and such the parameter type was PDO::PARAM_INT. The following binds a string parameter.

$stmt->bindParam(':country', $country, PDO::PARAM_STR); 

Note: For numbers/floats and dates use the PDO::PARAM_STR binding type.

The Array Approach

Once executed the values from the query can be retrieved a number of ways. An associate array can be retrieved using the fetchAll() method.

require_once('includes/conn.inc.php'); 
$sql= "SELECT * FROM movies";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

The $result variable contains an array of all the result's rows and can be looped:

foreach($result as $row){
	echo "<li>{$row['filmName']}</li>";
}

The total number of records returned could be calculated the PHP count() method as $result is an array.

$total = count($result);

The Object Approach

Alternatively we could achieve the same as the above but with using Object syntax. First build and execute the query.

require_once('includes/conn.inc.php'); 
$sql= "SELECT * FROM movies";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$total = $stmt->rowCount();

The above uses the rowCount() method of the PDO statement to give us a record count.

To output the values held in the PDO statement we could do the following:

while ($row = $stmt->fetchObject()) {
	echo "<li>{$row->filmName}</li>";
}

If your SQL only extracts only one value then no need for the while loop.

$sql= "SELECT filmID, filmName, filmDescription, filmImage, filmPrice, filmReview FROM movies WHERE filmID = :filmID"; 
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $filmID, PDO::PARAM_INT); 
$stmt->execute();
$obj = $stmt->fetchObject();
echo $obj->filmName;

Notice the use of the object arrow (->) syntax to reference the values.

Search / Results Page

To build a simple search / results page we would need a user input - in this example a GET from a form. If you would like to use the SQL LIKE statement and its wildcards, then we need to concatenate % (percentages) to the variable to be used in the bindParam() method. Note that we will not need to put the variable in quotes in the SQL, as the bindParam() method and the use of PDO::PARAM_STR to declare the data type will resolve that for us.

if(isset($_GET['filmName'])){
	$filmName = "%".$_GET['filmName']."%";
}else{
	$filmName = "none";
}
$sql= "SELECT * FROM movies WHERE filmName LIKE :filmName";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $filmName, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();

... and then to output.

while ($row = $stmt->fetchObject()) {
   echo $row->filmName;
}

17 Comments

  1. James
    Jul 15, 2013 @ 16:17:34

    Looking at the Search / Results Page section. Is this immune to SQL_injection (or any other dodgy) attacks?

    Reply

    • admin
      Jul 15, 2013 @ 17:53:15

      I would say prepare() statements are certainly the way to go. As well as using prepare statements I would also advise using appropriate validation. Unfortunately hackers are a clever bunch but this approach will put plenty of barriers in their way.

      Reply

      • David
        Dec 01, 2013 @ 00:54:03

        Hi Martin would you say prepare or execute is the best way to go? Im just starting using PDO and i want to get into good habits from day one !

        Thanks

        Reply

  2. David
    Dec 01, 2013 @ 00:48:26

    Hi just wanted to say awesome guide ! I have been trying to get my head round this today and move onto more secure code.

    Thanks for taking the time to write these guides !

    Reply

    • admin
      Dec 02, 2013 @ 09:26:20

      Thanks - glad you like it

      Reply

  3. Shiju
    Jul 02, 2014 @ 09:55:10

    Thanks i like it

    Reply

  4. Panagiotis
    Dec 02, 2014 @ 23:03:42

    Excellent article,i recently switch to pdo and found this article pretty useful

    Reply

  5. hamid
    Jan 07, 2015 @ 12:39:50

    thanks , that was great

    Reply

  6. SMS Blaster | Nick Witmer Portfolio
    Jun 10, 2015 @ 20:02:52

    […] This is an awesome tutorial about PDO, PDO Prepared Statements […]

    Reply

  7. vincent
    Jul 28, 2015 @ 19:42:59

    thanks alot admin....ive been trying all week....

    Reply

  8. sudhir
    Oct 08, 2015 @ 13:05:45

    my query is select * from table where id in (1,5,16,8)
    where i am trying with PDO (select * table where id in (?));
    and in array i am putting like array(',5,16,8')

    but still i am not getting any record

    Reply

  9. James Miller
    Dec 07, 2015 @ 20:06:28

    Very nice tutorial.

    Reply

  10. Services 4 Web
    Dec 14, 2015 @ 18:25:21

    Nice Article about PDO

    Reply

  11. sofia
    Jan 21, 2016 @ 20:25:37

    thanks a lot

    Reply

  12. Leonnardo
    Mar 02, 2016 @ 13:12:36

    It's save my life!! <3 Thak you very much!

    Reply

  13. Gerone Coder
    Jun 08, 2016 @ 04:17:11

    very nice article
    good for beginner for PDO
    keep it up

    Reply

  14. John Sno
    Feb 20, 2017 @ 13:45:49

    Hi,

    Just want to let you know that this website is the most comprehensive php learning site I have encountered.
    And I hope it will always remain online. I created a complete schoolapp with knowledge mostly from this website. Still had to do some research for more advanced principles but for starters this is php-heaven.

    Good luck too you and thanks,
    John Sno

    Reply

Leave a Reply

*