php-and-mysql

How to upgrade MySQL?

2008-06-26T05:53:00.000-07:00

New versions of MySQL are released periodically, and you can upgrade from one version of MySQL to a newer version. Upgrading information is provided in the MySQL manual at dev.mysql.com/doc/refman/5.0/en/upgrade. html. However, there are special considerations when upgrading. As a precaution, it is wise to back up your current databases, including the grant tables in the mysql database, before upgrading. MySQL recommends that you do not skip versions. If you want to upgrade from one version to a version more than one version newer, such as from MySQL 4.0 to MySQL 5.0, you should upgrade to the next version first. After that version is working correctly, you can upgrade to the next version.
And so on. In other words, upgrade from 4.0 to 4.1, then from 4.1 to 5.0. Occasionally, incompatible changes are introduced in new versions of MySQL. Some releases introduce changes to the structure of the grant tables. For instance, MySQL 4.1 changed the method of encrypting passwords, requiring a longer password field in the grant tables. After upgrading to the newer version, you should run the mysql_upgrade script. It checks your files, repairing them if needed, and upgrades the system tables if needed. Before MySQL version 5.0.19, the mysql_upgrade script does not run on Windows; it runs only on Unix. On Windows, you can run a script called mysql_fix_privileges_tables with MySQL versions prior to 5.0.19. The script upgrades the system tables but does not perform the complete table check and repair that mysql_upgrade performs.

How to Restore Your Data?

2008-06-13T05:29:00.000-07:00

At some point, one of your database tables might become damaged and unusable. It’s unusual, but it happens. For instance, a hardware problem or an unexpected shutdown of the computer can cause corrupted tables. Sometimes an anomaly in the data that confuses MySQL can cause corrupt tables. In some cases, a corrupt table can cause your MySQL server to shut down.
Here is a typical error message that signals a corrupted table:
Incorrect key file for table: ‘tablename’.
You can replace the corrupted table(s) with the data stored in a backup copy. In some cases, the database might be lost completely. For instance, if the computer where your database resides breaks down and can’t be fixed, your current database is lost, but your data isn’t gone forever. You can replace the broken computer with a new computer and restore your database from a backup copy.
You can replace your current database table(s) with the database stored in a backup copy. The backup copy contains a snapshot of the data as it was when the copy was made. Any changes to the database since the backup copy was made are not included; you have to re-create those changes manually. Again, if you access MySQL through an IT department or through a Web hosting company, you need to ask the MySQL administrator to restore your database from a backup. If you’re the MySQL administrator, you can restore it yourself.
As I describe in Chapter 4, you build a database by creating the database and then adding tables to the database. The backup created by the mysqldump utility is a file that contains all the SQL statements necessary to rebuild the tables, but it does not contain the statements needed to create the database. Your database might not exist, or it could exist with one or more corrupted tables. You can restore the entire database or any single table. Follow these steps to restore a single table:

If the table currently exists, delete the table with the following SQL query: DROP TABLE tablename where tablename is the table that you want to delete.
Point your browser at mysql_send.php. For a description of mysql_send.php.
Copy the CREATE query for the table from the backup file into the form in the browser window. For instance, choose EditCopy and EditPaste.
Type the name of the database in which you are restoring the table. The form shows where to type the database name.
Click Submit. A new Web page shows the results of the query.
Click New Query.
Copy an INSERT query for the table from the backup file into the form in the browser window. For instance, choose EditCopy and EditPaste.
Type the name of the database in which you are restoring the table. The form shows where to type the database name.
Click Submit. A new Web page shows the results of the query.
Click New Query.
Repeat Steps 7–10 until all the INSERT queries from the backup file have been sent.

If you have so many INSERT queries for the table that sending them one by one would take forever — or if there are just a lot of tables — you can send all the queries in the backup file at once. First, you may need to edit the backup file, as follows:

Open the backup file in a text editor.
Locate the line that shows the Server Versions.
If you want to rebuild an entire database, add the following statement after the line located in Step 2: CREATE DATABASE IF NOT EXISTS databasename
After the line in Step 3, add a line specifying which database to add the tables to: USE databasename
Check the blocks of statements that rebuild the tables. If you do not want to rebuild a table, comment out the lines that rebuild the table by adding -- (two hyphens) in front of each line.
Check the INSERT lines for each table. If you do not want to add data to any tables, comment out the lines that INSERT the data.
Save the edited backup file.

After the backup file contains the statements that you want to use to rebuild your database or table(s), you need to perform a few more steps.
On Linux, Unix, and Mac:

Change to the bin subdirectory in the directory where MySQL is installed. Type a cd command to change to the correct directory. For instance, type cd /usr/local/mysql/bin.
Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>

Change to the bin subdirectory in the directory where MySQL is installed.

a. Open a command prompt window. For instance, choose StartAll ProgramsAccessoriesCommand Prompt.

b. Type a cd command to change to the correct directory.

For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin.

Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>

How to Back Up Your Data?

2008-06-12T23:07:00.000-07:00

You need to have at least one copy of your valuable database. Disasters occur rarely, but they do occur. The computer where your database is stored can break down and lose your data, the computer file can become corrupted, the building can burn down, and so on. Backup copies of your database guard against data loss from such disasters.
You should have at least one backup copy of your database, stored in a location that is separate from the copy that is currently in use. More than one copy — perhaps as many as three — is usually a good idea:

Store one copy in a handy location, perhaps even on the same computer, to quickly replace a working database that has been damaged. _ Store a second copy on another computer in case the computer breaks down, and the first backup copy isn’t available.
Store a third copy in a different physical location, for that remote chance that the building burns down. If the second backup copy is stored via a network on a computer at another physical location, this third copy isn’t needed.

If you don’t have access to a computer offsite where you can back up your database, you can copy your backup to a portable medium, such as a CD or DVD, and store it offsite. Certain companies will store your computer media at their location for a fee, or you can just put the media in your pocket and take it home.
If you use MySQL on someone else’s computer, such as the computer of your employer or a Web hosting company, the people who provide your access are responsible for backups. They should have automated procedures in place that make backups of your database. When evaluating a Web hosting company, ask about their backup procedures. You want to know how often backup copies are made and where they are stored. If you aren’t confident that your data is safe, you can discuss changes or additions to the backup procedures. If you are the MySQL administrator, you are responsible for making backups. MySQL provides a program called mysqldump that you can use to make backup copies. The mysqldump program creates a text file that contains all the SQL statements needed to re-create your entire database. The file contains the CREATE statements for each table and INSERT statements for each row of data in the tables. You can restore your database by executing the set of MySQL statements. You can restore it in its current location, or you can restore it on another computer if necessary.
Follow these steps to make a backup copy of your database in Linux, in Unix, or on a Mac:

Change to the bin subdirectory in the directory where MySQL is installed. For instance, type cd /usr/local/mysql/bin.
Type the following: mysqldump—user=accountname—password=password databasename >path/backupfilename ,where

accountname is the name of the MySQL account that you’re using to back up the database
password is the password for the account
databasename is the name of the database that you want to back up
path/backupfilename is the path to the directory where you want to store the backups and the filename the SQL output will be stored in The account that you use needs to have select permission. If the account doesn’t require a password, you can leave out the entire password option. You can type the command on one line, without pressing Enter. Or you can type a backslash (\), press Enter, and continue the command on another line. For example, to back up the PetCatalog database, the command might be mysqldump—user=root—password=secret PetCatalog \ >/usr/local/mysql/backups/PetCatalogBackup Note: With Linux or Unix, the account that you are logged into must have permission to write a file into the backup directory.

To make a backup copy of your database in Windows, follow these steps:

Open a command prompt window. For instance, choose StartAll ProgramsAccessoriesCommand prompt.
Change to the bin subdirectory in the directory where MySQL is installed. For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin.
Type the following: mysqldump—user=accountname—password=password databasename >path\backupfilename, where

accountname is the name of the MySQL account that you’re using to back up the database

password is the password for the account

databasename is the name of the database that you want to back up

path\backupfilename is the path to the directory where you want to store the backups and the filename the SQL output will be stored in The account that you use needs to have select permission. If the account does not require a password, you can leave out the entire password option. You must type the mysqldump command on one line without pressing Enter. For example, to back up the PetCatalog database, the command might be mysqldump—user=root PetCatalog >PetCatalogBackup Backups should be made at certain times — at least once per day. If your database changes frequently, you might want to back up more often. For example, you might want to back up to the backup directory hourly but back up to another computer once a day.

How to Remove accounts and permissions?

2008-06-12T18:38:00.000-07:00

To remove an account, you can use the DROP USER query, which was added in MySQL 4.1.1, as follows:

DROP USER accountname@hostname, accountname@hostname, ...

You must be using an account that has DELETE privileges on the mysql database to execute the DROP USER query.
The behavior of DROP USER has changed through MySQL versions. As of MySQL 5.0.2, it removes the account and all records related to the account, including records that give it permissions on specific databases or tables. However, before MySQL 5.0.2, DROP USER drops only accounts with no privileges. Therefore, in older versions, you must remove all the privileges from an account, including database or table permissions, before you can drop it.
To remove permissions, use the REVOKE query. The general format is

REVOKE permission (columns) ON tablename FROM accountname@hostname

You need to fill in the following information:

permission (columns): You must list at least one permission. You can remove each permission from one or more columns by listing the column name in parentheses following the permission. If no column name is listed, the permission is removed from all columns in the table(s). You can list as many permissions/columns as needed, separated by commas. For instance, a REVOKE query might start like this: REVOKE select (firstName,lastName), update, insert (birthdate) ...
tablename: Indicate which tables the permission is being removed from. At least one table is required. You can list several tables, separated by commas. The possible values for tablename are

tablename: The entire table named tablename in the current database. You can use an asterisk (*) to mean all tables. If you use an asterisk when no current database is selected, the privilege will be revoked on all tables in all databases.

databasename.tablename: The entire table named tablename in databasename. You can use an asterisk (*) for either the database name or the table name to mean all. Using . revokes the permission on all tables in all databases.

accountname@hostname: The account is identified by the accountname and the hostname as a pair. If an account exists with the specified account name but a different hostname, the REVOKE query will fail, and you will receive an error message. You can remove all the permissions for an account with the following REVOKE query: REVOKE all ON . FROM accountname@hostname

How to change permissions?

2008-06-12T17:52:00.000-07:00

You can see the current permissions for an account with the following query:

SHOW GRANTS ON accountname@hostname

The output is a GRANT query that would create the current account. It shows all the current permissions. If you do not include the ON clause, you see the current permissions for the account that issued the SHOW GRANTS query. You can change permissions for an account with the GRANT query, which has the following general format:

GRANT permission (columns) ON tablename TO accountname@hostname IDENTIFIED BY ‘password’

You can also create a new account or change a password with the GRANT query. You need to fill in the following information:

permission (columns): You must list at least one permission. You can limit each permission to one or more columns by listing the column name in parentheses following the permission. If no column name is listed, the permission is granted on all columns in the table(s). You can list as many permissions and columns as needed, separated by commas. For instance, a GRANT query might start with this: GRANT select (firstName,lastName), update, insert (birthdate) ...

tablename: This indicates which tables the permission is granted on. At least one table is required. You can list several tables, separated by commas. The possible values for tablename are

tablename: The entire table named tablename in the current database. You can use an asterisk (*) to mean all tables in the current database. If you use an asterisk and no current database is selected, the privilege will be granted to all tables on all databases.

databasename.tablename: The entire table named tablename in databasename. You can use an asterisk (*) for either the database name or the table name to mean all. Using . grants the permission on all tables in all databases.

accountname@hostname: If the account already exists, it is given the indicated permissions. If the account doesn’t exist, it’s added. The account is identified by the accountname and the hostname as a pair. If an account exists with the specified account name but a different hostname, the existing account is not changed; a new one is created.
password: This is the password that you’re adding or changing. A password is not required. If you don’t want to add or change a password for this account, leave out the phrase IDENTIFIED BY ‘password’. The GRANT query to add a new account for use in the PHP programs for the PetCatalog database might be GRANT select ON PetCatalog.* TO phpuser@localhost IDENTIFIED BY ‘A41!14a!’

How to add and change SQL passwords?

2008-06-12T17:51:00.000-07:00

You can add or change a password for an existing account with the SET PASSWORD query, as follows:

SET PASSWORD FOR username@hostname = PASSWORD(‘password’)

The account is set to password for the account username@hostname. If the account currently has a password, the password is changed. You do not need to specify the FOR clause. If you do not, the password is set for the account you are currently using.
You can remove a password by sending the SET PASSWORD query with an empty password, as follows:

SET PASSWORD FOR username@hostname = PASSWORD(‘’)

How to Add Accounts?

2008-06-12T17:47:00.000-07:00

The preferred way to access MySQL from PHP is to set up an account specifically for this purpose with only the permissions that are needed. In this section, I describe how to add accounts. If you’re using an account given to you by a company IT department or a Web hosting company, it might or might not have all the permissions needed to create an account. If it doesn’t, you won’t be able to successfully execute the query to add an account, and you’ll have to request a second account to use with PHP.
If you need to request a second account, get an account with restricted permission (if at all possible) because your Web database application will be more secure if the account used in your PHP programs doesn’t have more privileges than are necessary.
To create one or more users, you can use the CREATE USER query added to MySQL in version 5.0.2, as follows:

CREATE USER accountname@hostname IDENTIFIED BY ‘password’, accountname@hostname IDENTIFIED BY ‘password’,...

This query creates the specified new user account(s) with the specified password and no permissions. You do not need to specify a password. If you leave out IDENTIFIED BY ‘password’, the account is created with no password. You can add or change a password for the account at a later time. Adding passwords and permissions is discussed in the following sections. If you’re using a version of MySQL before 5.0.2, you must use a GRANT query to create an account. The GRANT query is described in the “Changing permissions” section.

Identifying what accounts currently exist

2008-05-23T05:59:00.000-07:00

To see what accounts currently exist for your database, you need an account that has the necessary permissions. Try to execute the following query on a database named mysql:

SELECT * FROM user

You should get a list of all the accounts. However, if you’re accessing MySQL through your company or a Web hosting company, you probably don’t have the necessary permissions. In that case, you might get an error message like this:

No Database Selected

This message means that your account is not allowed to select the mysql database. Or you might get an error message saying that you don’t have SELECT permission. Even though this message is annoying, it’s a sign that the company has good security measures in place. However, it also means that you can’t see what privileges your account has. You must ask your MySQL administrator or try to figure it out yourself by trying queries and seeing whether you’re allowed to execute them.

The MySQL security database

2008-05-23T05:53:00.000-07:00

When MySQL is installed, it automatically creates a database called mysql. All the information used to protect your data is stored in this database, including account names, hostnames, passwords, and permissions.
Permissions are stored in columns. The format of each column name is permission_priv. For instance, the column containing ALTER permissions is named alter_ priv. The value in each permission column is Y or N, meaning yes or no. So, for instance, in the user table (described in the following list), there would be a row for an account and a column for alter_priv. If the account field for alter_priv contains Y, the account can be used to execute an ALTER query. If alter_ priv contains N, the account doesn’t have permission to execute an ALTER query. The mysql database contains the following tables that store permissions:

