php 101

Working with Databases

Throughout this tutorial, we've been talking about how your data sets often come from a database, and about how PHP has lots of tools built in for getting data out of (or putting data into) a database. But how exactly does that work?

Before you can continue, you'll need a database to work with. Your web hosting control panel should provide some kind of interface for creating new databases, and for giving them usernames and passwords (all database connections need to be authenticated with custom credentials, not with your username and password at the web host.) Find that control panel and create a new, blank database. Save the database name, username, and password in a file - you'll need them in a minute.

On many hosts, you'll need to create the database, create a db user and password, and then give that new user permission to access the database. Give the new username all necessary permissions to use the new database. Return here when your database has been set up and you've successfully given a db user permission to use it.

Giving a database user permission to use a particular database. You must do this before PHP can be configured to speak to the database.

Your control panel should also provide some kind of management interface onto the databases you control. On most hosts, it's called phpMyAdmin. Look for phpMyAdmin or other database management tool at your web host. Open it up and select your blank database. Within that interface, you'll see a button or panel labeled SQL. Click that. You should be looking at something similar to this:

Find the SQL field in your database management tool. Here's the one provided by phpMyAdmin.

Rather than have you create database fields from scratch, I've created a small sample table you can load into your database quickly. Copy the following SQL code to your clipboard, paste it in the empty SQL field, and click Go (we're not going to get into explaining SQL in this tutorial - suffice to say that SQL stands for Structured Query Language, and is used for interacting with databases independently of particular programming languages).

A new table should be created in your database. Select it, then click the Browse button in your database management tool. You should see something like this:

At this point, we'll assume you've verified that the table exists and that your sample data has been loaded. Now, back to PHP. Let's start by writing a script that will pull all records from the database and display them in a table. Notice that we'll put the table header in HTML mode, then generate the tables rows and cells from within PHP mode, and finally switch back to HTML mode for the table footer.

On lines 8,9, and 10, replace the placeholder values with the database name, db username, and db password you established earlier. Line 11 will probably work as-is, unless your web host has provided an alternate "host" or "database server" that you should be using. If the credentials aren't correct, you'll see some messy error output when you try to pull up the script. If so, go back to your host's database management interface and confirm the db name, username, password, and host/server.

If all went well, you'll see the data in your database in a neat little table. Let's step through the code. Lines 12 and 13 set up a connection to the database server and select the database we want it to use. Line 15 gives us some basic error checking in an if/else statement, so we can report back to the user if the database connection could not be made.

Line 17 creates a variable containing a bit of SQL code to make a query against the database (it says simply, "give me all fields of all records" - in practice you'd want SQL here to limit the number of records and fields by some criteria, such as "Give me the headlines and blurbs of all articles published in the past week," but advanced SQL is another tutorial.) Line 18 actually executes the query you just set up.

Line 20 looks a bit familiar - remember when we learned about while() loops earlier? Here we're saying "Loop through rows in the query results until they're exhausted, and save each row temporarily in an array of fields, called $db_row."

On the following lines, we emit HTML table code, where the contents of each cell is an element from the current array $db_row -- just like we did when iterating over arrays earlier in the tutorial.

On line 31, we close the "handle" on the database so it doesn't take up memory in PHP. This line just says "I'm finished for now."

Pretty cool, huh? Now, what if we want to insert a new row into our database? Try this code:

The code here is pretty similar to the previous script, except that it runs a SQL "insert" query instead of a "select" query. Run the script, then pull up the script from the previous example to confirm that new data is now in your database.

We set variables for the values to insert into the database on lines 12-13. Then on lines 18-27, we convert those variables into an actual SQL query. Since we're inserting rather than selecting data, we've removed the HTML table generating code. In actual practice, the data would probably be obtained from form input, but we've hard-coded it here for illustration purposes.

You can write similar scripts to update or delete existing records - learn the SQL statements for those actions and see if you can modify these scripts to perform them!

Conclusion

This has been a very cursory introduction to PHP - just enough to get your feet wet and be off to the races. Believe it or not, you now know enough to look at the code for most content management systems and basically understand what they're doing - maybe even to modify them! If you're feeling ambitious, try building your own PHP scripts from scratch. Remember, it's practically a right of passage to write your own blogging engine or content management system (though I wouldn't recommend putting one into production - that wheel has been invented many times over).

PHP is truly a "Swiss Army Knife" for web developers, and can be used for solving problems large and small in thousands of web publishing environments. Remember - just reading about programming isn't going to get you off the ground. The more time you spend writing actual, working code, the more capable you'll become. Enjoy the process!