Database Operations

Unit - V

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

1
MHSSP
 5.1 Introduction to MySQL

• MySQL is an open-source relational database management system.

• Its name is a combination of “My”, the name of co-founder Michael

Widenius’s daughter, and “SQL”, the abbreviation for Structured
Query Language.

• It is written in C and C++.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

2
MHSSP
 5.1 Introduction to MySQL
• Create a Database: Open XAMPP Control panel and start MySQL
service, then click on Admin button to open phpMyAdmin user
interface.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

3
MHSSP
 5.1 Introduction to MySQL
• Create a Database: In order to work with database, click on the Database tab.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

4
MHSSP
 5.1 Introduction to MySQL
• Create a Database: now you should see the option to create a Database, Write
the Database name and click the ‘Create’ button. By default the host name is
‘localhost’, MySQL user is ‘root’ and have no password.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

5
MHSSP
 5.1 Introduction to MySQL
• Create a Table: To create a table, first select the database and then click on
the ‘Structure’ tab. Below the list at the bottom of the page you will see create
table wizard, add the table name and total number of columns you need and click
the ‘Go’ button.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

6
MHSSP
 5.1 Introduction to MySQL
• Set password to phpMyAdmin:
• For phpMyAdmin, by default the username is ‘root’ and the password remains
empty.

• To change the password, go to phpMyAdmin home page.

• Click on ‘User Accounts’ option at the top of the page.

• Now click the ‘Edit Privilidges’ under ‘Actions’ option for the username ‘root’
and Hostname ‘localhost’.

• Now choose the third tab ‘Change password’ and type your password in the
provided field, retype the password to confirm it and then finally click on the
‘Go’ key to conclude the process.
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
7
MHSSP
 5.1 Introduction to MySQL
• Set password to phpMyAdmin:

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

8
MHSSP
 5.2 Connecting to a MySQL Database
• MySQL : MySQLi : PDO
• These are the APIs of PHP to access MySQL databases and tables.

• Developers can choose either of them for their project.

• MySQL was the main extension that was designed to help PHP
applications send and receive data from MySQL database.

• However MySQL has been deprecated and removed as of PHP7 and

its newever versions.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

9
MHSSP
 5.2 Connecting to a MySQL Database
• MySQL : MySQLi : PDO
• In MySQLi, i stands for Improved. It is an improved version of MySQL
with procedural and object oriented approach.

• PDO (PHP Data Objects): the main advantage of using PDO is that it
supports, and provides a uniform method of access to 11 different
databases.

• PDO supported databases are:

CUBRID, MS SQL Server, Firebird/Interbase, IBM, Informix, MySQL,
Oracle, ODBC and DB2, PostgreSQL, SQLite, 4D

• However PDO doesn’t allow the usage of all features available in present
version of the MySQL server.
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
10
MHSSP
 5.2 Connecting to a MySQL Database
• MySQL : MySQLi : PDO
Parameters MySQL MySQLi PDO
Connection $connection_link = $mysqli_db = new mysqli('host', $pdo = new PDO('mysql:host=host;
mysql_connect("host", "username", 'username', 'password', dbname=database_name;
"password"); 'database_name'); charset=utf8',
'username', 'password');
mysql_select_db("database_name",
$connection_link);

mysql_set_charset('UTF-8',
$connection_link);
Error Error handling in MySQL is not Error handling in MySQLi is a bit PDO has the best error handling
Handling considered to be a good approach. easier. methods. It also provides error
modes for error handling
Data Fetching General programming loops such as Same as MySQL, code however will PDO provides many built-in
for, or while can be used in MySQL. be a bit different. statements: fetchAll(),
fetchColumn() etc.
API support MySQL provides a ProceduralPrepared
way. By: Khan
MySQLi provides
Mohammed both
Zaid, Lecturer, Procedural
Comp. Engg., as PDO provides an Object Oriented
11
well as MHSSP
Object Oriented way approach
 Functions to fetch data from Database
• mysqli_fetch_row()

• mysqli_fetch_assoc()

• mysqli_fetch_array()

• mysqli_fetch_object()

• mysqli_fetch_lengths()

• mysqli_fetch_field()

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

