Most Read Technology Reporter For More Than Two Decades

Maureen O'Gara

Subscribe to Maureen O'Gara: eMailAlertsEmail Alerts
Get Maureen O'Gara: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Related Topics: Apache Web Server Journal, MySQL Journal, PHP Developer's Journal

Apache Web Server: Article

Building Web Sites with PHP and MySQL Part 1 of 2

Building Web Sites with PHP and MySQL Part 1 of 2

This article is about designing a simple database with MySQL and getting PHP to use the database. Hopefully, this will provide enough information to get anyone a good start towards developing more complicated PHP/MySQL Web sites.

Most Linux distributions come with MySQL, although it may not be installed by default by some distributions. For instance, Red Hat 7.1 installs PostgreSQL instead of MySQL by default. This can be corrected during the initial install by selecting MySQL to be installed, or by installing it afterwards. It can also be downloaded from www.mysql.com. Most modern distributions of apache include PHP, although you can get it from www.php.net.

Configuring the database for the first time
Before the MySQL database software can be used, the default database files need to be configured. Login or "su" as the "mysql" user and run the "mysql_install_db" command. It copies the default files required for the MySQL to work. We want the database files to be owned by the MySQL user to prevent security problems.

Starting and stopping the database
The "safe_mysqld" command starts up the database. It will run under the "mysql" user as a security precaution, and the database files should be owned by that user. This is to prevent a security problem in MySQL from compromising the system. Preferably, you'll want to run the database in the background using the "&" - as in "safe_mysqld&".

Shutting down the database is done using the mysqladmin command. The "mysqladmin" command is a general purpose program for performing much of the maintenance of the database. Anyone can run the command, but users must identify themselves to the database using the -u (to identify the user) and -p (to provide a password) options. Using the "-p" option will prompt for the password. The typical syntax for mysqladmin is "mysqladmin -u root -p [command]" where the command is one of many that is supported. Note that the previous command running as the "root" user in this example - this is the root user in MySQL and not the root user in Linux.

Setting the root password
Now MySQL is running, but it's running highly insecure since we haven't configured the root password. Anyone can connect to the database using the "root" user and no password.

The root user on MySQL should have its own password - for this article, we'll use the password "jTx8cwO3". Here's the command to change the password for the MySQL root user:

mysqladmin -u root -p password jTx8cw03

The mysqladmin utility
The mysqladmin command is used for more than just changing the password. Some of the more interesting commands that can be issued from the mysqladmin are:

shutdown - shutdown the database server
create dbname - creates a database [dbname] on the server
drop dbname - drops the database [dbname] from the server

You can type "mysqladmin -h" to get the list of valid commands. All of the commands issued using the mysqladmin application require a user/password to identify the user running the command. For instance, now to create our own database to use, the command:

mysqladmin -u root -p create Poll

creates a database named "Poll" ready for us.

The mysql utility.
MySQL also has a program call mysql for establishing an interactive connection with the database to issue SQL commands. It requires authentication like the mysqladmin program. For instance, this example shows a connection to MySQL and a query on the databases within MySQL.

[[email protected] articlephp]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.36

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

mysql> databases;

+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> use mysql;
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;

+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)

mysql> quit

The Application
The PHP/MySQL application is going to use to demonstrate the power of PHP/MySQL. It's going to be a simple Web site which can ask the user a question from the database, display a list of possible answers and allow the user to select one of the answers. The application should keep track of the number of times each answer is chosen.

Database design
The database consists of two tables - a "Question" table which stores the questions, and an "Answer" table which stores each of the answers to the questions. The answers should have a foreign key back to their questions so we can display the appropriate answers to the question.

	Question                   Answer
------------ --------------
questionid <------ answerid
text | text
------- fkquestionid
votes
Building the database
The script to build the database is small. The assumption is that the database may already exist so we want to destroy it if it's already there and then create it. Most of the script is standard SQL. There are some specific MySQL features - one is the "auto_increment" used on the id's of the tables. This automatically increments the id each time an item is added to the database. The other specific MySQL feature is the "PRIMARY KEY" which adds an using the field as the key.

