One of the most compelling things PHP has going for it is it support for a wide variety of databases. And this week, PHP 101 is going to take advantage of that database support to create dynamic data-driven Web sites. This primer covers different techniques to select, insert and delete records, together with some tips to track and squash bugs when building SQL-driven sites.
If you're familiar with SQL, you know that there are four basic types of operations possible with a database:
SELECT a record;
INSERT a record;
UPDATE a record;
DELETE a record.
In order to demonstrate these operations, we're going to build a little application
that allows users to maintain a list of their three favourite Web sites in a database, and share this list with the rest of the Internet community.
The first step that goes into building such an application involves designing the database tables - in our case, each user will be identified by a unique login id, which will map to the list of three Web sites. We've put together a "dump file", which lets you create the database tables and initial set of records quickly - we suggest that you import this data into your mySQL database server, as we'll be using it throughout this article.
Or you could insert the contents manually - here is what you 'll need:
# Table structure for table 'url_list'
CREATE TABLE url_list (
title1 varchar(30) NOT NULL,
url1 text NOT NULL,
url2 text NOT NULL,
title3 varchar(30) NOT NULL,
PRIMARY KEY (uid)
# Dumping data for table 'url_list'
INTO url_list VALUES( 'john', 'Melonfire',
INSERT INTO url_list VALUES( 'bill', 'Yahoo',
This will create a table named "url_list" with columns for usernames, Web site
titles and Web site URLs for two mythical users, "bill" and "john".
Now check whether or not the data has been successfully imported with a SELECT query (the SELECT SQL statement is used to retrieve information from a database.) Enter this at your mySQL command prompt:
mysql> select count(*) from url_list;
which, in English, means "count all the records in the table url_list and give
me the total", and you should see the number "2" as a result, indicating that there are two records in the table.