user table: This table stores permissions that apply to all the databases and tables. It contains a row for each valid account that includes the column’s user name, hostname, and password. The MySQL server will reject a connection for an account that does not exist in this table.
db table: This table stores permissions that apply to a particular database. It contains a row for the database, which gives permissions to an account name and a hostname. The account must exist in the user table for the permissions to be granted. Permissions that are given in the user table overrule permissions in this table. For instance, if the user table has a row for the account designer that gives INSERT privileges, designer can insert into all the databases. If a row in the db table shows N for INSERT for the designer account in the PetCatalog database, the user table overrules it, and designer can insert in the PetCatalog database.
host table: This table controls access to a database depending on the host. The host table works with the db table. If a row in the db table has an empty field for the host, MySQL checks the host table to see whether the db has a row there. In this way, you can allow access to a db from some hosts but not from others. For instance, suppose you have two databases: db1 and db2. The db1 database has sensitive information, so you want only certain people to see it. The db2 database has information that you want everyone to see. If you have a row in the db table for db1 with a blank host field, you can have two rows for db1 in the host table. One row can give all permissions to users connecting from a specific host, whereas another row can deny privileges to users connecting from any other host.
tables_priv table: This table stores permissions that apply to specific tables.
columns_priv table: This table stores permissions that apply to specific columns.

You can see and change the tables in mysql directly if you’re using an account that has the necessary permissions. You can use SQL queries such as SELECT, INSERT, and UPDATE. If you’re accessing MySQL through your employer, a client, or a Web hosting company, it is unlikely that you will be given an account that has the necessary permissions.

Setting Up MySQL Accounts

2008-05-07T02:18:00.000-07:00

An account is identified by the account name and the name of the computer allowed to access MySQL using this account. When you create a new account, you specify it as accountname@hostname. You can specify a password when you create an account or you can add a password later. You can set up permissions when you create an account or add permissions later. All the account information is stored in a database named mysql that is automatically created when MySQL is installed. To add a new account or change any account information, you must use an account that has the proper permissions on the mysql database.
You need at least one account to access the MySQL server. When MySQL is installed, it automatically sets up some accounts, including an account called root that has all permissions. If you have MySQL access through a company Web site or a Web hosting company, the MySQL administrator for the company should give you the account; the account is probably not named root, and it might or might not have all permissions.
In the rest of this section, I describe how to add and delete accounts and change passwords and permissions for accounts. If you have an account that you received from your company IT department or from a Web hosting company, you might receive an error when you try to send any or some of the GRANT or REVOKE queries described. If your account is restricted from performing any of the necessary queries, you need to request an account with more permissions or ask the MySQL administrator to add a new account or make the changes you need.

Taking a look at account permissions

2008-05-07T02:16:00.000-07:00

MySQL uses account permissions to specify who can do what. Anyone using a valid account can connect to the MySQL server, but he or she can only do those things that are allowed by the permissions for the account. For example, an account might be set up so that users can select data but cannot insert or update data.
Permissions can be granted for particular databases, tables, or columns. For instance, an account can be set up that allows the user to select data from all the tables in the database but insert data into only one table and update only a single column in a specific table.
Permissions are added by using the GRANT query and removed by using the REVOKE query. The GRANT or REVOKE query must be sent using an account that has permission to execute GRANT or REVOKE statements in the database. If you attempt to send a GRANT query or a REVOKE query using an account without grant permission, you get an error message. For instance, if you try to grant permission to use a select query, and you send the query using an account that does not have permission to grant permissions, you might see the following error message: grant command denied Permissions can be granted or removed individually or all at once.
Permission &Description
ALL All permissions
ALTER Can alter the structure of tables
CREATE Can create new databases or tables
DELETE Can delete rows in tables
DROP Can drop databases or tables
FILE Can read and write files on the server
GRANT Can change the permissions on a MySQL account
INSERT Can insert new rows into tables
SELECT Can read data from tables
SHUTDOWN Can shut down the MySQL server
UPDATE Can change data in a table
USAGE No permissions
Granting ALL is not a good idea because it includes permissions for administrative operations, such as shutting down the MySQL server. You are unlikely to want anyone other than yourself to have such sweeping privileges.

Finding out about passwords

2008-05-07T02:14:00.000-07:00

A password is set up for every account. If no password is provided for the account, the password is blank, which means that no password is required. MySQL doesn’t have any limit for the length of a password, but sometimes other software on your system limits the length to eight characters. If so, any characters after eight are dropped.
For extra security, MySQL encrypts passwords before it stores them. That means passwords are not stored in the recognizable characters that you entered. This security measure ensures that no one can look at the stored passwords and see what they are.
Unfortunately, some bad people out there might try to access your data by guessing your password. They use software that tries to connect rapidly in succession using different passwords — a practice called cracking. The following are some recommendations for choosing a password that is as difficult to crack as possible:

Use six to eight characters.
Include one or more of each of the following — uppercase letter, lowercase letter, number, and punctuation mark.
Do not use your account name or any variation of your account name.
Do not include any word in a dictionary, including foreign language dictionaries.
Do not include a name.
Do not use a phone number or a date.

A good password is hard to guess and easy to remember. If it’s too hard to remember, you might need to write it down, which defeats the purpose of having a password. One way to create a good password is to use the first characters of a favorite phrase. For instance, you could use the phrase “All for one! One for all!” to make this password:
Afo!Ofa!
This password doesn’t include any numbers, but you can fix that by using the
numeral 4 instead of the letter f. Then your password is
A4o!O4a!
Or you could use the number 1 instead of the letter o to represent one.
Then the password is
A41!14a!
This password is definitely hard to guess. Other ways to incorporate numbers into your passwords include substituting 1 (one) for the letter l or substituting 0 (zero) for the letter o.

Sun Asserts MySQL Will Remain Open Source

2008-05-02T18:43:00.000-07:00

By Brian Prince

Marten Mickos, head of Sun Microsystems' database group, says MySQL will remain an open-source product.

After several days of blog posts and headlines, the dust that kicked up over Sun Microsystems' commitment to keeping MySQL open source seems to have settled.

At the center of the controversy were reports of plans to close-source backup features in Version 6.0 of the MySQL database, making them available only to subscription-paying users of its enterprise version. In an effort to clear the air, Marten Mickos, senior vice president of Sun's Database Group, responded to blog posts on Slashdot and elsewhere.

In an interview with eWEEK, he stated emphatically that Sun will not withhold or close-source any features that would make the MySQL community server less functional for users.

The core backup functionality and a backup API in Version 6.0 will be available to users of both the community and enterprise versions of the database, he said. However, the company plans to develop high-end add-ons such as encryption and compression solely for its MySQL Enterprise subscription customers, Mickos said.

Mickos calls MySQL the Ferrari of databases. Read why here.

“It is not a question of close-sourcing any existing code, nor anything in the core server,” Mickos said. “Everything MySQL has released under the GPL license continues to be under GPL, and the core MySQL server will always be under GPL, or some other free and open-source software [FOSS] license.”

Mickos said it has not been decided yet what license the add-ons will be released under. Sun, he added, was not part of the initial plans regarding the backup functionality—those discussions were taking place well before the acquisition by Sun earlier this year.

“At all times, because the main backup functionality goes into the core server under GPL, anyone can use the API and build their own add-ons or other modifications,” he said. “We are also offering them a convenient, low-cost option through our add-ons, but not forcing them or restricting them from building their own.”

Several MySQL users objected on blogs last week to talk of making key functionality available only to paying customers. It wasn’t the first time open-source advocates took MySQL to task for such moves; there was a similar, albeit more subdued, reaction when MySQL AB announced it was removing Enterprise source tarballs from ftp.mysql.com last August, analyst Matt Aslett noted.

“MySQL has been quick to respond to the criticism this time, and has been very open in explaining its strategy on user blogs, and the company does appear to have been able to stop the resentment from growing,” said Aslett, who is with The 451 Group. “It would be prudent for the company to prioritize deciding on which licenses it will use for the new functionality so that it can explain its strategy properly before doubt continues to spread.”

Mickos admitted the initial information was released awkwardly by the company and said he understood why some members of the open-source community were upset at first. This is why the company sought to aggressively address some of their concerns, he said.

“Open-source software is really just now reaching the radar of mainstream IT,” Mickos said. “There is still work to be done in formulating business models around open source, and so some degree of experimentation is essential. Keep in mind, open source itself began as an experiment in the software industry. So all vendors should not be afraid to continue to experiment both in software and in business models.”

Understanding account names and hostnames

2008-05-02T18:37:00.000-07:00

Together, the account name and hostname (the name of the computer that is authorized to connect to the database) identify a unique account. Two accounts with the same name but different hostnames can exist and can have different passwords and permissions. However, you cannot have two accounts with the same name and the same hostname.
The MySQL server will accept connections from a MySQL account only when it is connecting from hostname. When you build the GRANT or REVOKE query (which I describe later in this chapter), you identify the MySQL account by using both the account name and the hostname in the following format: accountname@hostname (for instance, root@localhost). The MySQL account name is completely unrelated in any way to the Unix, Linux, or Windows user name (also sometimes called the login name). If you’re using an administrative MySQL account named root, it is not related to the Unix or Linux root login name. Changing the MySQL login name does not affect the Unix, Linux, or Windows login name — and vice versa.
MySQL account names and hostnames are defined as follows:

An account name can be up to 16 characters long. You can use special characters in account names, such as a space or a hyphen (-). However, you cannot use wildcards in the account name.
An account name can be blank. If an account exists in MySQL with a blank account name, any account name will be valid for that account. A user could use any account name to connect to your database, given that the user is connecting from a hostname that is allowed to connect to the blank account name and uses the correct password, if required. You can use an account with a blank name to allow anonymous users to connect to your database.
The hostname can be a name or an IP address. For example, it can be a name such as thor.mycompany.com or an IP (Internet protocol) address such as 192.163.2.33. The machine on which the MySQL server is installed is localhost.
The hostname can contain wildcards. You can use a percent sign (%) as a wildcard; % matches any hostname. If you add an account for george@%, someone using the account named george can connect to the MySQL server from any computer.
The hostname can be blank. A blank hostname is the same as using % for the hostname.

An account with a blank account name and a blank hostname is possible. Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. An account with a blank name and a percent sign (%) for the hostname is the same thing. It is unlikely that you would want such an account. Such an account is sometimes installed when MySQL is installed, but it’s given no privileges, so it can’t do anything.

When MySQL is installed, it automatically installs an account with all privileges:
root@localhost. Depending on your operating system, this account may be installed without a password. Anyone who is logged in to the computer on which MySQL is installed can access MySQL and do anything to it by using the account named root. (Of course, root is a well-known account name, so this account is not secure. If you’re the MySQL administrator, you should add a password to this account immediately.)
On some operating systems, additional accounts besides root@localhost are automatically installed. For instance, on Windows, an account called root@% might be installed with no password protection. This root account with all privileges can be used by anyone from any machine. You should remove this account immediately or, at the very least, give it a password.

How to Control Access to Your MySQL Data?

2008-05-02T18:31:00.000-07:00

You need to control access to the information in your database. You need to decide who can see the data and who can change it. Imagine what would happen if your competitors could change the information in your online product catalog or copy your list of customers — you’d be out of business in no time flat. Clearly, you need to guard your data. MySQL provides a security system for protecting your data. No one can access the data in your database without an account. Each MySQL account has the following attributes:

A name
A hostname — the machine from which the account can access the MySQL server
A password
A set of permissions

To access your data, someone must use a valid account name and know the password associated with that account. In addition, that person must be connecting from a computer that is permitted to connect to your database via that specific account.
After the user is granted access to the database, what he or she can do to the data depends on what permissions have been set for the account. Each account is either allowed or not allowed to perform an operation in your database, such as SELECT, DELETE, INSERT, CREATE, or DROP. The settings that specify what an account can do are privileges, or permissions. You can set up an account with all permissions, no permissions, or anything in between. For instance, for an online product catalog, you want the customer to be able to see the information in the catalog but not be able to change it. When a user attempts to connect to MySQL and execute a query, MySQL controls access to the data in two stages:

Connection verification: MySQL checks the validity of the account name and password and checks whether the connection is coming from a host that is allowed to connect to the MySQL server by using the specified account. If everything checks out, MySQL accepts the connection.
Request verification: After MySQL accepts the connection, it checks whether the account has the necessary permissions to execute the specified query. If it does, MySQL executes the query. Any query that you send to MySQL can fail either because the connection is rejected in the first step or because the query is not permitted in the second step. An error message is returned to help you identify the source of the problem.

SQL injection attack hits hundreds of thousands of web page

2008-04-27T21:01:00.000-07:00

An automated SQL injection attack has affected a large number of web sites - as in hundreds of thousands of pages - resulting in malware being downloaded to visitors' computers.

The attack appears to take advantage of features of Microsoft SQL Server, though according to security vendor F-Secure the problem lies in poor site coding, not in vulnerabilities in Microsoft's software. SQL injection is a well-known technique for attacking sites, and one that professional web developers should know they need to guard against.

Affected sites include some operated by the United Nations and the US, UK and Australian governments or their agencies.

According to F-Secure, the domains hosting the malicious content ultimately delivered by the hack are nmidahena.com, aspder.com and nihaorr1.com.

Some reports have suggested the problem is linked to an unpatched flaw in IIS that surfaced last week, but Microsoft has categorically denied that is the case. "We have also determined that these attacks are in no way related to Microsoft Security Advisory (951306)," wrote communications manager Bill Sisk in the Microsoft Security Response Center blog.

"The attacks are facilitated by SQL injection exploits and are not issues related to IIS 6.0, ASP, ASP.Net or Microsoft SQL technologies," he added.

Removing information

2008-04-27T21:00:00.000-07:00

Keep the information in your database up to date by deleting obsolete information.
You can remove a row from a table with the DELETE query:

DELETE FROM tablename WHERE clause

Be extremely careful when using DELETE. If you use a DELETE query without a WHERE clause, it will delete all the data in the table. I mean all the data. I repeat, all the data. The data cannot be recovered. This function of the DELETE query is right at the top of my don’t-try-this-at-home list.
You can delete a column from a table by using the ALTER query:

ALTER TABLE tablename DROP columnname

Or you could remove the whole thing and start over again with

DROP TABLE tablename
or
DROP DATABASE databasename

Updating information

2008-04-27T20:56:00.000-07:00

Changing information in an existing row is updating the information. For instance, you might need to change the address of a member because she has moved, or you might need to add a fax number that a member left blank when he originally entered his information.
The UPDATE query is straightforward:

UPDATE tablename SET column=value,column=value,...WHERE clause

In the SET clause, you list the columns to be updated and the new values to be inserted. List all the columns that you want to change in one query. Without a WHERE clause, the values of the column(s) would be changed in all rows. But with the WHERE clause, you can specify which rows to update.
For instance, to update an address in the Member table, use this query:

UPDATE Member SET street=”3333 Giant St”,
phone=”555-555-5555”
WHERE loginName=”bigguy”

So You Think You Know SQL?

2008-04-24T08:21:00.000-07:00

I was reading an old post by a buddy of mine about bad SQL being the cause of a lot of application problems and decided to weigh in my two cents.

A lot of developers get forced into writing SQL as part of their jobs. Should they be doing it? I don't think so. It's not necessarily the best of ideas, and in MOST cases should probably be avoided at all costs.

Besides, developers cannot be experts in every language or technology right? Something has got to give somewhere. It's usually their SQL skills that suffer. Developers are, sometimes, forced into situations where they have no choice but to write their own SQL. There is either no DBA, a DBA who isn't interested in helping developers with their queries, or a DBA who isn't even in the development loop (never a good sign). In these cases, developers may have to write their own SQL.

Sometimes developers have to know their limitations when it comes to writing queries, especially complex queries. I don't think a lot of developers do truly know their skill limitations. Yet, these intrepid souls will trudge on thinking they can write SQL just fine. When, in reality, they really and truly do not know the little tricks and tweaks that can make the SQL perform better.

I've seen it a thousand times. It's not an indictment of the developer, just a limitation of their skills that needs to be recognized.

