Accessing SQL from the Web using Forms, PHP and MySQL

Updated 7-Jan-2006

Here are the free packages to install for a Web Database Interface

Apache Web Server - Apache-devel
mysql_server - mysql-devel - mysql-client - mysql-client-shared-libraries MySQL 5.0 Docs
The MySQL 5.0.x HTML docs as a 6MB zip file

Tests

Apache installs OK and runs the default home page and PHP script.
mySQL lets you change the root password and create a database and add data
You can write a web form that does a little perl or a little PHP
You grant access to the Read Only account for all your public data.
You log into mySQL from a 2nd computer and access a public table.
You connect to MySQL from a web PHP form and send data from this same table back to the browser.

Case Study

The county administrator comes to your office and says that they want to stop putting real estate transactions in the paper.

Your job, if you decide to accept, is to put it on the net!!!

Design the table
Design the web form LIVE DEMO!!!
Write the PHP interface
Load the first data.
Set up the security

Extras

1. Backup your Data
Stop the mysqld daemon. Copy the important files to a separate location and archive them off site. Important files are (for RH FC3):

/var/lib/mysql/mysql/*.* --- (system files - drop dead important)
/var/lib/mysql/yourdb/*.* --- (yourdb = any user data on your Server)

Restart the daemon.
Write down their exact location.
Keep the root password in a sealed envelop in a safe place.
2. Restoring after a crash or panic attack
Stop the mysqld daemon. Copy the important files BACK to where they belong (Remember, you wrote down the exact location). Set the proper ownership and permissions (chown and chmod).
```
 
-rw-rw----  1 mysql mysql 9148 Jun 13 10:55 user.frm
-rw-rw----  1 mysql mysql 2354 Jun 14 12:34 user.MYD
-rw-rw----  1 mysql mysql 2048 Jun 14 12:34 user.MYI
```
Restart the daemon.
3. Recover your forgotten/lost root password Create a new instance of the mysql database on the server or a 2nd Linux machine. Create one user account named root@localhost and give it a good password. Stop the daemon on this 2nd instance. Follow the instructions for #2 with user.frm, user.MYD and user.MYI. Restart the daemon.

4. Quicker way to recover your forgotten/lost root password

     $ su - root
     # /etc/rc.d/init.d/network stop
     # #  limit access to your SQL server
     # /etc/rc.d/init.d/mysqld stop
     # safe_mysqld --skip-grant-tables &
     # # be quick, EVERYONE has full access to your MySQL server 
     # # when you use the skip-grant-tables parameter
     # # That is why I turned off the network.
     # mysql -u root mysql
     mysql> use mysql
     mysql> UPDATE user SET password=PASSWORD('XXXXX') WHERE user = 'root';
     mysql> FLUSH PRIVILEGES;
     mysql> EXIT;
     # mysqladmin -u root  -p shutdown
     # /etc/rc.d/init.d/network start
     # /etc/rc.d/init.d/mysqld start

5. Web Server Mngt. Tricks

Access Race Results from the 2000-2007 Charlottesville 10 Miler. LIVE DEMO - updated April 2005

Access the Race Results and Real Estate Sales Demo using a PHP library with full source code called Query Maker. LIVE DEMO - updated April 2005

MySQL Control Center is a great GPL Multi-Platform Tool for Querying, Browsing and Changing MySQL databases:
Get it here.

Suggestions for a database data dictionary

Create a table with this structure:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| column_name | varchar(80)  | YES  |     | NULL    |       |
| table_name  | varchar(40)  | YES  |     | NULL    |       |
| type_name   | varchar(4)   | YES  |     | NULL    |       |
| value_name  | varchar(10)  | YES  |     | NULL    |       |
| label_name  | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

 create table datadict(
        column_name varchar(80),
        table_name varchar(40),
        type_name varchar(4),
        value_name  varchar(10),
        label_name  varchar(100));


Populate it with code values from all the tables in your database. Ex-

insert into datadict values( "glasses", "health", "Code", "*", "No Answer");
insert into datadict values( "glasses", "health", "Code", "0", "None");
insert into datadict values( "glasses", "health", "Code", "1", "Reading");
insert into datadict values( "glasses", "health", "Code", "2", "Distance");
insert into datadict values( "glasses", "health", "Code", "3", "Bifocals");
insert into datadict values( "glasses", "health", "Code", "4", "Variable Focus");

For the RACE Table example:

+-------------+------------+-----------+------------+------------+
| column_name | table_name | type_name | value_name | label_name |
+-------------+------------+-----------+------------+------------+
| sex         | race       | K         | M          | Male       |
| sex         | race       | K         | F          | Female     |
+-------------+------------+-----------+------------+------------+
2 rows in set (0.00 sec)

mysql> insert into datadict values( "sex", "race", "K", "X", "Middlesex");
Query OK, 1 row affected (0.00 sec)

When entering the data from a web form, you can even construct the select box in real time by getting the data out of the data dictionary with PHP. Live Demo - Link to Working Source Code

Required Reading

mySQL Manual and PHP Manual available from
PHP home page
MySQL home page

This page is http://people.virginia.edu/~rtg2t/webdb/home.html