facebook youtube pinterest twitter reddit whatsapp instagram

PDO: Querying Data From Database - PHP MariaDB/MySQL

By DevsrealmGuy
|
Published: 21 NOV, 2020
|
Updated: 6 SEP, 2022
PDO: Querying Data From Database - PHP MariaDB/MySQL

In this guide, you'll learn how to query data from a MariaDB database using PHP PDO, plus an understanding of how the nuances work.

So, here are the details...

I have a table cmusicin marn_melodicdatabase, in short, here is how the table looks if I use the SELECT statement directly from mariadb client:

MariaDB [marn_melodic]> SELECT * FROM cmusic;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood  | artiste         | title                   | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
|        1 |      1 | Dance | Avicii          | Silhouette              |        2 |           1 | NULL        |
|        2 |      1 | NULL  | Beautiful Nubia | Higher Steps            |        3 |           3 | NULL        |
|        3 |      1 | NULL  | Beautiful Nubia | Tables Turn             |        3 |           3 | NULL        |
|        4 |      1 | Dance | Fela            | Observation Is No Crime |        4 |           4 | NULL        |
|        5 |      0 | Dance | Avicii          | Levels                  |        2 |           1 | NULL        |
|        6 |      0 | Dance | Fela            | Colonial Mentality      |        4 |           4 | NULL        |
|        7 |      1 | NULL  | Asa             | Jailer                  |        1 |           2 | NULL        |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+

Suppose I want to SELECT FROM cmusic table where artiste is Beautiful Nubia, I would do something like so:

Note: If you don't know mariadb, I have tons of guides here: Mariadb

MariaDB [marn_melodic]> SELECT * FROM cmusic WHERE artiste = 'Beautiful Nubia';
+----------+--------+------+-----------------+--------------+----------+-------------+-------------+
| music_id | status | mood | artiste         | title        | genre_id | producer_id | description |
+----------+--------+------+-----------------+--------------+----------+-------------+-------------+
|        2 |      1 | NULL | Beautiful Nubia | Higher Steps |        3 |           3 | NULL        |
|        3 |      1 | NULL | Beautiful Nubia | Tables Turn  |        3 |           3 | NULL        |
+----------+--------+------+-----------------+--------------+----------+-------------+-------------+

Easy enough in the client right, but how do we do all this in our PHP script. PDO to the rescue. Establish a connection to your database server using:

<?php
$host = 'localhost';
$user = 'the_devsrealm_guy';
$pass = 'YOUR_PASSWORD';
$dbname = 'marn_melodic';
$char = 'utf8mb4';

// Set DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $char;

// Create a PDO instance
$pdo = new PDO($dsn, $user, $pass);
  • The $host is the localhost, localhost means your server and your PHP application are on the same server, if your database is on a remote server, then your $host should be remote.
  • $user is the username that is granted access to the database
  • $pass is the password of the user
  • $dbname is the database we are connecting to
  • $char is the charset utf8mb4, this is probably not useful if your database and the tables are already created, but you should include it should incase you want to create a table from your script, this would prevent mariadb from using the default charset UTF-8, which is probably what you don't want.
  • DSN is a data source name, and that is what PDO uses for creating a connection to a database server. The DSN typically contains the database server, database name, host, etc. Note that, different database system requires different data source name. mariadb is identical to mysql
  • We then create a PDO instance to connect to our MariaDB or MySQL database server, as you can see, I am creating a new connection object with the data source name, user name, and password. The connection object is an instance of the PDO class.

To then use the SELECT statement I can do something like so:

$artist = 'Beautiful Nubia';

$sql = 'SELECT * FROM cmusic WHERE artiste = :artist';
$stmt = $pdo->prepare($sql);
$stmt->execute([':artist' => $artist]);
$artists = $stmt->fetchAll();

foreach($artists as $value){
    echo $value[title].'<br>';
}

// Output =>
Higher Steps
Tables Turn

The above uses PDO::prepare, this prepares an SQL statement to be executed by the  PDOStatement::execute() method. So, we are trying to do: SELECT * FROM cmusic WHERE artiste = 'Beautiful Nubia'; I stored 'Beautiful Nubia' in a variable $artist, I can as well, just include 'Beautiful Nubia' directly in the SELECT statement, but in a real-world application, you would hold the value of what the user is trying to query somehow, which is why I used a variable.

Our statement contained one named parameter (:artist) markers for which real values will be substituted when the statement is executed, in our case, 'Beautiful Nubia' will be substituted.

The next statement then prepares the SQL statement: $stmt = $pdo->prepare($sql);, this prevents SQL injections, I'll come back to why this is useful in a moment.

We then execute it: $stmt->execute([':artist' => $artist]);

