Unit 4: Connecting to Databases

4.1. Connect to an existing Database

4.2. Send Data to a Database
4.3. Retrieve Data from a Database
4.4. Modify Existing Data
4.5. Remove Existing Data
4.6. Data base security using server side scripting
 PHP | MySQL Database Introduction
• What is MySQL?
MySQL is an open-source relational database management
system (RDBMS).
• It is the most popular database system used with PHP.
• MySQL is developed, distributed, and supported by Oracle
Corporation.
• The data in a MySQL database are stored in tables which
consists of columns and rows.
 MySQL is a database system that runs on a server.
 MySQL is ideal for both small and large applications.
 MySQL is very fast, reliable, and easy to use database
system. It uses standard SQL
 MySQL compiles on a number of platforms.
How to connect PHP with MySQL Database?
PHP 5 and later can work with a MySQL database using:
o MySQLi extension.
o PDO (PHP Data Objects).
Difference Between MySQLi and PDO
• PDO works on 12 different database systems, whereas MySQLi works
only with MySQL databases.
• Both PDO and MySQLi are object-oriented, but MySQLi also offers a
procedural API.
• If at some point of development phase, the user or the development
team wants to change the database then it is easy to that in PDO than
MySQLi as PDO supports 12 different database systems. He would
have to only change the connection string and a few queries. With
MySQLi,he will need to rewrite the entire code including the queries.
There are three ways of working with MySQl and PHP
 MySQLi (object-oriented)
 MySQLi (procedural)
 PDO
Connecting to MySQL database using PHP
There are 3 ways in which we can connect to MySQl from PHP as
listed above and described below:
 Using MySQLi object-oriented procedure:
We can use the MySQLi object-oriented procedure to establish a
connection to MySQL database from a PHP script.
Syntax:
<?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";
?>
-> operator is used for call member function or member variable.
PHP has two object operators.
The first, -> , is used when you want to call a method on an instance or access an instance
property.
The second, ::, is used when you want to call a static method, access a static variable, or cal
Explanation:
o We can create an instance of the mysqli class providing
all the necessary details required to establish the
connection such as host, username, password etc.
o If the instance is created successfully then the
connection is successful otherwise there is some error
in establishing connection.

 Using MySQLi procedural procedure :

There is also a procedural approach of MySQLi to establish
a connection to MySQL database from a PHP script as
described below.
Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username,
$password);
// Checking connection
if (!$conn) {
die("Connection failed: " .
mysqli_connect_error());
}
echo "Connected successfully";
?>
Explanation:
o In MySQLi procedural approach instead of creating an
instance we can use the mysqli_connect() function available in
PHP to establish a connection.
o This function takes the information as arguments such as host,
username , password , database name etc.
o This function returns MySQL link identifier on successful
connection or FALSE when failed to establish a connection.
 Using PDO procedure:
o PDO stands for PHP Data Objects.
o That is, in this method we connect to the database using data
objects in PHP as described below:
o Syntax:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB",
$username, $password);
// setting the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
Explanation:
 The exception class in PDO is used to handle any problems that
may occur in our database queries.
 If an exception is thrown within the try{ } block, the script stops
executing and flows directly to the first catch(){ } block.
 Closing A Connection
• When we establish a connection to MySQL database from a PHP
script , we should also disconnect or close the connection when
our work is finished.
• Here we have described the syntax of closing the connection to a
MySQL database in all 3 methods described above.
• We have assumed that the reference to the connection is stored in
$conn variable.
Using MySQLi object oriented procedure
Syntax
$conn->close();
•Using MySQLi procedural procedure
Syntax
mysqli_close($conn);
•Using PDO procedure
Syntax
$conn = null;
 PHP | mysqli_connect() Function

• The mysqli_connect() function in PHP is used to connect you to

the database.
• In the previous version of the connection mysql_connect() was
used for connection and then there
comes mysqli_connect() where i means improved version of
connection and is more secure than mysql_connect().
Syntax:

mysqli_connect ( "host", "username", "password",

"database_name" )
Parameters used:

host: It is optional and it specify the host name or IP address. In case of local
server localhost is used as a general keyword to connect local server and run the
program.
username: It is optional and it specify mysql username. In local server username
is root.
Password: It is optional and it specify mysql password.
database_name: It is database name where operation perform on data. It also
optional.
Return values:
It returns an object which represent MySql connection. If connection failed then
it return FALSE.
Program: Below is the implementation of mysqli_connect() function.

<?php
mysqli_connect("localhost", "root", "", "GFG");

if(mysqli_connect_error())
echo "Connection Error.";
else
echo "Database Connection Successfully.";
?>
Output:

Database Connection Successfully.

