Web Coding Tips - Guide For Web Programmers

Retrieving Data Using PHP

2011-03-21T00:01:00.003+08:00

If you use SELECT statements with mysql_query(), the result set (as you expect to see displayed on the mysql command line) is passed into result memory, and a result identifier is returned. You normally store this in a variable called $result, and use it to specify your result set in subsequent function calls.

You've already seen how mysql_fetch_row() returns a single row of data from a result set. The simple example in the last chapter used a while loop to step through each row of a result set for the SHOW DATABASES statement. Here's a slightly more advanced version entitled show_db.php.

<?php
include " ./common_db.inc";

$link_id = db_connect('sample_db');
$result = mysql_query("SELECT * FROM user", $link_id);

while($query_data  =  mysql_fetch_row($result)) {
   echo "'", $query_data[1],"' is a ", $query_data[3], "<br>";
}
?>

It returns the following:

'Nicrot' is a Mid
'Spargy' is a Mid
'Pads' is a Winger
'Dodge' is a Link
'Mac' is a Winger
'Greeny' is a Utility back

You begin by connecting to the server with db_connect (which is included in common_db.inc), specifying that you PPwant to use the sample_db database. You query the database with the SQL statement "SELECT * FROM user", whose result set is the entire contents of the user table.

The variable $result stores the returned result identifier, with which you specify the result set when calling mysql_fetch_row(). This in turn fetches the first row of results, from which you echo the second and fourth fields (userid and userposition, respectively). The internal pointer then moves to the next row, and the while loop cycles until all the rows in the table have been fetched.

You can achieve the same result more efficiently by saying:

$result = mysql_query("SELECT userid, userposition FROM user", $link_id);

while($query_data = mysql_fetch_row($result)) {
   echo "'", $query_data[0] , "' is a ",$query_data[1],"<br>";
}

By extracting only the fields in which you're interested, you save both time and memory.

PHP has two more functions that you can use to fetch data: mysql_fetch_array() and mysql_fetch_object(). Both basically work the same as mysql_fetch_row(), the only difference being their return types. This is best illustrated by showing how the preceding example would look if you'd used either of these other functions to fetch values from the result set.

mysql_fetch_array() returns a single associative array from a result set, storing each entry in the record against the respective field name. You'd therefore say:

while($query_data = mysql_fetch_array($result)) {
   echo "'", $query_data["userid"],"' is also known as ",
   $query_data["userposition"], "<br>";
}

mysql_fetch_object() returns a single object from a result set, storing each entry as a property of that object. The properties are named according to the field name, so you might say:

while($query_data = mysql_fetch_object($result)) {
   echo "'",$query_data->userid," ' is a ",
   $query_data->userposition,"<br>";
}

These two functions are particularly useful in situations where you might need to alter the structure of the database tables. Unless you change the names of the fields, you don't have to worry about the order in which they appear in the table because you can fetch the field value by specifying its name and not its index number. Although both functions are slightly slower than mysql_fetch_row(), they do have the added benefit of making your scripts more readable (later in a long stretch of code, for example, you might get confused about to which field is being referred to if you were just specifying indices).

There's one more function that deserves a mention at this point: mysql_result(), which returns the value of a specified field in a specified row. It takes the usual result indicator, plus a row number and field name as its arguments. You could rewrite the example like this:

$result = mysql_query("SELECT * FROM user", $link_id);
for($i  = 0;  $i < mysql_num_rows($result) ; $i++) {
   echo  "'",  mysql_result($result, $i, "userid"),"' is a ,
   mysql_result($result, $i, "userposition"),"<br>";
}

You also have the option of doing it backward!

$result = mysql_query("SELECT * FROM user", $link_id);

for($i = mysql_num_rows($result)-1; $i >=0; $i--){
   echo "'", mysql_result($result, $i, "userid"), " is a "',
   mysql_result($result, $i, "userposition"),"<br>";
}

And your result set will be:

'Greeny' is a Utility back 
'Mac' is a Winger
'Dodge' is a Link 
'Pads' is a Winger 
'Spargy' is a Mid 
'Nicrot' is a Mid

The field name argument can also be written as an integer indicating the field's offset:

echo   "'",   mysql_result($result, $i, 1),"' is a ", mysql_result($result, $i, 3),"<br>";

Another way to jump straight to a specific row of data is to use the mysql_data_seek() function. It works in a similar way to the fseek() function, which you used in Chapter 7 to navigate within a file stream. It takes as arguments a result indicator and an integer that indicates the position in the result set to move to. As you might expect, the function returns True on success and False if you move past the array boundary. Save this file as show_seek.php and view it in your browser:

<?php
include "./common_db.inc";

$link_id = db_connect('sample_db');
$result = mysql_query("SELECT * FROM user", $link_id);

for($i = mysql_num_rows($result)-1; $i >=0; $i--){
   mysql_data_seek($result,   $i);
   $query_data  = mysql_fetch_array($result);
   echo "'", $query_data["userid"], "' is also known as",
   $query_data["username"], "<P>";
}
?>

You've now seen a number of different ways to access result sets from your PHP scripts. With these techniques under your belt, you should be able to deal with quite a broad range of situations, and show the data you want, presented the way you want it. However, this is only half the story.

You already know that you can improve the efficiency of your scripts by keeping the result sets small. You did this by specifying the fields you wanted to view in the original SELECT statement:

$result = mysql_query("SELECT userid, username FROM user", $link_id);

What's more, you had to go to some trouble to show the results in reverse order. What if you want them displayed alphabetically? The mind boggles! You're going to save yourself a whole lot of bother if you can arrange the data that gets put into the result set. In fact, there is a huge variety of data manipulation that is almost certainly going to be more efficiently performed by a well-constructed SQL query than by PHP itself, in terms of both execution time and complexity. For that reason, let's get back to some pure MySQL and take a look at what's possible with the SELECT command.

Chapter 10: Retrieving Data From MySQL using PHP

2011-03-14T00:01:00.003+08:00

Up to now, we've concentrated mainly on ensuring that you can connect to MySQL, either through the client or through PHP's mysql functions, and create and fill tables with data. One of the first SQL statements you came across in Chapter 9 was a basic SELECT statement. There's quite a lot more you can do to precisely define the kind of data you want to retrieve, and this chapter focuses on the different ways you can use PHP scripts to get at the data stored in a MySQL database.

We start off by looking at PHP's data retrieval functions, and then take a close look at how to construct SQL SELECT statements so that they access the data you want, arranged in the way you want.

You'll learn how to limit the number of results returned, and how to order and group them, and you'll build a "user record" viewer script that'll enable you to navigate around the database tables you developed in the last chapter.

Secure Password Generator

2011-03-07T00:01:00.012+08:00

During the course of my work I frequently set or sign up for accounts on a variety of sites. Its a good practice to vary your passwords and make them hard enough so that hackers and other scrupulous people will find it impossible to break them.

Here are a few simple rules when making passwords:

Use tools that automatically generate random passwords.

Use alphanumeric characters and unique symbols to create stronger passwords.

Instead of mnemonics, try a pass-phrase.

Change passwords periodically.

Avoid generating passwords using personal information.

For convenience, you can also try this online password generator.

Free Internet Marketing Videos

2011-02-28T00:01:00.003+08:00

Yesterday, I just got back from a *private* Affiliate event in Vegas. Great networking.

While at the event, I got to meet and hang out with a lot of successful online marketers.

But, one guy stood out amongst all the rest.

He is a CRUSHER -- made over $10 million online. Yet, you'd *never* know it by meeting
him.

Super nice guy.

He's not an Internet Marketing "guru". Chances are you've never heard of him.

People love his straight forward approach to making money. His training videos are very
easy to follow and filled with stuff you can implement right away.

For example, hit the link below and check out these 5 free videos:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1cogeaTnWgcD0P&b=TnR8eU8wulPtdwDzo.eolw

Note: You'll need to opt-in to get access to all the videos, but it's well worth it. Don't worry,
you are not going to get a bunch of stupid emails. This guy is different. You'll get a lot of
value and stuff you can actually use.

Go check it out now before you forget about it:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1cogeaTnWgcD0P&b=TnR8eU8wulPtdwDzo.eolw

Online Lead Finder - Ready For Download

2011-02-21T00:01:00.003+08:00

You asked me to let you know when I will be allowing people to download the Online Lead Finder again.... well I am back and its now open!!

www.onlineleadfinder.com

Please register for the free members area, so you can download it.

We have some amazing updates that will be released also over the next months. Remember the only way to get these updates is to stay on my mailing list - Don't worry, we wont spam you !

Enjoy the software.

Trademarks in Domain Names?

2011-02-14T00:01:00.006+08:00

Personally, I think it's a bad (risky) idea to use a trademarked name in your domain name.

You are just asking for trouble.

This is a perfect example:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1czW2OFFmgcD0P&b=jUZq1SrxoEnDH5GcnZsjFQ

But, the tactic they reveal to you is totally *killer*.

It works UNBELIEVABLY well -- nothing sneaky, shady or illegal here either.

If you want lots of good traffic (for pennies per click) check out this little loophole.

This video explains how it works:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1czW2OFFmgcD0P&b=jUZq1SrxoEnDH5GcnZsjFQ

I'd go watch this now if I were you.

Why?

As you probably know, Google recently went postal on a bunch of websites that included
the word "Google" in the domain name. Lawsuits galore. Those sites all got taken down.

Once you visit this site, you'll know what I mean:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1czW2OFFmgcD0P&b=jUZq1SrxoEnDH5GcnZsjFQ

Dangerous move using that domain name... wouldn't you say?

P.S. - The guy behind this is a *really* sharp online marketer. I'm surprised that he chose to use that domain name. But, the guy a genius. So, I'm sure he has his reasons.

Go check out the video now before "Big G" steps in and puts the brakes on this:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1czW2OFFmgcD0P&b=jUZq1SrxoEnDH5GcnZsjFQ

Online Lead Finder Software - Enhancement Requests

2011-02-07T00:01:00.003+08:00

I just wanted to let you know that we will be continuing to update the Online Lead Finder software this year.

I wanted to ask if you had any suggestions for enhancements or new ideas to help make it the better. Please also let me know why you want the feature enhancement, and how you would use it . If your idea is considered, I will give you a Free FULL copy of the software!

Let me know at onlineleadfinder@gmail.com

I look forward to hearing from you.

Magic Bullet System - Bonuses

2011-01-31T00:01:00.016+08:00

If you read my e-mail yesterday, you should already know about this...

Amish Shah's Magic Bullet System goes live today at 3PM Eastern.

I put together a BIG bonus for you when you order The Magic Bullet System through my link

(see below, only 25 available).

As I'm sure you already know, throughout 2009 everyone was talking about...

>>> CPA MARKETING

The problem?

CPA marketing takes a lot of manual work.

And nobody gives you all the tools you need to get the job done, quickly and easily.

Well... today, that has changed.

The Magic Bullet automates the process, which means you will crank out more money-making CPA profit centers, faster than ever before.

Once you combine The Magic Bullet System with my bonuses below, you'll be on the fast-

track to CPA success:

>> Bonus #1: Private, closed door "CPA Money Fast-Track" teleseminar with Amish Shah

>> Bonus #2: Private, CPA Money Mastermind Think-Tank

>> Bonus #3: LIFETIME ACCESS to PPC Kahuna (worth several thousand dollars)

To claim these bonuses, just hit the link below and order The Magic Bullet:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1ehvQrMN7gcD0P&b=J8jaJDcfU.RZB60G9p2iMQ

There are only 25 of these bonuses available.

(once you place your order using the above link, send your email receipt to support@traffickahuna.com)

I look forward to personally working with you to ensure that you CRUSH IT in 2011 using The Magic Bullet as your secret weapon.

Go here now for details and to reserve your spot:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1ehvQrMN7gcD0P&b=J8jaJDcfU.RZB60G9p2iMQ

Time is running out (no joke).

I just got word that The Magic Bullet System will be sold out at midnight Eastern tonight (or sooner).

Obviously, the demand for this is HUGE.

>> Bonuses still available...

If you think you are too late to get my awesome "Success Booster" bonuses, you are not too late.

We've decided to add another 25 spots since the first 25 spots were gone within the first hour! It's only fair.

Just make sure that you place your order before midnight tonight, so that you don't miss out:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1eZWXsErtgcD0P&b=hmYDKyoyyDTG7vmJZTf0Kw

>> Can I afford it?

Yes. These guys offer a sweet payment plan that makes this affordable to almost everyone. Several people who already ordered through my affiliate link are taking the monthly payments versus one payment.

>> Is there a guarantee?

Yes. To put you at ease, there is a generous guarantee. Nobody is trying to trick you here. Put The Magic Bullet System to the test. Give it a shot. You are in complete control.

>> Here's what you need to do right away...

To claim your bonuses from me, just hit the link below and order The Magic Bullet System:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1eZWXsErtgcD0P&b=hmYDKyoyyDTG7vmJZTf0Kw

(once you place your order using the above link, send your email receipt to

support@traffickahuna.com)

I look forward to personally working with you to ensure that you CRUSH IT in 2011 using The Magic Bullet as your secret weapon.

Go here now for details and to reserve your spot:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1eZWXsErtgcD0P&b=hmYDKyoyyDTG7vmJZTf0Kw

