You have come to the conclusion that your
website needs more scale that a database is added.
For most people, testing database project at
the first time will lead to MySQL, a free database and available for enterprises.
It is better that it is included with most versions of Linux, so adding it to
your system will be very fast. Another benefit of choosing MySQL because it's
so popular with Linux community, its language interfaces is usually available
with all versions. Therefore, when you are adding MySQL to your system, you can
usually add programming language interfaces for it. This month, we will
approach the evaluation and use MySQL from the perspective of PHP and Perl.
Access MySQL from PHP
Linux web servers usually run Apache as their
web server, and Apache supports PHP, which is a language primarily exists in
websites. As a result, you can run PHP scripts though your website only has
static web pages. Making PHP to talk with MySQL PHP is very easy because hooks
are installed to talk with it. Everything you need to be able to start using
MySQL database with PHP is to ensure that PHP and MySQL extensions are
installed. Most versions of Linux are installed both.
To use MySQL, firstly, you have to connect to
MySQL server whose database is open. This connection lasts as long as the PHP
script is running or until you close the connection. After you have connected
to the MySQL server, you will need to select the database you want to use.
Then, you can interact with multiple tables, rows and columns of the database
as the way you want.
A simple PHP example
The best way to see how MySQL and PHP work
together is with a sample script. Suppose that you have a web store where
people can order products and log into their accounts. Let's assume further
that the database is called "store", and account information has the
username and password of customers, which are stored in a table named
"logins". Imagine that logins table has many columns containing
information of each client and store them in columns named
"first_name", "last_name", "email", etc… Finally,
suppose that when someone logs in, you want to welcome them by their first
name. The code would look like this:
<?php
$login_email = $_POST["email"];
$db_host = "localhost";
$db_user = "root";
$db_pass = "rootpassword";
$db_name = "store"
$db_link = mysql_connect ($db_host,
$db_user, $db_pass, true);
mysql_select_db ($db_name, $db_link);
$sql = "SELECT first_name, last_name
FROM logins WHERE
email='$login_email'";
$result = mysql_query ($sql, $db_link);
$login_row = mysql_fetch_array ($result);
print "Welcome to our store front,
" . $login_row['first_name'] .
"!<br>";
mysql_close ($db_link);
?>
The first line gets the email address from a
web page which requires email address of the user. The next four lines define
multiple attributes of the database we are talking about. $ db_host points to
the current computer which the web server is running, so if you used to change
the MySQL server, you can just change that value. $ db_user and $ db_pass are
MySQL username and password for the server, while the $ db_name is the name of
the database that we want to use.
The next 2 lines only connects to the MySQL
server which is running on the current machine, and then to the database store.
After that, we define a simple SQL query to get values of first_name and
last_name columns from the logins table, but only for email address which the
user entered. At this time, we actually do not send queries to MySQL; it's what
mysql_query () command does. To get results from mysql_query () query to do
something with the data, we call mysql_fetch_array (). What this command does
is to obtain data from the SQL query and store it in $ login_row variable.
Finally, we print a welcome message to users and then close the database
connection.
Obviously, it is a very simple example which
misses many things. There is no security in this example; it does not have any
error checking. Because there is no password testing, you can type your email
address and sign in as them. Above code does not check to see if typed email
address actually exists in the database. However, it will give you a good idea
about how to talk with MySQL from PHP.