PHP MySQL Create Database
Since PHP 4.3, mysql_create_db() function is deprecated. Now it is recommended to
use one of the 2 alternatives.
mysqli_query()
PDO::__query()
PHP MySQLi Create Database Example
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$conn = mysqli_connect($host, $user, $pass);
if(! $conn )
{
die('Could not connect: ' . mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$sql = 'CREATE Database mydb';

if(mysqli_query( $conn,$sql)){
echo "Database mydb created successfully.";
}else{
echo "Sorry, database creation failed ".mysqli_error($conn);
}
mysqli_close($conn); Output:
?>
Connected successfully
Database mydb created
successfully.
PHP MySQL Create Table
o PHP mysql_query() function is used to create table.
o Since PHP 5.5, mysql_query() function is deprecated.
Now it is recommended to use one of the 2 alternatives.
mysqli_query()
PDO::__query()
PHP MySQLi Create Table Example
Example

<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){
die('Could not connect: '.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$sql = "create table emp5(id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,

emp_salary INT NOT NULL,primary key (id))";
if(mysqli_query($conn, $sql)){
echo "Table emp5 created successfully";
}else{
echo "Could not create table: ". mysqli_error($conn);
} Output:
Connected successfully
mysqli_close($conn); Table emp5 created
?> successfully
PHP MySQL Insert Record
o PHP mysql_query() function is used to insert record in a table.
o Since PHP 5.5, mysql_query() function is deprecated. Now it is
recommended to use one of the 2 alternatives.
mysqli_query()
PDO::__query()
PHP MySQLi Insert Record Example
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){
die('Could not connect: '.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$sql = 'INSERT INTO emp4(name,salary) VALUES ("sonoo", 9000)';

if(mysqli_query($conn, $sql)){
echo "Record inserted successfully";
}else{
echo "Could not insert record: ". mysqli_error($conn);
}
Output:
mysqli_close($conn); Connected successfully
?> Record inserted successfully
PHP MySQL Update Record
• PHP mysql_query() function is used to update record in a table.
• Since PHP 5.5, mysql_query() function is deprecated.
• Now it is recommended to use one of the 2 alternatives.
mysqli_query()
PDO::__query()
PHP MySQLi Update Record Example
Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';

$conn = mysqli_connect($host, $user, $pass,$dbname);

if(!$conn){
die('Could not connect: '.mysqli_connect_error());
}
echo 'Connected successfully<br/>';

$id=2;
$name="Rahul";
$salary=80000;
$sql = "update emp4 set name=\"$name\", salary=$salary where id=$id";
if(mysqli_query($conn, $sql)){
echo "Record updated successfully";
}else{
echo "Could not update record: ". mysqli_error($conn);
Output:
} Connected successfully
mysqli_close($conn); Record updated successfully
?>
 PHP MySQL Select Query

 There are two other MySQLi functions used in select query.

mysqli_num_rows(mysqli_result $result): returns number of
rows.
mysqli_fetch_assoc(mysqli_result $result): returns row as an
associative array.
Each key of the array represents the column name of the table.
It return NULL if there are no more rows.

PHP MySQLi Select Query Example

Example
<?php
$host = 'localhost:3306';
$user = '';
$pass = '';
$dbname = 'test';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(!$conn){
die('Could not connect: '.mysqli_connect_error());
}
echo 'Connected successfully<br/>';
$sql = 'SELECT * FROM emp4'; Output:
$retval=mysqli_query($conn, $sql); Connected successfully
EMP ID :1
if(mysqli_num_rows($retval) > 0){ EMP NAME : ratan
while($row = mysqli_fetch_assoc($retval)){ EMP SALARY : 9000
echo "EMP ID :{$row['id']} <br> ". --------------------------------
"EMP NAME : {$row['name']} <br> ". EMP ID :2
"EMP SALARY : {$row['salary']} <br> ". EMP NAME : karan
"--------------------------------<br>"; EMP SALARY : 40000
} //end of while --------------------------------
}else{ EMP ID :3
echo "0 results"; EMP NAME : jai
} EMP SALARY : 90000
mysqli_close($conn); --------------------------------
 Lab tool
• XAMPP is an abbreviation for cross-platform, Apache,
MySQL, PHP and Perl, and it allows you to build
WordPress site offline, on a local web server on your
computer. This simple and lightweight solution works
on Windows, Linux, and Mac – hence the “cross-
platform” part.
• WAMP is an acronym that stands for Windows,
Apache, MySQL, and PHP. It's a software stack which
means installing WAMP installs Apache, MySQL, and
PHP on your operating system (Windows in the case of
WAMP).
 Individual lab work
• A Chatbot for Students
• Portal for Doctors
• Clothes Recommendation System
• Product Rating System as per the Consumer Reviews
• Automatic Time Table Creation
• Movie Recommendation App
• Image Processing and Generation
• Facebook Application Development
• Online Voting System
• Task management application
• Online survey application
• Online marketplace
• Fake Review Identification