In this chapter, we're heading back to our PHP pages. From now on, we'll be learning how to interact with a database using PHP. It'll also be our chance to get acquainted with the SQL language, which we'll be exploring in depth over the next few chapters.

We'll start by practicing how to read data from a table. It's strongly recommended that you've already played around a bit with your database manager: this tool is perfect for checking whether the changes you're making in PHP are actually taking effect in your database.

Connecting to the Database in PHP

Before we can do anything with the database in PHP, we first need to connect to it.

In this chapter, we'll learn how to read data from a database (often abbreviated as "DB" or "db"). As a quick reminder: PHP acts as the go-between for you and MySQL. But here's the catch — PHP can't just walk up to MySQL and say "Hey, fetch me this data." Nope. MySQL wants a username and password first. Otherwise, anyone could poke around in your database and read whatever it contains (including potentially sensitive info!).

So PHP has to log in, or as we say, establish a connection to MySQL. Once the connection is made, you can go ahead and do whatever you like with your database!

Great question! PHP actually gives us a few different ways to connect to a MySQL database:

• The mysql_ extension: These are older functions for connecting to MySQL. They all start with mysql_. However, they're outdated and no longer recommended. In fact, they've been officially removed since PHP 7.
• The mysqli_ extension: These are improved functions for working with MySQL. They offer more features and are up to date.
• PDO (PHP Data Objects): This is a robust, flexible tool that lets you connect to any kind of database, not just MySQL. So you can use it with MySQL, PostgreSQL, Oracle — you name it.

These are all called extensions because PHP is modular. You can easily add or remove features based on what you need (since not everyone uses every feature PHP has to offer).

As you've probably guessed, you should forget about mysql_ — it's obsolete. That leaves us with mysqli_ and PDO. In this tutorial, we're going with PDO because it's becoming the standard way to access databases in modern PHP. Plus, its big advantage is that you can use it the same way with any database system (PostgreSQL, Oracle, SQL Server, etc.).

That means what you're about to learn will still be useful if you ever switch to a different database system.

Enabling PDO

Normally, PDO is enabled by default. To check, left-click on the Uniform Server icon in the system tray, go to the PHP / PHP Extensions menu, and make sure php_pdo_mysql is checked.

What if I'm using a different AMP?

You can open PHP's config file (usually called php.ini) and search for the line with php_pdo_mysql. If you see a semicolon in front of it, remove it to enable the extension:

;extension=php_pdo_firebird.dll  
;extension=php_pdo_mssql.dll  
extension=php_pdo_mysql.dll  
;extension=php_pdo_oci.dll  
;extension=php_pdo_odbc.dll  

If you're on Linux and using XAMPP, find the line pdo_mysql.default_socket and set it like this:

pdo_mysql.default_socket = /opt/lampp/var/mysql/mysql.sock

Save the file and restart PHP. Just relaunch your favorite AMP stack — Uniform, AMPPS, WAMP, MAMP, XAMPP, etc. — and you're good to go.

Connecting to MySQL with PDO

Now that we're sure PDO is enabled, we can connect to MySQL. You'll need four pieces of information:

• Host name: This is the address of the machine running MySQL (sort of like an IP address). Most of the time, MySQL is on the same computer as PHP, so you'll use localhost. But if your web host tells you to use something else (like sql.webhost.com), you'll need to change this when your site goes live.
• Database name: This is the name of the database you want to connect to. In our case, it's test, which we created using phpMyAdmin in the last chapter.
• Login (username): This identifies you. Ask your web host for it — it's often the same login you use for FTP access.
• Password: Often, the password is the same as for FTP. Again, check with your host if you're not sure.

Right now, we're testing everything on our own machine. This is what we call "local" development. So the host will be localhost.

As for the login and password, by default the login is root and the password is empty.

Here's how you connect to MySQL using PDO on the test database:

<?php
$db = new PDO('mysql:host=localhost;dbname=test', 'root', '');
?>

Don't worry if that code doesn't make sense yet — totally normal!

