Chapter Three Connecting to Databases

Chapter Three

3. Connecting to Databases

3.1. Introduction

One of the reasons for PHP’s popularity as a Web scripting language is its support for a wide
range of relational database systems. This support makes it easy for Web developers to create
data-driven Web sites and to prototype new Web applications quickly and efficiently.

PHP supports more than fifteen different database engines, including Microsoft SQL Server,
IBM DB2, PostgreSQL, MySQL, and Oracle. Using database in applications helps us to:
Read/write data, Store more data, have better organized data, faster access to data, easier to
manipulate and relate data to other data.

Database: is a set of tables. We should have 1 database for 1 application.

Tables: is a set of rows and columns. It represents a single concept such as products, customers,
orders etc. We can create relationships among tables.

Columns: a set of data of single data type. Ex. FirstName, LastName, Email, Password etc.
columns have types such as strings, integers, float, date etc.

Rows: single record of data. Ex. “Abebe”, “Kebede”, “abe@gmail.com”, “password”

Field: is the intersection of a row and a column. Ex. FirstName: ”Abebe”

Index: data structure on a table to increase look up speed.

Foreign key: table columns whose values references rows in another table. It is the foundation
of relational table.

PHP allows developers to interact with databases in two ways: by using a customized database
specific extension, or by using the database-neutral PHP Data Objects (PDO) extension. While
the PDO extension is more portable, many developers still find it preferable to use the native
database extension, especially when the native extension offers better performance or more
features than the PDO version.

Of the various database engines supported by PHP, the most popular one by far is MySQL. Both
PHP and MySQL are open-source.

Page 1 of 14
Chapter Three Connecting to Databases

3.2. Connect to a MySQL server/existing Database

PHP provides us different APIs (application program interfaces) to deal with MySQL server
databases: mysql( Original MySQL), mysqli( MySQL improved) and PDO( PHP Data Objects).
The differences between these APIs are shown on the table given below:

PHP database interactions in five steps:

 Create a database connection

 Perform Database query
 Use returned data if any
 Release returned data
 Close database connection

Creating a database connection:

Before we enable do anything with MySQL in PHP, we should first connect to the MySQL
server using specific connection variables. Connection variables consist of the following
common parameters, of which the first one is required while others are optional:-

 Host name: This is the name of the server. We can change to whatever host is acting as
MySQL server.
 User name: The root user of the system.
 User name’s password:- This is encrypted written with the form for security.

The common function in PHP that uses for server connection is mysql_connect( ) or
mysqli_connect() function. This function has the following syntax:- mysql_connect
("hostname", "user", "pass") to connect with MySQL server. PHP provides mysql_connect
function to open a database connection. This function can take up to five parameters and returns
a MySQL link identifier on success, or FALSE on failure.

The five parameters are the three above and the two below options.

Page 2 of 14
Chapter Three Connecting to Databases

Optional - If a second call is made to mysql_connect() with the same

new_link arguments, no new connection will be established; instead, the identifier of
the already opened connection will be returned.

Optional - A combination of the following constants:

MYSQL_CLIENT_SSL - Use SSL encryption

MYSQL_CLIENT_COMPRESS - Use compression protocol

client_flags
MYSQL_CLIENT_IGNORE_SPACE - Allow space after function names

MYSQL_CLIENT_INTERACTIVE - Allow interactive timeout seconds of

inactivity before closing the connection

Therefore, mysql_connect() function written as follows with respective parameters:-

mysql_connect(server,username,passwd,new_link,client_flag);

There are also functions in PHP which have different purposes. For instance,

 mysql_select_db("database name"): Equivalent to the MySQL command USE; makes

the selected database the active one.
 mysql_query("query"): Used to send any type of MySQL command to the server.
 mysql_fetch_rows("results variable from query"): Used to return a row of the entire
results of a database query.
 mysql_fetch_array("results variable from query"): Used to return several rows of the
entire results of a database query.
 mysql_free_result(“result variable from query”): Used to release the returned results.
 mysql_error(): Shows the error message that has been returned directly from the
MySQL server.

We issue this connection command with the PHP function called mysql_connect() or
mysqli_connect(). As with all of our PHP/MySQL statements, you can either put the information
into variables, or leave them as text in MySQL query as shown below:-:
$host = “localhost”;
$user = “root”;
$pass = “”;
$connect = mysql_connect($host, $user, $pass); Or simply $connect = mysql_connect(“localhost”, “root”, “”);

