Chapter 4
Connecting to Databases
1. Introduction
Database is a collection of inter-related data which helps in efficient retrieval, insertion and
deletion of data from database and organizes the data in the form of tables, views, schemas, reports
etc. For Example, university database organizes the data about students, faculty, and admin staff
etc. which helps in efficient retrieval, insertion and deletion of data from it.
A database is a separate application that stores a collection of data. Each database has one or more
distinct APIs for creating, accessing, managing, searching and replicating the data it holds. Other
kinds of data stores can also be used, such as files on the file system or large hash tables in memory
but data fetching and writing would not be so fast and easy with those type of systems.
Nowadays, we use relational database management systems (RDBMS) to store and manage huge
volume of data. This is called relational database because all the data is stored into different tables
and relations are established using primary keys or other keys known as Foreign Keys.
A Relational Database Management System (RDBMS) is a software that
Enables you to implement a database with tables, columns and indexes.
Guarantees the Referential Integrity between rows of various tables.
Updates the indexes automatically.
Interprets an SQL query and combines information from various tables.
2. RDBMS Terminology
Before we proceed to explain the MySQL database system, let us revise a few definitions related
to the database.
o Database: - A database is a collection of tables, with related data.
o Table: - A table is a matrix with data. A table in a database looks like a simple
spreadsheet.
o Column: - One column (data element) contains data of one and the same kind, for
example the column postcode.
1
o Row: - A row (= tuple, entry or record) is a group of related data, for example the data
of one subscription.
o Redundancy: - Storing data twice, redundantly to make the system faster.
o Primary Key: - A primary key is unique. A key value cannot occur twice in one table.
With a key, you can only find one row.
o Foreign Key: - A foreign key is the linking pin between two tables.
o Compound Key:- A compound key (composite key) is a key that consists of multiple
columns, because one column is not sufficiently unique
o Index: - An index in a database resembles an index at the back of a book
o Referential Integrity: - Referential Integrity makes sure that a foreign key value always
points to an existing row.
3. Overview on MySQL database
MySQL is the most popular Open Source Relational SQL database management system. MySQL
is one of the best RDBMS being used for developing web-based software applications. It is the
most popular database system used with PHP. MySQL is developed, distributed, and supported
by Oracle Corporation.
o The data in a MySQL database are stored in tables which consists of columns and rows.
o MySQL is a database system that runs on a server.
o MySQL is a very powerful program in its own right. It handles a large subset of the
functionality of the most expensive and powerful database packages.
o MySQL works on many operating systems and with many languages including PHP,
PERL, C, C++, JAVA, etc.
o MySQL is very friendly to PHP, the most appreciated language for web development.
o MySQL is ideal for both small and large applications.
o MySQL is very fast, reliable, and easy to use database system. It uses standard SQL
o MySQL compiles on a number of platforms.
o MySQL supports large databases, up to 50 million rows or more in a table. The default
file size limit for a table is 4GB, but you can increase this (if your operating system can
handle it) to a theoretical limit of 8 million terabytes (TB).
4. Creating Database Connection in PHP
2
PHP provides mysqli contruct or mysqli_connect() function to open a database connection. This
function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.
Syntax
$mysqli = new mysqli($host, $username, $passwd, $dbName, $port,
$socket);
Parameters Description
The host name running the database server. If not
$host
specified, then the default value will be localhost: 3306.
The username accessing the database. If not specified,
$username then the default will be the name of the user that owns the
server process.
The password of the user accessing the database. If not
$passwd
specified, then the default will be an empty password.
$dbName Database name on which query is to be performed.
The port number to attempt to connect to the MySQL
$port
server.
$socket Socket or named pipe that should be used.
You can disconnect from the MySQL database anytime using another PHP function close().
Syntax
$mysqli->close();
Example-1
<html>
<head>
<title>Connecting MySQL Server</title>
</head>
<body>
<?php
3
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$mysqli->close();
?>
</body>
</html>
Example-2
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = new mysqli($servername, $username, $password);
// Checking connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
5. Sending Query to MySQL Database using PHP and Processing Query Result
4
Database operations in PHP are a very crucial thing that is especially needed in CRUD (Create,
Read, Update and Delete) operations. Here we will discuss the Read part i.e. data fetching from
database.
Connecting to a Database: You have two options to connect with MySQL database
1. MySQLi Object-Oriented
$conn = new mysqli($servername, $username,
$databasename)
2. MySQLi Procedural
$conn = mysqli_connect($servername, $username,
$password, $databasename);
Executing Queries
After connecting to the database we need to run queries to fetch data. In Read operations, we will
use only select queries to fetch data from the database.
MySQLi Object-Oriented
$conn->query($query);
MySQLi Procedural
mysqli_query($conn, $query)
Close Connection
After the fetching is performed, you should close the connection to the database using the close()
function.
$conn->close();
Example: Create the following sample database and create table in the database
CREATE TABLE `Student Details` (
`Roll_No` int(11) NOT NULL,
5
`Name` varchar(255) NOT NULL,
`City` varchar(255) NOT NULL,
`Age` int(11) NOT NULL,
PRIMARY KEY (`Roll_No`)
);
MySQLi Object-Oriented approach: PHP Code for the above table
<?php
$servername = "localhost";
$username = "root";
$password = "";
$databasename = "StudentDataBase";
// CREATE CONNECTION
$conn = new mysqli($servername,
$username, $password, $databasename);
// GET CONNECTION ERRORS
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
// SQL QUERY
$query = "SELECT * FROM `Student Details`;";
// FETCHING DATA FROM DATABASE
$result = $conn->query($query);
if ($result->num_rows > 0)
6
{
// OUTPUT DATA OF EACH ROW
while($row = $result->fetch_assoc())
echo "Roll No: " .
$row["Roll_No"]. " - Name: " .
$row["Name"]. " | City: " .
$row["City"]. " | Age: " .
$row["Age"]. "<br>";
else {
echo "0 results";
$conn->close();
?>
MySQLi Procedural approach: PHP Code for the above table
<?php
$servername = "localhost";
$username = "root";
$password = "";
$databasename = " StudentDataBase ";
// CREATE CONNECTION
7
$conn = mysqli_connect($servername,
$username, $password, $databasename);
// GET CONNECTION ERRORS
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
// SQL QUERY
$query = "SELECT Roll_No, Name FROM `Student Details`;";
// FETCHING DATA FROM DATABASE
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
// OUTPUT DATA OF EACH ROW
while($row = mysqli_fetch_assoc($result)) {
echo "Roll No: " . $row["Roll_No"]
. " - Name: " . $row["Name"]. "<br>";
} else {
echo "0 results";
$conn->close();
?>