There are a few new things in there. PDO is what we call an object-oriented extension. That's a slightly different way of programming compared to the regular functions we've been using so far.

We'll cover object-oriented programming (OOP) later in the course. For now, just copy and reuse the code I provide — follow along with the examples. You'll understand how it all works soon enough.

That line of code creates what we call an object: $db. It looks like a variable, but it's actually an object that represents the database connection. We set it up by providing, in order:

• The host name (localhost)
• The database name (test)
• The login (root)
• The password (empty string in this case)

Once your site is live, you'll likely need to use a different host, login, and password, like this:

<?php
$db = new PDO('mysql:host=sql.siteraw.com;dbname=mydatabase', 'admin', 's3cr3t');
?>

So when you upload your site to the web, don't forget to update that line with your actual hosting info.

That first parameter (starting with mysql) is called the DSN — Data Source Name. This is usually the only part you'll need to change when connecting to a different type of database.

Testing for Errors

If you've entered all the correct info (host name, database name, login, password), nothing should show up on your screen. However, if you made a mistake (wrong password, incorrect database name, etc.), PHP might print the entire error line — including your password!

You definitely don't want your visitors to see your password if an error pops up once your site is online. That's why it's better to handle errors properly. When something goes wrong, PDO throws what's called an exception — this lets you catch and handle errors gracefully.

Here's the approach I recommend:

<?php
try
{
	$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
	$db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
}
catch (Exception $e)
{
        die('Error: ' . $e->getMessage());
}
?>

Yep — this is another piece of code that looks a bit new. I won't dive too deep into the details just yet — we'll be exploring all of this later.

Still, you're probably wondering basically how it works. Here's the gist: PHP tries to run the code inside the try { } block. If something goes wrong, it skips straight to the catch { } block to figure out what to do. In our example, we choose to display the error and stop the script if we end up in the catch.

If everything goes fine, PHP simply moves on and skips the catch block. So right now, your PHP page should show... nothing.

Also, take note: we created a $pdo_options array and passed it in as a parameter. The goal here is to enable PDO exceptions. Thanks to this little trick (which I highly recommend you use every time), you'll be able to catch and manage errors when they happen.

Not at all! Seriously. Keep in mind, PDO is introducing us to some PHP features we haven't studied yet (like object-oriented programming and exceptions). For now, just reuse the code snippets I give you, and don't stress — we'll come back and explain it all in detail later on.

If your page is blank, that's a good sign — you're ready to continue. If you get an error, read the message carefully and try to understand what it means. And if you're stuck, don't hesitate to ask for help on forums — otherwise, you won't be able to move forward.

Retrieving Data

We're going to start by learning how to read data from a database. Then in the next chapter, we'll see how to add and modify data.

To follow along, we'll need a "ready-to-use" database that we can use as our testbed. I suggest you create a table (for instance, called video_games) in your test database.

Our goal is to create a PHP page that will display the contents of the video_games table.

Making a Query

Now comes the big moment you've all been waiting for: it's time to talk to MySQL. That means we're going to start speaking SQL! To do that, we'll send what's called a query. In this case, we'll politely ask MySQL to show us everything in the video_games table.

To fetch information from the database, we need our connection object — remember that one? It's called $db. We'll make the query like this:

<?php
$response = $db->query('Type your SQL query here');
?>

This line sends a query to the database.

We then store the result MySQL gives us in another object — here, we've called it $response.

Your First SQL Query

As I mentioned, SQL is a language — it's what lets us talk to MySQL.

Here's our first SQL command:

SELECT * FROM video_games

This means: "Take everything from the video_games table."

Let's break it down:

• SELECT: This keyword tells MySQL what kind of operation we want. In this chapter, we'll only use SELECT. It asks MySQL to display the contents of a table.
• *: After SELECT, you specify which fields to fetch. If you're only interested in, say, the "name" and "owner" fields, you'd type:
• SELECT name, owner FROM video_games
• But if you want everything, just use a *. This little star means "all": "Give me everything..."
• FROM: A linking word, kind of like "in". It connects the list of fields to the name of the table.
• video_games: That's the name of the table we're digging into.