P.S. - If you would like more information about the "Success Booster" bonuses you will receive from us, visit the blog here:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1eZWXsErtgcD0P&b=K2LM0w4qmy.aBw8H12bG0A

Whatever you do, do it fast because time REALLY IS running out on this!

What is the Purpose of Your Blog for Building Your Business?

2011-01-24T00:01:00.006+08:00

Tomorrow (Monday, Jan 25th) you will be hearing a lot about Amish Shah's Magic Bullet System.

That's why I wanted you to read this email TODAY before all the craziness kicks in.

As usual, everyone and their uncle is going to be offering you various bonuses.

But NOTHING like these "Success Booster" bonuses, I can assure you.

Read all about them here:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1cXbdu4HBgcD0P&b=07X9wXh5bWfPUdUeKqhMyA

As you will see, my partner and I are putting a lot on the table here -- investing *months* of our personal time -- working with you to put you on the CPA-money fast-track to success.

You'll also get *lifetime* access to our powerful Pay-Per-Click system and software... and more!

This is *not* your usual ho-hum bonus.

Get all the details and read this blog post:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1cXbdu4HBgcD0P&b=07X9wXh5bWfPUdUeKqhMyA

I hope you decide to grab one of the 25 available spots and join us.

Easy Button - Traffic Bot Software

2011-01-17T00:01:00.008+08:00

Over the past 9 years, I've been involved in many software projects.

Some simple. Some very complex.

But, I've NEVER seen a *monster* like this.

A few minutes ago, I watched a video about a guy who doubled his profits in just 8 days (which puts an extra $171,272.60 in his pocket per year).

How did he do it?

It took 5 minutes, using his ingenious Easy Button "traffic bot" software.

This software automatically grabs what keywords to use, what to promote, builds a blog for you.

Then, it tweaks everything automatically -- to squeeze out even *more* traffic and profits.

IT'S UNREAL!

If you've ever wanted an online marketing "Easy Button", this is it:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1bp0.xTBVgcD0P&b=XfogpWzkozAc4Igjio.ghA

If I tried to describe this software to you, it would seem unbelievable or over-the-top.

That's why you really need to see this in action (like I did, by watching this video) to truly
believe it:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1bp0.xTBVgcD0P&b=XfogpWzkozAc4Igjio.ghA

This thing is a beast. Crazy powerful and totally automated.

You'll see.

Creating Databases and Tables From MySQL

2011-01-10T00:01:00.011+08:00

Let's go back to the command line to construct a database from the mysql client. To create a database called sample_db, you just have to say:

mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.05sec)
mysql>

Removing it is just as simple:

mysql> DROP DATABASE sample_db;
Query OK, 0 rows affected (0.00sec)
mysql>

The number of rows mysql counts as having been affected doesn't include the ones it has removed. You could have just deleted thousands of data entries, but from the information presented here, you'd have no way of knowing! This is all the more reason to use extreme caution—this small, harmless-looking command will totally wipe out the specified database and all the tables in it, and won't even hint at what it's done, let alone ask you for confirmation. You have been warned!

Assuming you just got rid of your sample_db database, CREATE it again. You'll use it rather a lot in the next couple of chapters, and the rest of the examples in this chapter take you through the steps to set it up with a structure and records.

Creating a database table is a little more complicated. Let's say you want to create a table called user. Start off with the obvious syntax:

mysql> CREATE TABLE user;
ERROR 1113: A table must have at least 1 column
mysql>

Okay, you're going to need a little more than that. Although a database can quite happily exist without any tables living under its roof, tables are rather more fussy. Give it some structure by putting a list of the field descriptors within parentheses following the table name. The field descriptors each follow the form:

fieldname TYPE(length)

For example, you could create a very simple two-field table with:

mysql> CREATE TABLE test_table (name CHAR(40), number INT);
Query OK, 0 rows affected (0.06sec)
mysql>

You already had a look at the various types of variables supported by MySQL. You also have the option of specifying attributes for each of the fields—to do so, simply append them to the descriptor. You can use any combination of the attributes described in the following table.

Attributes	Description
BINARY	Makes the field's value case-sensitive. Works only with CHAR and VARCHAR type fields
NULL, or NOT NULL	NOT NULL fields cannot take a NULL value. Fields that are declared NULL will store a specified default value whenever a NULL value is given. If there is no default value provided, they simply store NULL. If unspecified, NULL is assumed
DEFAULT default_value,	Specifies the default value to store when NULL is given
AUTO_INCREMENT	When a NULL value is placed in an AUTO_INCREMENT field, the field value is set to one greater than its current maximum value in that table. AUTO_INCREMENT only works with unique, integer type fields There can be only one AUTO_INCREMENT field per table

After you've listed all those that you need, you can specify selected fields as holding indexes, primary keys, and unique values. Here's a quick recap of what each of these entails:

KEY/INDEX: These are synonymous keywords that specify fields to be used as indexes. The specified field entries are copied to a separate index table, where they're listed against pointers to the corresponding entries in the original table. They're then sorted, producing an index much the same as you'd find in the back of a book. If you specify multiple fields ("field1", "field2",...) for your index, the index sorts first on "field1", and then any duplicate values in "field1" are sorted on "field2", and so on.
PRIMARY KEY: You can specify exactly one field in any given table as a primary key; it is then be used as an index of unique values. Consequently, each entry can be used in other tables as a unique reference to the record to which it belongs—it's essentially the glue that holds a relational database together. To use a field as a primary key, it must be declared NOT NULL. Once again, if you pass more than one field name when defining a primary key, the specified field names are combined to create a primary key.
UNIQUE: The value of each entry in a unique field must be unique among all other entries in the same field. That is, if one record's entry in the field contains the value 10, it's guaranteed that no other entry in that field will have the same value. As mentioned, uniqueness is a requirement for AUTO_INCREMENT fields.

In the course of the next couple of chapters, you'll see a lot of examples based around a homemade relational database comprising two tables. The first is called user, and it serves as a record of all users registered on an imaginary Web server. The other, access_log, contains relative paths to the Web pages each user accesses, plus counters measuring how many times she visits each page. Both tables are housed in the sample_db database.

The first table, user, is structured as follows:

Field	Type	Null	Attributes
Usernumber	MEDIUMINT(10)	no	AUTO_INCREMENT
Userid	VARCHAR(8)	no	BINARY
Userpassword	VARCHAR(20)	no	BINARY
Username	VARCHAR(30)	no	none
Userposition	VARCHAR(40)	no	none
Useremail	VARCHAR(50)	no	none
Userprofile	VARCHAR(250)	no	none

The usernumber field has an AUTO_INCREMENT attribute, so the field is automatically incremented (that is, its value increased by 1) whenever a new record is inserted with a NULL value in the first field. For example, when a new user registers and his record is added to the user table, the user number assigned will be one more than the largest number that's already in the table. You also declare this field UNIQUE, ensuring that no two rows can have the same value.

The fields userid and userpassword are both variable-length strings (lengths specified as 8 and 20 characters, respectively). They are declared BINARY, and are therefore case-sensitive. By default, character fields are case-insensitive.

The userid field will be defined as a primary key and used to establish a relationship with the access_log table, which also has a userid field.

The userposition field describes the position that a player on the team holds—in the example there are values such as Mid and Link, but you could easily modify this to football positions such as quarterback or wide receiver if your database was used to store information about an American football team.

The second table, access_log, is defined as follows:

Field	Type	Null	Attributes
page	VARCHAR(250)	no	None
userid	VARCHAR(8)	no	BINARY
visitcount	MEDIUMINT(5)	no	None
accessdate	TIMESTAMP(14)	yes	None

A TIMESTAMP type field can always take a NULL value because it stores the current system time in YYYMMDDhhmmss format when a new record is inserted, or an existing record is updated, unless it is explicitly given a date and time to store.

The access_log table has the same userid field as the user table. The userid field serves as the key to define a relationship between the two tables.

Now that you've defined the structures of the tables, let's create them and their home, the sample_db database.

If you're dreading the prospect of typing in the following 12-line SQL statement (because you don't want to have to retype the whole thing when it doesn't work the first time), here are a couple of suggestions.

If you're running PHP from UNIX, use the edit command from the mysql command line, which calls the text editor of your choice (this command doesn't work on Windows). This is held in the $EDITOR environment variable, and is typically the vi editor: mysql> edit. If you make a typo, just reissue the edit command again. Your editor still has what you previously typed in.

The previous command is remembered by the mysql client program. You can go back a single line by pressing the up arrow key.

You can save your lines of SQL in a text file and feed it to the mysql client: > mysql uphpuser pphppass hlocalhost < query.sql

The following CREATE TABLE command creates the user table in the sample_db database:

mysql> CREATE TABLE user (
   ->     usernumber MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
   ->     userid VARCHAR(8) BINARY NOT NULL,
   ->     userpassword VARCHAR(20) BINARY NOT NULL,
   ->    username VARCHAR(30) NOT NULL,
   ->    userposition VARCHAR(50) NOT NULL,
   ->    useremail VARCHAR(50) NOT NULL,
   ->    userprofile TEXT NOT NULL,
   ->    PRIMARY KEY (userid),
   ->    UNIQUE usernumber (usernumber)
   -> );
Query OK, 0 rows affected (0.69 sec)
mysql>

The following CREATE TABLE command creates the access log table:

mysql> CREATE TABLE access_log (
   ->     page VARCHAR(250) NOT NULL,
   ->     userid VARCHAR(8) BINARY NOT NULL,
   ->     visitcount MEDIUMINT(5) DEFAULT '0' NOT NULL,
   ->     accessdate TIMESTAMP(14),
   ->     PRIMARY KEY (userid, page));
Query OK, 0 rows affected (0.00 sec)
mysql

The userid in the access_log table isn't declared as a primary key or a unique value because a user can access multiple Web pages, and each visited Web page constitutes a record in the table. Instead, the combination of userid and page is defined as the primary key.

Creating the Sample Database and Tables with PHP

If all has gone according to plan, you are now the proud owner of a couple of tables, user and access_log. All well and good, but is the main topic slipping away? The fact is that it's just as easy to generate these tables from PHP scripts. Let's see how you might create the same database and tables using PHP instead of the mysql client.

First, you need a way to issue queries to the MySQL server from within PHP. The mysql_query() function is for precisely that purpose. It takes a query string as its first argument, and makes that query on the currently selected database, using the link identifier specified as the second argument. (As usual, you can omit the identifier, in which case the last opened link is assumed.) If no link is currently open, it attempts to establish a connection first.

Do not use semicolons to terminate query strings when using them as arguments to PHP functions because they may cause your script to kick up errors.

If the given query is successfully executed, mysql_query() returns a non-zero value pointing to the returned result set, or False upon error. The following call is equivalent to having made the query SHOW DATABASES in the mysql client:
$link_id = db_connect();
$result = mysql_query("SHOW DATABASES", $link_id);
while($query_data = mysql_fetch_row($result)) {
  echo $query_data[0],"<P>";
}

This passes the expected list of databases to a table, returning a pointer to that table. You then just fetch each row and echo it out. It should look like this:

mysql
sample_db
test

$link_id could have been omitted from the previous code snippet because the mysql_query() function would have defaulted to the last opened connection.

If you want to specify the name of the database to issue a query on, you can use the PHP function mysql_db_query() like this:

$result = mysql_db_query("sample_db", "SHOW TABLES");

This call returns the list of tables available in the sample.db database—even if you haven't used mysql_select_db() to select it explicitly.

You can also use mysql_query() to create and drop databases:

$result = mysql_query("CREATE DATABASE dummy_db");
$result = mysql_query("DROP DATABASE dummy_db");

PHP provides you with another alternative—a pair of functions specifically designed for creating and dropping databases: mysql_create_db() and mysql_drop_db(). Both take the name of a database to be created or dropped as the first argument, followed by an optional link identifier, and return True if a database is successfully created/dropped and False upon error. For example:

$link_id = db_connect();
if(!mysql_create_db("dummy_db", $link_id)) die(sql_error());
                       echo "Successfully created the database dummy_db.";
if(!mysql_drop_db("dummy_db", $link_id)) die(sql_error());
                       echo "Successfully dropped the database dummy_db.";

Try it Out: Create a Database and Tables

The following script is an alternative to your earlier exploits at the command line. First, it creates the sample_db database, and then it defines the tables user and access_log as specified previously.

<?php
include "./common_db.inc";

$dbname = "sample_db";
$user_table = 'user';
$user_table_def = "usernumber MEDIUMINT(10)DEFAULT '0' NOT NULL
AUTO_INCREMENT,";
$user_table_def .= "userid VARCHAR(8) BINARY NOT NULL,";
$user_table_def .= "userpassword VARCHAR(20) BINARY NOT NULL,";
$user_table_def .= "username VARCHAR(30) NOT NULL,";
$user_table_def .= "userposition VARCHAR(50) NOT NULL,";
$user_table_def .= "useremail VARCHAR(50) NOT NULL, ";
$user_table_def .= "userprofile TEXT NOT NULL,";
$user_table_def .= "PRIMARY KEY (userid),";
$user_table_def .= "UNIQUE usernumber (usernumber)";