Here's the first part of the script that creates the database. (Comments included - comments are the lines beginning with "--".)

--
-- Destroy the database if it already existed.
-- We're in development and want to start fresh each time.
--
DROP DATABASE poll;

--
-- Create the database from scratch
--
CREATE DATABASE poll;

--
-- Use the created database so that when we do other
-- operations (like create tables), we do it in
-- the poll database.
--
USE poll;

The next step is to create the two tables.

--
-- The Question table stores the poll question.
--
-- questionid - unique id of the question
-- text - The poll question
--
CREATE TABLE Question (
questionid INT NOT NULL auto_increment,
text VARCHAR (255),

PRIMARY KEY (questionid)
);

--
-- The Answer table stores information about the answer.
--
-- fkquestionid - link to the question that this is an answer of
-- text - text of the answer
-- votes - how many votes this answer has received.
--
CREATE TABLE Answer (
answerid INT NOT NULL auto_increment,
fkquestionid INT NOT NULL,
text VARCHAR (255),
votes INT NOT NULL,

PRIMARY KEY (answerid)
);

Inserting data for testing
For testing, this inserts two questions into the list. This could be made more elaborate by having a Web page allowing an admin to enter in a question and a set of answers instead of hard coding it in the SQL, however, this is just a demo, so we'll insert canned data into the database.

--
-- Insert a couple of questions into the database.
--
INSERT INTO Question (questionid, text)
VALUES (1, "What is your favorite operating system?");
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (1, 1, "Linux", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (2, 1, "BSD", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (3, 1, "Microsoft", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (4, 1, "Macintosh", 0);

INSERT INTO Question (questionid, text)
VALUES (2, "What is your favorite scripting language?");
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (5, 2, "PHP", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (6, 2, "Perl", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (7, 2, "Python", 0);
INSERT INTO Answer (answerid, fkquestionid, text, votes)
VALUES (8, 2, "ASP", 0);

Database Permissions
Currently, only the root user is listed in the database. Just as it is a bad idea to be logging in as root to run user level programs, it's a bad idea to write an application that connects tothe database as root. Ideally, another user should be created with only access to this database and only using permissions that are required. To create a user and give that user permissions, use the "mysql" database and add a user into the "user" table and give the user permissions in the db table. In the example below, the user created is called "polluser" and has privileges to "SELECT", "INSERT", and "UPDATE". The user cannot drop tables or delete any information from the database.

The "FLUSH PRIVILEGES" needs to be called when all the permission changes have been made or the changes won't be reflected in the database until the database is restarted.

--
-- Create a user and privileges for the poll
--
USE mysql;

--
-- Create a user for the poll. We don't want the php script
-- using the "root" user for accessing the database.
--
INSERT INTO user (Host, User, Password)
VALUES ('localhost', 'polluser', PASSWORD ('Cnm32A00FD'));

--
-- Create privileges for the "polluser" user in the "poll" database.
--
INSERT INTO db (Host, db, User, Select_priv, Insert_priv, Update_priv)
VALUES
('localhost', 'poll', 'polluser', 'Y', 'Y', 'Y');

--
-- Activate the changes to the privileges
--
FLUSH PRIVILEGES;

More Stories By Maureen O'Gara

Maureen O'Gara the most read technology reporter for the past 20 years, is the Cloud Computing and Virtualization News Desk editor of SYS-CON Media. She is the publisher of famous "Billygrams" and the editor-in-chief of "Client/Server News" for more than a decade. One of the most respected technology reporters in the business, Maureen can be reached by email at maureen(at)sys-con.com or paperboy(at)g2news.com, and by phone at 516 759-7025. Twitter: @MaureenOGara

Comments (3) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
belahbib said 12/11/01 08:14:00 AM EST

Hello

Could you please inform about subscription of your magazines.

Kind Regards/belahbib

Jay 12/03/01 08:24:00 PM EST

Thanks,

Wick Swain 12/03/01 11:21:00 AM EST

Interesting article. What happened to the last two pages of this article? It stops on page 6 of 8 with no navigation available to another page.