How PHP and DBMS work together

See more about:

Where are we?

You’ve seen an overview of PHP database applications that run two small businesses. We’ll work slowly towards being able to write applications like that.

Let’s see how PHP programs and MySQL work together. Not in detail yet. That comes later.

This lesson’s goals

In this lesson, you will learn:

  • There is a database client and a database server (a DBMS).
  • The client sends SQL statements to the server. The server returns status codes (like OK) and, sometimes, some data.
  • You write PHP programs that create SQL statements, and send them to the server. Each one of these programs is a database client.

Database servers

Remember that a server is software (or an entire computer) that does something for other software (or other computers).

An email server helps email clients. They exchange messages using protocols. Recall that a protocol is a language, a set of standards for things like saying hello and saying goodbye. SMTP is an email protocol.

Email server and client

Figure 1. Email server and client

A Web server helps Web clients (like browsers). They exchange messages in the protocol HTTP.

Webserver and client

Figure 2. Web server and client

A database server works with database clients. The client sends requests in the language SQL. The server responds with data and status messages.

DB server and client

Figure 3. DB server and client

Figure 3 is a little different from the others. There’s a client and a server, and they communicate. But often the client and server software are on the same machine. They don’t use the Internet to communicate. Instead, they use something like a socket file to exchange messages. A socket file is a way of sending messages between software running independently on the same computer.

Another difference is the type of client. We will be writing our own clients, in PHP. In the DogRock CMS, each PHP program like add-article.php and delete-article.php is a client. It will generate SQL, and send it to the server.

DB server software is usually called a database management system (DBMS). An individual data set is a database.

Interaction pattern

The pattern for interaction is:

  • Client sends an SQL statement.
  • Server returns a status code, and maybe some data.

Suppose you want your PHP page to add data to a database.

Adding data

Figure 4. Adding new data

Your page sends an SQL statement that includes the data to add. The statement would start with the word INSERT. The DBMS executes the SQL, and returns a status code, like “OK.”

If you want your PHP page to delete some data, your page would send a DELETE statement.

Deleting data

Figure 5. Deleting data

To fetch some data from the database, send an SQL statement. This one starts with the word SELECT.

Fetching data

Figure 6. Fetching data

The DBMS returns a status code, and some data. Your PHP can then do something with the data, like show it to the user.

MySQL

There are many different DBMS. The one we’ll talk about is MySQL. Why this one? Because:

  • It’s free.
  • It’s available on every Unix shared hosting account you would want to use.
  • It’s used by many open source (free) Web applications.
  • There’s lots of documentation, and many people know how to use it.

MySQL is included with the XAMPP package we talked about earlier. So if you installed XAMPP, you already have MySQL. W00f!

SQL

SQL stands for structured query language. It’s been part of the geek toolkit for about 30 years. Yes, you read that right. SQL has survived for a long time. And it’s not going away anytime soon.

We’ll talk more about SQL throughout this chapter. But let’s start with some examples, so you get the flavor of it.

Remember, don’t get hung up on the details. I just want you to understand the idea of “send SQL, get status and data.”

Suppose we have some dog data. For each dog, we know its name, breed, and weight. Like this:

Dog data

Figure 7. Dog data

This data is sitting on the DB server.

We could send this SQL query to the server:

select name, weight from dogs;

This means “fetch the name and weight of every dog.” Here’s what the DBMS would return:

Two columns

Figure 8. Two columns

Renata
Renata

Earlier you wrote about SELECT, and now about select. Which is right?

Kieran
Kieran

Both are. MySQL doesn’t care whether keywords like select and insert are in upper- or lowercase.

It also doesn’t care whether SQL statements have semicolons(;) at the end. Strictly, they should be there, but you can leave them off if you like.

Here’s another query:

select name from dogs where weight > 50;

This says “Give me the names of the dogs whose weight is more than 50.”

The DBMS would return:

Heavy dogs

Figure 9. Heavy dogs

This shows that a DBMS can filter data, sending back only what the client asks for. In our case, the client will be a PHP program.

Another example:

select name, weight from dogs order by weight;

This gives:

Sorted dogs

Figure 10. Sorted dogs

The DBMS sorts the data before returning it.

Another example:

insert into dogs (name, breed, weight) values ('Brian', 'Lab', 51);

This adds a new row to the dogs table:

New dog

Figure 11. New dog

This shows that SQL statements can change a database, as well as fetch data from it.

One more example:

delete from dogs where weight < 20;

This would remove all of the light dogs. You would get:

No light dogs

Figure 12. No light dogs

PHP creates SQL statements

PHP pages send SQL statements to DBMS. So, to mess with data, you write PHP that writes SQL statements. Then you send the SQL to the DBMS.

For example:

$query = "insert into dogs (name, breed, weight) 
    values ('Brian', 'Lab', 51)";
$db->query($query);

Figure 13. Adding a dog

The first line puts the SQL statement into a variable. The second line sends it to the DBMS.

Here’s another one:

$query = "delete from dogs where weight < 20";
$db->query($query);

Figure 14. Removing light dogs

The first line creates the SQL, and the second one executes it.

What happens when you get a bunch of data back from a database? Remember this query:

select name from dogs where weight > 50;

This gives:

Heavy dogs

Figure 9 (again). Heavy dogs

You write PHP to handle each row. The general pattern is:

  • Make an SQL query.
  • Send it to the DBMS.
  • For each row:
    • Get the row data.
    • Do something with it.

Here’s an example. Don’t worry about all of the details; just look for the general pattern. The comments explain what each line does.

//Run the SQL query.
$query = 'select name from dogs where weight > 50';
$record_set = $db->query($query);
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a row.
  $name = $row['name'];
  //Output
  print "<p>Dog name: $name</p>";
}

Figure 13. Showing heavy dogs

Lines 2 and 3 run the query. The DBMS sends back some data, in a table (see Figure 9). The table has one column, and a bunch of rows. Each row is also called a record. The bunch of rows the DBMS returns is called a record set.

Lines 5 to 10 form a loop. The loop runs once for each row in the record set. (Don’t worry about how it works; just know that each record is passed through lines 6 to 9.)

Line 7 gets some data from a record, and puts it into a variable. Line 9 shows the data.

CC
CC

I’m getting worried here. It’s looking awfully complex.

Kieran
Kieran

I don’t blame you. It does get messy. It’s important to take baby steps. Just one little thing at a time.

That’s what we’ll do. But it won’t work if you skip the exercises. The more difficult the material, the more important the hands-on stuff is.

Summary

  • There is a database client and a database server (a DBMS).
  • The client sends SQL statements to the server. The server returns status codes (like OK) and, sometimes, some data.
  • You write PHP programs that create SQL statements, and send them to the server. Each one of these programs is a database client.

What now?

Let’s look at a tool that can make your database work easier.


How to...

Lessons

User login

Log in problems? Try here


Dogs