$access_log_tablename = "access_log";
$access_log_table_def = "page VARCHAR(250) NOT NULL,";
$access_log_table_def .= "userid VARCHAR(8) BINARY NOT NULL, ";
$access_log_table_def .= "visitcount MEDIUMINT(5) DEFAULT '0' NOT NULL,";
$access_log_table_def .= "accessdate TIMESTAMP(14),KEY page (page),";
$access_log_table_def .= "PRIMARY KEY (userid, page)";

$link_id = db_connect();
if(!$link_id) die(sql_error());

if(!mysql_query("CREATE DATABASE $dbname")) die(sql_error());

echo "Successfully created the $dbname database.<BR>";

if(!mysql_select_db($dbname)) die (sql_error());

if(!mysql_query("CREATE TABLE $user_tablename ($user_table_def)"))
                                                      die(sql_error());

if(!mysql_query("CREATE TABLE $access_log_tablename ($access_log_table_def)"))
  die (sql_error());

echo "Successfully created the $user_tablename and $access_log_tablename
tables.";

?>

Assuming that you followed along with the command line example, running this script would generate a nice friendly error:

1007: Can't create database 'sample_db'. Database exists

To overcome this, simply go back to the command, and use the mysql client to DROP the sample_db database, and then run the script again. You should see the following output:

Successfully created the sample_db database.


Successfully created the user and access_log tables.

How it Works

The script is very straightforward. It begins by including common_db.inc, giving you use of the functions db_connect() and sql_error(), which you defined earlier:

<?php
include "./common_db.inc";

You then define the variables that describe the two tables. This is where all the hard work pays off—you just have to specify the same field descriptors that you did at the command line:

$dbname = "samp1e_db";
$user_tablename = "user";
$user_table_def = "usernumber MEDIUMINT(10) DEFAULT '0' NOT NULL
AUTO_INCREMENT,";
$user_table_def .= "userid VARCHAR(8) BINARY NOT NULL, ";
$user_table_def .= "userpassword VARCHAR(20) BINARY NOT NULL,";
$user_table_def .= "username VARCHAR(30) NOT NULL,";
$user_table_def .= "userposition VARCHAR(50) NOT NULL,";
$user_table_def .= "useremail VARCHAR(50) NOT NULL, ";
$user_table_def .= "userprofile TEXT NOT NULL,";
$user_table_def .= "PRIMARY KEY (userid),";
$user_table_def .= "UNIQUE usernumber (usernumber)";

$access_log_tablename = "access_log";
$access_log_table_def = "page VARCHAR(250) NOT NULL,";
$access_log_table_def .= "userid VARCHAR(8) BINARY NOT NULL,";
$access_log_table_def .= "visitcount MEDIUMINT(5) DEFAULT '0' NOT NULL,";
$access_log_table_def .= "accessdate TIMESTAMP(14),KEY page (page),";
$access_log_table_def .= "PRIMARY KEY (userid, page)";

Connect to the server with the following:

$link_id = db_connect();
if(!$link_id) die(sql_error());

Create the sample_db database:

if(!mysql_query("CREATE DATABASE $dbname")) die(sql_error());
echo "Successfully created the $dbname database.<BR>";

And then select it with mysql_select_db():

if(!mysql_select_db($dbname)) die(sql_error());

Subsequent calls to mysql_query() create the user and access_log tables. If an error occurs during a query, the error number and text are displayed by calling the sql_error() function.

if(!mysql_query("CREATE TABLE $user_tablename ($user_table_def)"))
                                                           die(sql_error());

if(!mysql_query("CREATE TABLE $access_log_tablename ($access_log_table_def)"))
                                                           die(sql_error());

If you make it through to the end of the script, you confirm a successful run:

echo "Successfully created tables $user_tablename and $access_log_tablename.";
?>

Recall that the sql_error() function calls the mysql_errno() and mysql_error() functions if the $MYSQL_ERRNO variable is empty:

if(empty($MYSQL_ERRNO)) {
    $MYSQL_ERRNO = mysql_errno();
    $MYSQL_ERROR = mysql_error();
}

This ensures that the function returns the error number and text even when a database connection is successful but a subsequent query fails.

Altering Tables

So you've created a database, and populated it with carefully constructed tables. You've used it for a while and not had too much trouble—it's not perfect, but it does the job. Then out of the blue, you realize one day that your little database isn't all it's cracked up to be. You've caught on to the fact that your tables are badly designed. You've found some new data that doesn't quite fit the format you expected. Any number of things can make you sit up and realize that you need to modify the tables in your database.

MySQL provides the ALTER TABLE command to do just that. With this command you can:

Add and delete fields or indexes (ADD and DROP).
Change the definition of existing fields (ALTER, CHANGE, and MODIFY).
Rename fields (CHANGE) or even the table itself (RENAME AS).

For example, if you wanted to change the name of the table test to tested, you could issue the following command:

mysql> ALTER TABLE test RENAME AS tested;
Query OK, 0 rows affected (0.02 sec)

The AS keyword is optional—this works just as well:

mysql> ALTER TABLE test RENAME tested;
Query OK, 0 rows affected (0.02 sec)

Let's add a new ENUM field to the user table. Call it sex; it can be used to indicate whether a user is male or female:

mysql> ALTER TABLE user ADD sex ENUM('M', 'F') DEFAULT 'M';
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0 Warnings: 0

mysql> DESC user;
+----------------+---------------+------+-----+--------+----------------+
| Field          | Type          | Null | Key | Default| Extra          |
+----------------+---------------+------+-----+--------+----------------+
| ...            |               |      |     |        |                |
| sex            | enum('M','F') | YES  |     |  M     |                |
+----------------+---------------+------+-----+--------+----------------+
10 rows in set (0.00 sec)

The new field is added as the last field in the table. You can insert a new field in the middle of a table by means of the keyword AFTER. Now drop the field so that you can insert it again:

mysql> ALTER TABLE user DROP sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0 Warnings: 0

To place the new sex field right after the username field, you could say:

mysql> ALTER TABLE user ADD sex ENUM('M', 'F') DEFAULT 'M' AFTER username;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0 Warnings: 0

mysql> desc user;
+----------------+--------------------+------+-----+------------+----------+
| Field          | Type               | Null | Key | Default    | Extra    |
+----------------+--------------------+------+-----+------------+----------+
|...             |                    |      |     |            |          |
| sex            | enum('M','F')      | YES  |     |  M         |          |
+----------------+--------------------+------+-----+------------+----------+
10 rows in set (0.00 sec)

To place a new field at the start of the field list, you'd use the FIRST keyword instead, because there would be no preceding field.

If you're running a site aimed at female visitors, you'd probably want the default value of the sex field changed from M to F:

mysql> ALTER TABLE user ALTER sex SET DEFAULT 'F';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0 Warnings: 0

To change the whole definition of a field, use the MODIFY keyword:

mysql> ALTER TABLE user MODIFY userprofile VARCHAR(250) NOT NULL
    -> DEFAULT 'No Comment';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0 Warnings: 0

To change the field's name and its definition, you can use the CHANGE keyword:

mysql> ALTER TABLE user CHANGE userposition playerposition
VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0 Warnings: 0

Adding or dropping indexes or primary keys is just as easy:

mysql> ALTER TABLE user ADD INDEX (username);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0 Warnings: 0
mysql> ALTER TABLE user DROP INDEX username;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0 Warnings: 0

Experiment a little with the ALTER TABLE command, and you'll soon master it. As an exercise, change back the structure of the user table.

Altering table structures in PHP is as simple as issuing any other query using the mysql_query() function. Assuming you're connected to the sample_db database, the following line is all it takes to drop the index registerdate:

mysql_query("ALTER TABLE user DROP INDEX registerdate");

Inserting Data into a Table

It's time to insert some data into the tables you created. (You'll see how to insert data using PHP in the next chapter, where it's dealt with in great detail.)

Unsurprisingly, you use the SQL command INSERT to insert new records into a table. Try this:

mysql> INSERT INTO access_log VALUES('/score.html', 'Pads', 2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM access_log WHERE userid = 'Pads';
+-------------+--------+------------+----------------+
| page        | userid | visitcount | accessdate     |
+-------------+--------+------------+----------------+
|/score.html  | Pads   |          2 | 20040804153559 |
+----------------------------------------------------+
1 row in set (0.00 sec)

If you're inserting a string value, you have to specify it in quotes.

If you wanted to carry on using this syntax, you'd need to specify all the values to be inserted into every corresponding field in a table. You give NULL as a value for the accessdate field, which is of type TIMESTAMP so that it saves the current system time in the format YYYYMMDDhhmmss. You provide a NULL or 0 value to an integer type field with an AUTO_INCREMENT attribute.

If you want to insert values into only a subset of the fields in a table, you use a slightly different syntax:

mysql> INSERT INTO access_log (page, userid, visitcount)
    -> VALUES('/stats.html', 'Pads', 1);
Query OK, 1 row affected (0.00 sec)

Assuming this query is successful, the specified fields are given the values listed, whereas the rest are assigned default values. For example, accessdate is a timestamp field, so its default is the current system time—just what you want:

mysql> SELECT * FROM access_log WHERE userid = 'Pads';
+-------------+--------+------------+----------------+
| page        | userid | visitcount | accessdate     |
+-------------+--------+------------+----------------+
|/score.html  | Pads   |          2 | 20040804153559 |
|/stats.html  | Pads   |          1 | 20040804152382 |
+-------------+--------+------------+----------------+
1 row in set (0.00 sec)

Escaping Quotes

When you insert a string value into a character or text type fields, make sure no unescaped single quotes are inserted, otherwise you're bound to see an error:

mysql> INSERT INTO user (userproflie)
    -> VALUES('I'm a rugby player.');
    '>
    '>
    '> ';
ERROR 1064: You have an error in your SQL syntax near 'm a PHP developer.');
 at line 1

Even if you issue the query terminating it with a semicolon, the mysql client insists on getting more of it because the MySQL server expects another single quote to pair off the last one. That's why there's a quote mark before the prompt. When you supply one more single quote to satisfy the server's expectation, it generates an error. The solution? You can either escape the inside single quote:

mysql> INSERT INTO user (userprofile) VALUES('I\'m a rugby player.');

Or use double quotes to surround the string value:

mysql> INSERT INTO user (userprofile) VALUES("I'm a rugby player.");

Remember that a backslash (which is used to denote a directory in a path on the DOS/Windows platform, for example) also needs to be escaped. In this case, use a double backslash:

mysql> INSERT INTO user (userprofile) VALUES("C:\\Program Files\\PHP");

If you try to insert a new record that contains the same value for a primary or unique key of an existing record, an error occurs:

mysql> INSERT INTO user (userid, userprofile)
    -> VALUES('Pads', 'I\'m a rugby player.');
ERROR 1062: Duplicate entry 'Pads' for key 1

The MySQL server complains that you are trying to insert a duplicate record that contains the same value for a primary key, userid. User Pads already exists in the table. Because the userid field is defined as a primary key, no duplicate entries are allowed.

However, you might want to insert a new record overwriting the existing one. Say, for example, that you inserted wrong values for a user record and rather than deleting the record and inserting it again, you want to replace it with a new correct one. In this case, you'd use the REPLACE command. The only difference between the INSERT and REPLACE is this: if the primary key for the new record duplicates an existing one, REPLACE overwrites the existing record, although INSERT won't, as you've seen.

mysql> REPLACE INTO user (userid, userprofile)
    -> VALUES('Pads', 'I\'m a rugby player.');
Query OK, 1 row affected (0.00 sec)

Populating the Database Tables

Now you're all set to put some records into your tables. For example, you can use the following INSERT command to insert the record for the user Pads:

mysql> INSERT INTO user VALUES(
    -> NULL,
    -> 'Pads',
    -> password('12345'),
    -> 'Brian Reid',
    -> 'Winger',
    -> 'Stickypads@doggiesrugby.co.za',
    -> 'A top class ball-handler.');

Use the function password() to encrypt the password 12345. This is a MySQL server function that you'll learn more about these in the next chapter.

Give a NULL value to the usernumber field—you'll remember that because it's an AUTO_INCREMENT field, this automatically adds one to the number assigned to each new user.

Here's how I've populated the user table. Please note that a password field isn't included because the values saved by mysql will be encrypted anyway. You should insert whatever values you want for the password field for each user:

user number	User	User name	User position	User email	User profile
1	Pads	Brian Reid	Winger	Stickypads@doggiesrugby.co.za	A top class ball-handler
2	Nicrot	Nic Malan	Mid	therot@mywebsiteaddress.com	Can't stop the rot
3	Spargy	Andrew Sparg	Mid	Spargy@whatyoumaycallit.com	Never gets the ball from Dodge
4	Dodge	Dave Mercer	Link	davidm@contechst.com	The admirals!
5	Mac	Murray McCcallum	Winger	murray@doggiesrugby.co.za	That is not my number
6	Greeny	Mark Greenfield	Utility back	greeny@greenyweb.co.za	Would never drop the ball over the line

The following command inserts an access log record for user Greeny in the access_log table:

mysql> INSERT INTO access_log VALUES(
    -> '/penalties/index.html',
    -> 'Greeny',
    -> 9,
    -> '20040321123155');

Again, the accessdate field would normally be assigned a NULL value. The access_log table need only contain a few dummy records for use in the upcoming examples. It will be populated automatically toward the end of the database chapters by the application you build, which uses your table to log the number of accesses made on your fictional Web site.

Congratulations! You now have a database, populated and ready for use!

PHP MySQL Connectivity

2011-01-03T00:01:00.009+08:00

To work with a MySQL server (or any other RDBMS) in PHP, you must follow these steps:

Open a connection to the server.
Work with databases in the server.
Close the connection.

Does this list sound familiar? You followed similar steps when working with files and directories in the preceding chapter. No big deal!

Basic Connection Functions

Let's begin this section by exploring some basic PHP functions you can use to work with MySQL:

mysql_connect(): Creates a connection to a MySQL server. It takes three string arguments: the hostname; the database username, and the database user password. The function returns a link identifier when it successfully connects to the specified MySQL server (or NULL upon error):
```
$link_id = mysql_connect("localhost", "phpuser", "phppass");
```

The link identifier works much the same as a file or directory handle. You'll use it later to issue queries. All of the arguments are optional, and when none are provided, "localhost", the Web server owner's username, and an empty password are all assumed.

mysqlclose(): The link to the MySQL server is closed when the script is terminated. If you want to close the connection earlier, use this function, with the link identifier as its argument:
```
mysql_close($link_id);
```

The function returns true on success and false upon error. If the link identifier is omitted, the previously opened link is used (if not specified otherwise, all mysql_* functions which take an optional link ID argument assume the previously opened link in its absence).

mysql_list_dbs(): PHP's equivalent to MySQL's SHOW DATABASES command. Its only argument is the (optional) link identifier. It returns a pointer ($result below) to the array containing the names of available databases:
```
$result = mysql_list_dbs($link_id);
```

mysql_select_db(): Used to select a database, returning True on success and False upon error. It takes the name of the database as an argument, although a link identifier argument is optional. Here's an example:
```
mysql_select_db("mysql", $link_id);
```

If no connection has been made before calling this function, it attempts to establish a link before selecting the specified database. Let's take a moment to illustrate how you can connect to the local MySQL server with the account you created earlier, using PHP:

$link_id = mysql_connect("localhost" "phpuser", "phppass");
if(mysql_select_db("mysql", $link_id)) echo "Connected to the localhost.";
else die ("Connection failed.");

This code establishes a connection to the local MySQL server, and selects the mysql access privilege database. If the connection fails, an error message is displayed.

mysql_list_tables(): Equivalent to MySQL's SHOW TABLES command. The database name and the optional link identifier are taken as arguments. A pointer is returned to the array containing the names of available tables associated with the database:
```
$result = mysql_list_tables("mysql", $link_id);
```
mysql_num_rows(): Use to find the number of rows in a result set returned by a given query. It takes the result set pointer as an argument:
```
$num_rows = mysql_num_rows($result);
```

Use it on result sets returned by SELECT queries, and other functions that retrieve rows.

mysql_affected_rows(): Use instead of mysql_num_rows() to get the number of rows affected by INSERT, UPDATE, or DELETE commands. It takes an optional link ID argument:
```
$num_rows = mysql_affected_rows($link_id);
```
A DELETE query with no WHERE clause wipes out all the records in a given table and causes the mysql_affected_rows() function call to return zero.

The return value of mysql_num_rows() and mysql_affected_rows() is the actual count of rows selected/affected. The return value from mysql_list_dbs() and mysq_list_tables() is a pointer to a result set.

mysql_fetch_row(): Use to retrieve the rows of records returned from the server. It takes a result set pointer returned from a previous query, and returns an array corresponding to the fetched row (or False if there are no more rows left):

$fetched_row = mysql_fetch_row($result_set);

PHP maintains an internal pointer to the row returned from a previous query. Each subsequent call to this function moves the pointer to the next row available. When the pointer moves past the result set array boundary, mysql_fetch_row() returns False.

For a comprehensive list of MySQL functions, please see Appendix B.

Try it Out: Connect to a MySQL Server in PHP

Here's a sample script, db_disconnect.php, which uses the functions already introduced to list the databases and tables available to the MySQL user phpuser:

<?php
$link_id = mysql_connect("localhost", "phpuser", "phppass");
$result = mysql_list_dbs($link_id);

while($db_data = mysql_fetch_row($result)) {
        echo $db_data[0] . "<BR>";
        $result2 = mysql_list_tables($db_data[0]);
        $num_rows = = mysql_num_rows($result2);
        while($table_data =mysql_fetch_row($result2)) {
           echo"--" . $table_data[0]. "<BR>";
         }
        echo "==> $num_rows table(s) in " . $db_data[0] . "<P>";
}
?>

This script lists all databases available and tables in each of them. Here's a sample run:

mysql
--columns_priv
--db
--func
--host
--tables_priv
--user
==> 6 table(s) in mysql
test_db
--sample_table1
--sample_table2
==> 2 table(s) in test_db

Hang on, though! If you've been following along with the SQL commands, you probably got an error. If you did, go back to the section on GRANT and REVOKE, and grant phpuser enough privileges to be able to look at the tables in the databases, and then run the script again.

Alternatively, you can follow along and view the error-handling section after this example.

How it Works

The script first makes a connection to the server with the given set of values: localhost, phpuser, and phppass. Replace the values here with the appropriate ones for you, if you want to use your own username and password instead of those you gave to the user you created earlier.

The link identifier returned by the mysql_connect() function is used by the mysql_list_dbs() function call. In fact, you don't need to pass the link identifier to the latter function because mysql_list_dbs() would assume the last opened link in the absence of a link argument, like so:

$result = mysql_list_dbs();

A common mistake is overwriting the result pointer when you issue another query while stepping through the result set returned from a previous query. Just as you can open multiple files with different file handles, you should maintain multiple result sets by assigning unique pointers to each set.

In this example, a while loop in the script cycles through the result set returned by the mysql_list_dbs() function call. Another while loop is set up to look into every element in the result set returned by the mysql_list_tables() function call. Both loops are terminated when there are no more rows left in the result sets because $db_data and $table_data are False when the mysql_fetch_row() function is done fetching the returned rows.

while($db_data = mysql_fetch_row($result)) {
        echo $db_data[0]. "<BR>";
        $result2 = mysql_list_tables($db_data[0]);
        $num_rows = mysql_num_rows($result2);
        while($table_data = mysql_fetch_row($result2)) {
          echo "--" . $table_data[0]. "<BR>";
        }
        echo ==> $num_rows table(s) in " . $db_data[0] . "<P>";
}

Each of the arrays returned by mysql_list_dbs() and mysql_list_tables() contains only one element; the name of a database $db_data [0] and the name of a table $table_data[0], respectively. If you want to retrieve more than one field from a table, you can refer to each one by a corresponding array index:

$result_set = mysql_fetch_row($result);
$first_column = $result_set[0];
$second_column = $result_set[1];
$third_column = $result_set[2];

You haven't used the mysql_close() function, but it's seldom used in real-life applications unless you need to close the connection for some reason before your applications end. PHP automatically closes the open connection before your script ends.

You learn more about how MySQL and PHP can work in tandem in the following chapters.

Handling Server Errors

Now let's try something rather dumb: take the last script, change the database username to no_such_user, and see what happens. Unless you've set the error reporting level in the php.ini configuration file to suppress them, a bunch of warnings are thrown out:

Warning: MySQL Connection Failed: Access denied for user: 'no_such_user@local
host' (Using password: YES) in /home/apache/htdocs/db_connect.php on line 3
Warning: Supplied argument is not a valid MySQL-Link resource in /home/apache/
htdocs/db_connect.php on line 5

Warning: Supplied argument is not a valid MySQL result resource in /home/
apache/htdocs/db_connect.php on line 6

Warning: Supplied argument is not a valid MySQL result resource in /home/
apache/htdocs/db_connect.php on line 8

Why do you get such uninformative warnings? It's simple—the script you just created isn't smart enough to say what went wrong upon error; it just knows that it tried to access some MySQL resources but failed. PHP is generating the warnings, but the errors are on the database server.

PHP provides a couple of functions that catch server errors and determine what went wrong, returning values from the server's own error-handler. If a MySQL server encounters an error while executing a specified task, it returns the text and number of the error message. You can get at these values using the PHP functions mysql_errno() and mysql_error().

The mysql_errno() function returns the error number and mysql_error() returns the error text from the previous MySQL operation. They both take an optional link identifier argument (defaulting to the previously-opened link):

$MYSQL_ERRNO = mysql_errno($link_id);

$MYSQL_ERROR = mysql_error($link_id);

These functions assume the connection to the server has already been made, so if you call them to see why a connection has failed, no error is reported because no opened link is available. As you've seen, though, you can still use the die() function to exit with a message that the connection attempt has failed:

if(!mysql_connect("localhost", "no_such_user", "phppass"))
                                                   die("Connection failed!");

The only problem with this approach is that PHP still churns out its own warning messages when the connection attempt fails. To prevent warnings and/or error messages, either precede the function call with the error message suppressing operator @, or use the error_reporting() function.

You can use the error_reporting() function call to set the level of error reporting that PHP applies while executing subsequent code. By specifying error_reporting (0), you can ensure that no PHP error/warning messages will be invoked. This is what you do in the following example.

It's not a good idea to set the level to produce warnings in production environment because they might scare away your users. Set it to a higher level only when you are debugging your applications.

Try it Out: Handle Server Errors

We're going to define a couple of functions that you'll find very useful in later sections. Create a new file called common_db.inc and enter the following code:

<?pbp
$dbhost = 'localhost';
$dbusername = 'phpuser';
$dbuserpassword = 'phppass';
$default_dbname = 'mysql';

$MYSQL_ ERRNO = '';
$MYSQL_ERROR = '';

function db_Connect(){
   global $dbhost. $dbusername, $dbuserpassword, $default_dbname;
   global, $MYSQL_ ERRNO, $MYSQL_ERROR;

   $link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);
   if(!$link_id) {
      $MYSQL_ERRNO = 0;
      $MYSQL_ERROR = "Connection failed to the host $dbhost.";
      return 0;
   }
   else if(empty($dbname) && !mysql_select_db($default_dbname)) {
      $MYSQL_ERRNO = mysql_errno();
      $MYSQL_ERROR = mysql_error();
      return 0;
   }
   else return $linx_id;
}

function sql_error() {
   global. $MYSQL_ERRORNO, $MYSQL_ERROR;

   if (empty ($MYSQL_ERROR)) {
       $MYSQL_ERRNO = mysql_errno();
       $MYSQL_ERROR = mysql_error();
   }
   return "$MYSQL_ERRNO: $MYSQL_ERROR";
}
?>

You can include this file in later examples, and expand it as necessary. Note the function db_connect(), which is dedicated to making connections to the MySQL server. You'll use it a lot!

You should be aware that this file contains some pretty important information that you don't want to fall into the hands of unethical users. It's generally suggested that files containing plain-text passwords be stored outside of your Web tree for security purposes. PHP can still access them there, and this prevents o user from getting at the file in a non programmatic way,, such as tricking the server into offering up the page as plain text. The file has to be executed, which would prevent the password from being displayed.

Now try the following script, db_connect.php, with an incorrect username/password combination oz nonexistent database names:

<?php
include "common_db.inc";
error_reporting(0);
$link_id = db_connect();
if(!$link_id) die(sql_error());
else echo "Successfully made a connection to $dbhost.<BR>";
?>

Try the script using an invalid username or password, and you should see the following error message:

0: Connection failed to the host localhost.

If $default_dbname is empty, you get:

1046: No Database Selected

If it's set to a nonexistent database name (no_such_db for example), you see:

1049: Unknown database 'no_such_db'

How it Works

Once you've included these variable and function definitions, you specify error_reporting(0) to prevent PHP from displaying any error/warning messages.

You also could have suppressed error messages by prefixing the following mysql_connect() and mysql_select_db() function calls with an @ symbol.

If the database connection is made successfully, db_connect() returns a link identifier. Otherwise, it sets global variables $MYSQL_ERRNO and $MYSQL_ERROR accordingly, and returns zero to indicate an error. Consequently, the script dies:

if(!$link_id) die(sql_error());

Rather than directly specifying an error message, you've called sql_error() to produce one for you:

function sql_error() {
   global $MYSQL_ERRNO, $MYSQL_ERROR;

   if(empty($MYSQL_ERROR)) {
      $MYSQL_ERRNO = mysql_errno();
      $MYSQL_ERROR = mysql_error();
   }
   return "$MYSQL_ERRNO: $MYSQL_ERROR";
}

Basically all this function does is return a character string containing the error number and error message, separated by a colon, but it will be quite useful to you in situations where you can't assume that the returned variables have been defined. For example, you might want to call sql_error() following a database query error; the trouble is that $MYSQL_ERRNO and $MYSQL_ERROR would not have been defined. To make this function useful in such an event (as well as the failed connection you're anticipating), check the $MYSQL_ERROR global variable's value. If it's empty, call mysql_errno() and mysql_error() again and assign values to both.

After a successful connection, db_connect() attempts to select the default database, which you've set in the $default_dbname global variable. You can improve db_connect() by having it take an optional argument $dbname, so that it selects whichever database is specified in the argument:

function db_connect($dbname='') {
   global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
   global $MYSQL_ERRNO, $MYSQL_ERROR;

   $link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);
   if(!$link_id)    {
      $MYSQL_ERRNO = 0;
      $MYSQL_ERROR = "Connection failed to the host $dbhost.";
      return 0;
    }
    else if(empty($dbname) && !mysql_select_db($default_dbname)) {
       $MYSQL_ERRNO = mysql_errno();
       $MYSQL_ERROR = mysql_error();
       return 0;
    }
    else if(!empty($dbname) && !mysql_select_db($dbname)) {
       $MYSQL_ERRNO = mysql_errno();
       $MYSQL_ERROR = mysql_error();
       return 0;
    }
    else return $link_id;
}

You can now call it like this:

$link_id = db_connect("sample_db");

The connection held by $link_id will automatically use the database sample.db. If you don't specify an argument, db_connect() defaults to the database specified by $default_dbname.

Connecting to MySQL From PHP

2010-12-27T00:01:00.003+08:00

Okay, let's get back to PHP, and look at how you can tap into all this database power from your dynamic Web pages.

If you're feeling a little overwhelmed after going through all those SQL statements, don't worry! It's not essential that you understand every last detail at this stage. You just want to get an overall feel for how you go about interacting with a relational database such as MySQL. You'll become a lot more familiar with the specifics as you move on and start building applications for yourself.

Just as you used client programs to access the MySQL database server from the command line, PHP needs some client code of its own to talk to MySQL.

In the days of PHP4, this was a no-brainer because the necessary code was integrated right into the depths of PHP itself. Unfortunately that's no longer the case, and PHP5 expects the MySQL client libraries to be present on your system before compiling with PHP support. This is quite easy for Linux users: just ensure that you compile PHP5 using the -with-mysql option.

For Windows users, it's a little more complex. You need to:

Ensure that the libmysql.dll library is in your system root path (libmysqli for versions of MySQL 4.1 and later).
Uncomment the mysql extension in the extensions section of your php.ini file.
Ensure that your php_mysql.dll extension is in a folder where PHP can find it. (This is set in the extension directory setting in the php.ini file.)
Restart your Web server after you've implemented these changes.

That's it! Nothing too complicated—it's just like using any other extension in PHP. With everything set up, let's look at what you need to do to get PHP5 and MySQL talking to each other.

A Quick Play with MySQL

2010-12-20T00:01:00.006+08:00

You'll start working with the MySQL server using the client program mysql and a few simple database queries. Along the way, you'll play with some database settings, create some new users (without quite as much access to everything as the root user has), and get a taste of how queries work in practice.

Starting the mysql Client Program

Fire up the mysql client by issuing the following command at your command prompt:

> mysql -uUSER -pPASSWORD -hHOST

Replace the USER, PASSWORD, and HOST arguments to reflect your personal settings. For example, if your database username and password are phpuser and phppass and you're connecting to the host db.whatever.com, the command would look like this:

> mysql -uphpuser -pphppass -hdb.whatever.com

All the arguments are optional. If missing, the following values are assumed for them:

Argument	Value
-u	Your shell account username
-P	No password
-h	localhost

The mysql client now connects to the database server running on the specified HOST with the given user ID/password combination. You may also specify a database to use by providing its name at the end of the command, like this:

> mysql -uUSER -pPASSWORD test

You should see a response similar to the following:

Welcome to the MySQL monitor.  Commands end with; or \ g.
Your MySQL connection id is 4 to server version: 4.0.18-nt

Type 'help;' or '\ h' for help. Type '\ c' to clear the buffer.

mysql>

If the mysql client complains that it can't connect to the specified server, check to see if the provided arguments are correct.

Selecting a Database to Use

To see the list of databases available, use the SHOW DATABASES command:

mysql> SHOW DATABASES;
+---------------+
| Database      |
+---------------+
| mysql         |
| test          |
+---------------+
2 rows in set (0.00 sec)

This is mostly the same information you got from mysqlshow earlier: there are still two databases, mysql and test, available in the system. This time, though, you're also told how many rows were returned (2) and how long it took the server to execute the query (0.00 seconds to two decimal places).

In fact, you can use the mysql client to do just about anything that the other, more specialized client programs can, as long as you know the correct syntax. Let's dig a little deeper.

To select a particular database, you can utilize the USE databasename syntax. Let's have a poke around inside one of the databases:

mysql> USE mysql;
Database changed

Your MySQL server is now ready to work with the database mysql. A word to the wise: be careful how you use the user table; it contains important information that you probably don't want to delete.

For the sake of readability, it is recommended that SQL keywords are written in uppercase characters, and user-defined names such as table and field names are lowercase. Remember that on the Linux/UNIX platforms, arguments are case-sensitive: Mysql, MYSQL, and mysql are all different database names.

Looking at Tables Inside a Database

You can use the SHOW TABLES command to list the existing tables in the currently selected database:

mysql> SHOW TABLES;
+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv
| db              |
| func            |
| host            |
| tables_priv
| user            |
+-----------------+
6 rows in set (0.00 sec)

Once again, a slight elaboration on the results you previously got from mysqlshow. This time you can have a look at what's going on inside the tables. For example, let's see how the user table is structured. Use the DESCRIBE command, or DESC for short:

mysql> DESC user;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| Host               | varchar(60) binary  |      | PRI |         |       |
| User               | varchar(16) binary  |      | PRI |         |       |
| password           | varchar(16)         |      |     |         |       |
| Select_priv        | enum('N','Y')       |      |     | N       |       |
| Insert_priv        | enum('N','Y')       |      |     | N       |       |
| Update_priv        | enum('N','Y')       |      |     | N       |       |
| Delete_priv        | enum('N','Y')       |      |     | N       |       |
| Create_priv        | enum('N','Y')       |      |     | N       |       |
| Drop_priv          | enum('N','Y')       |      |     | N       |       |
| Reload_priv        | enum('N','Y')       |      |     | N       |       |
| Shutdown_priv      | enum('N','Y')       |      |     | N       |       |
| Process_priv       | enum('N','Y')       |      |     | N       |       |
...
...
| File_priv          | enum('N','Y')       |      |     | N       |       |
| Grant_priv         | enum('N','Y')       |      |     | N       |       |
| References_priv    | enum('N','Y')       |      |     | N       |       |
| max_connections    | int(11) unsigned    |      |     | 0       |       |
+--------------------+---------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)

This command describes the user table's structure—what fields are defined and how they've been configured. For example, you can see the Host field can hold up to 60 characters and is defined as a primary key. The User field also is defined as a primary key. That doesn't mean that the Host and User fields are both primary keys (you should remember that this is forbidden). Instead, the combination of the Host and User fields works as a sole primary key in Host-User format.

For example, if user john at localhost has been given the server access privileges, localhost-john becomes the primary key value for his record. Another user at localhost whose name is also john cannot be inserted as a record in this table. However, john at whatever.com can.

All the fields in the user table except the Host, User, Password, are declared as ENUM('N', 'Y'). This means that only one from the specified set of values (either N or Y in this case) can be used in each field. Note that N is the default value when no value is provided.

Using SQL to Look at Data

All the queries you've given to the mysql client so far have been specific to the MySQL database engine, and won't necessarily work with another system, whether or not it supports SQL. Now that you're working down at the level of actual data, though, it's time to use some bona fide SQL statements to find out what's there.

Let's say you want to find out if there are any users registered with access privileges from the local machine localhost. You might do this by issuing the following SELECT query:

mysql> SELECT User,Host FROM user;
+----------+-----------+
| User     | Host      +
+----------+-----------+
| root     |localhost  +
| dodge    |doggiesr   +
| james    |digistrawb +
+------------—---------+
3 rows in set (0.00 sec)

Because there are only three rows, it's not hard to see that only root is currently registered as a local user. Of course, it might be somewhat harder to spot this if you had a few hundred more users registered in the database, so here's another way to get the same result:

mysql> SELECT User FROM user WHERE Host='localhost';
+----------+
| User     |
+----------+
| root     |
+----------+
1 row in set (0.00 sec)

Using a WHERE clause narrows the scope of the retrieved records. This works much the same as PHP's if statement, except that the former uses a single equal sign (=) whereas the latter uses two (==) to test for equality.

If you wanted the server to retrieve all fields available instead of a few specified ones, you could use the * wildcard. For example:

mysql> SELECT * FROM tablename;

This command would retrieve all of the records from tablename.

Manipulating Data in a Database

Now let's create a new database user, by inserting a new record into the user table using the INSERT command:

mysql> INSERT INTO user VALUES(
    -> 'localhost',
    -> 'phpuser',
    -> Password('phppass'),
    -> 'N', 'N', 'N', 'N', 'N', 'N', 'N',
    -> 'N', 'N', 'N', 'N', 'N', 'N', 'N',
    -> 'N', 'N', 'N', 'N', 'N', 'N', 'N',
    -> 'N', 'N', 'N', 'N', 'N', 'N', 'N');
Query OK, 1 row affected (0.00 sec)

This INSERT query creates a record for the user phpuser, using the password phppass, in the user table, and specifies no access privileges. Each string value is placed within single quotes—this issue is discussed in greater detail later on. As you can see, the mysql client reports that the query was successfully executed, and that one row of data has been inserted as requested.