Let's now run this query using the method we just learned:

<?php
$response = $db->query('SELECT * FROM video_games');
?>

And on you go — $response now holds MySQL's reply.

Displaying the Query Result

The issue is, $response holds something pretty unmanageable. MySQL gives us a ton of info, and we need to make sense of it.

Just imagine all the data it could contain! If your table has 10 fields and 200 rows, that's over 2,000 pieces of information! Instead of handling it all at once, we'll process it line by line — or rather, entry by entry.

To grab a single entry, we run fetch() on the MySQL response. That gives us the first row.

<?php
$data = $response->fetch();
?>

$data is now an array containing the values for each field in that entry. For example, to access the "console" field, you'd use $data['console'].

To go through all the entries, we'll use a loop. Every time you call $response->fetch(), you move to the next row. So the loop runs as many times as there are entries in the table.

Phew! That's a lot to take in. Let's summarize everything — from connecting with PDO to displaying the query results:

<?php
try
{
    // Connect to MySQL
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    // Fetch all content from the video_games table
    $response = $db->query('SELECT * FROM video_games');
    
    // Display each entry one at a time
    while ($data = $response->fetch())
    {
    ?>
        <p>
        <strong>Game</strong>: <?php echo $data['name']; ?><br />
        This game belongs to: <?php echo $data['owner']; ?>, and they're selling it for <?php echo $data['price']; ?> euros!<br />
        This game runs on <?php echo $data['console']; ?> and supports up to <?php echo $data['max_players']; ?> players<br />
        <?php echo $data['owner']; ?> left a comment about <?php echo $data['name']; ?>: <em><?php echo $data['comments']; ?></em>
        </p>
    <?php
    }
    
    $response->closeCursor(); // Finish processing the query
}
catch(Exception $e)
{
    // If there was an error, show a message and stop everything
    die('Error: '.$e->getMessage());
}
?>

So, what do you think? o_O

That's quite a wall of text! But don't worry — the table I gave you contains about fifty entries, so it's totally normal to get lots of results!

What's actually going on here? We loop through each row of the table — entry 1, entry 2, and so on. Every new loop brings up a new row to display.

$response contains the raw reply from MySQL, as an object.

$data is an array created by fetch(). Every time we loop, fetch() pulls the next row from $response and breaks it into individual fields stored in $data.

Yes, that line's a little tricky if you're new to it. It actually does two things:

• It gets the next entry and stores its content in $data
• It checks if $data is still valid (i.e., not false)

Once fetch() hits the end of the results, it returns false. That makes the while condition false too, and the loop ends.

You might've noticed this line at the end:

<?php $response->closeCursor(); ?>

This closes the "result parsing cursor". In other words: always call closeCursor() when you're done with a query result. It avoids issues with later queries and signals that you've finished working with that response.

Displaying Only Some Fields

Now that you've learned all this, you should be able to display just what you want.

You're not required to show all the fields! For example, if I just wanted to list the names of the games, I'd use this SQL query:

SELECT name FROM video_games

Let's adapt the full example above to show just one game name per line:

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    $response = $db->query('SELECT name FROM video_games');
    while ($data = $response->fetch())
    {
        echo $data['name'] . '<br />';
    }
    
    $response->closeCursor();
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

This code is very similar to the previous one, but it's a great opportunity to get more comfortable with MySQL and PDO. Just keep these key points in mind:

• You only need to connect to the database once, at the start of the page.
• Always close your query results with closeCursor() once you're done processing them.

Selection Criteria

Let's say I only want to get the list of games available on the "Xbox" console and sort them by price, from cheapest to most expensive. Sounds complicated? Not with SQL!

You'll see that by tweaking our SQL queries, filtering and sorting your data becomes incredibly easy. We're going to look at the following SQL keywords:

• WHERE
• ORDER BY
• LIMIT

WHERE

