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;

}

Source : http://www.mustbebuilt.co.uk/php/select-statements-with-pdo/