MySQL saves database user passwords after encrypting them. It uses its own password encryption scheme (which is different than Linux's), so use MySQL's built-in password() function to encrypt your password.

What if you change your mind and want to give user phpuser (who presently has no access privileges at all) the server administration privileges Reload_priv and Shutdown_priv? You could update the user table to reflect your whimsical decision using an UPDATE query, like this:

mysql> UPDATE user SET Reload_priv='Y', Shutdown_priv='Y'
    ->  WHERE User='phpuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The mysql client reports that only one row matched the condition set in the query and that it has been successfully updated. User phpuser now has server administration privileges only: phpuser is only allowed to reload or shut down the server.

The WHERE clause is optional, and when omitted, the UPDATE query changes all the records in a given table with the new values provided. Warning: Make sure you don't change records by accident. A nightmare can sometimes turn into reality if you're not careful because a careless UPDATE query could, for example, change 10,000 user records to have the same password. Even worse, all the records in a table could unwittingly be deleted with a DELETE query such as:

mysql> DELETE FROM test;
Query OK, 0 rows affected (0.00 sec)

Here all of the records in the table test have been deleted. Don't be fooled by the mysql client reporting that no row is affected by this query. Following the query, the server has no way of knowing how many rows are affected (deleted) because they're gone forever after the query. Note that the syntax of this example DELETE command contains no clauses. Thankfully, you may specify which records are to be deleted with a WHERE clause:

DELETE FROM tablename WHERE condition(s);

Now you change you mind again and want to give user phpuser all of the server administration privileges. What do you do? You can make either another UPDATE query or you can REPLACE the whole record. The syntax is pretty simple:

mysql> REPLACE INTO user VALUES(
    -> 'localhost', 'phpuser', Password('phppass'),
    -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)

So REPLACE overwrites an old record with a new one. Note that the new record and the old record must have the same value in a field designated as a key (or the combined key formed by the Host and User fields in this case). The difference between the UPDATE and REPLACE commands is that UPDATE replaces only the selected set of fields in a record, whereas REPLACE replaces the whole record with the given values.

Finally, to activate the newly created account phpuser, you need to flush privileges, reloading the privilege information from the table. (The server normally reads the access privilege information only once, when it's loaded.) First, exit mysql client, by typing quit, (otherwise you'll get an error saying that your SQL syntax is incorrect). Then:

> mysqladmin -uUSER -pPASSWORD -hHOST flush-privileges

Alternately you can issue the FLUSH PRIVILEGES command to reflect the change in the mysql client program:

mysql> FLUSH PRIVILEGES;

Using GRANT and REVOKE Commands

GRANT and REVOKE commands enable you to allocate and remove database privileges as you choose. They can be used at various levels of your database permissions structure, going from global to database to table to column level—depending on how fine grain you want your control to be. Of course, for whatever permissions you can grant a user, you can use a corresponding REVOKE statement to remove them.

In the following sections you'll see how to use these commands to control permissions on your tables. For example, earlier you created a new user by manipulating the user table directly. You can achieve the same goal more simply using the GRANT command. Let's see how.

GRANT

Here's the simplest form of the GRANT command:

mysql> GRANT ALL PRIVILEGES ON *.* TO
    -> phpuser@localhost IDENTIFIED BY 'phppass';

It gives every access privilege on every database in the system to the user phpuser at localhost if he logs on to the server using the password phppass. Note that the user and host arguments are not given in quotes.

If you want to let this user access the tables in the test database only, you'd use the following command:

mysql> GRANT ALL PRIVILEGES ON test.*
    -> TO phpuser@localhost IDENTIFIED BY 'phppass';

Here, test.* denotes all tables in the test database. Similarly you can grant access to only the sample table in test:

mysql> GRANT ALL PRIVILEGES ON test.sample
    -> TO phpuser@localhost IDENTIFIED BY 'phppass';

By replacing the ALL PRIVILEGES keyword with a selection of query types, a set of access privileges can be granted:

mysql> GRANT SELECT,INSERT,UPDATE ON test.*
    -> TO phpuser@% IDENTIFIED BY 'phppass';

With this command, the user phpuser can issue only SELECT, INSERT, and UPDATE queries to any of the tables in the test database. Any other query, such as DELETE, won't be allowed. Wildcards like * are extremely useful. For example, by replacing localhost with the % wild card, you can allow the user phpuser to access the specified tables from any server.

You can also specify the host with a partial domain name: if you used phpuser@%.whatever.com for instance, only users with the user ID phpuser connecting from the domain whatever.com would be granted access to the server.

You can split hairs even further, by specifying the fields that the user can access:

mysql> GRANT SELECT (User, Host) ON mysql.user
    -> TO phpuser@localhost IDENTIFIED BY 'phppass';

Here the user phpuser can issue only SELECT queries on the User and Host fields in the user table.

You can use the WITH GRANT OPTION clause to give the specified user the capability to grant other users any privileges he has at the specified privilege level:

mysql> GRANT ALL PRIVILEGES ON test.*
    -> TO phpuser@localhost IDENTIFIED BY 'phppass' WITH GRANT OPTION;

This command is equivalent to creating another superuser, called phpuser. Be careful with the WITH GRANT OPTION clause: two users with different privileges can easily team up and extend their privileges by exchanging them!

REVOKE

The REVOKE command removes access privileges from a user. If you want to revoke all privileges given to user phpuser, for example, issue the following command:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM phpuser;

You can specify multiple usernames by separating them with commas:

mysql> REVOKE ALL PRIVILEGES ON *.*
     > FROM phpuser@localhost, phpuser2, phpuser3;

All specified users must exist, and possess the specified privileges, in order for this command to work.

The following command revokes only the SELECT privilege from the user phpuser:

mysql> REVOKE SELECT ON *.* FROM phpuser@localhost;

Again, you can split hairs by specifying field names:

mysql> REVOKE SELECT (User, Host) ON mysql.user FROM phpuser@localhost;

If you modify the grant tables using GRANT or REVOKE commands, the changes take effect immediately. You don't have to flush privileges or reload the server.

Relational Databases

2010-12-13T00:01:00.006+08:00

One key characteristic of databases that you haven't looked at so far is how a database organizes data for users to interact with. Whenever you use the database engine to retrieve, modify, or add new data, you need to know what to ask for—moreover, when you come to design a database for yourself, you need to tell the database engine what sort of data to expect where, and how it's all meant to hang together.

Most of the databases you considered earlier use what's known as a relational data model. As such, they're known as relational databases (or Relational Database Management Systems—RDBMS). They arrange data into tables, each of which is divided into rows and columns.

In database parlance, each row in a table (apart from the heading) represents a data record: a set of intrinsically connected pieces of data. Likewise, each column represents a field: a specific type of data that has the same significance for each record in the table.

For all practical purposes, the term row is synonymous with record, whereas column is synonymous with field. This is useful to bear in mind when visualizing tables.

Suppose that the manager of a touch-rugby team (don't worry if you aren't familiar with touch rugby —the sport is incidental to the topic and won't affect your understanding of databases in any way) sets up a database so that he can track the matches in which his players compete. Naively, he asks each player to enter his details into the database after each match. After the second round of matches, the manager's table looks like this:

Player_Number	Name	Phone_number	Date_Played	Nickname
42	David	555-1234	03/03/04	Dodge
6	Nic	555-3456	03/03/04	Obi-d
2	David	555-6543	03/03/04	Witblitz
14	Mark	555-1213	03/03/04	Greeny
2	David	555-6543	02/25/04	Witblitz
25	Pads	555-9101	02/25/04	Pads
6	Nic	555-3456	02/25/04	Obi-d
7	Nic	555-5678	02/25/04	Nicrot

He soon realizes that this is going to make for a huge table after everyone on the team has played an entire season's worth of games. As you can see, the structure of the table is inefficient because each player's details—number, name, phone number, and so on—are entered every time he plays a match.

Such redundancy is undesirable in a database. For example, say that the player with the number 6 keeps dropping the ball, and his teammates decide to give him a new nickname (which won't be mentioned here). To update the table, every one of this player's records would have to be modified to reflect his new nickname.

In addition, every time a player enters his details after a match, all of that information is consuming valuable space on the hard drive. Redundancy is terribly inefficient, wasting a great deal of time and space.

Fortunately, in the early 1970s, Dr. E. F. Codd came up with a unique and powerful way to alleviate this type of problem. He stipulated a set of rules that, when applied to data, ensure that your database is well designed. In truth, he stipulated no small number of rules, but for most purposes you need only concern yourself with the first few. These requirements can be divided up into what are called normal forms, and making sure your data complies with these normal forms goes a long way to ensuring good relational database design. Let's take a look at what normalization is all about.

Normalization

Normalization is defined as "the process of breaking up the data into several tables, so as to minimize the number of times you have to repeat the same data." The normal forms are degrees of normalization, and they are governed by an elegant set of rules that can be summarized as follows:

First Normal Form (1NF)

Create a new table for each new set of related data you want to store.
Eliminate repeating information in an individual table.
Uniquely identify each record with a primary key.

For example, in 1NF you could split the preceding table into a player information table:

Player_Id	Name	Phone_number	Nickname
42	David	555-1234	Dodge
6	Nic	555-3456	Obi-d
14	Mark	555-1213	Greeny
2	David	555-6543	Witblitz
25	Pads	555-9101	Pads
6	Nic	555-3456	Obi-d
7	Nic	555-5678	Nicrot

And match log table:

Player_Id	Date_Played
42	03/03/04
6	03/03/04
2	03/03/04
14	03/03/04
2	25/02/04
25	25/02/04
6	25/02/04
7	25/02/04

Notice how the original table splits naturally into these two new tables. This is because the original table provided data about two distinct things (or entities): players and matches. Each new table contains data concerning just one entity. The split into entity tables is an important part of the normalization process.

Now you can take another step toward conforming to the first normal form: eliminating repeating information in an individual table. There's some redundant information in the player information table that can be removed to give you the following:

Player_Id	Name	Phone_number	Nickname
42	David	555-1234	Dodge
6	Nic	555-3456	Obi-d
2	David	555—6543	Witblitz
14	Mark	555-1213	Greeny
25	Pads	555-9101	Pads
7	Nic	555-5678	Nicrot

Are you done with this table yet? There's no longer any duplicated information, and the table represents only related information (information about the player entities). But what about a unique identifier? Each entity must have (at least) one unique field—in other words, a field in which no entries are repeated. Because of the exclusive nature of this field, each entry in it uniquely identifies (IDs) each record in the table. The field used to ID records is often known as the primary key. Only one primary key is permitted per table.

Remember, by moving the player information to one table and the match log entries to another, you simplify the process of modifying a player's details—you only have to modify one record in the player table.

You could add a unique key, which would assign a unique number to each record, but let's look at the information closely. In any given sport, a player's number must be unique, so you know that all the values in the Player_Id field are unique. But the Nickname field is also unique because each player can only have one nickname at a time. Which one should you choose? You've already seen that nicknames can change, based on performance, so the Player_Id field is the sensible choice for a primary key.

What about the match log table? Unfortunately, the Player_Id field is no longer unique because each player plays on many dates. Obviously, the date field can't be unique either, so you can't make a unique identifier out of either field. In this case, you need to add one yourself (you'll learn how later in the book). Once that's done, both tables are in first normal form. Adding an ID field could have hidden uses, especially if you wanted to join the information in the two fields of the match log table to create a unique key that you could relate to some other data (the number of tries scored per player per match, for example).

The second normal form has two goals, which are applicable only after you've achieved 1NF.

Second Normal Form (2NF)

Create new tables for each new group of values that apply to many records.
Relate new tables to existing tables with a foreign key (a key that identifies records in a different table).

Because both example tables contain a common field (Player_Id), whose values correspond between tables, you have a way to join them together—you don't actually need a foreign key to accomplish this, but there are definitely advantages to using foreign keys (For a more in-depth discussion on them, please go to www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html for a discussion on MySQL foreign keys.) This means that records in one table have a well-defined relationship to records in the other. The database now has all the information it needs to answer complex queries involving both tables, which it couldn't answer by looking at either table in isolation. With tables related like this, the database can truly be termed relational. The specific relationship described here is also called a one-to-many relationship. For each record in the player information table, you may have many records in the match log table. The ability to construct meaningful one-to-many relationships is a good indicator that your database meets the requirements of the second normal form.

Third Normal Form (3NF)

The third normal form's goal is to eliminate fields that do not depend on the primary key.

This is where the real world and the elegance of theory can collide. To some extent, it reverses the premise of a master table relationship by suggesting that anytime you may have data repeat in a record such as ZIP codes in a list of addresses, that data should be stored in a separate table and linked back to the individual records that use them with a new keyed relationship. Make no mistake; the purists will chastise you roundly for not doing it this way. But the truth is, database design is often a series of trade-offs; and you do sometimes have to weigh design idealism against a novel but useful concept—simplicity. The requirements of the third normal form simply may not satisfy your needs; and it's all right for you to decide on a case-by-case basis what will work best for any given situation.

Other Normal Forms

Believe it or not, there are more normal forms, the fourth and fifth among them. The fourth normal form states that you should isolate independent multiple relationships. This rule primarily seeks to deal with the problems of many-to-many relationships, where many records may have relationships to intersecting records in another table that may in turn link back to multiple records in the first table. Confused? Don't feel bad. This is where things get pretty hairy and you don't need to hold yourself accountable to this standard. The concept is placed here because you may very well encounter this situation down the road and it's useful as a strong suggestion for dealing with these issues.

Talking to Databases with SQL

At this point, you probably aren't at all surprised to learn that MySQL itself is a relational database. In fact, you're about to start applying the principles we've just been discussing with your very own database engine. First, though, you need to learn a little about the language used to interact with relational databases—a language called SQL.

SQL, the Structured Query Language, is a standard command set used to communicate with a relational database management system on any given platform. Every task such as creating databases or tables, as well as saving, retrieving, deleting, and updating data from databases, is done via SQL statements. Implementation of SQL features vary among RDBMS vendors, but because the basic concepts are identical, applying skills learned on one platform to another should be no more difficult than porting a computer program written for one platform to another using the same language. In this section you'll examine some basic features of SQL: data types, indexes, keys, and queries.

There are some differences in the way MySQL implements certain things and the ANSI SQL standard. You can check out a list ofthese differences at www.mysql.com/doc/en/Differences_from_ANSI.html.

SQL Data Types

When you create a database table, the type and size of each field must be defined. A field is similar to a PHP variable except that you can store only the specified type and size of data in a given field. Therefore, unlike PHP variables, you can't insert characters into an integer field, for example. The three usual sets of data types are supported in MySQL: numeric, date/time, and characters, which are described in the following tables:

Numeric Data Types	Description	Range/Format
INT	Normal-sized integer	(-231 to 231 -1), or (0 to 232 -1) if UNSIGNED
TINYINT	Very small integer	(-27 to 27 -1), or (0 to 28 -1) if UNSIGNED
SMALLINT	Small integer	( -215 to 215 -1), or (0 to 28 -1) if UNSIGNED
MEDIUMINT	Medium-sized integer	(-223 to 223 -1), or (0 to 224 • -1) if UNSIGNED
BIGINT	Large integer	(-263 to 263 -1), or (0 to 264 -1) if UNSIGNED
FLOAT	Single-precision floating-point number	Minimum non-zero ±1.176 × 10 - 38; maximum non-zero ±3.403 × 10 + 38
DOUBLE/REAL	Double-precision floating-point number	Minimum non-zero ±2.225 × 10 - 308; maximum non-zero ±1.798 × 10 + 308
DECIMAL	Float stored as string	Maximum range same as DOUBLE

Date/Time Data Types	Description	Range/Format
DATE	Date	YYYY-MM-DD format. Range 1000-01-01 to 9999-12-31
DATETIME	Date and time	YYYY-MM-DD hh:mm:ss format. Range 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP	Timestamp	YYYYMMDDhhmmss format. Range 19700101000000 to sometime in 2037
TIME	Time	hh:mm:ss format. Range -838:59:59 to 838:59:59
YEAR	Year	YYYY format. Range 1900 to 2155

Character Data Types	Description	Range/Format
CHAR	Fixed-length string	0–255 characters
VARCHAR	Variable-length string	0–255 characters
BLOB	Binary Large Object	Binary data 0-65535 bytes long
TINYBLOB	Small BLOB value	Binary data 0–255 bytes long
MEDIUMBLOB	Medium-sized BLOB	Binary data 0-16777215 bytes long
LONGBLOB	Large BLOB value	Binary data 0-4294967295 bytes long
TEXT	Normal-sized text field	0-65535 bytes
TINYTEXT	Small text field	0–255 bytes
MEDIUMTEXT	Medium-sized text	0-16777215 bytes
LONGTEXT	Large text field	0-4294967295 bytes
ENUM	Enumeration	Column values are assigned one value from a set list
SET	Set value(s)	Column values are assigned zero or more values from a set list

The difference between a CHAR and VARCHAR type field is that the former stores a fixed-length value no matter how short it may be, whereas the latter stores exactly as many bytes as necessary to keep a given value. Suppose you insert the string dodge into the fields defined as char_field CHAR(10) and varchar_field(10), for example. They will store the same string slightly differently:

char_field: 'dodge     '// five blank spaces are right padded
varchar_field: 'dodge' // no space

It follows that declaring character fields as VARCHAR type will save you some disk space. Don't be tempted to use VARCHAR type fields for storing every string, though, because that has drawbacks, too. The MySQL server processes CHAR type fields much faster than VARCHAR type, for one thing, because their length is predetermined. If your strings don't vary in length much, you're better off using CHAR type fields. Moreover, when your strings are all the same length, VARCHAR takes up more disk space, because it has to store the length of each string in one additional byte.

A final note on VARCHAR/CHAR type fields: if your table has at least one VARCHAR field, all character fields are converted to VARCHAR type even if you define them otherwise.

Indexes and Keys

Inexperienced database designers sometimes complain about their database engines being slow, a problem that's often explained by the lack of an index. An index is a separate sorted list of a selected field (or fields) in a table. To explain why indexing a table has a dramatic effect on database performance, first consider a table without indexes. Such a table is basically the same as a plain text file because the database engine must search it sequentially. Rows in a relational database are not inserted in any particular order—the server inserts them in an arbitrary manner. To make sure it finds all entries matching the information you want, the engine must perform a full table scan, which is slow and inefficient, particularly if there are only a few matches.

Now consider an indexed table. Instead of moving straight to the table, the engine can scan the index for items that match your requirements. Because the index is a sorted list, this scan can be performed very quickly. The index guides the engine to the relevant matches on the database table, and a full table scan is not necessary.

So why not just sort the table itself? This might be practical if you knew that there was only one field on which you might want to search. However, this is rarely the case. Because it's not possible to sort a database by several fields at once, the best option is to use an index, which is separate from the table.

What about the case of searching multiple tables at once? This is where you really benefit from an index. Searching for a possible match across joined databases without indexes is a terrible idea—the engine would have to check all possible combinations of rows in one table with those in another. For two tables each with 500 rows, this would be 500 times 500, or 250,000 combinations! Indexing speeds searches up dramatically. The engine checks the index of the first table to find the position of matches to the first part of the query, and then it uses the index to the second table to find matches to the second part of the query. In other words, you pull out the relevant records directly.

A primary key is a special index that, as you saw at the beginning of the chapter, is used to ID records and to relate tables to one another, providing the relational database model. Each related table must have one (and only one) primary key.

Indexes and primary keys can be derived from combinations of fields. For a key to be formed in this way, the combination of items from each field must still be unique.

Because an index brings about a significant boost in performance, you could create as many indexes as possible for maximum performance gain, right? Not always. An index is a sure-fire way to increase the speed of searching and retrieving data from a database, but sacrifices performance when saving or updating records, and also increases the size of a table. Why? When you insert a record into an indexed table, the database engine has to record its position in the corresponding index table. Do the math!

What's more, if you have more than one index on a table, multiple write operations have to be performed on the index table, too. So when creating indexes on a table, don't create more than you need. Limit indexed columns to those that will be searched or sorted frequently. If required, you can create additional indexes on a table as you need them to increase performance.

Queries

SQL statements or commands are used to construct queries. Queries are the questions your application asks a database engine, which then returns the records that meet the criteria specified in the query. Queries return an array of records that meet the specified conditions, and contain information from selected fields. PHP, and other languages that support database connectivity for that matter, can treat the returned array as a normal array variable. We'll return to this topic later; for now, just bear it in mind. The returned array of records, called the result set, is the database engine's answer to your query. If you ask the engine to retrieve the records containing John as the first name, for example, it returns all of the records conforming to the query. If none are found, NULL (discussed in the next section) is returned.

Some SQL commands are literally commands that tell the database engine to do something instead of asking for an answer: "Delete those rows of information that contain John as the first name!" SQL commands of this type don't return a result set.

NULL

Consider the following scenarios:

A class takes a spelling test at school, but the teacher has yet to mark the test sheets and insert the results into a database table. It would be unfair of the teacher to insert default values into the results column before the sheets have been marked because a default score is irrelevant in this context. What could you place in the column to signify that you are awaiting data?

You want to construct a database table containing information about endangered bird species. One of the fields provides the fastest recorded flight speed for each bird in the table. You begin to create records for endangered penguins, and then you remember—penguins can't fly! What do you place in the flight speed column to indicate that the field is not applicable to penguins?

In both of these cases data is missing from a table. The only difference is that, in the first case, the situation is temporary because the teacher will soon add the missing test results to the table, but in the second case the values for the flight speed of penguins is impossible to obtain. You need a way to represent missing data in fields.

In a MySQL table, a NULL entry represents a missing value. NULL doesn't belong to any particular data type, but it can replace any value. Because it is not a data type or value, but it can be a field entry, the concept of a NULL is often difficult to grasp for beginners and experienced programmers alike. Programmers often have a mistaken idea of NULL. For example, a common mistake is to think of NULL as zero, which is wrong because zero is a value; NULL is not. Strings filled with one or more blank spaces, and strings of zero length, may also be mistaken for NULL because string is a data type, but NULL isn't. NULL is nothing, no data type, no value.

So what happens if the result set from one of your queries contains a NULL, and that result set is then used in your program in subsequent calculations? The rule of thumb for math with NULL is that it propagates. Any arithmetic operation involving a NULL returns NULL. This makes sense because how could you provide results when all the data needed to perform the calculation is not present? This also applies to dividing a NULL value by zero; NULL is returned.

Query Commands

MySQL queries issued to manipulate data in a table can be constructed using the following main commands:

SELECT: Retrieves data from a database.
DELETE: Deletes data from a database.
INSERT—Inserts data into a database.
REPLACE: Replaces data in a database. If the same record exists in a table, the command overwrites the record with the new data.
UPDATE: Updates data in a table.

The rest of the command set involves creating or modifying the database structures, rather than the data stored in the databanse:

CREATE: Creates a database, table or index.
ALTER: Modifies the structure of a table.
DROP: Wipes out a database or table.

You'll see more of these commands as you work through the next few chapters. Just to give you a taste though, let's take a look at the typical form of a MySQL SELECT query, which retrieves records from a table:

mysql> SELECT field1, field2, ... , fieldn FROM table WHERE condition;

The first thing to note is that each query is terminated with a semicolon, just as a PHP statement is. A query statement may expand to multiple lines. The following, slightly more specific query, is essentially the same as this generic case:

mysql> SELECT last_name, first_name
    -> FROM user
    -> WHERE first_name = 'John'

Take a closer look at the FROM, WHERE, and ORDER BY clauses in the query. The query returns any record from the user table where the value of the first_name field is John. Assuming you have a table called user, here's a sample of the query's output:

Simpleton John
Smith John
Thomas John

Now let's put this into practice and have some fun with MySQL!

Setting up MySQL

2010-12-06T00:01:00.005+08:00

You'll use MySQL for the database examples in this book, so you need to get it up and running.

At time of writing, the latest production release is MySQL 4.0, and you can find the server files (both as source code and as precompiled binaries for a wide range of platforms) on the MySQL site at www.mysql.com/downloads/mysql-4.0.html.

Details of installation (such as how you install it, and where the crucial files end up) can vary quite a lot, depending on factors such as the operating system you use and the particular type (source code or precompiled) of MySQL package you've downloaded, so let's look at several different versions of the installation process.

Installing on Windows

The Windows binary is provided as a ZIP file (for example, mysql-4.0.16-win.zip) containing a number of files including an executable called setup.exe. Run this file, and an installation wizard prompts you with a few settings options. These just determine which files are to be installed and where they are to be stored. The defaults (a Typical install in C:\MySQL) should be fine.

All of the core MySQL programs are stored in the bin directory of your installation. Assuming you've gone with the default settings, you'll find them in C:\mysql\bin. Most of these programs (such as mysql.exe, mysqladmin.exe, and mysqld.exe) are command-line tools, so do not try to run them by double-clicking their icons. You'll see them in action a little later on.

One very useful tool that comes with the standard Windows release of MySQL is the snappily titled winmysqladmin.exe, which provides you with a useful interface for controlling the MySQL server program. It's a graphical program, so you can run it by double-clicking as normal.

The first time you run it, you're prompted to enter a username and password, which generate a database user account for the administration tool to use. The administration tool then starts the MySQL server program, which, if installed properly, should come up first time.

Once it's running, winmysqladmin hides itself in your system tray (at the right-hand end of your Start bar), where it's represented by a traffic light. A green (go) light shows when the server's running, and a red (stop) light when it's not.

Right-clicking the icon brings up a menu that enables you to start and stop the server, and shows the winmysqladmin window from which you can view and edit various parameters on the server.

Installing on Linux

Linux users can install MySQL from a package or by compiling from source code. If you're after RPMs, then make sure you download the server, the client, the include files and libraries, and the client shared libraries for the correct platform. You should end up with the following four files (although the exact names may vary according to the MySQL version and OS you're working with):

MySQL-4.0.16.i386.rpm
MySQL-client-4.0.16.i386.rpm
MySQL-devel-4.0.16.i386.rpm
MySQL-shared-4.0.16.i386.rpm

If you're after the source code, you just need the tarball, mysql-4.0.18.tar.gz.

Installing MySQL using RPMs

Install RPMs using the following command:

> rpm -Uvh filename.rpm

Install them in the order listed in the preceding section.

When you install the first package, which contains the MySQL server, the following documentation appears on the screen:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !


This is done with:


/usr/bin/mysqladmin -u root password 'new-password'


See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at http://www.
mysql.com


Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy.


Starting mysqld daemon with databases from /var/lib/mysql

The mysqladmin program is one of the client tools, so you have to wait to set your password until after you install the client package, which you'll do a little later in this chapter. However, the RPM immediately starts up the MySQL server program mysqld (for MySQL daemon). It also creates the startup and shutdown script /etc/rc.d/init.d/mysql, that ensures that MySQL starts whenever your computer is booted, and shuts down conveniently whenever it is halted. You can use this script to start and stop mysqld, with the commands:

> /etc/init.d/mysql start

> /etc/init.d/mysql stop

Now install the MySQL-client, MySQL-devel, and MySQL-shared packages, and you are ready to move on to Configuring MySQL section of this chapter.

Installing MySQL From Source

It's fairly simple to install MySQL source code. Downloaded the mysql tarball from the MySQL Web site and install it like this:

> tar -zxvf mysql-4.0.18.tar.gz
> cd mysql-4.0.18
> ./configure --prefix=/usr
> make

If make fails, it is often because of a lack of memory, even on fairly high-spec machines. In that case, try:

> rm -f config.cache
> make clean
> ./configure --prefix=/usr --with-low-memory
> make

If that doesn't work, you may find some help at www.mysql.com/doc/en/Compilation_problems.html.

Assuming configure and make have run without a hitch, simply run the following statements to complete the installation:

> make install
> mysgl_install_db

You need some scripts to start and stop the MySQL server, mysqld. Here's one typical startup script:

#!/bin/bash
/usr/bin/mysqld_safe &

And one typical script to shut the server down:

#!/bin/bash
kill 'cat /usr/var/$HOSTNAME.pid'

These scripts are in the source code download for this book (www.wrox.com). If you choose to create startup and stop scripts yourself, produce the files using your favorite text editor, and then use the chmod command to tell the system that they are executable scripts. If you saved the startup script as startmysqld, for example, you would need to type:

> chmod ugo+rx startmysqld

Configuring MySQL

With the MySQL database engine installed and running on your local test machine, take a few moments to configure the system.

MySQL, like most networked systems (including other client-server databases, operating systems, and many more besides), requires you to log in with a specific user account before doing anything else. This is a fairly obvious security measure, and it limits access to the data by specifying permissions for each account. For example, one user may only have permission to view existing data; another may have permission to add new data, and perhaps even change other users' permissions.

root is the name traditionally given to a system's most senior user, who automatically has permission to view and modify all data and settings: a powerful position to be in. When MySQL installs, it creates the root account automatically, but doesn't set a password for it! Right now, your pristine installation could be wide open to use and abuse by anyone with a MySQL client and a network connection to the server, and you need to do something about that.

To set up a root account from the command line, follow these steps:

Call up a command prompt and navigate to the directory containing the MySQL program files (typically C:\mysql\bin\ on Windows or /usr/bin/ on Linux).
Type in the following command, substituting a suitably obscure password of your own in place of elephant:
```
> mysqladmin -uroot password elephant
```

With the command line still in place, try out a few commands. For example, executing the command mysqlshow gives you a list of the databases currently available through your server:

> mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

Right now it shows that the server has already created two databases for itself. The first, called mysql, is where it stores all the information it uses to authenticate users. The second is an empty test database, mysqlshow can also show you what's inside a database, as long as you have the correct password:

> mysqlshow -uroot -p mysql
Enter password: ********
Database: mysql
+--------------+
|   Tables     |
+--------------+
| columns_priv |
| db           |
| func         |
| host         |
| tables_priv  |
| user         |
+--------------+

So here's another clue about what's lurking inside that mysql database—all the system data is arranged into six named tables. This is your first direct glimpse of the data storage model that MySQL uses to organize its data. Before you dig any deeper, let's take a quick look at some more theory, and see how collections of tables such as these help you store data efficiently.

Storing Data

2010-11-29T00:01:00.005+08:00

Whenever you start work on a data-driven application, one of the first design decisions you have to make is this: what's the best data store to use? In other words, how and where is the application going to store and access its data. The correct choice is always going to depend on the application's requirements. At the simplest level, you should be asking questions like:

How much data will the application use?
How often will it need access to the data?
How often will it need to modify the data?
How many users are likely to want access to the data at once?
How will the data grow over time?
How much do I stand to lose if the data is broken, stolen, or lost?

If the answer to any of these questions is "a lot," then you probably want to steer clear of using plain text files to store your data.

That's not to say that text files are useless—for instance, when visitors attempt to enter a password-protected area, an Apache Web server (unless configured otherwise) authenticates them against a plain text file containing a complete list of user IDs and passwords. This isn't a problem as long as it's dealing with a small group of users, but if it has to validate hundreds at the same time, it must scan the text file one line at a time until it finds a match. One unlucky user would have to wait for Apache to find his details on the last line of the file before it let him enter the password-protected area, and that could cost quite some time.

Often, the most efficient alternative is to use a database engine—or to use its more technical name, a Database Management System (DBMS)—to store, retrieve, and modify the data for you. A good database engine really just serves as a very, very smart go-between for you and your data. It organizes, catalogs, backs up, and a whole lot more besides, all with a view toward making that data quicker and easier to work with.

So where does all the data go? Well, it depends to some extent on the database engine you're using. Chances are, though, it'll end up being stored as streams of bits and bytes in a number of files—yes, files! Truth is you can't really get away from using files at some point. The trick is in finding ways to use them as efficiently as possible, and a good database engine has many, many such tricks up its metaphorical sleeves.

Databases and Databases

At this point it's worth taking a moment to look at exactly what the word database refers to. Strictly speaking, a database is just an efficiently organized collection of data, just as a library is really no more than an ordered collection of books. However, everyone tends to use the word library to refer to the institution as a whole—not just the books, but the staff, its working practices, and the building, too—and the same is true of databases. So, when you hear someone talk about a database, he's more than likely referring to the whole caboodle: the database engine and the data.

This isn't so surprising because under normal circumstances the only contact you ever have with the raw data is through the DBMS, or database engine to be slightly less formal. Most of the time you never see the join, so there's no real practical distinction. Later we discuss installing the database, connecting to the database, talking to the database, and so on—just remember that we're using the word in its more common, all-encompassing sense.

Database Architectures

Before we get going here, you need to settle on a particular database with which to experiment, and that means first deciding on the type of database architecture you're going to use. There are two main options: embedded and client/server. Let's take a quick look at both.

Embedded Databases

An embedded database runs—and stores its data—on the same machine as the program that wants to use it (PHP in this case). The database is not networked, and only one program can connect to it at any given time. Moreover, the database can't be shared between different machines because each one would simply end up storing and manipulating its own separate version of the data. It's analogous to having your own personal library, staff and all, to which you have exclusive access. For smaller applications there can often be advantages to using an embedded database. For larger systems, the advantages provided by the larger Relational Database Management Systems (RDBMS) tip the scales in their favor and as a result, most commercial enterprises use client/server database architectures.

Long-standing, popular examples of embedded database engines include dBase and DBM, and PHP provides connectivity to both. A more recent addition to the fold is SQLite, which is not just available as a PHP5 extension, but is actually bundled as part of the PHP5 download. For that reason alone it's well worth a look, and some impressive performance stats certainly help back up its placement as the rising star of PHP database technologies. You can learn more about SQLite in Appendix C, Using SQLite.

Client/Server Databases

Client/server databases are designed for use over networks, enabling multiple users (who may be scattered across a whole host of different locations) to work simultaneously with the same data. The database (the term database is also often used as shorthand for Database Management System, or DBMS) itself acts as a server, not unlike the Web servers discussed in the opening chapters. In principle it can field requests from just about anywhere with a network connection and a suitable client program. That said, there's no reason why you can't run both on the same machine.

Client/server databases are more closely analogous to the earlier metaphor of the public lending library. It's open to anyone who has a membership card (is registered with the library) and, with several staff members on the reception desk, can deal with requests from a number of visitors at once. The visitors may be there in person, or may call in on the telephone.

The actual job of finding or returning a book to the shelf might then be delegated to an assistant, but visitors don't need to worry about that. Each receptionist may have to deal with several visitors at once, but if he's good at his job, he'll spend a reasonable amount of time on each, and all visitors feel that they're getting good service. Likewise, when several users access a client/server database engine at the same time, the engine regularly switches its attention among them, giving each one the sense that she's being attended to all the while.

This is the kind of database you're more likely to find being used in a large company, where large quantities of data need to be shared among large numbers of people, where access may be needed from all sorts of different locations, and where having a single centralized data store makes important jobs like administration and backup relatively straightforward. Any applications that need to access the database use specialized, lightweight client programs to communicate with the server.

RDBMSs (Relational Database Management Systems) are often expensive and complex to set up and administer. The widely acknowledged big three in this field are Oracle, DB/2 (from IBM), and SQL Server (from Microsoft). All three are massive, feature-rich systems, seemingly capable of just about any kind of data storage and processing that a modern business could need. The flip side of the coin is that these systems are big and expensive, and may contain more functionality than you will ever need.

Fortunately there are alternatives such as PostgreSQL and MySQL, which are both Open Source client/server database systems that have proven very popular with PHP developers for many years. They're fast, stable, easily meet the needs of most small-to-medium sized projects, and, to top it all, they're free!

Choosing a Database

In principle, you can use any of these database systems in your PHP applications. There's even no reason why you can't hook one application up to several different database engines. To keep these chapters to a reasonable length, however, the focus will be on just one—MySQL.

Compared to the other choices, it offers several advantages:

It's one of the most popular databases being used on the Web today.
It's freely available as a download to install and run on your own machine.
It's easy to install on a wide range of operating systems (including Windows and UNIX).
It's available as a relatively cheap feature in many Web-hosting packages.
It's simple to use and includes some handy administration tools.
It's a fast, powerful client/server system that copes well with very large, complex databases, and should stand you in good stead when it comes to larger projects.

If you're not too fussed about the last criterion (and particularly if you don't want to pay out extra for database functionality on your Web account!) you might well find that an embedded database such as SQLite does a perfectly good job.

MySQL is a freely available RDBMS, which fully joined the Open Source Community only recently, when it was released under the GNU Public License (GPL). Even before it went free, you didn't need a license unless you wanted to make money out of it, or run the server on the Windows platform (the Windows version of MySQL was shareware). Because you now don't have to pay a dime to use it, this alone makes MySQL a solid candidate for developing database applications. If the GPL worries you for any reason, or you need to incorporate MySQL into a commercial application, you can still buy a commercially licensed version from the developers at www.mysql.com.

That said, a lot of the concepts to be introduced here and in the following chapters extend well beyond one specific database engine, so I still recommend following along with MySQL for the time being.

Chapter 9: An Introduction to Databases and SQL

2010-11-22T00:01:00.003+08:00

You've spent the last couple of chapters seeing how your PHP scripts can use external files to store and retrieve data. Although direct file access does a great job in many circumstances, it's pretty limited as data storage solutions go—what you see is what you get, no more, no less. Any sifting, sorting, or other work that has to be done on the data ultimately falls to you and your scripts, and the more data there is to handle, the longer it takes the script to deal with it. This might not present much of a problem when you're dealing with a few kilobytes of data, but who's to say when those kilobytes will grow into megabytes (or even gigabytes) of data. If you're actively setting out to build a successful, popular, data-driven Web site, you really have to allow for that volume possibility.

Just think—if a large commercial site such as Amazon accessed all its data directly from plain text files, it could take you months just to track down a single book, let alone place an order!

Databases are specifically designed to get around this problem. With their own special capabilities of organization and immaculate record keeping, you can almost imagine them as the super-hero staffed lending libraries of the IT world. No more searching for hours through shelves of musty tomes; just a word at the front desk, a blur of blue and red, and the last remaining copy of Love in the Time of Cholera appears—as if by magic—on the desk in front of you.

This is the first in a series of three chapters in which you'll explore databases and learn how you can use them to start creating powerful, efficient (and potentially very large) PHP applications. You'll examine the general advantages of using databases rather than files to store your data. You'll also learn about some of the popular databases that you're likely to come across, and how they differ.

Relational databases will be explained, and you'll explore related concepts such as normalization and indexing. You'll see how to set up MySQL, a database system that's freely available and widely used with PHP, and learn some PHP functions for connecting to a database in MySQL as well as how to use MySQL to retrieve and modify the contents of a database.

There's a lot of ground to cover in this chapter, so you may find it's a little heavier on theory than most. It won't all be brain strain, but the practical element involves getting a database up and running on your test machine, and that will be a lot easier if you already understand what's needed. For that reason, PHP itself takes something of a back seat until the last few sections, when you hook PHP up to your working database in preparation for the next two chapters. If you're someone who's already familiar with databases and how they work, you may want to skip straight on to that—otherwise, it all starts right here!

Online Lead Finder Update

2010-11-15T00:01:00.001+08:00

A new update to the Online Lead Finder has been posted today in the members area.

http://www.onlineleadfinder.com/login.php

Following fixes done:

1. Craigslist problem solved, many results were going to "Page notfound" as craigslist again changed something in their structure.
2. Auto Campaigning Pause Interval reduced to default 1 minute, now stop buttonwould work faster.
3. Problems on Twitter Stop Messaging button solved.

Next month, we will be releasing some brand new features for current members.

Remember, you have to stay on our mailing list to get your free updates.

Hooked on Overages Webinar

2010-11-08T00:01:00.008+08:00

I never expected the kind of results my friend Rick Dawson is seeing today.

He's an investor who changed just one thing in his business and now has five-figure finder-fee checks showing up in his mailbox like clockwork.

What's the change?

I promised Rick the only thing I'd say is that it's a new twist on an old technique. One thing's for sure, it flat-out works.

He even let me see a couple checks that came in last month - one for $18k+.

Rick's created an Insider's Guide, "Five-Figure Finder-Fees With Real Estate Unclaimed Funds". It's jam-packed with info and you'll get it right away when you register for Rick's "Hooked on Overages" webinar:

**************************
When: Tuesday, November 15th
Time: 9:00 PM Eastern/6:00PM Pacific

Click below or copy and paste the link into your internet browser to register and get the call in details.

Space is limited. Reserve your Webinar seat now at:
https://www2.gotomeeting.com/register/179853899

**************************

Snatch it while you still can because this one thing really does change EVERYTHING.

From the reaction he's getting from guys who don't want this info made public, my guess is it won't be up much longer, so go get it right now.

Rick has already trained a number of students who are not happy this information is being put out again - grab it before he takes it down.

Space is limited. Reserve your Webinar seat now at:
https://www2.gotomeeting.com/register/179853899

Magic Bullet System - Video Training

2010-11-01T00:01:00.007+08:00

Hey - this is super cool:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1lSJpz70XwcD0P&b=rTskTdSAGi.7HfnE0ix30g

It's a brand new video that shows a virtually UNKNOWN way to get tons of traffic to *any* website.

It doesn't matter if it's your website or if you're sending traffic straight to an affiliate link
...it works just the same.

In fact, the guys in the video are bringing in 756.15 per DAY with this method ...and they're
not even using their own site. They're sending traffic straight to an affiliate link!

Check it out:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1lSJpz70XwcD0P&b=rTskTdSAGi.7HfnE0ix30g

Seriously, you get to see exactly what site they're promoting, where they're getting traffic, how much they're spending, what their net profit is, and everything else you want to know.

This is an excellent video and it's 100% content:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1lSJpz70XwcD0P&b=rTskTdSAGi.7HfnE0ix30g

I read a study that Google is only responsible for between 15 and 30% of all traffic out there.

That means if you're just using Adwords, you're leaving up to 85% of your traffic (and your MONEY) on the table.

This video shows you one of the greatest traffic sources out there (and nobody in our community is using it!)

It's truly excellent. You can watch it here:

http://clicks.prosender.com/y/ct/?l=70FmR&m=1lSJpz70XwcD0P&b=rTskTdSAGi.7HfnE0ix30g

Magic Bullet System - Recorded Seminar

2010-10-25T00:01:00.005+08:00

Well the feedback is AWESOME... so thanks!

The call turned into 60 minutes of PURE content... you will love it!

Amish answered every question that was thrown at him and I for one am very impressed. These questions were from you there readers and were very pointed!

Go download the call now and listen.

http://clicks.prosender.com/y/ct/?l=Je2ap&m=1eJuWwZY_foH0P&b=U5KeZ6nqa1YJmjKVBzLkmg

Oh and if you have not already checked out the free stuff that Amish is giving away, then go here... you will like it (well I do anyway).

PS - I am giving away the 100% total details of the exact website and promotion strategies i am using for killing it from an seo perspective (and no this is not using Johncow.com but instead a brand new site that is only 1 week old (brand new domain too) and already getting 150+ visitors a day... and i am not paying for any of the traffic. I am going to give you flow charts and everything... but you need to have opt in with this link...

http://clicks.prosender.com/y/ct/?l=Je2ap&m=1eJuWwZY_foH0P&b=AoSF9Wkw8qSdMh13xwbXeg

I hope you like the call... i did and so did everyone who was one it!

Again the MP3 is available at...

http://clicks.prosender.com/y/ct/?l=Je2ap&m=1eJuWwZY_foH0P&b=U5KeZ6nqa1YJmjKVBzLkmg