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; }
Looking at the Search / Results Page section. Is this immune to SQL_injection (or any other dodgy) attacks?
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.
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
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 !
Thanks – glad you like it
Thanks i like it
Excellent article,i recently switch to pdo and found this article pretty useful
thanks , that was great
[…] This is an awesome tutorial about PDO, PDO Prepared Statements […]
thanks alot admin….ive been trying all week….
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
Very nice tutorial.
Nice Article about PDO
thanks a lot
It’s save my life!! <3 Thak you very much!
very nice article
good for beginner for PDO
keep it up
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