Don't lose hope though, there are ways to combat this and make your SQL as good as it should be, or as good as it can be. What are these answers, then? Personally, I opt for something like Hibernate (Java) or Transfer (ColdFusion) to abstract my SQL for me. What does this mean? It means that my queries will be optimized and I can spend my time focused on developing the business logic rather than spending that time mired in persistence.

It also gives me the agility of being (most of the time) database agnostic. This lets us easily develop in one database locally, and port the same code to the server on a different database with minimal configuration changes. If ORMs are not an option for you and you have access to a good DBA, engage them. Talk to them and mine them for all the information they're worth. It's their job to know the database you're using and how to optimize queries. Make them earn their money by at least helping you write your queries, if not transferring the database functionality to them altogether (good luck).

None of these two options are viable? Well maybe you'll have to engage an outside consultancy to help you with your persistence layer optimizations, or pursue another path for optimizing your queries that has not been mentioned here. The bottom line is this: If you optimize your persistence today, then you'll have to spend less time dealing with it later when it causes you problems tomorrow. You may be able to write SQL, you may be able to do complex joins and create some wicked stored procedures, but are you a SQL expert? If so, then never mind this post. If not, then don't be afraid to swallow your pride and ask for help.

How to Use Join?

2008-04-24T08:14:00.000-07:00

Combining tables side by side is a join. Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if one table has two columns (memberID and height), and the second table has two columns (memberID and weight), a join results in a table with four columns: memberID (from the first table), height, memberID (from the second table), and weight.

The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table. The results table produced by an inner join contains only rows that existed in both tables. The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table. For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight. The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are determined by which table contributes all its rows, requiring the second table to match it. Two kinds of outer joins control which table sets the rows and which match: a LEFT JOIN and a RIGHT JOIN. You use different SELECT queries for an inner join and the two types of outer joins. The following query is an inner join:

SELECT columnnamelist FROM table1,table2
WHERE table1.col2 = table2.col2
And these queries are outer joins:
SELECT columnnamelist FROM table1 LEFT JOIN table2
ON table1.col1=table2.col2
SELECT columnnamelist FROM table1 RIGHT JOIN table2
ON table1.col1=table2.col2

In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names. The two columns must contain the same type of data.

How to Use UNION

2008-04-24T07:59:00.000-07:00

UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:

SELECT query UNION ALL SELECT query ...

You can combine as many SELECT queries as you need. A SELECT query can include any valid SELECT format, including WHERE clauses, LIMIT clauses, and so on. The rules for the queries are

All the select queries must select the same number of columns.
The columns selected in the queries must contain the same type of data.

The result set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the result set are the column names from the first SELECT query. The series of SELECT queries can select different columns from the same table, but situations in which you want a new table with one column in a table followed by another column from the same table are unusual. It’s much more likely that you want to combine columns from different tables. For example, you might have a table of members who have resigned from the club and a separate table of current members. You can get a list of all members, both current and resigned, with the following query:

SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember

The result of this query is the last and first names of all current members, followed by the last and first names of all the members who have resigned. Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.
You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire result set, rather than just to the query that it follows, use parentheses as follows:

(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName

The UNION statement was introduced in MySQL 4.0. It is not available in MySQL 3.

Microsoft to Offer SQL Server 2005 SP3

2008-04-20T06:52:00.000-07:00

Microsoft will release a third service pack for SQL Server 2005, just before the next version of the server software comes out.

Service Pack 3 is expected to come out after the release to manufacturing of SQL Server 2008, which is scheduled to happen in the third quarter this year.

Microsoft didn't reveal much about what the service pack would include, except to say in a Tuesday blog post that it will contain all cumulative updates to the software plus some additional fixes to bugs that customers have reported on MS Connect, a Microsoft Web site for customer feedback.

The development of a third service pack does not change the Incremental Servicing Model that Microsoft introduced last year. The model, unveiled last July, introduced a regular update process to SQL Server 2005. Since then, every two months Microsoft issues a cumulative update for SQL Server 2005 that includes all critical fixes that had been released during that time as well as updates for less urgent issues.

Customers like the predictability of the model, so Microsoft will continue using it, said Francois Ajenstat, director of SQL Server marketing for Microsoft, in a separate blog post. However, users also say that there's a need for a third service pack, so Microsoft plans to release one, he said. The company is announcing its plans now so that customers can plan for deployment, he said.

CodeGear delivers Delphi for PHP 2.0.

2008-04-20T06:49:00.000-07:00

CodeGear, the developer tools arm of Borland Software, has upgraded its tool set for PHP developers.

The company announced Delphi for PHP 2.0 on April 14. Delphi for PHP is an IDE (integrated development environment) for rapidly building interactive Web applications using visual drag-and-drop design capabilities. The product also features a PHP component framework.

To read about the debugger CodeGear contributed to Eclipse, click here.

CodeGear officials said Delphi for PHP 2.0 features several enhancements, including HTML templates with embedded dynamic PHP; database support for MySQL, CodeGear InterBase, Microsoft SQL Server, Oracle, PostgreSQL, IBM Informix and DB2, and Sybase's SQL Anywhere, plus the ability to build rich data-driven Web applications without requiring database connectivity coding; an expanded and faster VCL (Visual Component Library) for PHP with support for popular PHP packages and libraries including the Zend Framework from Zend Technologies; productivity and performance enhancements including new error insight and source code formatting; and new PHP debugging features and integrated PHP performance profiling and tuning.

According to Michael Swindell, CodeGear's vice president of products, Version 2.0 of Delphi for PHP is a major advancement over the initial version, which introduced PHP developers to the productivity of a specialized IDE.

Yet, "perhaps the most significant advance is the new HTML template system, which enables PHP developers to seamlessly build rich PHP applications against HTML templates for design flexibility and easy customization with popular HTML tools," Swindell said.

Delphi for PHP 2.0 is available at an introductory price of $249 through the end of June 2008. Upgrades from Delphi for PHP 1.0 are currently $179.

Combining information from tables

2008-04-20T06:29:00.000-07:00

In previous sections of this chapter, I assume that all the information you want is in a single table. However, you might want to combine information from different tables. You can do this easily in a single query. Two words can be used in a SELECT query to combine information from two or more tables:

UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a single result. For example, if your query selected 6 rows from one table and 5 rows from another table, the result would contain 11 rows.
JOIN: The tables are combined side by side, and the information is retrieved from both tables.

Retrieving data from a specific source

2008-04-20T06:25:00.000-07:00

Frequently, you don’t want all the information from a table. You want information from selected database objects, that is, rows. Three SQL words are frequently used to specify the source of the information:

WHERE: Allows you to request information from database objects with certain characteristics. For instance, you can request the names of members who live in California, or you can list only pets that are cats.
LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request all the information from the first three rows in the table.
DISTINCT: Allows you to request information from only one row of identical rows. For instance, in the Login table, you can request loginName but specify no duplicate names, thus limiting the response to one record for each member. This would answer the question, “Has the member ever logged in?” rather than the question “How many times has the member logged in?”

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss asks for a list of all the members whose last names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number. I’m sure there are many uses for such a list. You can get this list for your boss with a SELECT query by using a WHERE clause.
The basic format of the WHERE clause is

WHERE expression AND|OR expression AND|OR expression ...

expression specifies a value to compare with the values stored in the database. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR. When you use AND, both of the expressions connected by the AND (that is, both the expression before the AND and the expression after the AND) must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected. You can combine any of the expressions with ANDs and ORs. In some cases, you need to use parentheses to clarify the selection criteria. For instance, you can use the following query to answer your boss’s urgent need to find all people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”

AND (phone LIKE “%8%” OR fax LIKE “%8%”) Notice the parentheses in the last line. You would not get the results that your boss asked for without the parentheses. Without the parentheses, each connector would be processed in order from the first to the last, resulting in a list that includes all members whose names begin with B and who live in Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members are selected whose characteristics match the expression before the OR or the expression after the OR. The expression before the OR is connected to previous expressions by the previous ANDs and so does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.

LIMIT specifies how many rows can be returned. The form for LIMIT is
LIMIT startnumber,numberofrows

The first row that you want to retrieve is startnumber, and the number of rows to retrieve is numberofrows. If startnumber is not specified, 1 is assumed. To select only the first three members who live in Texas, use this query:

SELECT * FROM Member WHERE state=”TX” LIMIT 3

Some SELECT queries will find identical records, but in this example you want to see only one — not all — of the identical records. To prevent the query from returning all identical records, add the word DISTINCT immediately after SELECT.

Retrieving data in a specific order

2008-04-20T06:23:00.000-07:00

You might want to retrieve data in a particular order. For instance, in the Member table, you might want members organized in alphabetical order by last name. Or, in the Pet table, you might want the pets grouped by type of pet. In a SELECT query, ORDER BY and GROUP BY affect the order in which the data is delivered to you:

ORDER BY: To sort information, use the phrase ORDER BY columnname The data is sorted by columnname in ascending order. For instance, if columnname is lastName, the data is delivered to you in alphabetical order by the last name. You can sort in descending order by adding the word DESC before the column name. For example: SELECT * FROM Member ORDER BY DESC lastName
GROUP BY: To group information, use the following phrase: GROUP BY columnname The rows that have the same value of columnname are grouped together. For example, use this query to group the rows that have the same value as petType:

SELECT * FROM Pet GROUP BY petType

You can use GROUP BY and ORDER BY in the same query.

Retrieving specific information

2008-04-20T06:19:00.000-07:00

To retrieve specific information, list the columns containing the information you want. For example:

SELECT columnname,columnname,columnname,... FROM tablename

This query retrieves the values from all the rows for the indicated column(s). For instance, the following query retrieves all the last names and first names stored in the Member table:

SELECT lastName,firstName FROM Member

You can perform mathematical operations on columns when you select them.
For example, you can use the following SELECT query to add two columns:

SELECT col1+col2 FROM tablename

Or you could use the following query:

SELECT price,price*1.08 FROM Pet

The result is the price and the price with the sales tax of 8 percent added.
You can change the name of a column when selecting it, as follows:

SELECT price,price*1.08 AS priceWithTax FROM Pet

The AS clause tells MySQL to give the name priceWithTax to the second column retrieved. Thus, the query retrieves two columns of data: price and priceWithTax.
In some cases, you don’t want to see the values in a column, but you want to know something about the column. For instance, you might want to know the lowest value in the column or the highest value in the column. For example, the query to find out the highest price in the Pet table is

SELECT MAX(price) FROM Pet

SQL words that look like MAX() and SUM(), with parentheses following the name, are functions. Some functions provide information about a column. Other functions change each value selected. For example, SQRT() returns the square root of each value in the column, and DAYNAME() returns the name of the day of the week for each value in a date column, rather than the actual date stored in the column. More than 100 functions are available for use in a SELECT query. For descriptions of all the functions, see the MySQL online manual at dev.mysql.com/doc/refman/5.0/en/functions.html.

Retrieving information from database

2008-04-08T15:48:00.000-07:00

The only purpose in storing information is to have it available when you need it. A database lives to answer questions. What pets are for sale? Who are the members? How many members live in Arkansas? Do you have an alligator for sale? How much does a dragon cost? What is Goliath Smith’s phone number? And on and on. You use the SELECT query to ask the database questions.
The simplest, basic SELECT query is

SELECT * FROM tablename

This query retrieves all the information from the table. The asterisk (*) is a wildcard meaning all the columns.
The SELECT query can be much more selective. SQL words and phrases in the SELECT query can pinpoint the information needed to answer your question. You can specify what information you want, how you want it organized, and the source of the information:

You can request only the information (the columns) that you need to answer your question. For instance, you can request only the first and last names to create a list of members.
You can request information in a particular order. For instance, you can request that the information be sorted in alphabetical order.
You can request information from selected objects (the rows) in your table. For instance, you can request the first and last names for only those members whose addresses are in Florida.

In MySQL 4.1, MySQL added the ability to nest a SELECT query inside another query. The nested query is called a subquery. You can use a subquery in SELECT, INSERT, UPDATE, or DELETE queries or in SET clauses. A subquery can return a single value, a single row or column, or a table, which is used in the outer query. All the features of SELECT queries can be used in subqueries. See the MySQL online manual at dev.mysql.com/doc/refman/5.0/en/ subqueries.html for detailed information on using subqueries.

How to adding a bunch of data into database?

2008-04-08T15:13:00.000-07:00

If you have a large amount of data to enter and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL database. The SQL query that reads data from a text file is LOAD. The LOAD query requires you to specify a database.

Because data in a database is organized in rows and columns, the text file being read must indicate where the data for each column begins and ends and where the end of a row is. To indicate columns, a specific character separates the data for each column. By default, MySQL looks for a tab character to separate the fields. However, if a tab doesn’t work for your data file, you can choose a different character to separate the fields and tell MySQL in the query that a different character than the tab separates the fields. Also by default, the end of a line is expected to be the end of a row — although you can choose a character to indicate the end of a line if you need to. A data file for the Pet table might look like this:

UnicornhorseSpiral horn5000.00/pix/unicorn.jpg
PegasushorseWinged8000.00/pix/pegasus.jpg
LioncatLarge; Mane on neck2000.00/pix/lion.jpg

A data file with tabs between the fields is a tab-delimited file. Another common format is a comma-delimited file, where commas separate the fields. If your data is in another file format, you need to convert it into a delimited file. To convert data in another file format into a delimited file, check the manual for that software or talk to your local expert who understands the data’s current format. Many programs, such as Excel, Access, and Oracle, allow you to output the data into a delimited file. For a text file, you might be able to convert it to delimited format by using the search-and-replace function of an editor or word processor. For a truly troublesome file, you might need to seek the help of an expert or a programmer.
The basic form of the LOAD query is

LOAD DATA INFILE “path/datafilename” INTO TABLE tablename

The query loads data from a text file located on your server. If the filename does not include a path, MySQL looks for the data file in the directory where your table definition file, called tablename.frm, is located. By default, this file is located in a directory named for your database, such as a directory named PetDirectory. This directory is located in your data directory, which is located in the main directory where MySQL is installed. For example, if the file was named data.dat, the LOAD command might look for the file at C:\Program Files\MySQL\MySQL Server 5.0\data\PetDirectory\ data.dat.
This basic form can be followed by optional phrases if you want to change a
default delimiter. The options are

FIELDS TERMINATED BY ‘character’
FIELDS ENCLOSED BY ‘character’
LINES TERMINATED BY ‘character’

Suppose that you have the data file for the Pet table, shown previously in this section, except that the fields are separated by a comma rather than a tab. The name of the data file is pets.dat, and it’s located in the same directory as the database. The SQL query to read the data into the table is

LOAD DATA INFILE “pets.dat” INTO TABLE Pet FIELDS TERMINATED BY ‘,’

To use the LOAD DATA INFILE query, the MySQL account must have the FILE privilege on the server host.
You can also load data from a text file on your local computer by using the word LOCAL, as follows:

LOAD DATA LOCAL INFILE “path/datafilename” INTO TABLE tablename

You must include a path to the file. Use forward slashes for the path, even on a Windows computer, such as “C:/data/datafile1.txt”. If you get an error message when sending this query, LOCAL may not be enabled.

To look at the data that you loaded — to make sure that it’s correct — use an SQL query that retrieves data from the database. I describe these types of SQL queries in detail in the next section. In brief, use the following query to look at all the data in the table so that you can check it:

SELECT * FROM Pet

How to add one row at a time to a database?

2008-04-08T15:06:00.000-07:00

You use the INSERT query to add a row to a database. This query tells MySQL which table to add the row to and what the values are for the fields in the row.
The general form of the query is

INSERT INTO tablename (columnname, columnname,...,columnname)
VALUES (value, value,...,value)

The following rules apply to the INSERT query:

Values must be listed in the same order in which the column names are listed. The first value in the value list is inserted into the column that’s named first in the column list; the second value in the value list is inserted into the column that’s named second; and so on.
A partial column list is allowed. You don’t need to list all the columns. Columns that are not listed are given their default value or left blank if no default value is defined.
A column list is not required. If you’re entering values for all the columns, you don’t need to list the columns at all. If no columns are listed, MySQL will look for values for all the columns, in the order in which they appear in the table.
The column list and value list must be the same length. If the list of columns is longer or shorter than the list of values, you get an error message like this: Column count doesn’t match value count.

The following INSERT query adds a row to the Member table:

INSERT INTO Member (loginName,createDate,password,lastName, street,city,state,zip,email,phone,fax)
VALUES (“bigguy”,”2001-Dec-2”,”secret”,”Smith”, “1234 Happy St”,”Las Vegas”,”NV”,”88888”, “gsmith@GSmithCompany.com”,”(555) 555-5555”,””)

Notice that firstName is not listed in the column name list. No value is entered into the firstName field. If firstName were defined as NOT NULL, MySQL would not allow this. Also, if the definition for firstName included a default, the default value would be entered, but because it doesn’t, the field is left empty. Notice that the value stored for fax is an empty string. To look at the data that you entered and ensure that you entered it correctly, use an SQL query that retrieves data from the database. I describe these SQL queries in detail in “Retrieving information,” later in this chapter. In brief, the following query retrieves all the data in the Member table:

SELECT * FROM Member

How to add information?

2008-04-04T02:07:00.000-07:00

Every database needs data. For example, you might want to add data to your database so that your users can look at it. Or you might want to create an empty database for users to put data into, making the data available for your eyes only — an example of this is the Member Directory. In either scenario, data will be added to the database.
If your data is still on paper, you can enter it directly into a MySQL database, one row at a time, by using an SQL query. However, if you have a lot of data, this process could be tedious and involve a lot of typing. Suppose that you have information on 1000 products that must be added to your database. Assuming that you’re greased lightening on a keyboard and can enter a row per minute, that’s 16 hours of rapid typing — well, rapid editing, anyway. Doable, but not fun. On the other hand, suppose that you need to enter 5000 members of an organization into a database and that it takes five minutes to enter each member. Now you’re looking at more than 400 hours of typing — who has time for that?
If you have a large amount of data to enter, consider some alternatives. Sometimes scanning in the data is an option. Or perhaps you need to beg, borrow, or hire some help. In many cases, it could be faster to enter the data into a big text file than to enter each row in a separate SQL query. The SQL query LOAD can read data from a big text file (or even a small text file). So, if your data is already in a computer file, you can work with that file; you don’t need to type all the data again. Even if the data is in a format other than a text file (for example, in an Excel, Access, or Oracle file), you can usually convert the file to a big text file, which can then be read into your MySQL database. If the data isn’t yet in a computer file and there’s a lot of data, it might be faster to enter that data into the computer in a big text file and transfer it into MySQL as a second step.
Most text files can be read into MySQL, but some formats are easier than others. If you’re planning to enter the data into a big text file, read the section, “Adding a bunch of data,” to find the best format. Of course, if the data is already on the computer, you have to work with the file as it is.

How to Move Data Into and Out of the Database?

2008-04-04T02:03:00.000-07:00

An empty database is like an empty cookie jar — it’s not much fun. And searching an empty database is no more interesting or fruitful than searching an empty cookie jar. A database is only useful with respect to the information that it holds.
A database needs to be able to receive information for storage and to deliver information on request. For instance, the MemberDirectory database needs to be able to receive the member information, and it also needs to be able to deliver its stored information when you request it. If you want to know the address of a particular member, for example, the database needs to deliver that information when you request it.
Your MySQL database responds to four types of requests:

Adding information: Adding a row to a table.
Updating information: Changing information in an existing row. This includes adding data to a blank field in an existing row.
Retrieving information: Looking at the data. This request does not remove data from the database.
Removing information: Deleting data from the database.

Sometimes your question requires information from more than one table. For instance, the question, “How much does a green dragon cost?” requires information from the Pet table and from the Color table. You can ask this question easily in a single SELECT query by combining the tables. In the following sections, I discuss how to receive and deliver information as well as how to combine tables.

Changing the database structure

2008-04-02T21:21:00.000-07:00

Your database isn’t written in stone. By using the ALTER query, you can change the name of the table; add, drop, or rename a column; or change the data type or other attributes of the column.
The basic format for this query is ALTER TABLE tablename, followed by the specified changes.

ADD columnname definition Adds a column; definition includes the data type and optional definitions.
ALTER columnname SET DEFAULT value Changes the default value for a column
ALTER columnname DROP DEFAULT Removes the default value for a column.
CHANGE columnname newcolumnname definition Changes the definition of a column and renames the colum, definition includes the data type and optional definitions
DROP columnname Deletes a column, including all the data in the column. The data cannot be recovered.
MODIFY columnname definition Changes the definition of a column; definition includes the data type and optional definitions.
RENAME newtablename Renames a table.

Changing a database is not a rare occurrence. You might want to change your database for many reasons. For example, suppose that you defined the column lastName with VARCHAR(20) in the Member table of the MemberDirectory database. At the time, 20 characters seemed sufficient for a last name. But now you just received a memo announcing the new CEO, John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the first 20 letters, a less-than-desirable new name for the boss. So you need to make the column wider — pronto. Send this query to change the column in a second:
ALTER TABLE Member MODIFY lastName VARCHAR(50)

How to add tables to a database?

2008-03-30T07:12:00.000-07:00

You can add tables to any database, whether it’s a new, empty database that you just created or an existing database that already has tables and data in it. You use the CREATE query to add tables to a database. Because a table is created in a database, you must indicate the database name where you want the table created. That is, when using the form , you must type a database name in the top field. If you don’t, you see the error message No Database Selected.
The query to add a table begins with

CREATE TABLE tablename

Next comes a list of column names with definitions. The information for each column is separated from the information for the next column by a comma. The entire list is enclosed in parentheses. Each column name is followed by its data type and any other definitions required. Here are some definitions that you can use:

NOT NULL: This column must have a value; it can’t be empty.
DEFAULT value: This value is stored in the column when the row is created if no other value is given for this column.
AUTO_INCREMENT: You use this definition to create a sequence number. As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assigning a specific value to the column.
UNSIGNED: You use this definition to indicate that the values for this numeric field will never be negative numbers.

The last item in a CREATE TABLE query indicates which column or combination of columns is the unique identifier for the row — the primary key.
Each row of a table must have a field or a combination of fields that is different for each row. No two rows can have the same primary key. If you attempt to add a row with the same primary key as a row already in the table, you get an error message, and the row is not added. The database design identifies the primary key. You specify the primary key by using the following format:

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL),
PRIMARY KEY(columnname) )