Page 3 of 14
Chapter Three Connecting to Databases

Or you can also use

$connect=mysqli_connect($host, $user, $pass);

Choosing and Creating the working Database

After establishing a MySQL connection with the code above, you then need to choose which
database you will be using with this connection. This is done with the
mysql_select_db(“database-name”) or mysqli_select_db(“connection”,”databasename”)
function. If the database you are looking to work on is not available, you can create it using
mysql_query() or mysqli_query() function together with CREATE command followed by
database name. mysql_query function can take two parameters and returns TRUE on success or
FALSE on failure. The parameters are:- sql and connection. The syntax of the function is:-
mysql_query(sql, connection variable); or mysqli_query(connection variable,sql);

To create a database uses the following sql syntax:

CREATE DATABASE database_name

Example Using : mysql Example Using : mysqli

<?php <?php
$connection=$mysql_connect("localhost", "root", "") ; $connection=$mysqli_connect("localhost", "root", "");
If($connection) If($connection)
echo "Connected to MySQL<br />"; echo "Connected to MySQL<br />";
$sql=mysql_select_db("test"); }else{
If($sql){ $result=mysqli_query($connection,“create database
echo "Connected to Database";//display this message if test”);//create a database called test if not available
database is selected If($result)
}else{ mysqli_select_db($connection,"test")
$result=mysql_query(“create database test”, else
$connection);//create a database called test if not die(“Database not
available selected:”.mysql_error($connection));//select test
If($result) database
mysql_select_db("test");//select test database }
else mysqli_close($connection);//closing connection
die(“Database not selected:”.mysql_error()); ?>
}
?>
mysql_close();//closing connection

Output:

Page 4 of 14
Chapter Three Connecting to Databases

Connected to MySQL
Connected to Database

 mysql_query ("create database test”,$connection): told MySQL to create a database

called test.
 die(mysql_error()); will print out an error if there is a problem in the database creation
process.

Closing Query

 When you are finished working with query results retrieved with the mysql_query() function,
use the mysql_free_result() function to close the resultset
 To close the resultset, pass to the mysql_free_result() function the
variable containing the result pointer from the mysql_query() function

Create Table MySQL

Before you enter data (rows) into a table, you must first define what kinds of data will be stored
(columns).This can be done using Create sql statement.

Syntax:

CREATE TABLE table_name (column_name1 data_type,column_name2 data_type,....)

We are now going to design a MySQL query to summon our table from database test.

Example Using : mysql Example Using : mysqli

<?php <?php
// Make a MySQL Connection // Make a MySQL Connection
$server=”localhost”; $server=”localhost”;
$dbuser=”root”; $dbuser=”root”;
$dbpassword=””; $dbpassword=””;
$dbname=”test”; $dbname=”test”;
$connection=$mysql_connect($server,$dbuser, $connection=$mysqli_connect($server,$dbuser,
$dbpassword); $dbpassword,$dbname);
if($connection) if($connection)
{ { // Create a MySQL table in the selected database
$sql=mysql_select_db($dbname); $sql2="CREATE TABLE example(
If($sql) id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
{ // Create a MySQL table in the selected database name VARCHAR(30), age INT)";
$sql2="CREATE TABLE example( $result=mysqli_query($connection,$sql2);
id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), If($result)

Page 5 of 14
Chapter Three Connecting to Databases

name VARCHAR(30), age INT)"; echo "Table Created!";

$result=mysql_query($sql2,$connection); else
If($result) die(“Table Creation
echo "Table Created!"; failed:”.mysqli_error($connection));
else }else{
die(“Table Creation failed:”.mysql_error()); die(“Mysql server Connection
}else{ Failed:”.mysqli_error($connection));
die(“Database selection failed:”.mysql_error()); }
} mysqli_close($connection);//closing connection
}else{
die(“Mysql server Connection Failed:”.mysql_error()); ?>
}
mysql_close();//closing connection
?>

3.3. Send/Insert Data to a Database

When data is put into a MySQL table it is referred to as inserting data. When inserting data it is
important to remember the exact names and types of the table's columns. If you try to place a 500
word essay into a column that only accepts integers of size three, you will end up with errors.
Inserting data into a table can be performed using Insert into sql statement.

Syntax:

INSERT INTO table_name VALUES (value1, value2, value3,...) Or

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...)

