Planning Your Web Database Application

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

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?

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

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

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

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

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.