The columnname is enclosed in parentheses. If you’re using a combination of columns as the primary key, include all the column names, separated by commas. For instance, you would designate the primary key for the Login table in the MemberDirectory database by using this in the CREATE query:
PRIMARY KEY (loginName,loginTime)
Listing below shows the CREATE TABLE query used to create the Member table of the MemberDirectory database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. However, the format shown in Listing below makes it easier to read. This human-friendly format also helps you spot typos.

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password CHAR(255) NOT NULL,
lastName VARCHAR(50),
firstName VARCHAR(40),
street VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
zip CHAR(10),
email VARCHAR(50),
phone CHAR(15),
fax CHAR(15),
PRIMARY KEY(loginName) )

Notice that the list of column names in Listing above is enclosed in parentheses (one on the first line and one on the last line), and a comma follows each column definition.
Remember not to use any MySQL reserved words for column names. If you do, MySQL gives you an error message that looks
like this:
You have an error in your SQL syntax near ‘order var(20))’ at line 1
Note that this message shows the column definition that it didn’t like and the line where it found the offending definition. However, the message doesn’t tell you much about what the problem is. The error in your SQL syntax that it refers to is the use of the MySQL reserved word order as a column name. After a table has been created, you can query to see it, review its structure, or remove it.

To see the tables that have been added to a database, use this query: SHOW TABLES
To see the structure of a table, use this query: DESCRIBE tablename
To remove any table, use this query: DROP TABLE tablename

Use DROP carefully because it is irreversible. After a table is dropped, it is gone forever. And any data that was in it is gone as well.

How to delete a database?

2008-03-30T07:00:00.000-07:00

You can delete any database with this SQL query:
DROP DATABASE databasename
Use DROP carefully because it is irreversible. After a database is dropped, it is gone forever. And any data that was in it is gone as well.

How to create a new database?

2008-03-30T06:42:00.000-07:00

To create a new, empty database, use the following SQL query:
CREATE DATABASE databasename where databasename is the name that you give the database. For instance, these two SQL queries create the sample databases used in this book:
CREATE DATABASE PetCatalog
CREATE DATABASE MemberDirectory
Some Web hosting companies don’t allow you to create a new database. You are given one database to use with MySQL, and you can create tables in only this one database. You can try requesting another database, but you need a good reason. MySQL and PHP don’t care that all your tables are in one database instead of organized into databases with meaningful names. It’s just easier for humans to keep track of projects when they’re organized.
To see for yourself that a database was in fact created, use this SQL query:
SHOW DATABASES
After you create an empty database, you can add tables to it.

A quick way to send SQL queries to the MySQL server

2008-03-24T09:04:00.000-07:00

When MySQL is installed, a simple, text-based program called mysql (or sometimes the terminal monitor or the monitor) is also installed. Programs that communicate with servers are client software; because this program communicates with the MySQL server, it’s a client. When you enter SQL queries in this client, the response is returned to the client and displayed onscreen. The monitor program can send queries across a network; it doesn’t have to be running on the machine where the database is stored.
To send SQL queries to MySQL by using the mysql client, follow these steps:

Locate the mysql client. By default, the mysql client program is installed in the subdirectory bin, under the directory where MySQL is installed. In Unix/Linux, the default is /usr/local/mysql/bin or /usr/local/bin. In Windows, the default is c:\Program Files\MySQL\MySQL Server 5.0\bin. However, the client might be installed in a different directory. Or, if you’re not the MySQL administrator, you might not have access to the mysql client. If you don’t know where MySQL is installed or can’t run the client, ask the MySQL administrator to put the client somewhere where you can run it or to give you a copy that you can put on your own computer.
Start the client. In Unix and Linux, type the path/filename (for example, /usr/local/mysql/bin/ mysql). In Windows, open a command prompt window and then type the path\filename (for example, c:\ Program Files\MySQL\MySQL Server 5.0\bin\mysql). This command will start the client if you don’t need to use an account name or a password. If you need to enter an account or a password or both, use the following parameters:

u user: user is your MySQL account name.

p: This parameter prompts you for the password for your MySQL account.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:

mysql -u root -p

If you’re starting the mysql client to access a database across the network, use the following parameter after the mysql command:

h host: host is the name of the machine where MySQL is located.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:
mysql -h mysqlhost.mycompany.com -u root -p
Press Enter after typing the command.

Enter your password when prompted for it. The mysql client starts, and you see something similar to this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 5.0.15 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql>
Select the database that you want to use. At the mysql prompt, type the following: use databasename Use the name of the database that you want to query.
At the mysql prompt, type your SQL query followed by a semicolon (;), and then press the Enter key. The mysql client continues to prompt for input and does not execute the query until you enter a semicolon. The response to the query is displayed onscreen.
To leave the mysql client, type quit at the prompt and then press the Enter key.

Building SQL queries

2008-03-24T08:53:00.000-07:00

SQL (Structured Query Language) is the computer language that you use to communicate with MySQL. SQL is almost English; it is made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work. The first word of each query is its name, which is an action word (a verb) that tells MySQL what you want to do. The queries that I discuss in this chapter are CREATE, DROP, ALTER, SHOW, INSERT, LOAD, SELECT, UPDATE, and DELETE. This basic vocabulary is sufficient to create — and interact with —databases on Web sites.

The query name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.
The following is a typical SQL query. As you can see, it uses English words:

SELECT lastName FROM Member

This query retrieves all the last names stored in the table named Member.
More complicated queries, such as the following, are less English-like:

SELECT lastName,firstName FROM Member WHERE state=”CA” AND city=”Fresno” ORDER BY lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. This query is less English-like but still pretty clear.
Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:

Capitalization: In this book, I put SQL language words in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. I did this to make it easier for you to read — not because MySQL needs this format. The case of the SQL words doesn’t matter; for example, select is the same as SELECT, and from is the same as FROM, as far as MySQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix or Linux. When using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — for example, lastname is not the same as lastName. Windows, however, isn’t as picky as Unix and Linux; from its point of view, lastname and lastName are the same.
Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.
Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are series of characters called text strings, or character strings. You are asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.

Communicating with MySQL

2008-03-24T08:33:00.000-07:00

The MySQL server is the manager of your database:

It creates new databases.
It knows where the databases are stored.
It stores and retrieves information, guided by the requests, or queries, that it receives.

To make a request that MySQL can understand, you build an SQL query and send it to the MySQL server.

Writing the programs

2008-03-19T20:10:00.000-07:00

Your programs perform the tasks for your Web database application. They create the display that the user sees in the browser window. They make your application interactive by accepting and processing information typed in the browser window by the user. They store information in the database and get information out of the database. The database is useless unless you can move data in and out of it.

The plan that you develop outlines the programs that you need to write. In general, each task in your plan calls for a program. If your plan says that your application will display a form, you need a program that displays a form. If your plan says that your application will store the data from a form, you need a program that gets the data from the form and puts it in the database.

The PHP language was developed specifically to write interactive Web applications. It has the built-in functionality needed to make writing application programs as painless as possible. Methods were included in the language specifically to access data from forms, to put data into a MySQL database, and to get data from a MySQL database.

Building the database

2008-03-19T20:07:00.000-07:00

Building the database means turning the paper database design into a working database. Building the database is independent of the PHP programs that your application uses to interact with the database. The database can be accessed using programming languages other than PHP, such as Perl, C, or Java. The database stands on its own to hold the data. You should build the database before writing the PHP programs. The PHP programs are written to move data in and out of the database, so you can’t develop and test them until the database is available. The database design names the database and defines the tables that make up the database. To build the database, you communicate with MySQL by using the SQL language. You tell MySQL to create the database and to add tables to the database. You tell MySQL how to organize the data tables and what format to use to store the data.

Date, time and enumeration data

2008-03-19T20:03:00.000-07:00

A third common type of data is date and time data. Data stored as a date can be displayed in a variety of date formats. It can also be used to determine the length of time between two dates or two times — or between a specific date or time and some arbitrary date or time.

Sometimes data can have only a limited number of values. For example, the only possible values for a column might be yes or no. MySQL provides a data type called enumeration for use with this type of data. You tell MySQL what values can be stored in the column (for example, yes, no), and MySQL will not store any other values in the column.

What is numerical data?

2008-03-14T20:00:00.000-07:00

Another common type of data is numerical data — data that is stored as a number. Decimal numbers (for example, 10.5, 2.34567, 23456.7) can be stored as well as integers (for example, 1, 2, 248). When data is stored as a number, it can be used in numerical operations, such as adding, subtracting, and squaring. If data isn’t used for numerical operations, however, storing it as a character string is better because the programmer will be using it as a character string. No conversion is required. For example, you probably won’t want to add the digits in the users’ phone numbers, so phone numbers should be stored as character strings.

