Open Source Cloud Authors: Zakia Bouachraoui, Liz McMillan, Elizabeth White, Yeshim Deniz, Pat Romanski

Related Topics: Open Source Cloud, PHP

Open Source Cloud: 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
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.

-- Create the database from scratch

-- 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
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.
answerid INT NOT NULL auto_increment,
fkquestionid INT NOT NULL,
text VARCHAR (255),

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)
('localhost', 'poll', 'polluser', 'Y', 'Y', 'Y');

-- Activate the changes to the 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


Could you please inform about subscription of your magazines.

Kind Regards/belahbib

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


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.

IoT & Smart Cities Stories
All in Mobile is a place where we continually maximize their impact by fostering understanding, empathy, insights, creativity and joy. They believe that a truly useful and desirable mobile app doesn't need the brightest idea or the most advanced technology. A great product begins with understanding people. It's easy to think that customers will love your app, but can you justify it? They make sure your final app is something that users truly want and need. The only way to do this is by ...
Digital Transformation and Disruption, Amazon Style - What You Can Learn. Chris Kocher is a co-founder of Grey Heron, a management and strategic marketing consulting firm. He has 25+ years in both strategic and hands-on operating experience helping executives and investors build revenues and shareholder value. He has consulted with over 130 companies on innovating with new business models, product strategies and monetization. Chris has held management positions at HP and Symantec in addition to ...
Dynatrace is an application performance management software company with products for the information technology departments and digital business owners of medium and large businesses. Building the Future of Monitoring with Artificial Intelligence. Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more busine...
DXWorldEXPO LLC announced today that Big Data Federation to Exhibit at the 22nd International CloudEXPO, colocated with DevOpsSUMMIT and DXWorldEXPO, November 12-13, 2018 in New York City. Big Data Federation, Inc. develops and applies artificial intelligence to predict financial and economic events that matter. The company uncovers patterns and precise drivers of performance and outcomes with the aid of machine-learning algorithms, big data, and fundamental analysis. Their products are deployed...
The challenges of aggregating data from consumer-oriented devices, such as wearable technologies and smart thermostats, are fairly well-understood. However, there are a new set of challenges for IoT devices that generate megabytes or gigabytes of data per second. Certainly, the infrastructure will have to change, as those volumes of data will likely overwhelm the available bandwidth for aggregating the data into a central repository. Ochandarena discusses a whole new way to think about your next...
CloudEXPO | DevOpsSUMMIT | DXWorldEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
Cell networks have the advantage of long-range communications, reaching an estimated 90% of the world. But cell networks such as 2G, 3G and LTE consume lots of power and were designed for connecting people. They are not optimized for low- or battery-powered devices or for IoT applications with infrequently transmitted data. Cell IoT modules that support narrow-band IoT and 4G cell networks will enable cell connectivity, device management, and app enablement for low-power wide-area network IoT. B...
The hierarchical architecture that distributes "compute" within the network specially at the edge can enable new services by harnessing emerging technologies. But Edge-Compute comes at increased cost that needs to be managed and potentially augmented by creative architecture solutions as there will always a catching-up with the capacity demands. Processing power in smartphones has enhanced YoY and there is increasingly spare compute capacity that can be potentially pooled. Uber has successfully ...
SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...
When talking IoT we often focus on the devices, the sensors, the hardware itself. The new smart appliances, the new smart or self-driving cars (which are amalgamations of many ‘things'). When we are looking at the world of IoT, we should take a step back, look at the big picture. What value are these devices providing. IoT is not about the devices, its about the data consumed and generated. The devices are tools, mechanisms, conduits. This paper discusses the considerations when dealing with the...