Unit 5

Database Operations
What is MySQL?
1. MySQL is a database system used on the web
2. MySQL runs on a server
3. MySQL is ideal for both small and large applications
4. MySQL is very fast, reliable, and easy to use
5. MYSQL is a main component of web application softwares like
XAMPP,LAMP & WAMP
6. MySQL is used by many database-driven web applications which
includes Drupal, WordPress etc.
7. MySQL compiles on a number of platforms
8. MySQL is free to download and use
9. MYSQL is multiuser database system.
10. MySQL is developed, distributed, and supported by Oracle Corporation
11. The data in a MySQL database are stored in tables. A table is a
collection of related data, and it consists of columns and rows.
12. Databases are useful for storing information categorically.
 Features of MYSQL
• Free to download
• Ease of management
• Follows client -server architecture
• Compatible on many operating systems
• Robust transactional support
• High performance
• Free of cost
• Secure
• High availability
• Scalability
• High flexibility
• Speed of execution is Fast
Data types in MYSQL
 Steps to create database using PHP
• Steps using PHP Code:
• Creating database: With CREATE DATABASE query

• Step 1: Set variables with values for servername,

username,password.
• Step 2: Set connection object by passing servername,
username,password as parameters.
• Step 3: Set query object with the query as "CREATE DATABASE
dept";
• Step 4: Execute query with connection object.
 Steps to create database
• Steps using phpMyAdmin
• Step 1: Click on Start and select XAMPP from the list. The Control
Panel is now visible and can be used to initiate or halt the working
of any module.

• Step2: Click on the "Start" button corresponding to Apache and

MySQL modules.

• Step 3: Now click on the "Admin" button corresponding to the

MySQL module. This automatically redirects the user to a web
browser to the following address - http://localhost/phpmyadmin
• Step 4: Screen with multiple tabs such as Database, SQL, User Accounts,
Export, Import, Settings, etc. will appear. Click on the "Database" tab. Give
an appropriate name for the Database in the
first textbox and click on create option.

• Step 5 : In the created Database, click on the 'Structure' tab. Towards the
end of the tables list, the user will see a 'Create Table' option.
• Give appropriate "Name" and "Number of Columns" for table and click on
'Go' button.

• Step 6 : Give details of columns based on their type. Enter the names for
each column, select the type, and the maximum length allowed for the
input field. Click on "Save" in the bottom right corner. The table with the
initialized columns will be created
 Database Related Commands
• 1. Creating a Database
• We can create a database using the CREATE DATABASE
statement. But, if database already exits, it throws an error. To
avoid the error, we can use the IF NOT EXISTS option with the
CREATE DATABASE statement.
• Syntax: CREATE DATABASE <database name>;
• We can use created database for use using MySQL USE
command
• USE <database name>;
• 2. Select Databases

• SELECT Database is used in MySQL to select a particular

database to work with, when multiple databases are available
with MySQL Server.

• Syntax: USE database_name;

• 3. Drop Database

• We can drop/delete/remove a MySQL database easily with

the MySQL DROP DATABASE command.
• It deletes all the tables of the database along with the
database permanently.
• We should be careful while deleting any database because we
will loose all the data available in the database.

• Syntax: DROP DATABASE database_name;

 Table Related Commands
• A) Creating a Table

• In order to create a table we have to choose an appropriate

database for operation using USE command.
• Table can be created using CREATE TABLE command into the
database and by mentioning the fields with its type.
• Syntax:
• CREATE TABLE [IF NOT EXISTS] <table name> (<field
name>dataType [optional parameters] ) ENGINE storage
Engine;
• B) Describe Command

• For checking whether your new table has been created , we can use DESCRIBE
command
• Syntax:
• DESCRIBE<table name>;

• Details of Describe table:

• 1. Field: The name of each field or column within a table.
• 2. Type: The type of data being stored in the field.
• 3. Null: Whether a field is allowed to contain a value of NULL.
• 4.Key: MySQL supports keys or indexes, which are quick ways to look up and
search for data.
• 5. Default : The default value that will be assigned to the field if no value is
specified when a new row is created.
• 6. Extra: Additional information, such as whether a field is set to auto-
increment.
• C) Adding Data to a Table

• INSERT command can be used to add data in a table.

• Syntax:
• INSERT INTO <table name> (column_1,column_2,...) VALUES
(value_1,value_2,...);
• D) Deleting a Table

• Deleting a table is very easy.

• Table can be deleted using DROP TABLE command

• Syntax:
• DROP TABLE <table name>;
 Querying a MySQL Database
• 1. SELECT Command

• The SELECT command is used to extract data from a table.

• Syntax:
• SELECT something FROM <tablename>;
• 2.DELETE Command

• A row from a table can be removed using DELETE command.

• The syntax is similar to the SELECT command and allows you
to narrow down the exact row or rows to delete using
qualifiers such as WHERE and LIMIT.

• 3.WHERE Command

• The WHERE keyword enables you to narrow down queries by