MySQL stores positive and negative numbers, but you can tell MySQL to store only positive numbers. If your data is never negative, store the data as unsigned (without using a + or – sign before the number). For example, a city population or the number of pages in a document can never be negative. MySQL provides a specific type of numeric column called an auto-increment column. This type of column is automatically filled with a sequential number when no specific number is provided. For example, when a table row is added with 5 in the auto-increment column, the next row is automatically assigned 6 in the column, unless a different number is specified. Auto-increment columns are useful when unique numbers are needed, such as a product number or an order number.

What is character data?

2008-03-14T19:57:00.000-07:00

The most common type of data is character data — data that is stored as strings of characters and can be manipulated only in strings. Most of the information that you store will be character data, such as customer name, address, phone, and pet description. Character data can be moved and printed. Two character strings can be put together (concatenated), a substring can be selected from a longer string, and one string can be substituted for another. Character data can be stored in a fixed-length or variable-length format.

Fixed-length format: In this format, MySQL reserves a fixed space for the data. If the data is longer than the fixed length, only the characters that fit are stored — the remaining characters on the end are not stored. If the string is shorter than the fixed length, the extra spaces are left empty and wasted.
Variable-length format: In this format, MySQL stores the string in a field that is the same length as the string. You specify a string length, but if the string is shorter than the specified length, MySQL uses only the space required rather than leaving the extra space empty. If the string is longer than the space specified, the extra characters are not stored.

If a character string length varies only a little, use the fixed-length format. For example, a length of 10 works for all zip codes, including those with the zip+4 number. If the zip code does not include the zip+4 number, only five spaces are left empty. However, if your character string can vary more than a few characters, use a variable-length format to save space. For example, your pet description might be Small bat or might run to several lines of description. It would be better to store this description in a variable-length format.

how to design member registration process for your web site?

2008-03-14T19:48:00.000-07:00

You create the following list of information that you want to store when customers register for the Members Only section of your Web site:

Member name
Member address
Member phone number
Member fax number
Member e-mail address

In addition, you would like to collect the date when the member registers and track how often the member goes into the Members Only section. You design the Members Only database by following the steps presented in the “Organizing data in tables” section, earlier:

Name your database. The database for the Members Only section may be named MemberDirectory or other.
Identify the objects. The information list is

Member name

Member address

Member phone number

Member fax number

Member e-mail address

Member registration date

Member logins

All this information pertains to members, so the only object for this list is member.

Define and name a table for each object. The MemberDirectory database needs a table called Member.
Identify the attributes for each object. Look at the information list in detail:

Member name: Two attributes: first name and last name.

Member address: Four attributes: street address, city, state, and zip code. Currently, you have pet stores only in the United States, so you can assume that the member address is an address in the U.S. mailing address format.

Member phone number: One attribute.

Member fax number: One attribute.

Member e-mail address: One attribute.

Member registration date: One attribute.

Several pieces of information are related to member logins:

Logging in to the Members Only section requires a login name and a password. These two items need to be stored in the database.

The easiest way to keep track of member logins is to store the date and time when the user logged into the Members Only section. Because each member can have many logins, many dates and times for logins need to be stored. Therefore, rather than defining the login time as an attribute of the member, define login as an object, related to the member, but requiring its own table.

The added table is named Login. The attribute of a login object is its login time (the time includes the date).

Define and name the columns. The Member table has one row for each member. The columns for the

Member table are

loginName

password

createDate

firstName

lastName

street
city
state
zip
email
phone
fax

The Login table has one row for each login: that is, each time a member logs into the Members Only section. It has the following columns:

loginName: The login name of the member who logged in. This is the column that links this table to the Member table. This value is unique in the Member table but not unique in this table.

loginTime: The date and time of login.

Identify the primary key.

The primary key for the Member table is loginName. Therefore, loginName must be unique.

The primary key for the Login table is both loginName and loginTime together.

Define the defaults. No defaults are defined for either table.
Identify columns with required data. The following columns should never be allowed to be empty:

loginName

password

loginTime

These columns are the primary key columns. A row without these values should never be allowed in the tables.

How to create relationships between tables?

2008-03-11T22:50:00.000-07:00

Some tables in a database are related. Most often, a row in one table is related to several rows in another table. A column is needed to connect the related rows in different tables. In many cases, you include a column in one table to hold data that matches data in the primary key column of another table.

A common application that needs a database with two related tables is a customer order application. For example, one table contains the customer information, such as name, address, and phone number. Each customer can have from zero to many orders. You could store the order information in the table with the customer information, but a new row would be created each time that the customer placed an order, and each new row would contain all the customer’s information. It would be much more efficient to store the orders in a separate table, named perhaps CustomerOrder. (You can’t name the table Order because that is a reserved word.) The CustomerOrder table would have a column that contains the primary key from a row in the Customer table so that the order is related to the correct row of the Customer table.

How to organize data in tables?

2008-03-11T22:22:00.000-07:00

RDBMS tables are organized like other tables that you’re used to — in rows and columns. The place where a particular row and column intersect, the individual cell, is a field.
The focus of each table is an object (a thing) that you want to store information about. Here are some examples of objects:

Customers Shapes Rooms
Companies Projects Computers
Cities Products Documents
Books Animals Weeks

You create a table for each object. The table name should clearly identify the objects that it contains with a descriptive word or term. The name must be a character string, containing letters, numbers, underscores, or dollar signs, with no spaces in it. It’s customary to name the table in the singular. Thus, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder. Uppercase and lowercase is significant on Linux and Unix but not on Windows: CustomerOrder and Customerorder are the same to Windows — but not to Linux or Unix.

In database talk, an object is an entity, and an entity has attributes. In the table, each row represents an entity, and the columns contain the attributes of each entity. For example, in a table of customers, each row contains information for a single customer. Some of the attributes contained in the columns might be first name, last name, phone number, and age.
Here are the steps for organizing your data into tables:

Name your database. Assign a name to the database for your application. For instance, a database containing information about households in a neighborhood might be named HouseholdDirectory.
Identify the objects. Look at the list of information that you want to store in the database (as discussed in the section, “Choosing the data,” earlier in this chapter). Analyze your list and identify the objects. For instance, the HouseholdDirectory database might need to store the following:

Name of each family member

Address of the house

Phone number

Age of each household member

Favorite breakfast cereal of each household member

When you analyze this list carefully, you realize that you’re storing information about two objects: the household and the household members. That is, the address and phone number are for the household in general, but the name, age, and favorite cereal are for a particular household member.
Define and name a table for each object. For instance, the HouseholdDirectory database needs a table called Household and a table called HouseholdMember.
Identify the attributes for each object. Analyze your information list and identify the attributes you need to store for each object. Break the information to be stored into its smallest reasonable pieces. For example, when storing the name of a person in a table, you can break the name into first name and last name. Doing this enables you to sort by the last name, which would be more difficult if the first and last name were stored together. You can even break down the name into first name, middle name, and last name, although not many applications need to use the middle name separately.
Define and name columns for each separate attribute that you identified in Step 4. Give each column a name that clearly identifies the information in that column. The column names should be one word, with no spaces. For example, you might have columns named firstName and lastName or first_name and last_name.
Some words are reserved by MySQL and SQL for their own use and can’t be used as column names. The words are currently used in SQL statements or are reserved for future use. For example, ADD, ALL, AND, CREATE, DROP, GROUP, ORDER, RETURN, SELECT, SET, TABLE, USE, WHERE, and many, many more can’t be used as column names. For a complete list of reserved words, see the online MySQL manual at www.mysql.com/doc/en/Reserved_words.html.
Identify the primary key. Each row in a table needs a unique identifier. No two rows in a table should be exactly the same. When you design your table, you decide which column holds the unique identifier, called the primary key. The primary key can be more than one column combined. In many cases, your object attributes will not have a unique identifier. For example, a customer table might not have a unique identifier because two customers can have the same name. When there is no unique identifier column, you need to add a column specifically to be the primary key. Frequently, a column with a sequence number is used for this purpose.
Define the defaults. You can define a default that MySQL will assign to a field when no data is entered into the field. A default is not required but is often useful. For example, if your application stores an address that includes a country, you can specify US as the default. If the user does not type a country, US will be entered.
Identify columns that require data. You can specify that certain columns are not allowed to be empty (also called NULL). For instance, the column containing your primary key can’t be empty. That means that MySQL will not create the row and will return an error message if no value is stored in the column. The value can be a blank space or an empty string (for example, “”), but some value must be stored in the column. Other columns, in addition to the primary key, can be set to require data.

Well-designed databases store each piece of information in only one place. Storing it in more than one place is inefficient and creates problems if information needs to be changed. If you change information in one place but forget to change it in another place, your database can have serious problems. If you find that you’re storing the same data in several rows, you probably need to reorganize your tables. For example, suppose you’re storing data about books, including the publisher’s address. When you enter the data, you realize that you’re entering the same publisher’s address in many rows.

A more efficient way to store this data would be to store the book information in one table and the book publisher information in a separate table. You can define two tables: Book and BookPublisher. In the Book table, you would have the columns title, author, pub_date, and price. In the BookPublisher table, you would have columns such as name, streetAddress, and city.

Data choosing tips when building database

2008-03-06T17:16:00.000-08:00

First, you must identify what information belongs in your database. Look at the list of tasks that you want the application to perform and determine what information you need to complete each of those tasks.
Here are a few examples:

An online catalog needs a database containing product information. _ An online order application needs a database that can hold customer information and order information.
A travel Web site needs a database with information on destinations, reservations, fares, schedules, and so on.

In many cases, your application might include a task that collects information from the user. You’ll have to balance your urge to collect all the potentially useful information that you can think of against your users’ reluctance to give out personal information — as well as their avoidance of forms that look too time-consuming. One compromise is to ask for some optional information. Users who don’t mind can enter it, but users who object can leave it blank. Another possibility is to offer an incentive: The longer the form, the stronger the incentive that you’ll need to motivate the user to fill out the form. A user might be willing to fill out a short form to enter a sweepstakes that offers two sneak-preview movie tickets for a prize. But if the form is long and complicated, the prize needs to be more valuable, such as a free trip to California and a tour of a Hollywood movie studio.

In the first example application, your customers search the online catalog for information on pets that they might want to buy. You want customers to see information that will motivate them to buy a pet. The information that you want to have available in the database for the customer to see is as follows:

The name of the pet (for example, poodle or unicorn)
A description of the pet
A picture of the pet
The cost of the pet

In the second example application, the Members Only section, you want to store information about registered members. The information that you want to store in the database is as follows:

Member name
Member address
Member phone number
Member fax number
Member e-mail address

Take the time to develop a comprehensive list of the information you need to store in your database. Although you can change and add information to your database after it’s developed, including the information from the beginning is easier. Also, if you add information to the database later — after it’s in use —the first users in the database will have incomplete information. For example, if you change your form so that it now asks for the user’s age, you won’t have the age for the people who have already filled out the form and are already in the database.

Writing it down

2008-03-06T17:15:00.000-08:00

Write your plan down. You will hear this often from me. I speak from the painful experience of not writing it down. When you develop your plan, it’s foremost in your mind and perfectly clear. But in a few short weeks, you will be astonished to discover that it has gone absolutely hazy while your attention was on other pressing issues. Or you want to make some changes in the application a year from now and won’t remember exactly how the application was designed. Or you’re working with a partner to develop an application and you discover that your partner misunderstood your verbal explanation and developed functions for the application that don’t fit in your plan. You can avoid these types of problems by writing everything down.

Leaving room for expansion

2008-03-06T17:14:00.000-08:00

One certainty about your Web application is that it will change over time. Down the line, you might think of new functions for it or just simply want to change something about it. Or maybe Web site software improves so that your Web application can do things that it couldn’t do when you first put it up. Whatever the reason, your Web site will change. When you plan your application, you need to keep future changes in mind.

You can design your application in steps, taking planned changes into account. You can develop a plan in which you build an application today that meets your most immediate needs and make it available as soon as it’s ready. Your plan can include adding functions to the application as quickly as you can develop them. For example, you can build a product catalog and publish it on your Web site as soon as it’s ready. You can then begin work on an online ordering function for the Web site, which you will add when it’s ready.

You can’t necessarily foresee all the functions that you might want in your application. For instance, you might design your travel Web site with sections for all possible destinations today, but the future could surprise you. Trips to Mars? Alpha Centauri? An alternate universe? Plan your application with the flexibility needed to add functionality in the future.

How to make the site easy to use?

2008-03-02T07:28:00.000-08:00

In addition to planning what your Web application is going to do, you need to consider how it is going to do it. Making your application easy to use is important: If customers can’t find your products, they aren’t going to buy them. And if customers can’t find the information they need in a short time, they will look elsewhere. On the Web, customers can easily go elsewhere. Making your application easy to use is usability engineering. Web usability includes such issues as

Navigation: What is on your site and where it is located should be immediately obvious to a user.
Graphics: Graphics make your site attractive, but graphic files can be slow to display.
Access: Some design decisions can make your application accessible or not accessible to users who have disabilities such as impaired vision.
Browsers: Different browsers (even different versions of the same browser) can display the same HTML file differently.

Web usability is a large and important subject, and delving into the topic more deeply is beyond the scope of this book. But fear not, you can find lots of helpful information on Web usability on — you guessed it — the Web. Be sure to check out the Web sites of usability experts Jakob Nielsen (www.useit.com) and Jared Spool (www.uie.com). Vincent Flanders also has a fun site full of helpful information about Web design at WebPagesThatSuck.com.

Taking the user into consideration when planning web application

2008-03-02T07:25:00.000-08:00

Identifying what you want your Web database application to do is only one aspect of planning. You must also consider what your users will want from it. For example, say your goal is to gather a list of names and addresses for marketing purposes. Will customers be willing to give up that information? Your application needs to fulfill a purpose for the users as well as for yourself. Otherwise, they’ll just ignore it. Before users will be willing to give you their names and addresses, for example, they need to perceive that they will benefit from giving you this information. Here are a few examples of why users might be willing to register their names and addresses at your site:

To receive a newsletter: To be perceived as valuable, the newsletter should cover an industry related to your products. It should offer news and spot trends — and not just serve as marketing material about your products.
To enter a sweepstakes for a nice prize: Who can turn down a chance to win an all-expense-paid vacation to Hawaii or a brand-new SUV?
To receive special discounts: For example, you can periodically e-mail special discount opportunities to customers.
To be notified about new products or product upgrades when they become available: For example, customers might be interested in being notified when a software update is available for downloading.
To get access to valuable information: For instance, you must register at The New York Times Web site to gain access to its articles online.

Now add the customer tasks to your list of tasks that you want the application to perform. For example, consider this list of tasks that you identified for setting up an online retailer:

Provide a form for customers to fill out
Store the customer information in a database

If you take the customer’s viewpoint into account, the list expands a bit:

Present a description of the advantages customers receive by registering with the site
Provide a form for customers to fill out
Add customers’ e-mail addresses to the newsletter distribution list
Store the customer information in a database

After you have a list of tasks that you want and tasks that your users want, you have a plan for a Web application that is worth your time to develop and worth your users’ time to use.

How to Identify what you want from the application?

2008-03-02T07:04:00.000-08:00

The first step in the planning phase is to identify exactly why you’re developing your application and what you want from it. For example, your main purpose might be to

Collect names and addresses from users so that you can develop a customer list
Deliver information about your products to users, as in a customer catalog
Sell products online
Provide technical support to people who already own your product

After you clearly identify the general purpose of your application, make a list of exactly what you want that application to do. For instance, if your goal is to develop a database of customer names and addresses for marketing purposes, the application’s list of required tasks is fairly short:

Provide a form for customers to fill out
Store the customer information in a database

If your goal is to sell products online, the list is a little longer:

Provide information about your products to the customer
Motivate the customer to buy the product
Provide a way for the customer to order the product online
Provide a method for the customer to pay for the product online
Validate the payment so you know that you’ll actually get the money
Send the order to the person responsible for filling the order and sending the product to the customer.

