Php pdo select from

PDO: Selecting rows from MySQL.

In this tutorial, I will show you how to select rows from a MySQL database using PHP’s PDO object. Note that this article is aimed at beginners who are looking for a “cheat sheet” of sorts.

PS: If you’re a “noob”, then you will need to know how to connect to a MySQL database before using any of the code below. You can’t select data from a database if you’re not connected to it.

In my last tutorial, I showed you how to insert rows into a MySQL database using the PDO object. I also used a set up a pretty simple table structure in order to help illustrate my code examples:

CREATE TABLE IF NOT EXISTS `cars` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `make` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `model` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

There are three main ways to select rows from MySQL with the PDO object:

• Use the PDO::query function – This is suitable for queries that do not require external data (i.e. data that has come from the client / user). You can use this when SQL injection is not a concern.
• Use the PDOStatement::fetchAll function – This is suitable for SELECT queries that will return multiple rows.
• Use the PDOStatement::fetch function – This is suitable for SELECT queries that will only return one row at a time.

PDO SELECT queries.

In scenarios where your SELECT queries are not susceptible to SQL injections, you can use the PDO query function like so:

//Selecting multiple rows from a MySQL database using the PDO::query function. $sql = "SELECT `id`, `make`, `model` FROM `cars` ORDER BY make ASC"; foreach($pdo->query($sql, PDO::FETCH_ASSOC) as $row)< echo 'Make: ' . $row['make'] . '
'; echo 'Model: ' . $row['model'] . '
'; >

Note that this function should NOT be used whenever external variables are being used! If you are using variables that have come from the client (a form variable or a GET variable, for example), then you should be using one of the methods below.

Selecting multiple rows with PDO.

If you’re looking to SELECT multiple rows from MySQL using a prepared statement, then you can do it like so:

//Selecting multiple rows using prepared statements. $sql = "SELECT `id`, `make`, `model` FROM `cars` WHERE `make` = :make"; //Prepare our SELECT statement. $statement = $pdo->prepare($sql); //The make that we are looking for. $make = 'Nissan'; //Bind our value to the paramater :make. $statement->bindValue(':make', $make); //Execute our statement. $statement->execute(); //Fetch our rows. Array (empty if no rows). False on failure. $rows = $statement->fetchAll(PDO::FETCH_ASSOC); //Loop through the $rows array. foreach($rows as $row)< echo 'Make: ' . $row['make'] . '
'; echo 'Model: ' . $row['model'] . '
'; >

Step-by-step guide to the code above:

1. We constructed our SQL SELECT statement. Notice how we created a placeholder / parameter called :make.
2. We “prepared” our SELECT statement.
3. The variable $make is a string that contains the car make that we want to select from our cars table.
4. We bind the $make variable to our :make placeholder.
5. We executed the prepared statement. It is at this stage that the SQL SELECT query is actually run.
6. We fetched our selected table rows from our PDO Statement using the PDOStatement::fetchAll function. We also provided an optional parameter called PDO::FETCH_ASSOC, which tells the function to return an associative array.
7. We looped through our data.

Selecting a single row with the PDO object.

In this example, we’re going to use a prepared statement to select a single row from a MySQL database:

//Selecting a single row! $sql = «SELECT `id`, `make`, `model` FROM `cars` WHERE `id` = :id»; //Prepare our SELECT statement. $statement = $pdo->prepare($sql); //The Primary Key of the row that we want to select. $id = 1; //Bind our value to the paramater :id. $statement->bindValue(‘:id’, $id); //Execute our SELECT statement. $statement->execute(); //Fetch the row. $row = $statement->fetch(PDO::FETCH_ASSOC); //If $row is FALSE, then no row was returned. if($row === false) < echo $id . ' not found!'; >else

As you can see, this code snippet is pretty similar to the “Fetch All” statement that we used in the example above. However, this time, instead of using “fetch all” to select multiple rows, we’re using “fetch” to select one row. Note that this function will return a boolean FALSE value if no values are found!

Related recommended reading:

Источник

PDO Querying Data

Summary: in this tutorial, you will learn to query data from a table using PHP PDO using the query() method of the PDO object and a prepared statement.

To select data from a table using PDO, you can use:

When a query doesn’t have any parameters, you can use the query() method. For example:

SELECT * FROM publishers;Code language: SQL (Structured Query Language) (sql)

However, if a query accepts one or more parameters, you should use a prepared statement for security reasons.

Using the query() method to select data from a table

To query data from a table using the query() method, you follow these steps:

The query() method returns a PDOStatement object. If an error occurs, the query() method returns false .

The following illustrates how to query all rows from the publishers table in the bookdb database:

 $pdo = require 'connect.php'; $sql = 'SELECT publisher_id, name FROM publishers'; $statement = $pdo->query($sql); // get all publishers $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); if ($publishers) < // show the publishers foreach ($publishers as $publisher) < echo $publisher['name'] . '
'; > > Code language: HTML, XML (xml)

McGraw-Hill Education Penguin/Random House Hachette Book Group Harper Collins Simon and Schuster

When you use the PDO::FETCH_ASSOC mode, the PDOStatement returns an associative array of elements in which the key of each element is the column name of the result set.

First, create a database connection to the bookdb database:

$pdo = require 'connect.php';Code language: PHP (php)

Second, define an SQL SELECT statement to select all rows from publishers table:

$sql = 'SELECT publisher_id, name FROM publishers';Code language: PHP (php)

Third, run the query by calling the query() method of the PDO object:

$statement = $pdo->query($sql);Code language: PHP (php)

Fourth, fetch all data from the result set:

$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

The fetchAll() method with the PDO::FETCH_ASSOC option returns an associative array of data where:

• The keys are the names that appear on the select list
• and the values are the data rows in the result set.

Finally, iterate over the result set and show the array’s element:

 // show the publishers if ($publishers) < foreach ($publishers as $publisher) < echo $publisher['name'] . '
'; > >Code language: HTML, XML (xml)

Using a prepared statement to query data

The following example illustrates how to use a prepared statement to query data from a table:

 $publisher_id = 1; // connect to the database and select the publisher $pdo = require 'connect.php'; $sql = 'SELECT publisher_id, name FROM publishers WHERE publisher_id = :publisher_id'; $statement = $pdo->prepare($sql); $statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT); $statement->execute(); $publisher = $statement->fetch(PDO::FETCH_ASSOC); if ($publisher) < echo $publisher['publisher_id'] . '.' . $publisher['name']; > else < echo "The publisher with id $publisher_id was not found."; > Code language: HTML, XML (xml)

First, define a publisher id. In practice, you may get it from the query string:

 $publisher_id = 1;Code language: HTML, XML (xml)

Second, use the connect.php to connect to the bookdb database and return a new instance of the PDO object:

$pdo = require 'connect.php';Code language: PHP (php)

Third, construct an SQL SELECT statement with a named placeholder ( :publisher_id )

$sql = 'SELECT publisher_id, name FROM publishers WHERE publisher_id = :publisher_id';Code language: PHP (php)

Fourth, bind the value of the id to the prepared statement:

$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);Code language: PHP (php)

Fifth, execute the prepared statement:

$statement->execute();Code language: PHP (php)

Sixth, fetch a row from the result set into an associative array:

$publisher = $statement->fetch(PDO::FETCH_ASSOC);Code language: PHP (php)

Finally, show the publisher information or an error message:

if ($publisher) < echo $publisher['publisher_id'] . '.' . $publisher['name']; > else < echo "The publisher with id $publisher_id was not found."; >Code language: PHP (php)

Summary

• Use the query() method of an PDO object to execute a SELECT statement to query data from one or more tables.

Источник