Open Source Cloud Authors: Yeshim Deniz, Pat Romanski, Stefana Muller, Karthick Viswanathan, Elizabeth White

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)

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.

IoT & Smart Cities Stories
The deluge of IoT sensor data collected from connected devices and the powerful AI required to make that data actionable are giving rise to a hybrid ecosystem in which cloud, on-prem and edge processes become interweaved. Attendees will learn how emerging composable infrastructure solutions deliver the adaptive architecture needed to manage this new data reality. Machine learning algorithms can better anticipate data storms and automate resources to support surges, including fully scalable GPU-c...
Machine learning has taken residence at our cities' cores and now we can finally have "smart cities." Cities are a collection of buildings made to provide the structure and safety necessary for people to function, create and survive. Buildings are a pool of ever-changing performance data from large automated systems such as heating and cooling to the people that live and work within them. Through machine learning, buildings can optimize performance, reduce costs, and improve occupant comfort by ...
The explosion of new web/cloud/IoT-based applications and the data they generate are transforming our world right before our eyes. In this rush to adopt these new technologies, organizations are often ignoring fundamental questions concerning who owns the data and failing to ask for permission to conduct invasive surveillance of their customers. Organizations that are not transparent about how their systems gather data telemetry without offering shared data ownership risk product rejection, regu...
René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
Predicting the future has never been more challenging - not because of the lack of data but because of the flood of ungoverned and risk laden information. Microsoft states that 2.5 exabytes of data are created every day. Expectations and reliance on data are being pushed to the limits, as demands around hybrid options continue to grow.
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 ...
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities - ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups.
As IoT continues to increase momentum, so does the associated risk. Secure Device Lifecycle Management (DLM) is ranked as one of the most important technology areas of IoT. Driving this trend is the realization that secure support for IoT devices provides companies the ability to deliver high-quality, reliable, secure offerings faster, create new revenue streams, and reduce support costs, all while building a competitive advantage in their markets. In this session, we will use customer use cases...