The WHERE keyword lets you filter your data!

Say I want to list only the games that belong to Patrick. The query starts off just like before, but I'll tack on WHERE owner='Patrick' at the end.

That gives us the following query:

SELECT * FROM video_games WHERE owner='Patrick'

Translation: "Select all fields from the video_games table where the possessor field equals Patrick."

Note that text values need to be enclosed in single quotes to be properly recognized, as is the case with 'Patrick'. Numbers, on the other hand, don't need quotes.

Want a quick script to try it out?

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    $response = $db->query("SELECT name, owner FROM video_games WHERE owner='Patrick'");
    
    while ($data = $response->fetch())
    {
        echo $data['name'] . ' belongs to ' . $data['owner'] . '<br />';
    }
    
    $response->closeCursor();
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

Try changing the possessor's name (for example: WHERE owner='Matt') and it'll only show games belonging to Matt! Give it a shot and see what happens!

You can also combine several conditions. Say I want to list Patrick's games that cost less than 20 dollars. I can combine selection criteria using the AND keyword:

SELECT * FROM video_games WHERE owner='Patrick' AND price < 20

Translation: "Select all fields from video_games where possessor is Patrick AND price is less than 20."

There's also the OR keyword:

SELECT * FROM video_games WHERE owner='Patrick' OR price < 20

ORDER BY

ORDER BY lets us sort our results. We could, for example, order them by price! The SQL query would be:

SELECT * FROM video_games ORDER BY price

Translation: "Select all fields from video_games and order results by ascending price."

Here's a little script to see it in action:

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    $response = $db->query('SELECT name, price FROM video_games ORDER BY price');
    
    while ($data = $response->fetch())
    {
        echo $data['name'] . ' costs ' . $data['price'] . ' USD<br />';
    }
    
    $response->closeCursor();
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

Easy: just add the DESC keyword at the end:

SELECT * FROM video_games ORDER BY price DESC

Translation: "Select all fields from video_games and sort the results by descending price."

LIMIT

LIMIT allows us to grab just a portion of the results (like the first 20). It's super useful when there are tons of results and you want to paginate them (for example, showing 30 results per page).

Just add the LIMIT keyword at the end of your query, followed by two numbers separated by a comma. For example:

SELECT * FROM video_games LIMIT 0, 20

These two numbers have a very specific meaning:

• The first number tells SQL where to start reading from the results. Here it's 0, meaning from the very first entry. Note: this has nothing to do with the ID field! If your query returns 100 results, LIMIT 0 starts at the 1st, LIMIT 20 starts at the 21st, and so on.
• The second number tells SQL how many entries to return. In our case: 20 entries.

Here are some examples:

• LIMIT 0, 20: shows the first 20 entries.
• LIMIT 5, 10: shows entries 6 to 15.
• LIMIT 10, 2: shows entries 11 and 12.

Example using LIMIT

Let's say we want to display the first 10 games in the table. Here's the code:

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    $response = $db->query('SELECT name FROM video_games LIMIT 0, 10');
    
    echo '<p>Here are the first 10 entries from the video_games table:</p>';
    while ($data = $response->fetch())
    {
        echo $data['name'] . '<br />';
    }
    
    $response->closeCursor();
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

There you have it! :)

But of course, my dear friend.

Here's something to twist your brain a bit:

SELECT name, owner, console, price FROM video_games WHERE console='Xbox' OR console='Playstation' ORDER BY price DESC LIMIT 0,10

You have to use the keywords in this exact order: WHERE then ORDER BY then LIMIT, or MySQL will get confused.

Try translating that into plain English first to see if you've really understood, then go ahead and test it to see if the result matches what you expected.

Building Queries with Variables

So far, all the queries we've looked at have been simple and always performed the same task. Things get more interesting when you start plugging PHP variables into your queries.

The bad idea: concatenating variables directly into a query

Here's the query that gets a list of games belonging to Patrick:

$response = $db->query('SELECT name FROM video_games WHERE owner=\'Patrick\'');

