Welcome!

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

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
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)

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
Bill Schmarzo, Tech Chair of "Big Data | Analytics" of upcoming CloudEXPO | DXWorldEXPO New York (November 12-13, 2018, New York City) today announced the outline and schedule of the track. "The track has been designed in experience/degree order," said Schmarzo. "So, that folks who attend the entire track can leave the conference with some of the skills necessary to get their work done when they get back to their offices. It actually ties back to some work that I'm doing at the University of San...
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...
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science," is responsible for setting the strategy and defining the Big Data service offerings and capabilities for EMC Global Services Big Data Practice. As the CTO for the Big Data Practice, he is responsible for working with organizations to help them identify where and how to start their big data journeys. He's written several white papers, is an avid blogge...
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...
If a machine can invent, does this mean the end of the patent system as we know it? The patent system, both in the US and Europe, allows companies to protect their inventions and helps foster innovation. However, Artificial Intelligence (AI) could be set to disrupt the patent system as we know it. This talk will examine how AI may change the patent landscape in the years to come. Furthermore, ways in which companies can best protect their AI related inventions will be examined from both a US and...
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.
Chris Matthieu is the President & CEO of Computes, inc. He brings 30 years of experience in development and launches of disruptive technologies to create new market opportunities as well as enhance enterprise product portfolios with emerging technologies. His most recent venture was Octoblu, a cross-protocol Internet of Things (IoT) mesh network platform, acquired by Citrix. Prior to co-founding Octoblu, Chris was founder of Nodester, an open-source Node.JS PaaS which was acquired by AppFog and ...
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...
Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...