Welcome!

Open Source Cloud Authors: Pat Romanski, Liz McMillan, Yeshim Deniz, Zakia Bouachraoui, William Schmarzo

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
Every organization is facing their own Digital Transformation as they attempt to stay ahead of the competition, or worse, just keep up. Each new opportunity, whether embracing machine learning, IoT, or a cloud migration, seems to bring new development, deployment, and management models. The results are more diverse and federated computing models than any time in our history.
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
IoT is rapidly becoming mainstream as more and more investments are made into the platforms and technology. As this movement continues to expand and gain momentum it creates a massive wall of noise that can be difficult to sift through. Unfortunately, this inevitably makes IoT less approachable for people to get started with and can hamper efforts to integrate this key technology into your own portfolio. There are so many connected products already in place today with many hundreds more on the h...
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...
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...
Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...