returning only those where a certain expression is true.
• PHP Connect to MySQL
• PHP 5 and later can work with a MySQL
database using:

• MySQLi extension (the "i" stands for improved)

• PDO (PHP Data Objects)
• MySQLi will only work with MySQL databases.
• PDO will work on 12 different database systems.
 mysqli_connect() Function
• The mysqli_connect() function is used to connect with MySQL database. It returns resource if
connection is established or null.
• Syntax: mysqli_connect(server, username, password)

• Example: For welcome.html file and it has a text field and submit button.
• <?php
• $host = 'localhost';
• $user =‘ ’;
• $pass = ‘ ';
• $conn = mysqli_connect($host, $user, $pass);
• if(! $conn)
• {
• die('Could not connect: ‘ .mysqli_error());
• }
• echo 'Connected successfully...';
• mysqli_close($conn);
• ?>
• Output:Connected successfully
 mysqli_close()
• The mysqli_close() function is used to close
the database connection.
• Syntax:
• mysqli_close (Connection)

• Example:
• mysqli_close ($conn)
 Connect MYSQL database with PHP Using MySQLi Object Interface

• <?php
• $servername = "localhost";
• $username = "root";
• $password = “ ";
• $conn = new mysqli($servername, $username, $password);
• if ($conn->connect_error) {
• die("Connection failed: " . $conn->connect_error);
• }
• echo "Connected successfully";
• mysqli_close($conn);
• ?>
• mysqli_connect_error() function
• returns the error description from the last
connection error, if any.

• Syntax-
• mysqli_connect_error();
 Program to connect PHP with MYSQL

• Solution1:
<?php
$servername = "localhost";
$username = "root";
$password = "";
// Connection
$conn = new mysqli($servername,$username, $password);
// For checking if connection is successful or not
if ($conn->connect_error)
{
die("Connection failed: ". $conn->connect_error);
}
echo "Connected successfully";
?>
• Output:
• Connected successfully
 Program to create database
<?php
$servername = "localhost";
$username = "root";
$password = “ ";
$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "CREATE DATABASE ifdept";
if ($conn->query($sql) === TRUE)
{
echo "Database created successfully";
}
else
{
echo "Error creating database: " . $conn->error;
}
$conn->close ();
?>
 Program to connect PHP with MYSQL
• Solution2:
• Create login.php
<?php
$hostname = 'localhost';
$username = 'root';
$password = ‘ ';
?>
• Create db2.php file
<?php
require_once 'login.php';
$conn = new mysqli($hostname, $username, $password);
//if ($conn->connect_error) die($conn->connect_error);
if ($conn->connect_error) {
die("Connection failed: "
. $conn->connect_error);
}
echo "Connected successfully";
?>
• Output:
• Connected successfully
• PHP mysqli _connect() Function
• mysqli_connect() function opens a new connection to the
MySQL server.
• mysqli_connect(host, username, password, dbname, port,
socket)
Parameter Values

Parameter Description
host Optional. Specifies a host name or an IP address
username Optional. Specifies the MySQL username
password Optional. Specifies the MySQL password
dbname Optional. Specifies the default database to be used
port Optional. Specifies the port number to attempt to
connect to the MySQL server
socket Optional. Specifies the socket or named pipe to be
used
 Explanation:

• The first part of the script is three variables (server name,

username, and password) and their respective values.
• These values should correspond to your connection details.
• Next is the main PHP function mysqli_connect(). It establishes a
connection with the specified database.
• When the connection fails, it gives the message Connection failed.
• The die function prints the message and then exits out of the script
• If the connection is successful, it displays “Connected
successfully.”
• When the script ends, the connection with the
database also closes.
• If you want to end the code manually, use the mysqli_close
function.
 Database Operations
• 1.Insert Operation
• Data can be inserted into an existing database
table with an INSERT INTO query with
appropriate values.
• After that we will execute this insert query
through passing it to the PHP query() function
to insert data in table.
• For example a student data is inserted into a
table using INSERT INTO statement.
<?php
require_once 'login.php';
$conn=new mysqli($sn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
$query= "INSERT INTO student(rollno, name, percent) VALUES
(‘1101', ‘Kimaya',95.40)";
$result =$conn->query($query);
if (!$result) die ("Database access failed: ". Sconn->error);
?>
• mysqli_query() is used to insert record in a
table. Data can be entered into MySQL tables
by executing SQL INSERT statement through
PHP function mysqli_query.
 Program to insert data
• <?php
• $host = 'localhost’;
• $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 (“ Yash", 90000);

• if(mysqli_query($conn, $sql))
• {
• echo "Record inserted successfully...";
• }
• else
• {
• echo "Could not insert record: ". mysqli_error($conn);
• }
• mysqli_close($conn);
• ?>
• Output:Connected successfully...
• Record inserted successfully…
• 2 Retrieving the Query Result

• The data can be retrieved from the table using SQL SELECT
statement which is used to select the records from database tables.
• SQL query using the SELECT statement will be executed by passing
this SQL query to the PHP query() function to retrieve the table
data.
• There are two other mySQLi functions used in select query.
• 1. mysqli_num_rows(mysqli_result $result ) which returns number
of rows.
• 2. mysqli_fetch_assoc(mysqli_result $result) which 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.
• Example: select_sample.php
• <?php
• require_once 'login.php';
• $conn = new mysqli($sn, $un, $pw, $db);
• if ($conn->connect_error) die(Sconn->connect_error);
• $query = "SELECT * FROM student";
• $result=$conn->query($query);
• if (!$result) die ("Database access failed:", Sconn->error);
• $rows =$result->num rows;
• echo "<table border=1><tr><th>Roll No.</th> <th>Name</th> <th>Percentage</th></tr>";
• for ($j = 0; $j<$rows; ++$j)
• {
• $result->data_seek($j);
• $row =$result->fetch_array(MYSQLI_NUM);
• echo "<tr>";
• for ($k=0: $k<3; ++$k) echo "<td>$row[$k]</td>";
• echo "</tr>";
• }
• echo "</table>";
• ?>
 Program for retrieving data
• <?php
• $host = 'localhost';
• $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';
• $retval=mysqli_query($conn, $sql);
• if(mysqli_num_rows($retval) > 0){
• while($row = mysqli_fetch_assoc($retval)){
• echo "EMP ID : {$row['id']} <br> ".
• "EMP NAME: {$row['name']} <br> ".
• "EMP SALARY: {$row['salary']} <br> ".
• “----------------------------------------<br>”;
• }//end of while
• }else{
• echo “0 results";
• }
• mysqli_close($conn);
• Output:
• Connected successfully...
• EMP ID :1
• EMP NAME:Yash
• EMP SALARY:90000
• ---------------------------------------
 Inserting and retrieving the query result operations
• <?php
• $con = mysqli_connect('localhost', 'root', ‘ ', 'class');
• # Connecting to Database
• $query = "insert into user values(1, 'Amit')";
• # Inserting Values
• $result = mysqli_query($con, $query);
• if($result) {
• echo 'Insertion Successful <br>';
• }
• else {
• echo 'Insertion Unsuccessful <br>';
• }
• $query = "select * from user";
• # Retrieving Values
• $result = mysqli_query($con, $query);
• foreach($result as $r) {
• echo $r['roll_number'].' '.$r['name'];
• }
• ?>
• Output
• Insertion Successful
• 1 Amit
• Explanation
• The above code connects with a database named
“class”.
• The exam database has a table named „user‟
with 2 columns roll_number and name.
• It executes an insert query on the user and checks
whether the insertion was successful or not.
• It executes a select query on the user and displays
the information retrieved.
 Queries to update and delete data in the database

• Update data : UPDATE query

• UPDATE command is used to change / update new value for field in

• row of table. It updates the value in row that satisfy the criteria given in
query.

• The UPDATE query syntax:

• UPDATE Table_name SET field_name=New_value WHERE
field_name=existing_value

• Example :
• UPDATE student SET rollno=4 WHERE name='abc'
• In the above query, a value from rollno field from student table is
• updated with new value as 4 if its name field contains name as ‘abc’.
 Updating data in database table

• Update data :
• <?php
• $servername = "localhost";
• $username = "root";
• $password = “ ";
• $dbname = "ifdept";
• $conn = new mysqli($servername, $username, $password,
• $dbname);
• if ($conn->connect_error)
• {
• die("Connection failed: " . $conn->connect_error);
• }
• $sql = "UPDATE student SET rollno=4 WHERE
• name='abc'";
• if ($conn->query($sql) === TRUE)
• {
• echo "Record updated successfully";
• } else
• {
• echo "Error updating record: " . $conn->error;
• }
• $conn->close();
• ?>
 Delete data: DELETE query
• DELETE command is used to delete rows that are no longer required
from the database tables. It deletes the whole row from the table.

• The DELETE query syntax:

• DELETE FROM table_name WHERE some_column = some_value

[WHERE condition] is optional. The WHERE clause specifies which

record or records that should be deleted. If the WHERE clause is not
used, all records will be deleted.

• Example :-
• $sql = "DELETE FROM student WHERE rollno=2";

• In the above query, a row from student table is deleted if rollno field
contains 2 in that row
 Program for Delete operation of PHP on table data

• Below is a simple example to delete records into the student.pridata table.

To delete a record in any table it is required to locate that record by using
a conditional clause. Below example uses name to match a record in
student.pridata table.
• Example:-
• Assume Following Table
• Database Name-student
• Table name – pridata
• <?php
• $server='localhost';
• $username='root';
• $password=‘ ';
• $con=mysqli_connect ($server,$username,$password);
• if(!$con){
• die("Connection to this database failed due to"
• .mysqli_connect_error($mysqli));
• }
• $sql="DELETE FROM student.pridata WHERE name='amit'";
• if($con->query($sql)==true){
• echo "Record deleted successfully";
• }
• else{
• "ERROR:error".$con->error();
• }
• $con->close();
• ?>
• Output:
• Record deleted successfully
END