Instead of always showing Patrick's games, we'd like this query to adapt based on the name of the person, which we'd get from a variable, say $_GET['owner'].

So, we might be tempted to write:

$response = $db->query('SELECT name FROM video_games WHERE owner=\'' . $_GET['owner'] . '\''); // Don't do this!

Yes, we still need quotes around the text (hence the backslashes for 'Patrick'), but...

Even though this works, it's the textbook example of what NOT to do — yet tons of sites still do it. Why? Because if $_GET['owner'] has been tampered with by a visitor (and let's face it: never trust the user!), you open yourself up to a huge security vulnerability called SQL injection. A malicious visitor could sneak a SQL query into your code and possibly read your entire database — passwords and all!

Explaining SQL injections in detail is a bit too much for now, but think of it like those include vulnerabilities we discussed in earlier chapters. The golden rule remains: NTUI — Never Trust User Input (I feel like I say this a lot).

Prepared statements.

The solution: Prepared Statements

Prepared statements are much safer and more efficient, especially when the query runs multiple times. If you're using variables in your queries, this is definitely the way to go.

Using ? markers

First, we "prepare" the query without its variable part, using a question mark as a placeholder:

$req = $db->prepare('SELECT name FROM video_games WHERE owner = ?');

Instead of calling query() like before, we use prepare().

Now, we execute the query with execute(), passing it an array of parameters:

$req = $db->prepare('SELECT name FROM video_games WHERE owner = ?');
$req->execute(array($_GET['owner']));

If there are several placeholders, make sure to pass the parameters in the correct order:

$req = $db->prepare('SELECT name FROM video_games WHERE owner = ? AND price <= ?');
$req->execute(array($_GET['owner'], $_GET['price_max']));

The first ? gets replaced by $_GET['owner'], and the second by $_GET['price_max']. PHP will automatically sanitize these inputs to protect against SQL injection.

Let's build a page that lists games belonging to a person and costing no more than a certain amount:

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    $req = $db->prepare('SELECT name, price FROM video_games WHERE owner = ? AND price <= ? ORDER BY price');
    $req->execute(array($_GET['owner'], $_GET['price_max']));
    
    echo '<ul>';
    while ($data = $req->fetch())
    {
        echo '<li>' . $data['name'] . ' (' . $data['price'] . ' EUR)</li>';
    }
    echo '</ul>';
    
    $req->closeCursor();
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

Even though this query is "secure" (which protects us against SQL injection), you should still verify that $_GET['price_max'] is indeed a number and falls within a reasonable range. So no, you're not off the hook when it comes to input validation.

Try calling this page (let's say it's called game_selection.php) with different parameters and see how the list of games changes! Some examples:

• game_selection.php?owner=Matt&price_max=20
• game_selection.php?owner=Matt&price_max=40
• game_selection.php?owner=Patrick&price_max=45
• game_selection.php?owner=Phil&price_max=15
• ... etc.

Using named placeholders

If your query has a lot of variables, named markers can be more convenient than question marks.

Here's how you'd do it:

$req = $db->prepare('SELECT name, price FROM video_games WHERE owner = :owner AND price <= :pricemax');
$req->execute(array('owner' => $_GET['owner'], 'pricemax' => $_GET['price_max']));

Here, the ? placeholders are replaced by named markers like :owner and :pricemax (they start with a colon).

Made it to the end in one piece? Congrats! 😄

You've just learned a ton of powerful stuff in this chapter! Once you finish the next one, you'll be ready to create news feeds, guestbooks, forums, and so much more.

Since this chapter is super important, don't hesitate to read it again (after a well-earned break 😴), because mastering SQL queries and displaying them with PHP is essential!

Enjoyed this PHP & MySQL course?

If you liked this lesson, you can find the book "How to Build a Website in HTML and CSS" from the same authors, available on SiteRaw, in bookstores and in online libraries in either digital or paperback format. You will find a complete PHP & MySQL workshop with many exclusive bonus chapters.

More information