The Art of Data Retrieval
authors
table. Now what? A common task is to retrieve those entries and display them on the screen. That’s where the SELECT
statement comes through.A Simple Select Statement
For the selection to work, we need to:
- Connect to the database
- Pass our select query
- Retrieve the results, which is just the rows of data.
- Loop through each of the results and extract data for each row that was returned.
- Close the connection
Straight forward. Let’s isolate the selection process first. It starts off with the select statement.
$sql = "SELECT * FROM authors";
$results = $mysqli->query( $sql );
We’re going to pass our SELECT * FROM authors
, which just means “select ALL COLUMNS from the authors table.” This is going to returns all of our fields: The id
, first_name
, last_name
, updated_at
, and created_at
fields.
The SQL
statement is passed to our query
method within the mysqli
object and it returns all of the rows within the table with all of those fields. They’re stored in the $results
variable.
Next, we need to loop through each of those rows and extract data from it.
while( $row = $results->fetch_assoc() )
{
var_dump($row);
}
The $row = $results->fetch_assoc()
fetches the row of data from $results
and stores it in the $row
variable. We’re just going to var_dump
the data to see what it gave us.
array (size=6)
'id' => string '1' (length=1)
'first_name' => string 'Dino' (length=4)
'last_name' => string 'Cajic' (length=5)
'email' => string 'dino@example.com' (length=16)
'updated_at' => string '2022-11-03 21:56:16' (length=19)
'created_at' => string '2022-11-03 21:56:16' (length=19)
It gave us back an associative array. That means that we can use each of those keys to echo out specific data.
while( $row = $results->fetch_assoc() )
{
echo $row['first_name'];
}
All we have to do now is add a check to make sure that we actually received some data.
if ( $results->num_rows > 0 )
{
while( $row = $results->fetch_assoc() ) {
var_dump($row);
}
}
The $results->num_rows
returns the number of rows that the query extracted out of the table. We check to see if the number of rows is greater than 0. If it is, loop through each of the associative arrays and do something with the data.
<?php
$mysqli = new mysqli(
"localhost",
"admin",
"E9bIyRDwzidT",
"dino_test_db"
);
if ( $mysqli->connect_error ) {
die( $mysqli->connect_error );
}
$sql = "SELECT * FROM authors";
$results = $mysqli->query( $sql );
if ( $results->num_rows > 0 ) {
while( $row = $results->fetch_assoc() ) {
var_dump($row);
}
} else {
echo "No results";
}
$mysqli->close();
The WHERE clause
We don’t have to return the full list each time. We can return a specific author since each author has a specific user id
. As long as we know that author’s id, we can retrieve it.
SELECT * FROM authors WHERE id = '1';
We still want to retrieve all of the fields for the author, such as first_name
and last_name
, but we only want one author. The author’s id
is 1 in this instance.
Does anything else need to change? No, even though you could. You can keep the while
loop, or you can just call the $row = $results->fetch_assoc()
outside of the while
loop.
$sql = "SELECT * FROM authors WHERE id = 1";
$results = $mysqli->query( $sql );
if ( $results->num_rows > 0 )
{
$row = $results->fetch_assoc();
var_dump($row);
}
Modifications to the Author Class
We have an Author
class. Let’s think about the kind of select methods that we may need. Return a specific author based on an id
and return all authors.
Select All
There’s a couple of ways to do this. We can select all of the items, store them in an array, and return the array.
public function select_all()
{
$sql = "SELECT * FROM authors";
$db = new DB;
$mysqli = $db->_connection;
$results = $mysqli->query( $sql );
$authors = [];
if ( $results->num_rows > 0 ) {
while( $row = $results->fetch_assoc() ) {
$authors[] = $row;
}
}
$db->close();
return $authors;
}
There is of course a simpler way to do this. After all, returning the result as an array is pretty common practice. We can do this with the fetch_all
method.
$results->fetch_all(MYSQLI_ASSOC);
You can pass an argument, such as MYSQLI_ASSOC
, or not. The difference is that if you don’t, you won’t get any keys.
$results->fetch_all(MYSQLI_ASSOC);
array (size=7)
0 =>
array (size=6)
'id' => string '1' (length=1)
'first_name' => string 'Dino' (length=4)
'last_name' => string 'Cajic' (length=5)
'email' => string 'dino@example.com' (length=16)
'updated_at' => string '2022-11-03 21:56:16' (length=19)
'created_at' => string '2022-11-03 21:56:16' (length=19)
1 =>...
$results->fetch_all();
array (size=7)
0 =>
array (size=6)
0 => string '1' (length=1)
1 => string 'Dino' (length=4)
2 => string 'Cajic' (length=5)
3 => string 'dino@example.com' (length=16)
4 => string '2022-11-03 21:56:16' (length=19)
5 => string '2022-11-03 21:56:16' (length=19)
1 =>
public function select_all()
{
$sql = "SELECT * FROM authors";
$db = new DB;
$mysqli = $db->_connection;
$results = $mysqli->query( $sql );
$db->close();
return $results->fetch_all(MYSQLI_ASSOC);
}
Select Author
We’re going to pass the id
to our select
statement and return the result. We could keep it exactly the same as the select_all
and return the $results->fetch_all
associative array, but that doesn’t make sense. We’re expecting only one row, so we’ll return one row of data.
public function select( int $id )
{
$sql = "SELECT * FROM authors WHERE id = " . $id;
$db = new DB;
$mysqli = $db->_connection;
$results = $mysqli->query( $sql );
$row = $results->fetch_assoc();
$db->close();
return $row;
}
If there is no data, it will return null
.
Full Code
That’s it. Let’s see what our code looks like right now.
<?php
require_once("./Author.php");
use Authors\Author;
$author = new Author;
//var_dump($author->select_all());
var_dump($author->select(1));
<?php
namespace Authors;
require_once("DB.php");
use Database\DB;
class Author
{
public function insert( array $authors )
{
$sql = "INSERT INTO authors (first_name, last_name, email)
VALUES (?, ?, ?)";
$db = new DB;
$mysqli = $db->_connection;
$statement = $mysqli->prepare( $sql );
foreach( $authors as $author ) {
$statement->bind_param("sss", $author['first_name'], $author['last_name'], $author['email']);
$statement->execute();
}
$statement->close();
$db->close();
}
public function select_all()
{
$sql = "SELECT * FROM authors";
$db = new DB;
$mysqli = $db->_connection;
$results = $mysqli->query( $sql );
$db->close();
return $results->fetch_all(MYSQLI_ASSOC);
}
public function select( int $id )
{
$sql = "SELECT * FROM authors WHERE id = " . $id;
$db = new DB;
$mysqli = $db->_connection;
$results = $mysqli->query( $sql );
$row = $results->fetch_assoc();
$db->close();
return $row;
}
}
The DB
has no changes.
MYSQL PREPARED STATEMENTS FOR BULK DATA INSERTS
PHP – P87: MYSQL INSERT MULTIPLE WITH PREPARED STATEMENTS
What if you wanted to insert multiple records with prepared statements. Do you need to keep binding variables for each set of data that you’re inserting? No.
The Art of Data Retrieval
A common task is to retrieve those entries and display them on the screen. That’s where the MySQL SELECT statement comes through.
REVAMPING DATA WITH PRECISION
The next logical step in our MySQL progression is updating an existing resource. We’re going to introduce the differences between PUT and PATCH and put it to rest once and for all.