MySQL Select

The Art of Data Retrieval

We’ve gone through the insertion process and have a few records inserted inside of our 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 idfirst_namelast_nameupdated_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.

https://github.com/dinocajic/php-youtube-tutorials

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.

MySQL Select

The Art of Data Retrieval

PHP – P88: mysql select

A common task is to retrieve those entries and display them on the screen. That’s where the MySQL SELECT statement comes through.

MySQL Update

REVAMPING DATA WITH PRECISION

PHP – P89: mysql update

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.

Leave a Reply