At this point in the planning process, the tasks that you want your application to perform are still pretty general. You can accomplish each of these tasks in many different ways. So now you need to examine the tasks closely and detail exactly how the application will accomplish them. For instance, if your goal is to sell products online, you might expand the preceding list like this:

Provide information about products to the customer.

Display a list of product categories. Each category is a link.

When the customer clicks a category link, the list of products in that category is displayed. Each product name is a link.

When a customer clicks a product link, the description of the product is displayed.

Motivate the customer to buy the product.

Provide well-written descriptions of the products that communicate their obviously superior qualities.

Use flattering pictures of the products.

Make color product brochures available online.

Offer quantity discounts.

Provide a way for customers to order the product online.

Provide a button that customers can click to indicate their intention to buy the product.

Provide a form that collects necessary information about the product the customer is ordering, such as size and color.

Provide forms for customers to enter shipping and billing addresses.

Compute and display the total cost for all items in the order.

Compute and display the shipping costs.

Compute and display the sales tax.

Provide a method for customers to pay for the product online.

Provide a button that customers can click to pay with a credit card.

Display a form that collects customers’ credit card information.

Validate the payment so you know that you’ll actually get the money. The usual method is to send the customer’s credit card information to a credit card processing service.
Send the order to the person responsible for filling the order and sending the product to the customer.

E-mailing order information to the shipping department should do it. At this point, you should have a fairly clear idea of what you want from your Web database application. However, this doesn’t mean that your goals can’t change. In fact, your goals are likely to change as you develop your Web database application and discover new possibilities. At the onset of the project, start with as comprehensive a plan as possible to keep you focused.

Planning Your Web Database Application

2008-02-26T00:59:00.000-08:00

Before you ever put finger to keyboard to write a PHP program, you need to plan your Web database application. This is possibly the most important step in developing your application. It’s painful to discover, especially just after you finish the last program for your application, that you left something out and have to start over from the beginning. It’s also hard on your computer (and your foot) when you take out your frustrations by drop-kicking it across the room.
Good planning prevents such painful backtracking. In addition, it keeps you focused on the functionality of your application, thus preventing you from writing pieces for the application that do really cool things but turn out to have no real purpose in the finished application. And if more than one person is working on your application, planning ensures that all the pieces will fit together in the end.

Testing whether mySQL work

2008-02-25T17:43:00.000-08:00

After you know that PHP is running okay, you can test whether you can access MySQL by using PHP. Just follow these steps:

Create the following file somewhere in your Web space with the name mysql_up.php. You can download the file from http://mihd.net/i5pmy9
The following lines 9, 10, and 11 of the program need to be changed:

$host=”host”;
$user=”mysqlaccount”;
$password=”mysqlpassword”;

Change host to the name of the computer where MySQL is installed —for example, databasehost.mycompany.com. If the MySQL database is on the same computer as your Web site, you can use localhost as the hostname.
Change mysqlaccountname and mysqlpassword to the appropriate values. An account named root is installed when MySQL is installed, which may or may not have a password. If your MySQL account doesn’t require a password, type nothing between the quotes, as follows:

$password=””;

Point your browser at mysql_up.php. You should see a table with a long list of variable names and values. You don’t want to see an error message or a warning message. Don’t worry about the contents of the table. It’s only important that the table is displayed so that you know your connection to MySQL is working correctly. If no error or warning messages are displayed, MySQL is working fine. If you see an error or a warning message, you need to fix the problem that’s causing the message.

The following is a common error message:

MySQL Connection Failed: Access denied for user:
‘user73@localhost’ (Using password: YES)

This message means that MySQL did not accept your MySQL account number or your MySQL password. Notice that the message reads YES for Using password but doesn’t show the actual password that you tried for security reasons. If you tried with a blank password, the message would read NO. If you receive an error message, double-check your account number and password. Remember that this is your MySQL account number — not your account number to log on to the computer. If you can’t connect with the account number and password that you have, you might need to contact the IT department or the Web hosting company that gave you the account number.

How to test whether PHP is working?

2008-02-25T16:51:00.000-08:00

To test whether PHP is installed and working, follow these steps:

Find the directory in which your PHP programs need to be saved. This directory and the subdirectories under it are your Web space. Apache calls this directory the document root. The default Web space for Apache is htdocs in the directory where Apache is installed. For IIS, it’s Inetpub\wwwroot. In Linux, it might be /var/www/html. The Web space can be set to a different directory by configuring the Web server (see Appendix C). If you’re using a Web hosting company, the staff will supply the directory name.
Create the following file somewhere in your Web space with the name test.php. Download the PHP file in http://mihd.net/4edokm

The file must be saved in your Web space for the Web server to find it.

Point your browser at the test.php file created in Step 1. That is, type the name of your Web server into the browser address window, followed by the name of the file (for example, www.myfinecompany.com/test.php). If your Web server, PHP, and the test.php file are on the same computer that you’re testing from, you can type localhost/test.hp. For the file to be processed by PHP, you need to access the file through the Web server — not by choosing FileOpen from your Web browser menu.

You should see the following in the Web browser:

This is an HTML line
This is a PHP line

Below these lines, you should see a large table that shows all the information associated with PHP on your system. It shows PHP information, pathnames and filenames, variable values, and the status of various options. The table is produced by the phpinfo() line in the test script. Anytime that you have a question about the settings for PHP, you can use the phpinfo() statement to display this table and check a setting.

Check the PHP values for the settings you need. For instance, you need MySQL support enabled. Looking through the listing, find the section for MySQL and make sure that MySQL support is On. PHP has many settings that can be changed. Various PHP settings are discussed throughout the book in the appropriate sections.
Change values if necessary. The general settings for PHP are stored in a file named php.ini. If you installed PHP yourself, you can edit php.ini and change settings. If your Web site is located on a company computer or a Web hosting company computer, you may not have access to php.ini to change settings. You can request the PHP administrator to change settings. For some settings, you can temporarily change a setting with a statement in your PHP program, but not all settings can be changed in a program.

Understanding PHP/MySQL functions

2008-02-22T07:56:00.000-08:00

PHP can communicate with any version of MySQL. However, PHP needs to be installed differently, depending on which version of MySQL you’re using. PHP provides one set of functions (mysql functions) that communicate with MySQL 4.0 or earlier and a different set of functions (mysqli functions) that communicate with MySQL 4.1 or later.

The mysql functions, which communicate with earlier versions of MySQL, can also communicate with the later versions of MySQL, but you may not be able to use some of the newer, advanced features that were added to MySQL in the later versions. The mysqli functions, which can take advantage of all the MySQL features, are available only with PHP 5 or later.

The programs in this blog, including the test programs in this section, use MySQL 5.0 and the mysqli functions. If you’re using PHP 4, you need to change the programs to use the mysql functions, rather than the mysqli functions. The functions are similar, but some have slight changes in syntax. Versions of the programs that will run with PHP 4 are available for download at my Web site (janet.valade.com).
You might see an error message similar to the following:

Fatal error: Call to undefined function mysql_connect()

The message means that you’re using a mysql function in your program, but the mysql functions are not enabled. MySQL support might not be enabled at all or mysqli support might be enabled instead of mysql support.

Testing Preparation

2008-02-22T07:54:00.000-08:00

Suppose you believe that PHP and MySQL are available for you to use, for one of the following reasons:

The IT department at your company or your client company gave you all the information that you asked for and told you that you’re good to go.
The Web hosting company gave you all the information that you need and told you that you’re good to go.
You followed all the instructions and installed PHP and MySQL yourself.

Now you need to test to make sure that PHP and MySQL are working correctly.

Installing PHP

2008-02-22T07:52:00.000-08:00

After you install MySQL, you’re ready to install PHP. As I mention earlier, you must install MySQL before you install PHP because you may need to provide the path to the MySQL software when you install PHP. If PHP isn’t compiled with MySQL support when it is installed, it won’t communicate with MySQL.

Before you install PHP, check whether it’s already installed. For instance, some Linux and Mac distributions automatically install PHP. To see whether PHP is installed, search your disk for any PHP files:

_ Linux/Unix/Mac: Type the following: find / -name “php*”
_ Windows: Use the Find feature (choose StartFind) to search for php*.

If you find PHP files, PHP is already installed, and you might not need to reinstall it. For instance, even if you installed MySQL yourself after PHP was installed, you might have installed it in the location where PHP is expecting it. Better safe than sorry, however: Perform the testing that I describe in the next section to see whether MySQL and PHP are working correctly together.

If you don’t find any PHP files, PHP is not installed. To install PHP, you need access to the Web server for your site. For instance, when you install PHP with Apache, you need to edit the Apache configuration file. All the information and software that you need is provided on the PHP Web site (www.php.net).

Installing MySQL

2008-02-17T19:19:00.000-08:00

After setting up the computer and installing the Web server, you’re ready to install MySQL. You need to install MySQL before installing PHP because you may need to provide the path to the MySQL software when you install PHP.

But before installing MySQL, be sure that you actually need to install it. It might already be running on your computer, or it might be installed but not running. For instance, many Linux distributions automatically install MySQL.
Here’s how to check whether MySQL is currently running:

Linux/Unix/Mac: At the command line, type the following: ps –ax ,The output should be a list of programs. Some operating systems (usually flavors of Unix) have different options for the ps command. If the preceding does not produce a list of programs that are running, type man ps to see which options you need to use. In the list of programs that appears, look for one called mysqld.
Windows: If MySQL is running, it will be running as a service. To check this, choose StartControl PanelAdministrative ToolsServices and scroll down the alphabetical list of services. If MySQL is installed as a service, it will appear in the list. If it’s currently running, its status displays Started.

Even if MySQL isn’t currently running, it might be installed but just not started.
Here’s how to check to see whether MySQL is installed on your computer:

Linux/Unix/Mac: Type the following: find / -name “mysql*” If a directory named mysql is found, MySQL has been installed.
Windows: If you did not find MySQL in the list of current services, look for a MySQL directory or files. You can search at StartSearch. The default installation directory is C:\Program Files\MySQL\MySQL Server version number for recent versions or C:\mysql for older versions. If you found MySQL in the service list, as described, but it is not started, you can start it by highlighting MySQL in the service list and clicking Start the Service in the left panel.

If you find MySQL on your computer but did not find it in the list of running programs (Linux/Unix/Mac) or the list of current services (Windows), here’s how to start it:

Linux/Unix/Mac:

Change to the directory mysql/bin. This is the directory that you should have found when you were checking whether MySQL was installed.

Type safe_mysqld & When this command finishes, the prompt is displayed.

Check that the MySQL server started by typing ps -ax. In the list of programs that appears, look for one called mysqld.

Windows:

Open a Command Prompt window. In Windows XP, choose Start>All ProgramsAccessories Command Prompt.

Change to the folder where MySQL is installed. For example, type cd C:\Program Files\MySQL\MySQL Server 5.0. Your cursor is now located in the MySQL folder.

Change to the bin subfolder by typing cd bin. Your cursor is now located in the bin subfolder.

Start the MySQL Server by typing mysqld—install.

The MySQL server starts as a Windows service. You can check the installation by going to the service list, as described previously, and making sure that MySQL now appears in the service list and its status is Started.

If MySQL isn’t installed on your computer, you need to download it and install it from www.mysql.com. The Web site provides all the information and software that you need.

Installing the Web server

2008-02-17T19:16:00.000-08:00

After you set up the computer, you need to decide which Web server to install.
The answer is almost always Apache. Apache offers the following advantages:

It’s free. What else do I need to say?
It runs on a variety of operating systems. Apache runs on Windows, Linux, Mac OS, FreeBSD, and most varieties of Unix.
It’s popular. Approximately 60 percent of Web sites on the Internet use Apache, according to surveys at news.netcraft.com/archives/web_ server_survey.html and www.securityspace.com/s_survey/ data/. This wouldn’t be true if it didn’t work well. Also, this means that a large group of users can provide help.
It’s reliable. After Apache is up and running, it should run as long as your computer runs. Emergency problems with Apache are rare.
It’s customizable. The open source license allows programmers to modify the Apache software, adding or modifying modules as needed to fit their own environment.
It’s secure. Free software is available that runs with Apache to make it into an SSL (Secure Sockets Layer) server. Security is an essential issue if you’re using the site for e-commerce.

Apache is automatically installed when you install most Linux distributions. All recent Macs come with Apache installed. For most other Unix flavors, you have to download the Apache source code and compile it yourself, although some binaries (programs that are already compiled for specific operating systems) are available. For Windows, Apache provides an installer that asks you questions and installs and configures Apache for you. For a public Web site, you should run Windows NT/2000/XP, although Apache also runs on Windows 95/98/Me.