Before I explain the execute method, here is how everything tighs together:

  • When we prepared the SQL statement, we aren't actually executing the statement, what happens is that the SQL statement is sent to the database server to be pared. After the server finishes this process, it checks the syntax, performs query optimization, and stores the statement for later use, and then...
  • The execute method ($stmt->execute(['artist' => $artist]); sends the parameter values to the server coupled with the SQL statement it has parsed, and then execute the statement once it has included where the parameter should be.

The next question is why am I having an array, well, the execute method expects the parameter to be an array, remember this is the parameter that would be used when the SQL statement is done parsing. I am using an associative array to point to the parameter I am passing. Since associative arrays are key-value pairs, we can say the 'artist' is the key, and the value is whatever is stored in the $value variable, make sure the 'artist' is the same as what you used in the SQL statement.

This is super useful if say you have multiple parameters, for example:

$artist = 'Beautiful Nubia';
$title = 'Tables Turn';

$sql = 'SELECT * FROM cmusic WHERE artiste = :artist AND title = :title';
$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$stmt->execute([':artist' => $artist, ':title' => $title]);
$artists = $stmt->fetchAll();

foreach($artists as $value){
   echo $value->title.'<br>';
}

// Output =>
Tables Turn

Now, to this line: $artists = $stmt->fetchAll();this fetch all rows and return the result-set as an associative array. So, all the rows returned would be stored in the $artists variable.

If we print the array $artists using:

print "<pre>";
   print_r($artists);
print "</pre>";

We get:

Array
(
    [0] => Array
        (
            [music_id] => 2
            [0] => 2
            [status] => 1
            [1] => 1
            [mood] => 
            [2] => 
            [artiste] => Beautiful Nubia
            [3] => Beautiful Nubia
            [title] => Higher Steps
            [4] => Higher Steps
            [genre_id] => 3
            [5] => 3
            [producer_id] => 3
            [6] => 3
            [description] => 
            [7] => 
        )

    [1] => Array
        (
            [music_id] => 3
            [0] => 3
            [status] => 1
            [1] => 1
            [mood] => 
            [2] => 
            [artiste] => Beautiful Nubia
            [3] => Beautiful Nubia
            [title] => Tables Turn
            [4] => Tables Turn
            [genre_id] => 3
            [5] => 3
            [producer_id] => 3
            [6] => 3
            [description] => 
            [7] => 
        )

)

As you can see the fetchall() fetch all rows and return the result-set as an associative array.

To output only the title we can use the foreach loop to loop through the array values and print only the value of the title:

foreach($artists as $value){
    echo $value[title].'<br>';
}

Foreach loops are different from a while and for loops, a for each loop lets you iterate (perform again) through each item of the array until it gets to the end.

The foreach loops take an array $artists, which is what you would be looping through, we add the special keyword “as” and the $value (it doesn’t have to be called $value) is going to be assigned an item as it loops through the items in the $array.

The $value[title] would then print whatever is in the title value in the array, in our case, the first time the loop goes through, it would print 'Higher Steps', and the second time it goes through, it would print 'Tables Turn', it is as simple as that.

Note: The $artists should be something that exists beforehand, you can’t just loop through a list that does not exist, on the other hand, the $value doesn’t exist, it is just a placeholder for holding the value.

So, here is the full code:

<?php
$host = 'localhost';
$user = 'the_devsrealm_guy';
$pass = 'YOUR_PASS';
$dbname = 'marn_melodic';
$char = 'utf8mb4';

// Set DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $char;

// Create a PDO instance
$pdo = new PDO($dsn, $user, $pass);

$artist = 'Beautiful Nubia';

$sql = 'SELECT * FROM cmusic WHERE artiste = :artist';
$stmt = $pdo->prepare($sql);
$stmt->execute([':artist' => $artist]);

$artists = $stmt->fetchAll();
foreach($artists as $art){
    echo $art[title].'<br>';
}

By default, the fetchAll() is using an Associative Array, if you want it as an object, then you can pass in a value in the fetchAll() like so:

$artists = $stmt->fetchAll(PDO::FETCH_OBJ);

And you can access the data like so:

foreach($artists as $value){
	echo $value->title.'<br>';
}

It's a matter of preference. I think this one looks less clunky, if you want to use this every time, then just set the default mode or the fetch style when you are don creating the PDO instance like so:

      // Create a PDO instance
	$pdo = new PDO($dsn, $user, $password);
	$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

This way you don't have to do it for every query, now, remove PDO::FETCH_OBJ in the fetchAll() as you don't have to pass it in any longer.

This is the complete code if you want to fetch as an object:

<?php
$host = 'localhost';
$user = 'the_devsrealm_guy';
$pass = 'YOUR_USERNAME';
$dbname = 'marn_melodic';
$char = 'utf8mb4';

// Set DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $char;

// Create a PDO instance
$pdo = new PDO($dsn, $user, $pass);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

$artist = 'Beautiful Nubia';
$title = 'Tables Turn';

$sql = 'SELECT * FROM cmusic WHERE artiste = :artist';
$stmt = $pdo->prepare($sql);
$stmt->execute([':artist' => $artist]);

$artists = $stmt->fetchAll();

foreach($artists as $value){
	echo $value->title.'<br>';
}

If you don't want to make your execute method clunky when you have more data to bind, then you can a seprarte bindParameter, something like this:

$artist = 'Beautiful Nubia';
$title = 'Tables Turn';

// SQL statement
$sql = 'SELECT * FROM cmusic WHERE artiste = :artist AND title = :title';
// Prepare the statement
$stmt = $pdo->prepare($sql);
// Bind Parameter to execute method
$stmt->bindParam(':artist', $artist);
$stmt->bindParam(':title', $title);
// execute the god damn thing
$stmt->execute();

$artists = $stmt->fetchAll();

foreach($artists as $value){
	echo $value->title.'<br>';
}

// Output =>
# Tables Turn

Back to why the prepare method is useful:

$stmt = $pdo->prepare($sql);

As said previously, one of the useful advantage of about doing this way is that it prevents SQL injections. So, what is SQL injections?

SQL injection is a code injection technique used by a malicious user to mangle your database, it is the placement of malicious code in SQL statements, mostly via a web page input.

Here is the thing, if we hadn't prepared the statement, and we did something like this:

$sql = 'SELECT * FROM cmusic WHERE artiste = $artist';

In a real-world application, the variable $artist would hold the value of the user input, in this case, we are asking the user for an artist name. Here is where it gets interesting, a malicious user that is trying to do SQL injection won't supply the right input, but instead an SQL statement that you will unknowingly run on your database.

So, here is an example if the user uses OR 1=1:

SELECT * FROM cmusic WHERE artiste = 'Beautiful Nubia' OR 1=1;

Instead of the user giving us "Beautiful Nubia" in the artist variable, he supplied 'Beautiful Nubia' OR 1=1 instead, hmm, guess what this would give you?

Well, this would return the entire row, which isn't the intended result-set:

MariaDB [marn_melodic]> SELECT * FROM cmusic WHERE artiste = 'Beautiful Nubia' OR 1=1;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood  | artiste         | title                   | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
|        1 |      1 | Dance | Avicii          | Silhouette              |        2 |           1 | NULL        |
|        2 |      1 | NULL  | Beautiful Nubia | Higher Steps            |        3 |           3 | NULL        |
|        3 |      1 | NULL  | Beautiful Nubia | Tables Turn             |        3 |           3 | NULL        |
|        4 |      1 | Dance | Fela            | Observation Is No Crime |        4 |           4 | NULL        |
|        5 |      0 | Dance | Avicii          | Levels                  |        2 |           1 | NULL        |
|        6 |      0 | Dance | Fela            | Colonial Mentality      |        4 |           4 | NULL        |
|        7 |      1 | NULL  | Asa             | Jailer                  |        1 |           2 | NULL        |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+

If you have sensitive info here, then that's definitely a problem. The reason why it returns every row is because OR 1=1 is always TRUE. In short, if you do SELECT * FROM cmusic WHERE artiste = 'Beautiful Nubia' OR TRUE;it is the exact same thing.

So, we have WHERE artiste = 'Beautiful Nubia' which is true, OR 1=1 which is also TRUE, then this is what the client sees:

SELECT * FROM cmusic WHERE TRUE;

You get the idea now right, it would return every single set. Here is where it gets dangerous the user can also do something like so:

'Beautiful Nubia' OR 1=1; DROP TABLE TABLE_NAME

This is an SQL injection based on a batch SQL statement, which is also valid, I can go on and on, so, that is the idea of using a prepared statement, it cleans up malicious queries for you. I hope this clarifies it.

Hope you enjoyed this guide, I would update this guide if I can find something useful to add, thanks.

Related Post(s)

  • Laravel - Edit and Delete Data (+ User Authentication)

    In this guide, you'll learn how to edit and delete post data in Laravel, before proceeding you should read the previous guides as this guide would be the continuation, here are the previous guides:

    Read More

  • Guide To Laravel - Model and Database Migrations

    I don't know if you have read my guide on Creating a Tiny PHP MVC Framework From Scratch where we create a tiny MVC framework in the hope of understanding the concepts of how major frameworks imple

    Read More

  • Building Dependency Injection and The Container from Scratch in PHP

    In this guide, you'll learn about dependency injection and a way to build a simple DIC (Dependency Injection Container) in PHP using PSR-11 from scratch. First, What is an Ordinary Dependency? This

    Read More

  • Creating a Tiny PHP MVC Framework From Scratch

    In this guide, we would go over creating a tiny PHP MVC Framework, this would sharpen your knowledge on how major frameworks (e.g Codeigniter or Laravel) works in general. I believe if you can unders

    Read More

  • PHP Pluggable and Modular System – Part 2 (Implementation) [Event Dispatcher]

    In the first series of this guide, we discussed the theoretical aspect of building a pluggable system in PHP, I wrote a bit of code in that guide plus a couple of stuff you should avoid, you can lear

    Read More

  • Best Way To Implement a Non-Breaking Friendly URL In PHP or Laravel or Any Language

    I was working on the link structure of my new Laravel app, and out of the blue I said: "What would happen if a user changes the slug of a post?" First Attempt - 301 Redirection The first solution I t

    Read More