Dumped! - PHP

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.

To import the data, download the dump file and use this command at your mySQL prompt:

 mysql> mysql -u username -p database < dumpfile 

Or you could insert the contents manually - here is what you 'll need:

 #
# Table structure for table 'url_list'
#
CREATE TABLE url_list (
uid varchar(8) 
NOT NULL,
title1 varchar(30) NOT NULL,
url1 text NOT NULL,
title2 varchar(30) 
NOT NULL,
url2 text NOT NULL,
title3 varchar(30) NOT NULL,
url3 text 
NOT NULL,
PRIMARY KEY (uid)
);
#
# Dumping data for table 'url_list'
#
INSERT 
INTO url_list VALUES( 'john', 'Melonfire',
'http://www.melonfire.com', 'Devshed',
'http://www.devshed.com', 
'PHP.Net',
'http://www.php.net');
INSERT INTO url_list VALUES( 'bill', 'Yahoo',
'http://www.yahoo.com',
'Slashdot', 
'http://www.slashdot.org', '32Bit.com',
'http://www.32bit.com');

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.

>>> More PHP Articles          >>> More By Vikram Vaswani and Harish Kamath, (c) Melonfire

blog comments powered by Disqus