PHP SELECTs a row

There are several ways to do DB work in PHP. We'll just use one: PDO. PDO makes it easy to sanitize user data, and prevent SQL injection attacks.

Here's what you do in your PHP:

  • Connect your PHP program to a DBMS.
  • Prepare SQL statements.
  • Send SQL to DBMS.
  • Process the results.
Show all

Connecting to a DB

Before you send SQL, you need to connect to a DB. You store the connection in a variable.

An example:

  1. // To connect to a DB, need DB name, user name, and password.
  2. $dbName = 'wombats';
  3. $dbUserName = 'something';
  4. $dbPassword = 'something';
  5. // Create DSN.
  6. $dsn = "mysql:host=localhost;dbname=$dbName";
  7. $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

Let's go through it backwards. To make a connection (line 7), you need three things:

  • A DSN, a string with the server name, and the database name.
  • A user name.
  • A password.

With that info, you make a connection, and put it in a variable.

  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

Line 6 makes the DSN string:

  • $dsn = "mysql:host=localhost;dbname=$dbName";

It gives the name of the server with the DB, and the name of the DB.

The server name is always localhost for us, both on your PC, and on your Reclaim server.

Ethan
Ethan

Wait, what? localhost on the Reclaim server?

This does seem strange. Thing is, MySQL runs on the same computer that your PHP program is running on.

PHP and MySQL on the same computer

So, as far as your PHP is concerned, MySQL is running on localhost.

localhost doesn't actually mean "your PC." When a program refers to localhost, it's saying "whatever computer I'm running on."

If a PHP program is running on your PC, localhost is your PC, as far as that PHP is concerned.

If a PHP program is running on your Reclaim server, localhost is your Reclaim server, as far as that PHP is concerned.

If a PHP program is running on the whistlingmonkeys.com server, localhost is the whistlingmonkeys.com server, as far as that PHP is concerned.

Since we'll always use localhost, we can hard code it into the DSN:

  • $dsn = "mysql:host=localhost;dbname=$dbName";

The DB name varies, though, so we'll use a variable:

  • $dbName = 'wombats';
  • ...
  • $dsn = "mysql:host=localhost;dbname=$dbName";
Show all

Here's the code again.

  1. // To connect to a DB, need DB name, user name, and password.
  2. $dbName = 'wombats';
  3. $dbUserName = 'wombats';
  4. $dbPassword = 'something';
  5. // Create DSN.
  6. $dsn = "mysql:host=localhost;dbname=$dbName";
  7. $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

When it's done, $dbConnection will have a connection to a DB, ready to receive SQL.

Adela
Adela

Having the user name and password in the code like that. Is that a security issue?

Maybe, but since the file is PHP, and PHP runs on the server and never gets to the client, then it's not so bad. Still, I take your point. We'll see how to protect it later.

Show all

There's a deployment problem, though. Deployment is how you move your code from development into production. In our case, how you move it from your local XAMPP environment, to your server.

You're writing code on your PC, then uploading it to your Reclaim server. The problem is, the DBs might have different names, since your Reclaim DBs have your Linux username at the front.

We'll see later how to store connection data separately, to deal with this issue.

Show all

Examples

Let's try some examples. Suppose you wanted to get the deets for wombat 3. You can try it.

Here's that data:

Wombat 3

A program that will do it:

  1. <?php
  2. // To connect to a DB, need DB name, user name, and password.
  3. $dbName = 'wombats';
  4. $dbUserName = 'something';
  5. $dbPassword = 'something';
  6. // Create DSN.
  7. $dsn = "mysql:host=localhost;dbname=$dbName";
  8. // Connect.
  9. $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);
  10. // Prepare SQL.
  11. $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  12. // Run it.
  13. $stmt->execute(['id' => 3]);
  14. // Get the record.
  15. $row = $stmt->fetch();
  16. ?><!DOCTYPE html>
  17. <html lang="en">
  18.     <head>
  19.         <meta charset="UTF-8">
  20.         <title>Wombat 3</title>
  21.         <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  22.     </head>
  23.     <body>
  24.         <h1>Wombat 3</h1>
  25.         <p>Name: <?php print $row['name']; ?></p>
  26.     </body>
  27. </html>

First, the program connects to the DB.

Now, we want to run this query:

  • SELECT * FROM wombats WHERE wombat_id = 3;

That is, get all of the columns from wombats for records with an id of 3.

Often, things like the 3 will come from the user somehow, maybe through the URL. For example:

  • $id = $_GET['id'];

We could insert $id directly into a string, to make a query:

  • $sql = "SELECT * FROM wombats WHERE wombat_id = $id;";

Easy enough, but that might leave the door open for hackers. Suppose a hacker typed:

  • ...?id=3;DELETE * FROM wombat;

The SQL we'd run:

  • SELECT * FROM wombats WHERE wombat_id = 3;DELETE * FROM wombat;

Wombat data gone! Not so good.

That's an SQL injection attack. It's the subject of a famous XKCD cartoon.

SQL injection

Show all

We want to sanitize data from users, before we use it. PDO knows how to do that.

  • // Prepare SQL.
  • $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  • // Run it.
  • $stmt->execute(['id' => 3]);

We prepare an SQL statement with placeholders, like :id. When we execute the prepared statement, we send params for the placeholders. PDO will ensure they don't do Evil Things.

Show all

Grabbing the data from the DBMS

So we pass SQL to the DBMS. How do we get the result?

  1. // Prepare SQL.
  2. $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');
  3. // Run it.
  4. $stmt->execute(['id' => 3]);
  5. // Get the record.
  6. $row = $stmt->fetch();
  7. ?><!DOCTYPE html>
  8. ...
  9. <p>Name: <?php print $row['name']; ?></p>

Line 2 creates a prepared statement, putting it into $stmt. Line 4 executes the statement, sending in a value for the id.

Line 6 is:

  • $row = $stmt->fetch();

There is only one record for the query, since there is only one wombat with an id of 3. Use fetch() when there's only one record.

fetch() returns an array, that I put into the variable $row. An array is a group of values with different keys. The keys are field names.

Here's the data again:

Wombat 3

Here's what's in $row:

  • $row['wombat_id'] is 3
  • $row['name'] is 'Sarah'
  • $row['weight'] is null
  • $row['comments'] is null

So this will show the wombat's name:

  • <p>Name: <?php print $row['name']; ?></p>
Show all

Grabbing a count

Try this one. It counts the number of wombats.

  1. // Prepare SQL.
  2. $stmt = $dbConnection->prepare('SELECT COUNT(*) AS num_wombats FROM wombats');
  3. // Run it.
  4. $stmt->execute();
  5. // Get the record.
  6. $row = $stmt->fetch();
  7. ?><!DOCTYPE html>
  8. ...
  9. <p>Count: <?php print $row['num_wombats']; ?></p>

This one doesn't have params to send to inject into the query. I named the count field num_wombats (line 2), so I know what to output (line 9).

Show all

What's that PDO thing?

We have statements like this:

  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

PHP has more than one code library for connecting to DBs. The two main ones for MySQL are mysqli and PDO. We're using the PDO library. It's a little easier to work with, and supports more than just MySQL.

Here's a good tutorial on PDO. Lots of examples, and such. It covers more than we need for this course, but it's a good thing to look at, nevertheless.

Show all

Next up

Let's get some user interaction, through URL params.