(The Apache Web site (httpd.apache.org) provides information, software downloads, extensive documentation that is improving all the time, and installation instructions for various operating systems. Other Web servers are available. Microsoft offers IIS (Internet Information Server), which is the second most popular Web server on the Internet with approximately 27 percent of Web sites. Sun offers a Web server, which serves less than 3 percent of the Internet. Other Web servers are available, but they have even smaller user bases.

A little bit about domain names

2008-02-17T19:15:00.000-08:00

Every Web site needs a unique address on the Web. The unique address used by computers to locate a Web site is the IP address, which is a series of four numbers between 0 and 255, separated by dots — for example, 172.17.204.2 or 192.163.2.33.

Because IP addresses are made up of numbers and dots, they’re not easy to remember. Fortunately, most IP addresses have an associated name that’s much easier to remember, such as amazon.com, www.irs.gov, or mycompany.com. A name that’s an address for a Web site is a domain name. A domain can be one computer or many connected computers. When a domain refers to several computers, each computer in the domain can have its own name. A name that includes an individual computer name, such as thor.mycompany.com, identifies a subdomain.

Each domain name must be unique in order to serve as an address. Consequently, a system of registering domain names ensures that no two locations use the same domain name. Anyone can register any domain name as long as the name isn’t already taken. You can register a domain name on the Web. First, you test your potential domain name to find out whether it’s available. If it’s available, you register it in your name or a company name and pay the fee. The name is then yours to use, and no one else can use it. The standard fee for domain name registration is $35 per year.

You should never pay more, but bargains are often available. Many Web sites provide the ability to register a domain name, including the Web sites of many Web hosting companies. A search at Google (www.google.com) for register domain name results in more than 85 million hits. Shop around to be sure that you find the lowest price. Also, many Web sites allow you to enter a domain name and see whom it is registered to. These Web sites do a domain name database search using a tool called whois. A search at Google for domain name whois results in more than 17 million hits. A couple of places where you can do a whois search are Allwhois.com (www.allwhois.com) and BetterWhois.com (www.betterwhois.com).

Setting up the computer

2008-02-13T07:01:00.000-08:00

Your first decision is to choose which hardware platform and operating system to use. In most cases, you’ll choose a PC with either Linux or Windows as the operating system. Here are some advantages and disadvantages of these two operating systems:

Linux: Linux is open source, so it’s free. It also has advantages for use as a Web server: It runs for long periods without needing to be rebooted; and Apache, the most popular Web server, runs better on Linux than Windows. Running Linux on a PC is the lowest cost option. The disadvantage of running Linux is that many people find Linux more difficult to install, configure, administer, and install software on than Windows, although Linux is getting easier to install every day.
Windows: Unlike Linux, Windows is not free. However, most people feel that Windows is easier to use, and because it’s widely used, many people can help you if you have problems. If you plan a public Web site, with users accessing your Web site from the WWW, you need Windows 2000 or later.

I assume that you’re buying a computer with the operating system and software installed, ready to use. It’s easier to find a computer that comes with Windows installed on it than with Linux, but Linux computers are available. For instance, at this time, Dell, IBM, and Hewlett-Packard offer computers with Linux installed.

If you’re building your own hardware, you need more information than I have room to provide in this book. If you have the hardware and plan to install an operating system, Windows is easier to install, but Linux is getting easier all the time. You can install Linux from a CD, like Windows, but you often must provide information or make decisions that require more knowledge about your system. If you already know how to perform system administration tasks (such as installing software and making backups) in Windows or in Linux, the fastest solution is to use the operating system that you already know.

For using PHP and MySQL, you should seriously consider Linux. PHP is a project of the Apache Software Foundation, so it runs best with the Apache server. And Apache runs better on Linux than on Windows. Therefore, if all other things are equal and the computer is mainly for running a Web site with a Web database application, Linux is well suited for your purposes.
Other solutions besides a PC with Windows or Linux are available, but they’re less popular:

Unix-based: Other free, Unix-based operating systems are available for PCs, such as FreeBSD (which some people prefer to Linux) or a version of Solaris provided by Sun for free download.
Mac: Mac computers can be used as Web servers. Most newer Macs come with PHP installed. Installing PHP and MySQL on Mac OS X is fairly simple. There are fewer Mac users, however, so it can be difficult to find help when you need it. One good site is www.phpmac.com.

Your computer must be connected to the Internet. In most cases, you obtain an account from an Internet service provider (ISP). When you obtain an account from the ISP, be sure to discuss the type of use you intend. A simple user connection to the Internet is sufficient to transfer Web page files from your development computer to a Web hosting company. However, if you plan for users to access your Web site from the WWW, you need an Internet connection with more resources.

Setting up and running a Web site on your local computer

2008-02-13T06:59:00.000-08:00

If you’re starting a Web site from scratch, you need to understand the Web site software fairly well. You have to make several decisions regarding hardware and software. You have to install a Web server, PHP, and MySQL — as well as maintain, administer, and update the system yourself. Taking this route, rather than using a Web site provided by others, requires more work and more knowledge. The advantage is that you have total control over the Web development environment.

You may want to set up a Web site on your personal computer to be a public Web site, accessed from the World Wide Web. Or you may want to set up a Web site on your personal computer where you can develop your Web site before transferring the Web page files to your work computer or a Web hosting company. These two types of use require different Internet connections, as described in Step 1 next.

Here are the general steps that lead to your dynamic Web site (I explain these steps in more detail in the next few sections):

Set up the computer.
Install the Web server.
Install MySQL.
Install PHP.

If you’re starting from scratch, with nothing but an empty space where the computer will go, start at Step 1. If you already have a running computer but no Web software, start at Step 2. Or if you have an existing Web site that does not have PHP and MySQL installed, start with Step 3.

How to pick a web hosting?

2008-02-13T06:53:00.000-08:00

A Web hosting company provides everything that you need to put up a Web site, including the computer space and all the Web site software. You just create the files for your Web pages and move them to a location specified by the Web hosting company.

About a gazillion companies offer Web hosting services. Most charge a monthly fee (often quite small), and some are even free. (Most, but not all, of the free ones require you to display advertising.) Usually, the monthly fee varies depending on the resources provided for your Web site. For instance, a Web site with 25Mb of disk space for your Web page files costs less than a Web site with 100Mb of disk space.
When looking for a place to host your Web site, make sure that the Web hosting company offers the following:

PHP and MySQL: Not all companies provide these tools. You might have to pay more for a site with access to PHP and MySQL; sometimes you have to pay an additional fee for MySQL databases.
A recent version of PHP: Sometimes the PHP versions offered aren’t the most recent versions. As of this writing, PHP 6 is close to being released. However, you may have trouble finding a Web hosting company that offers PHP 6. In fact, you may find that most Web hosting companies still offer PHP 4, although I hope that will change over time. It is worth the time to find a Web hosting company that offers at least PHP 5, if not PHP 6. Some Web hosting companies offer PHP 4 but have PHP 5 or 6 available for customers who request it.

Other considerations when choosing a Web hosting company are

Reliability: You need a Web hosting company that you can depend on —one that won’t go broke and disappear tomorrow, and one that isn’t running on old computers, held together by chewing gum and baling wire, with more downtime than uptime.
Speed: Web pages that download slowly are a problem because users will get impatient and go elsewhere. Slow pages could be a result of a Web hosting company that started its business on a shoestring and has a shortage of good equipment — or the Web hosting company might be so successful that its equipment is overwhelmed by new customers. Either way, Web hosting companies that deliver Web pages too slowly are unacceptable.
Technical support: Some Web hosting companies have no one available to answer questions or troubleshoot problems. Technical support is often provided only through e-mail, which can be acceptable if the response time is short. Sometimes you can test the quality of the company’s support by calling the tech support number, or test the e-mail response time by sending an e-mail.

The domain name: Each Web site has a domain name that Web browsers use to find the site on the Web. Each domain name is registered for a small yearly fee so that only one Web site can use it. Some Web hosting companies allow you to use a domain name that you have registered independently of the Web hosting company, some assist you in registering and using a new domain name, and some require that you use their domain name. For instance, suppose that your name is Lola Designer and you want your Web site to be named LolaDesigner. Some Web hosting companies will allow your Web site to be LolaDesigner.com, but some will require that your Web site be named LolaDesigner.webhostingcompanyname.com, or webhostingcompanyname.com/ ~LolaDesigner, or something similar. In general, your Web site will look more professional if you use your own domain name.
Backups: Backups are copies of your Web page files and your database that are stored in case your files or database are lost or damaged. You want to be sure that the company makes regular, frequent backup copies of your application. You also want to know how long it would take for backups to be put in place to restore your Web site to working order after a problem.
Features: Select features based on the purpose of your Web site. Usually a hosting company bundles features together into plans — more features equal a higher cost. Some features to consider are

Disk space: How many MB or GB of disk space will your Web site require? Media files, such as graphics or music files, can be quite large.

Data transfer: Some hosting companies charge you for sending Web pages to users. If you expect to have a lot of traffic on your Web site, this cost should be a consideration.

E-mail addresses: Many hosting companies provide you with a number of e-mail addresses for your Web site. For instance, if your Web site is LolaDesigner.com, you could allow users to send you e-mail at me@LolaDesigner.com.

Software: Hosting companies offer access to a variety of software for Web development. PHP and MySQL are the software that I discuss in this book. Some hosting companies might offer other databases, and some might offer other development tools such as FrontPage extensions, shopping cart software, and credit card validation.

Statistics: Often you can get statistics regarding your Web traffic, such as the number of users, time of access, access by Web page, and so on.

One disadvantage of hosting your site with a commercial Web hosting company is that you have no control over your development environment. The Web hosting company provides the environment that works best for it — probably setting up the environment for ease of maintenance, low cost, and minimal customer defections. Most of your environment is set by the company, and you can’t change it. You can only beg the company to change it.

The company will be reluctant to change a working setup, fearing that a change could cause problems for the company’s system or for other customers. Access to MySQL databases is controlled via a system of accounts and passwords that must be maintained manually, thus causing extra work for the hosting company. For this reason, many hosting companies either don’t offer MySQL or charge extra for it. Also, PHP has myriad options that can be set, unset, or given various values.

The hosting company decides the option settings based on its needs, which might or might not be ideal for your purposes. It’s pretty difficult to research Web hosting companies from a standing start —a search at Google.com for “Web hosting” results in almost 400 million hits.

The best way to research Web hosting companies is to ask for recommendations from people who have experience with those companies. People who have used a hosting company can warn you if the service is slow or the computers are down often. After you gather a few names of Web hosting companies from satisfied customers, you can narrow the list to the one that is best suited to your purposes and the most cost effective.

A company Web site

2008-02-09T06:16:00.000-08:00

When the Web site is run by the company, you don’t need to understand the installation and administration of the Web site software at all. The company is responsible for the operation of the Web site. In most cases, the Web site already exists, and your job is to add to, modify, or redesign the existing Web site. In a few cases, the company might be installing its first Web site, and your job is to design the Web site. In either case, your responsibility is to write and install the HTML files for the Web site. You are not responsible for the operation of the Web site.

You access the Web site software through the company’s IT department. The name of this department can vary in different companies, but its function is the same: It keeps the company’s computers running and up to date. If PHP or MySQL or both aren’t available on the company’s Web site, IT needs to install them and make them available to you.

PHP and MySQL have many options, but IT might not understand the best options — and might have options set in ways that aren’t well suited for your purposes. If you need PHP or MySQL options changed, you need to request that IT make the change; you won’t be able to make the change yourself. For instance, PHP must be installed with MySQL support enabled, so if PHP isn’t communicating correctly with MySQL, IT might have to reinstall PHP with MySQL support enabled. For the world to see the company’s Web pages, the HTML files must be in a specific location on the computer. The Web server that delivers the Web pages to the world expects to find the HTML files in a specific directory.

The IT department should provide you with access to the directory where the HTML files need to be installed. In most cases, you develop and test your Web pages in a test location and then transfer the completed files to their permanent home. Depending on the access that IT gives you, you might copy the files from the test location to the permanent location, or you might transfer the files via FTP (File Transfer Protocol), which is a method of copying a file from one computer to another on a network.

In some cases, for security reasons, the IT folks won’t give you access to the permanent location, preferring to install the files in their permanent location themselves.

To use the Web software tools and build your dynamic Web site, you need the following information from IT:

The location of Web pages: You need to know where to put the files for the Web pages. IT needs to provide you with the name and location of the directory where the files should be installed. Also, you need to know how to install the files — copy them, FTP them, or use other methods. You might need a user ID and password to install the files.
The default filename: When users point their browsers at a URL, a file is sent to them. The Web server is set up to send a file with a specific name when the URL points to a directory. The file that is automatically sent is the default file. Very often the default file is named index.htm or index. html, but sometimes other names are used, such as default.htm. Ask IT what you should name your default file.
A MySQL account: Access to MySQL databases is controlled through a system of account names and passwords. IT sets up a MySQL account for you that has the appropriate permissions and also gives you the MySQL account name and password.
The location of the MySQL databases: MySQL databases need not be located on the same computer as the Web site. If the MySQL databases are located on a computer other than that of the Web site, you need to know the hostname (for example, thor.companyname.com) where the databases can be found.
The PHP file extension: When PHP is installed, the Web server is instructed to expect PHP statements in files with specific extensions. Frequently, the extensions used are .php or .phtml, but other extensions can be used. PHP statements in files that don’t have the correct extension won’t be processed. Ask IT what extension to use for your PHP programs.

You will interact with the IT folks frequently as needs arise. For example, you might need options changed, you might need information to help you interpret an error message, or you might need to report a problem with the Web site software. So a good relationship with the IT folks will make your life much easier. Bring them tasty cookies and doughnuts often.

Finding a Place to Work

2008-02-09T06:15:00.000-08:00

To create your dynamic Web pages, you need access to a Web site that provides your three software tools (see the preceding section). All Web sites include a Web server, but not all Web sites provide MySQL and PHP. These are the most common environments in which you can develop your Web site:

A Web site put up by a company on its own computer: The company — usually the company’s IT (Information Technology) department — installs and administers the Web site software. Your job, for the purposes of this book, is to program the Web site, either as an employee of the company or as a contractor.
A Web site hosted by a Web hosting company: The Web site is located on the Web hosting company’s computer. The Web hosting company installs and maintains the Web site software and provides space on its computer where you can install the HTML (HyperText Markup Language) files for a Web site.
A Web site that doesn’t yet exist: You plan to install and maintain the Web site software yourself. It could be a Web site of your own that you’re building on your own computer, or it might be a Web site that you’re installing for a client on the client’s computer. How much you need to understand about the administration and operation of the Web site software depends on the type of Web site access you have. In the next few sections, I describe these environments in more detail and explain how you gain access to PHP and MySQL.

The Required Tools

2008-02-09T06:11:00.000-08:00

To put up your dynamic Web site, you need to have access to the following three software tools:

A Web server: The software that delivers your Web pages to the world
MySQL: The RDBMS (Relational Database Management System) that will store information for your Web database application
PHP: The scripting language that you’ll use to write the programs that provide the dynamic functionality for your Web site

Keeping Up with PHP and MySQL Changes

2008-02-04T00:52:00.000-08:00

PHP and MySQL are open source software. If you’ve used only software from major software publishers — such as Microsoft, Macromedia, or Adobe — you’ll find that open source software is an entirely different species. It’s developed by a group of programmers who write the code in their spare time, for fun and for free. There’s no corporate office.

Open source software changes frequently, rather than once every year or two like commercial software does. It changes when the developers feel that it’s ready. It also changes quickly in response to problems. When a serious problem is found — such as a security hole — a new version that fixes the problem can be released in days. You don’t receive glossy brochures or see splashy magazine ads for a year before a new version is released. Thus, if you don’t make the effort to stay informed, you could miss the release of a new version or be unaware of a serious problem with your current version. Visit the PHP and MySQL Web sites often.

You need to know the information that’s published there. Join the mailing lists, which often are high in traffic. When you first get acquainted with PHP and MySQL, the large number of mail messages on the discussion lists brings valuable information into your e-mail box; you can pick up a lot by reading those messages. And soon, you might be able to help others based on your own experience. At the very least, subscribe to the announcement mailing list, which delivers e-mail only occasionally. Any important problems or new versions are announced here.

The e-mail that you receive from the announcement list contains information you need to know. So, right now, before you forget, hop over to the PHP and MySQL Web sites and sign up for a list or two at www.php.net/mailing-lists. php and lists.mysql.com.
You should be aware of some significant changes in previous PHP versions because existing scripts that work fine on earlier versions could have problems when they’re run on a later version and vice versa.

How MySQL and PHP work together

2008-02-04T00:50:00.000-08:00

PHP provides the application part, and MySQL provides the database part of a Web database application. You use the PHP language to write the programs that perform the application tasks. PHP can be used for simple tasks (such as displaying a Web page) or for complicated tasks (such as accepting and verifying data that a user typed into an HTML form). One of the tasks that your application must do is move data into and out of the database — and PHP has built-in features to use when writing programs that move data into and out of a MySQL database.

PHP statements are embedded in your HTML files with PHP tags. When the task to be performed by the application requires storing or retrieving data, you use specific PHP statements designed to interact with a MySQL database. You use one PHP statement to connect to the correct database, telling PHP where the database is located, its name, and the password needed to connect to it. The database doesn’t need to be on the same machine as your Web site;

PHP can communicate with a database across a network. You use another PHP statement to send an SQL message to MySQL, giving MySQL instructions for the task you want to accomplish. MySQL returns a status message that shows whether it successfully performed the task. If there was a problem, it returns an error message. If your SQL message asked to retrieve some data, MySQL sends the data that you asked for, and PHP stores it in a temporary location where it is available to you.

You then use one or more PHP statements to complete the application task.
For instance, you can use PHP statements to display data that you retrieved. Or you might use PHP statements to display a status message in the browser, informing the user that the data was saved.

As an RDBMS, MySQL can store complex information. As a scripting language, PHP can perform complicated manipulations of data, on either data that you need to modify before saving it in the database or data that you retrieved from the database and need to modify before displaying or using it for another task. Together, PHP and MySQL can be used to build a sophisticated and complicated Web database application.

Advantages of the relationship

2008-02-04T00:41:00.000-08:00

MySQL and PHP as a pair have several advantages:

They’re free. It’s hard to beat free for cost-effectiveness.
They’re Web oriented. Both were designed specifically for use on Web sites. Both have a set of features focused on building dynamic Web sites.
They’re easy to use. Both were designed to get a Web site up quickly.
They’re fast. Both were designed with speed as a major goal. Together they provide one of the fastest ways to deliver dynamic Web pages to users.
They communicate well with one another. PHP has built-in features for communicating with MySQL. You don’t need to know the technical details; just leave it to PHP.
A wide base of support is available for both. Both have large user bases. Because they are often used as a pair, they often have the same user base. Many people are available to help, including those on e-mail discussion lists who have experience using MySQL and PHP together.
They’re customizable. Both are open source, thus allowing programmers to modify the PHP and MySQL software to fit their own specific environments.

How PHP works

2008-01-31T00:37:00.000-08:00

PHP is an embedded scripting language when used in Web pages. This means that PHP code is embedded in HTML code. You use HTML tags to enclose the PHP language that you embed in your HTML file — the same way that you would use other HTML tags. You create and edit Web pages containing PHP the same way that you create and edit regular HTML pages. The PHP software works with the Web server. The Web server is the software that delivers Web pages to the world. When you type a URL into your Web browser, you’re sending a message to the Web server at that URL, asking it to send you an HTML file. The Web server responds by sending the requested file. Your browser reads the HTML file and displays the Web page. You also request the Web server to send you a file when you click a link in a Web page. In addition, the Web server processes a file when you click a Web page button that submits a form.
When PHP is installed, the Web server is configured to expect certain file extensions to contain PHP language statements. Often the extension is .php or .phtml, but any extension can be used. When the Web server gets a request for a file with the designated extension, it sends the HTML statements as-is, but PHP statements are processed by the PHP software before they’re sent to the requester.
When PHP language statements are processed, only the output is sent by the Web server to the Web browser. The PHP language statements are not included in the output sent to the browser, so the PHP code is secure and transparent to the user. For instance, in this simple PHP statement:

Hello World”; ?>

is the closing tag. echo is a PHP instruction that tells PHP to output the upcoming text. The PHP software processes the PHP statement and outputs this:

Hello World

which is a regular HTML statement. This HTML statement is delivered to the user’s browser. The browser interprets the statement as HTML code and displays a Web page with one paragraph — Hello World. The PHP statement is not delivered to the browser, so the user never sees any PHP statements. PHP and the Web server must work closely together. PHP is not integrated with all Web servers but does work with many of the popular Web servers. PHP is developed as a project of the Apache Software Foundation — thus, it works best with Apache. PHP also works with Microsoft IIS/PWS, iPlanet (formerly Netscape Enterprise Server), and others. Although PHP works with several Web servers, it works best with Apache. If you can select or influence the selection of the Web server used in your organization, select Apache. By itself, Apache is a good choice. It is free, open source, stable, and popular. It currently powers more than 60 percent of all Web sites, according to the Web server survey at www.netcraft.com. It runs on Windows, Linux, Mac OS, and most flavors of Unix.

Advantages of PHP

2008-01-31T00:34:00.000-08:00

The popularity of PHP is growing rapidly because of its many advantages:

It’s fast. Because it is embedded in HTML code, the response time is short.
It’s inexpensive — free, in fact. PHP is proof that free lunches do exist and that you can get more than you paid for.
It’s easy to use. PHP contains many special features and functions needed to create dynamic Web pages. The PHP language is designed to be included easily in an HTML file.
It can run on many operating systems. It runs on a variety of operating systems — Windows, Linux, Mac OS, and most varieties of Unix.
Technical support is widely available. A large base of users provides free support through e-mail discussion lists.
It’s secure. The user does not see the PHP code.
It’s designed to support databases. PHP includes functionality designed to interact with specific databases. It relieves you of the need to know the technical details required to communicate with a database.
It’s customizable. The open source license allows programmers to modify the PHP software, adding or modifying features as needed to fit their own specific environments.

PHP, a Data Mover

2008-01-31T00:30:00.000-08:00

PHP, a scripting language designed specifically for use on the Web, is your tool for creating dynamic Web pages. Rich in features that make Web design and programming easier, PHP is in use on more than 20 million domains (according to the Netcraft survey at www.php.net/usage.php). Its popularity continues to grow, so it must be fulfilling its function pretty well. PHP stands for PHP: HyperText Preprocessor. In its early development by a guy named Rasmus Lerdorf, it was called Personal Home Page tools. When it developed into a full-blown language, the name was changed to be more in line with its expanded functionality.
The PHP language’s syntax is similar to the syntax of C, so if you have experience with C, you’ll be comfortable with PHP. PHP is actually simpler than C because it doesn’t use some of the more difficult concepts of C. PHP also doesn’t include the low-level programming capabilities of C because PHP is designed to program Web sites and doesn’t require those capabilities. PHP is particularly strong in its ability to interact with databases. PHP supports pretty much every database you’ve ever heard of (and some you haven’t). PHP handles connecting to the database and communicating with it. You don’t need to know the technical details for connecting to a database or for exchanging messages with it. You tell PHP the name of the database and where it is, and PHP handles the details. It connects to the database, passes your instructions to the database, and returns the database response to you. Technical support is available for PHP. You can join one of several e-mail discussion lists offered on the PHP Web site (www.php.net), including a list for databases and PHP. In addition, a Web interface to the discussion lists is available at news.php.net, where you can browse or search the messages.

Communicating with the MySQL server

2008-01-28T03:34:00.000-08:00

All your interaction with the database is accomplished by passing messages to the MySQL server. You can send messages to the MySQL server several ways, but this book focuses on sending messages using PHP. The PHP software has specific statements that you use to send instructions to the MySQL server.
The MySQL server must be able to understand the instructions that you send it. You communicate by using SQL (Structured Query Language), which is a standard language understood by many RDBMSs. The MySQL server understands SQL. PHP doesn’t understand SQL, but it doesn’t need to: PHP just establishes a connection with the MySQL server and sends the SQL message over the connection. The MySQL server interprets the SQL message and follows the instructions. The MySQL server sends a return message, stating its status and what it did (or reporting an error if it was unable to understand or follow the instructions).

How MySQL works?

2008-01-28T03:33:00.000-08:00

The MySQL software consists of the MySQL server, several utility programs that assist in the administration of MySQL databases, and some supporting software that the MySQL server needs (but you don’t need to know about). The heart of the system is the MySQL server. The MySQL server is the manager of the database system. It handles all your database instructions. For instance, if you want to create a new database, you send a message to the MySQL server that says “create a new database and call it newdata.” The MySQL server then creates a subdirectory in its data directory, names the new subdirectory newdata, and puts the necessary files with the required format into the newdata subdirectory. In the same manner, to add data to that database, you send a message to the MySQL server, giving it the data and telling it where you want the data to be added. You find out how to write and send messages to MySQL in Part II. Before you can pass instructions to the MySQL server, it must be running and waiting for requests. The MySQL server is usually set up so that it starts when the computer starts and continues running all the time. This is the usual setup for a Web site. However, it’s not necessary to set it up to start when the computer starts. If you need to, you can start it manually whenever you want to access a database. When it’s running, the MySQL server listens continuously for messages that are directed to it.

Advantages of MySQL

2008-01-28T03:30:00.000-08:00

MySQL is a popular database with Web developers. Its speed and small size make it ideal for a Web site. Add to that the fact that it’s open source, which means free, and you have the foundation of its popularity. Here is a rundown of some of its advantages:

It’s fast. The main goal of the folks who developed MySQL was speed. Thus, the software was designed from the beginning with speed in mind.
It’s inexpensive. MySQL is free under the open source GPL license, and the fee for a commercial license is reasonable.
It’s easy to use. You can build and interact with a MySQL database by using a few simple statements in the SQL language, which is the standard language for communicating with RDBMSs. Check out Chapter 4 for the lowdown on the SQL language.
It can run on many operating systems. MySQL runs on many operating systems — Windows, Linux, Mac OS, most varieties of Unix (including Solaris and AIX), FreeBSD, OS/2, Irix, and others.
Technical support is widely available. A large base of users provides free support through mailing lists. The MySQL developers also participate in the e-mail lists. You can also purchase technical support from MySQL AB for a small fee.
It’s secure. MySQL’s flexible system of authorization allows some or all database privileges (such as the privilege to create a database or delete data) to specific users or groups of users. Passwords are encrypted.
It supports large databases. MySQL handles databases up to 50 million rows or more. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
It’s customizable. The open source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

MySQL, My Database

2008-01-28T03:26:00.000-08:00

MySQL is a fast, easy-to-use RDBMS used on many Web sites. Speed was the developers’ main focus from the beginning. In the interest of speed, they made the decision to offer fewer features than their major competitors (such as Oracle and Sybase). However, even though MySQL is less full-featured than its commercial competitors, it has all the features needed by the majority of database developers. It’s easier to install and use than its commercial competitors, and the difference in price is strongly in MySQL’s favor. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. The company licenses it in two ways:

Open source software: MySQL is available through the GNU GPL (General Public License). MySQL provides two versions of the open source software: MySQL Community Edition: A freely downloadable, open source edition of MySQL, released early and often with the most advanced features. Anyone who can meet the requirements of the GPL can use the software for free. If you’re using MySQL as a database on a Web site (the subject of this book), you can use MySQL for free, even if you’re making money with your Web site. MySQL Network: An enterprise-grade set of software and services available for a monthly subscription fee. MySQL Network provides certified software, thoroughly tested and optimized. Services include technical support, regular updates, access to a knowledge base of hundreds of technical articles, and other services useful to a large business. The subscription is available at four levels, from the Basic level, with a limit of two incidents, no phone support, and a two-day response time, to Platinum support, with unlimited incidents, 24/7 phone support, and a 30-minute response time.
Commercial license: MySQL is available with a commercial license for those who prefer it to the GPL. If a developer wants to use MySQL as part of a new software product and wants to sell the new product rather than release it under the GPL, the developer needs to purchase a commercial license.

Finding technical support for MySQL Community Edition is not a problem. You can join one of several e-mail discussion lists offered on the MySQL Web site at www.mysql.com. You can even search the e-mail list archives, which contain a large archive of MySQL questions and answers.

E-mail discussion lists

2008-01-28T03:25:00.000-08:00

Good technical support is available from e-mail discussion lists, which are groups of people discussing specific topics through e-mail. E-mail lists are available for pretty much any subject you can think of: Powerball, ancient philosophy, cooking, the Beatles, Scottish terriers, politics, and so on. The list manager maintains a distribution list of e-mail addresses for anyone who wants to join the discussion. When you send a message to the discussion list, your message is sent to the entire list so that everyone can see it. Thus, the discussion is a group effort, and anyone can respond to any message that interests him or her. E-mail discussion lists are supported by various sponsors. Any individual or organization can run a list. Most software vendors run one or more lists devoted to their software. Universities run many lists for educational subjects. In addition, some Web sites manage discussion lists, such as Yahoo! Groups and Topica. Users can create a new list or join an existing list through the Web application.
Software-related e-mail lists are a treasure trove of technical support. Anywhere from a hundred to several thousand users of the software subscribe to the list. Often the developers, programmers, and technical support staff for the software vendor are on the list. You are unlikely to be the first person to ever experience your problem. Whatever your question or problem, someone on the list probably knows the answer or the solution. When you post a question to an e-mail list, the answer usually appears in your inbox within minutes. In addition, most lists maintain an archive of previous discussions so that you can search for answers. When you’re new to any software, you can find out a great deal simply by joining the discussion list and reading the messages for a few days. PHP and MySQL have e-mail discussion lists. Actually, each has several discussion lists for special topics, such as databases and PHP. You can find the names of the mailing lists and instructions for joining them on the PHP and MySQL Web sites.

The database

2008-01-28T03:24:00.000-08:00

The core of a Web database application is the database, which is the longterm memory (I hope more efficient than my long-term memory) that stores information for the application. A database is an electronic file cabinet that stores information in an organized manner so that you can find it when you need it. After all, storing information is pointless if you can’t find it. A database can be small, with a simple structure — for example, a database containing the titles and authors’ names of all the books that you own. Or a database can be huge, with an extremely complex structure — such as the database that Amazon.com has to hold all its information. The information that you store in the database comes in many varieties. A company’s online catalog requires a database to store information about all the company’s products. A membership Web site requires a database to store information about members. An employment Web site requires a database (or perhaps two databases) to store information about job openings and information from résumés. The information that you plan to store could be similar to information that’s stored by Web sites all over the Internet — or information that’s unique to your application.
Technically, the term database refers to the file or group of files that holds the actual data. The data is accessed by using a set of programs called a DBMS (Database Management System). Almost all DBMSs these days are RDBMSs (Relational Database Management Systems), in which data is organized and stored in a set of related tables.
In this book, MySQL is the RDBMS used because it is particularly well suited for Web sites. MySQL and its advantages are discussed in the section, “MySQL, My Database,” later in this chapter.

What Is a Web Database Application?

2008-01-28T03:21:00.000-08:00

An application is a program or a group of programs designed for use by an end user (for example, customers, members, or circus acrobats). If the end user interacts with the application via a Web browser, the application is a Web based or Web application. If the Web application requires the long-term storage of information using a database, it is a Web database application. This book provides you with the information that you need to develop a Web database application that can be accessed with Web browsers such as Internet Explorer and Netscape.
A Web database application is designed to help a user accomplish a task. It can be a simple application that displays information in a browser window (for example, current job openings when the user selects a job title) or a complicated program with extended functionality (for example, the book ordering application at Amazon.com or the bidding application at eBay).
A Web database application consists of just two pieces:

Database: The database is the long-term memory of your Web database application. The application can’t fulfill its purpose without the database. However, the database alone is not enough.
Application: The application piece is the program or group of programs that performs the tasks. Programs create the display that the user sees in the browser window; they make your application interactive by accepting and processing information that the user types in the browser window; and they store information in the database and get information out of the database. (The database is useless unless you can move data in and out.)

The Web pages that you’ve previously created with HTML alone are static, meaning the user can’t interact with the Web page. All users see the same Web page. Dynamic Web pages, on the other hand, allow the user to interact with the Web page. Different users might see different Web pages. For instance, one user looking at a furniture store’s online product catalog might choose to view information about the sofas, whereas another user might choose to view information about coffee tables. To create dynamic Web pages, you must use another language in addition to HTML.
One language widely used to make Web pages dynamic is JavaScript. JavaScript is useful for several purposes, such as mouse-overs (for example, to highlight a navigation button when the user moves the mouse pointer over it) or accepting and validating information that users type into a Web form. However, it’s not useful for interacting with a database. You wouldn’t use JavaScript to move the information from the Web form into a database. PHP, however, is a language particularly well suited to interacting with databases. PHP can accept and validate the information that users type into a Web form and can also move the information into a database. The programs in this book are written with PHP.

Introduction

2008-01-28T03:18:00.000-08:00

So you need to develop an interactive Web site. Perhaps your boss just put you in charge of the company’s online product catalog. Or you want to develop your own Web business. Or your sister wants to sell her paintings online. Or you volunteered to put up a Web site open only to members of your circus acrobats’ association. Whatever your motivation might be, you can see that the application needs to store information (such as information about products or member passwords), thus requiring a database. You can see also that the application needs to interact dynamically with the user; for instance, the user selects a product to view or enters membership information. This type of Web site is a Web database application. I assume that you’ve created static Web pages before, using HTML (HyperText Markup Language), but creating an interactive Web site is a new challenge, as is designing a database. You asked three computer gurus you know what you should do. They said a lot of things you didn’t understand, but among the technical jargon, you heard “quick” and “easy” and “free” mentioned in the same sentence as PHP and MySQL. Now you want to know more about using PHP and MySQL to develop the Web site that you need. PHP and MySQL work together very well; it’s a dynamic partnership. In this chapter, you find out the advantages of each, how each one works, and how they work together to produce a dynamic Web database application.