<html><style>
#style{ display:block;
width:220px;
height:120px;
border:1px solid black;
line-height:30px;
}
#style p{
margin-left:20px;
margin-right:20px;
}</style><body>

Page 6 of 14
Chapter Three Connecting to Databases

<?php
if(isset($_POST['submit']))
{
//getting name and age from the user
$name=$_POST['name'];
$age=$_POST['age'];
// Make a MySQL Connection
$connection=mysql_connect("localhost", "root", "");
If($connection){
$sql=mysql_select_db("test”);
If($sql){
// Insert a row of information into the table "example"
$sql2="INSERT INTO example(name, age) VALUES('$name', '$age') ";
$result=mysql_query($sql2,$connection) ;
If($result)
echo "Data Inserted!";
else
die(“Data not inserted:”.mysql_error());
} else
die(“Database not selected:”.mysql_error());

}else{
die(“Connection Failed:”.mysql_error());
}
mysql_close();//closing connection
?>
<div id="style"><p><form action="" method="post">
Name:<input type="text" name="name" required="" placeholder="name"><br>
Age:<input type="text" name="age" required="" placeholder="age"><br>
<input type="submit" value="Submit" name="submit"><input type="reset" value="Reset">
</form></p></div></body></html>

If you want to use mysqli, replace mysql connection and data insertion by the following code:
// Make a MySQL Connection
$connection=mysqli_connect("localhost", "root", "","test”);
If($connection){
// Insert a row of information into the table "example"
$sql2="INSERT INTO example(name, age) VALUES('$name', '$age') ";
$result=mysqli_query($connection,$sql2) ;

Page 7 of 14
Chapter Three Connecting to Databases

If($result)
echo "Data Inserted!";
else
die(“Data not inserted:”.mysqli_error($connection));
} else
die(“Database not selected:”.mysqli_error($connection));
}else{
die(“Connection Failed:”.mysqli_error($connection));
}
mysqli_close($connection);//closing connection
?>

3.4. Retrieve Data from a Database

Usually most of the work done with MySQL involves pulling down data from a MySQL
database. In MySQL, data is retrieved with the "SELECT" keyword. Think of SELECT as
working the same way as it does on your computer. If you want to copy some information in a
document, you would first select the desired information, then copy and paste.

Before attempting to retrieve data, be sure that you have created a table that contains some data.
In this example, we will output the first entry of our MySQL "examples" table to the web
browser.

The SELECT statement is used to select data from a database.

Syntax: SELECT column_name(s) FROM table_name

Example:

Example Using : mysql Example Using : mysqli

<?php <?php
// Make a MySQL Connection // Make a MySQL Connection
$connection=mysql_connect("localhost", "root", ""); $connection=mysqli_connect("localhost", "root", "","test”);
If($connection){ If($connection){
$sql=mysql_select_db("test"); / Retrieving all records from "example" table
If($sql){ $sql=”select * from example ";
// Retrieving all records from "example" table $result=mysqli_query($connection,$sql) ;
$sql2="select * from example"; If($result){
$result=mysql_query($sql2,$connection) ; //Displaying the values using a table
If($result){//Displaying the values using a table echo “<table border=1><tr><th>Id</th>”;
echo "<table border=1><tr><th>Id</th>"; echo “<th>Name</th><th>Age</th></tr>”;

Page 8 of 14
Chapter Three Connecting to Databases

echo "<th>Name</th><th>Age</th></tr>"; While($row=mysqli_fetch_assoc($result))

While($row=mysql_fetch_array($result)) {
{ echo "<tr><td>".$row['id']."</td>"; echo “<tr><td>”.$row[‘id’].”</td>”;
echo "<td>".$row['name']."</td>"; echo “<td>”.$row[‘name’].”</td>”;
echo "<td>".$row['age']."</td></tr>"; echo “<td>”.$row[‘age’].”</td></tr>”;
} }
echo "</table>"; echo “</table>”;
}else }else
die("Record Not Selected:".mysql_error()); die(“Record Not Selected:”.mysqli_error($connection));
} else }else{
die("Database not die(“Connection Failed:”.mysqli_error($connection));
selected:".mysql_error()); }
}else{ mysqli_close($connection);//closing connection
die("Connection Failed:".mysql_error());
} ?>

?>

Output:

When you select items from a database using mysql_query, the data is returned as a MySQL
result. Since we want to use this data in our table we need to store it in a variable. $result now
holds the result from our mysql_query.

The mysql_fetch_array function gets the next-in-line associative/numeric array from a MySQL
result. The mysql_fetch_row function gets the next-in-line numeric array from a MySQL result.
The mysqli_fetch_assoc function gets the next-in-line associative array from a MySQL result.
By putting it in a while loop it will continue to fetch the next array until there is no next array to
fetch. This function can be called as many times as you want, but it will return FALSE when the
last associative array has already been returned.

By placing this function within the conditional statement of the while loop,

 We can retrieve the next associative array from our MySQL Resource, $result, so that we
can print out the id, name and age of that person.

Page 9 of 14
Chapter Three Connecting to Databases

 We can tell the while loop to stop printing out information when the MySQL Resource
has returned the last array, as False is returned when it reaches the end and this will cause
the while loop to halt.

In our MySQL table "example" there are only three fields: id, name and age. These fields are the
keys to extracting the data from our associative array. To get the id, name and age we use
$row[‘id’], $row['name'] and $row['age'] respectively. The html table tag is used to let the output
look better. The above select statement retrieves everything from the example table. If you want
to retrieve specific record, you can use where clause in the select statement. For example:
mysql_query(“select * from example where id=1”);

3.5. Modify/Updating Existing Data

Imagine that you have a MySQL table that holds the information of all the employees in your
company. One of the columns in this table is called "Seniority" and it holds an integer value of
how many months an employee has worked at your company. Unfortunately for you, your job is
to update these numbers every month.

You may be thinking that you'll have to open up your MySQL administration tool and edit each
entry by hand. That would take hours. On the other hand, you could master MySQL and have an
automated script that you run each month to get the job done for you.

The UPDATE statement is used to update existing records in a table.

Syntax
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Example: In the example table we have 4 records. The person whose has an id number of 4 is
turned to 17. So change the persons age accordingly.

Example Using : mysql Example Using : mysqli

<?php <?php
// Make a MySQL Connection // Make a MySQL Connection
$connection=mysql_connect("localhost", "root", ""); $connection=mysqli_connect("localhost", "root", "","test”);
If($connection){ If($connection){
$sql=mysql_select_db("test"); // Updating record
If($sql){ $sql=”update example set age=17 where id=4 ";
// Updating record $result=mysqli_query($connection,$sql) ;
$sql2=" update example set age=17 where id=4 "; If($result){
$result=mysql_query($sql2,$connection) ; echo “Age successfully updated!”;

Page 10 of 14
Chapter Three Connecting to Databases

If($result){ }else
echo “Age successfully updated!”; die(“Record Not Updated:”.mysqli_error($connection));
}else }else{
die("Record Not updated:".mysql_error()); die(“Connection Failed:”.mysqli_error($connection));
} else }
die("Database not selected:".mysql_error()); mysqli_close($connection);//closing connection
}else{
die("Connection Failed:".mysql_error()); ?>

}
?>

Output:

Age successfully updated!

In the above example:

 UPDATE - Performs an update MySQL query

 SET - The new values to be placed into the table follow SET
 WHERE - Limits which rows are affected

3.6. Remove Existing Data

Maintenance is a very common task that is necessary for keeping MySQL tables current. From
time to time, you may even need to delete items from your database. Some potential reasons for
deleting a record from MySQL include when: someone deletes a post from a forum.

The DELETE query is very similar to the UPDATE Query. We need to choose a table, tell
MySQL to perform the deletion, and provide the requirements that a record must have for it to be
deleted.

Syntax:

DELETE from table_name where column_name comparison_operator value

For example: delete all records in which age is below 18.

Example Using : mysql Example Using : mysqli

<?php <?php
// Make a MySQL Connection // Make a MySQL Connection
$connection=mysql_connect("localhost", "root", ""); $connection=mysqli_connect("localhost", "root", "","test”);

Page 11 of 14
Chapter Three Connecting to Databases

If($connection){ If($connection){
$sql=mysql_select_db("test"); // Updating records
If($sql){ $sql=” delete from example where age<18";
// deleting records $result=mysqli_query($connection,$sql) ;
$sql2="delete from example where age<18 "; If($result){
$result=mysql_query($sql2,$connection) ; echo “Records Deleted!”;
If($result){ }else
echo “Records Deleted!”; die(“Record Not Deleted:”.mysqli_error($connection));
}else }else{
die("Record Not deleted:".mysql_error()); die(“Connection Failed:”.mysqli_error($connection));
} else }
die("Database not selected:".mysql_error()); mysqli_close($connection);//closing connection
}else{
die("Connection Failed:".mysql_error()); ?>

}
?>

Output:

Records Deleted!

3.7. Data base security using server side scripting

Nowadays, databases are cardinal components of any web based application by enabling
websites to provide varying dynamic content. Since very sensitive or secret information can be
stored in a database, you should strongly consider protecting your databases.

To retrieve or to store any information you need to connect to the database, send a legitimate
query, fetch the result, and close the connection.

Encryption in PHP

Once an attacker gains access to your database directly (bypassing the web server), stored
sensitive data may be exposed or misused, unless the information is protected by the database
itself. Encrypting the data is a good way to mitigate this threat, but very few databases offer this
type of data encryption.

The easiest way to work around this problem is to first create your own encryption package, and
then use it from within your PHP scripts. PHP provides us with different types of encryptions
such as: md5, sha1, hash, crypt, hashed_password etc.

Page 12 of 14
Chapter Three Connecting to Databases

Example:
<?php
$pass="12345678";
echo "md5 encryption $pass=".md5($pass)."<br>";
echo "sha1 encryption $pass=".sha1($pass)."<br>";
echo "hash encryption $pass=".hash('sha1',$pass)."<br>";
echo "crypt encryption $pass=".crypt($pass,$salt);
?>

Output:

md5 encryption 12345678=25d55ad283aa400af464c76d713c07ad

sha1 encryption 12345678=7c222fb2927d828af22f592134e8932480637c0d
hash encryption 12345678=7c222fb2927d828af22f592134e8932480637c0d
crypt encryption 12345678=$1$.90.tj5.$CG0sUopGFc1ADWxBqDjPu.

In the above example, the salt parameter is optional. However, crypt () creates a weak password
without the salt. Make sure to specify a strong enough salt for better security.

SQL Injection

SQL injection attacks are extremely simple to defend against, but many applications are still
vulnerable. Consider the following SQL statement:
<?php
$sql = "INSERT INTO users (reg_username,reg_password,reg_email) VALUES ('{$_POST['reg_username']}',
'$reg_password', '{$_POST['reg_email']}')";
?>

This query is constructed with $_POST, which should immediately look suspicious.

Assume that this query is creating a new account. The user provides a desired username and an
email address. The registration application generates a temporary password and emails it to the
user to verify the email address. Imagine that the user enters the following as a username:
bad_guy', 'mypass', ''), ('good_guy

This certainly doesn't look like a valid username, but with no data filtering in place, the
application can't tell. If a valid email address is given ( shiflett@php.net, for example),
and 1234 is what the application generates for the password, the SQL statement becomes the
following:
<?php

Page 13 of 14
Chapter Three Connecting to Databases

$sql = "INSERT INTO users (reg_username,reg_password,reg_email) VALUES ('bad_guy', 'mypass', ''),

('good_guy','1234',
'shiflett@php.net')";
?>

Rather than the intended action of creating a single account ( good_guy) with a valid email
address, the application has been tricked into creating two accounts, and the user supplied every
detail of the bad_guy account.

While this particular example might not seem so harmful, it should be clear that worse things
could happen once an attacker can make modifications to your SQL statements.

For example, depending on the database you are using, it might be possible to send multiple
queries to the database server in a single call. Thus, a user can potentially terminate the existing
query with a semicolon and follow this with a query of the user's choosing.

MySQL, until recently, does not allow multiple queries, so this particular risk is mitigated.
Newer versions of MySQL allow multiple queries, but the corresponding PHP extension
(ext/mysqli) requires that you use a separate function if you want to send multiple queries
(mysqli_multi_query() instead of mysqli_query()). Only allowing a single query is safer,
because it limits what an attacker can potentially do.

Protecting against SQL injection is easy:

 Filter your data: This cannot be overstressed. With good data filtering in place, most
security concerns are mitigated, and some are practically eliminated.
 Quote your data: If your database allows it (MySQL does), put single quotes around all
values in your SQL statements, regardless of the data type.
 Escape your data: Sometimes valid data can unintentionally interfere with the format of
the SQL statement itself. Use mysql_escape_string() or mysqli_real_escape_string() an
escaping function native to your particular database. If there isn't a specific
one, addslashes() is a good last resort.

Page 14 of 14