12
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_row():

• This function will fetch data about the single row with which the row pointer
currently exists.

• After fetching the entire row details, it will be returned as an array with
number indices corresponding to the MySQL field offset.

• If no results found for the query, then mysqli_fetch_row() will return NULL.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

13
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_row():

$conn = mysqli_connect("localhost", "root", "test", "blog_samples") or

die("Connection Error: " . mysqli_error($conn));
$query = "SELECT * from Users"; $result = mysqli_query($conn, $query) or
die(mysqli_error($conn));
$row = mysqli_fetch_row($result);
print "<pre>";
print_r($row);
print "<pre>";
Output:Array([0] => 1[1] => admin[2] => admin123[3] => student
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
14
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_assoc():
• This function is similar to the mysqli_fetch_row(), except that, it will return an array
of row information containing column values are indexed with the column name.
• So the result type is an associative array where each column name and values of a
single row are associated together as name, value pairs.
Output: Array
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
15
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_array():

• This MySQL fetch method returns resultant array with both indices.

• That is, field offset and field name. So, it would be used most probably by
having both option of indexing.

• mysqli_fetch_array() accepts an optional argument for specifying resultant

array index type and its possible values are,

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

16
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_array():

• MYSQLI_BOTH – It is the default value that would be taken if no second

argument is provided for this function. It will provide resultant array with both
indices.

• MYSQLI_NUM – With this option, mysqli_fetch_array() will return array with

offset indices as same as mysqli_fetch_row().

• MYSQLI_ASSOC – With this option, mysqli_fetch_array() will return array with

name indices as same as mysqli_fetch_assoc().

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

17
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_array():
Array
(
[0] => 1
[user_id] => 1
[1] => admin
[user_name] => admin
[2] => admin123
[password] => admin123
[3] => student
[user_type] => student
)
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
18
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_object():

• mysqli_fetch_object() function will return MySQL data with same structure as

returned by mysqli_fetch_assoc(), but its type is different.

• mysqli_fetch_object() returns object where as mysqli_fetch_assoc() returns

array.

• So, the way of accessing these data will also be differed.

echo $row->user_name;

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

19
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_lengths():

• It is used to returns the length of the fields in the result.

• It returns an array of integer that represents the size of each column or FALSE
if fails.

• Parameter:
Result: It specifies the result set identifier returned by mysqli_query(),
mysqli_store_result() or mysqli_use_result()

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

20
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_lengths():
<?php
$link = mysqli_connect("localhost","my_user", "my_password", "world");
$query = "SELECT * from Country ORDER BY Code LIMIT 1";
if ($result = mysqli_query($link, $query)) {
$row = mysqli_fetch_row($result);
foreach (mysqli_fetch_lengths($result) as $i => $val) {
printf("Field %2d has Length %2d\n", $i+1, $val);
}

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

21
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_field():

• It is used to retrieve the next field in the result set.

• Returns the definition of one column of a result set as an object. Call this
function repeatedly to retrieve information about all columns in the result set.

• Returns an object which contains field definition information or false if no

field information is available.

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

22
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_field():

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

23
MHSSP
 Functions to fetch data from Database
• mysqli_fetch_field():
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$query = "SELECT Name, SurfaceArea from Country ORDER BY Code LIMIT 5";
if ($result = mysqli_query($link, $query)) {
while ($finfo = mysqli_fetch_field($result)) {
printf("Name: %s\n", $finfo->name);
printf("Table: %s\n", $finfo->table);
printf("max. Len: %d\n", $finfo->max_length);
printf("Flags: %d\n", $finfo->flags);
printf("Type: %d\n\n", $finfo->type);
}
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
24
MHSSP
 5.2 Connecting to a MySQL Database
<html>
<head>
<title>Login Form</title> Login.php
</head>
<body>
<form name="login_form" action="login_process.php" method="post">
<p><label>Username: </label>
<input type="text" name="username" id="username"/></p>
<p><label>Password: </label>
<input type="password" name="pass" id="pass"/></p>
<p><input type="submit" id='btn' value="Login"></p>
</form></body>
</html>
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
25
MHSSP
 5.2 Connecting to a MySQL Database
<?php
//Accept values from Login page
Login_process.php
if($_SERVER['REQUEST_METHOD']=='POST')
{
if(!empty($_POST['username']))
{
$username=$_POST['username'];
}
if(isset($_POST['pass']))
{
$password=$_POST['pass'];
}
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
26
MHSSP
 5.2 Connecting to a MySQL Database
//Connecting to Server and Database
$con=mysqli_connect('localhost','root','','practical') or
die("connection not established");
Login_process.php

//Creating query and executing it

$com=mysqli_query($con,"select * from login where

username='$username' and password='$password'") or die("Failed to
query Table".mysqli_error($con));
$row=mysqli_fetch_array($com);
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
27
MHSSP
 5.2 Connecting to a MySQL Database
//Checking if executed query has returned anything?
if($row)
{
echo"Login Successful... Welcome ".$row['username'];Login_process.php
}
else
{
echo"Incorrect username or password";
}
mysqli_close($con);
}
?>
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
28
MHSSP
5.2 Connecting to a MySQL Database

Login.php Login_process.php

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

29
MHSSP
 5.3 Database Operations: Insert Data
<?php
if($_SERVER['REQUEST_METHOD']=='POST')
{ if(!empty($_POST['username']))
{ $username=$_POST['username']; }
if(!empty($_POST['pass']))
{ $password=$_POST['pass']; }
}
$con=mysqli_connect('localhost','root','','practical');
mysqli_query($con,"insert into login (username, password) values ('$username','$password')");
if($con)
echo"Records inserted successfully";
else
echo"Request cannot be completed: ".mysqli_error($con);
mysqli_close($con);
?>
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
30
MHSSP
5.3 Database Operations: Insert Data

Insert.html Register_process.php

Login table

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

31
MHSSP
5.3 Database Operations: Retrieve Data
<?php
$con=mysqli_connect('localhost','root','','practical') or die("Connection Failed");
$result=mysqli_query($con,"select username, password from login");
while($row=mysqli_fetch_assoc($result))
{
echo"User name: ".$row['username'];
echo"<br>";
echo"Password : ".$row['password'];
echo"<br><br>";
}
mysqli_close($con);
?>
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
32
MHSSP
5.3 Database Operations: Retrieve Data

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

33
MHSSP
 5.4 Database Operations: Update Data
<?php
if($_SERVER['REQUEST_METHOD']=='POST')
{ if(!empty($_POST['username']))
{ $username=$_POST['username'];
}
if(!empty($_POST['oldpass']))
{ $oldpassword=$_POST['oldpass'];
}
if(!empty($_POST['newpass']))
{ $newpassword=$_POST['newpass'];
}
}
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
34
MHSSP
 5.4 Database Operations: Update Data
$con=mysqli_connect('localhost','root','','practical') or die("Connection Failed..");
$result=mysqli_query($con,"update login set password='$newpassword' where
username='$username' and password='$oldpassword'");
if(!$result)
{ echo"Could not update“; }
else
{ echo"Password changed successfully..<br><br>“; }
$result=mysqli_query($con,"select password from login where
username='$username'");
$row=mysqli_fetch_row($result);
echo"you new password is : ".$row[0];
?>
Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
35
MHSSP
5.4 Database Operations: Update Data

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

36
MHSSP
 5.4 Database Operations: Delete Data
<?php
if($_SERVER['REQUEST_METHOD']=='POST')
{ if(!empty($_POST['username']))
{ $username=$_POST['username']; }
}
$con=mysqli_connect('localhost','root','','practical') or die("Connection Failed..");
$result=mysqli_query($con,"delete from login where username='$username'");
if(!$result)
{ echo"Could not delete“; }
else
{ echo"Record deleted successfully..<br><br>“; }
$result=mysqli_query($con,"select username, password from login");
while($row=mysqli_fetch_assoc($result))
{
echo"<br>User name : ".$row['username'];
echo"<br>Password : ".$row[‘password'];
}
mysqli_close($con);
?> Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,
37
MHSSP
5.4 Database Operations: Delete Data

Prepared By: Khan Mohammed Zaid, Lecturer, Comp. Engg.,

38
MHSSP