Chapter Six

Manipulating MySQL Databases with PHP

 What is Database?
 A database is a separate application that stores a collection of data.
 Relational Database Management Systems (RDBMS) is used to store data in tables
and relations are established using primary keys or other keys known as foreign
keys.
 MySQLi is one of the most popular relational database system being used on the
Web today.
» It is freely available and easy to install, however if you have installed
Wampserver/Xampp server it already there on your machine.
 MySQL database server offers several advantages:
» MySQLi is easy to use, yet extremely powerful, fast, secure, and scalable.
» MySQLi runs on a wide range of operating systems, including UNIX or Linux, Microsoft Windows,
Apple Mac OS X, and others.
» MySQLi supports standard SQL (Structured Query Language).
» MySQLi is ideal database solution for both small and large applications.
» MySQLi is developed, and distributed by Oracle Corporation.

2
 PHP Connect to MySQL
 The first step for interacting with MySQL connecting to the
server requires the appropriately named mysqli_connect( )
function:
» $dbc = mysqli_connect (hostname, username, password,
db_name);
 The first three arguments sent to the function (hostname,
username, and password) are based upon the users and
privileges established within MySQL.
 The fourth argument is the name of the database to use. This is
the equivalent of saying USE databasename within the mysql
client.
 If the connection was made, the $dbc variable, short for
database connection (but you can use any name you want, of
course). 3
 Connecting…

 Most of the PHP functions for working with MySQL will take
this variable as its first argument.
 If a connection problem occurred, you can call
mysqli_connect_error( ), which returns the connection error
message.
 It takes no arguments, so would be called using just
» mysqli_connect_error( );
 Set the MySQL host, username, password, and database
name as constants:
» DEFINE ('DB_USER', 'username');
» DEFINE ('DB_PASSWORD', 'password');
» DEFINE ('DB_HOST', 'localhost');
» DEFINE ('DB_NAME', 'sitename');
4
 Connecting…

PHP 5 and later can work with a MySQL database using:

» MySQLi extension (the "i" stands for improved)
» PDO (PHP Data Objects)
 PDO will work on 12 different database systems, where as MySQLi will only
work with MySQL databases.
 So, if you have to switch your project to use another database, PDO makes the
process easy.
» You only have to change the connection string and a few queries.
 With MySQLi, you will need to rewrite the entire code queries included.
 Both are object-oriented, but MySQLi also offers a procedural API.

There are three ways of style to connect PHP with MySQL:

» MySQLi (object-oriented)
» MySQLi (procedural)
» PDO

5
 PHP Connect to MySQL…
Example: MySQLi Object-Oriented
 PHP provides mysqli function to open a database connection.

Syntax: mysqli(server,username,password);
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

6
 PHP Connect to MySQL
Example: MySQLi Procedural
 PHP provides mysqli_connect function to open a database
connection.
Syntax: mysqli_connect(servername,username,password);
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
7
 PHP Connect to MySQL
Example: PDO (PHP Data Object)
 PDO will work on 12 different database systems.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username,
$password);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>

8
 Open a Connection to MySQL
Database( summary)
 Before we can access data in the MySQL database, we need to be
able to connect to the server.
 PHP provides mysqli(), mysqli_connect() and PDO() function to
open a database connection.
Syntax example: $conn=mysqli_connect(server, user, password);
 Server (Optional) :The host name running database server. If not
specified then default value is localhost.
 User( Optional): The username accessing the database. If not
specified then default is the name of the user that owns the server
process. E.g root
 Password(Optional ):The password of the user accessing the
database. If not specified then default is an empty password.
9
 Closing a connection
 The connection will be closed automatically when the script ends.
 You can disconnect from MySQL database anytime using the
following PHP function.
$conn->close(); - for object oriented
mysqli_close($conn); - for procedural
$conn = null; - for PDO

10
Example: Connecting MySQL Server
<html><head>
<title>Connecting MySQL Server</title>
</head><body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ){
die('Could not connect: ' . mysqli_connect_error());
}else{
echo 'Connected successfully <br />';
}
mysqli_close($conn); //close connection
?> </body></html> 11
 Create a MySQL Database
 The CREATE DATABASE statement is used to create a database in MySQL.
 Example: MySQLi Object-oriented
<?php // Create database
$servername = "localhost"; $sql = "CREATE DATABASE MAUDB";
$username = "root"; if ($conn->query($sql) === TRUE) {
$password = ""; echo "Database created successfully";
} else {
// Create connection echo "Error creating database: " .
$conn = new mysqli($servername, $conn->error;
$username, $password); }
// Check connection $conn->close();
if ($conn->connect_error) { ?>
die("Connection failed: " . $conn-
>connect_error);
}
12
 Create a MySQL Database
 Example: MySQLi Procedural

<?php // Create database

$servername = "localhost"; $sql = "CREATE DATABASE MAUDB";
$username = "root"; if (mysqli_query($conn, $sql)) {
$password = ""; echo "Database created successfully";
} else {
// Create connection echo "Error creating database: " .
$conn = mysqli_error($conn);
mysqli_connect($servername, }
$username, $password);
// Check connection mysqli_close($conn);
if (!$conn) { ?>
die("Connection failed: " .
mysqli_connect_error());
}
13
 Create a MySQL Database
 Example: PDO
// Create database
<?php
$sql = "CREATE DATABASE MAUDB";
$servername = "localhost";
// use exec()
$username = "root";
$conn->exec($sql);
$password = "";
echo "Database created
try {
successfully<br>";
//connection creation
} catch(PDOException $e) {
$conn = new
echo $sql . "<br>" . $e-
PDO("mysql:host=$servername",
>getMessage();
$username, $password);
}
$conn = null;
 A great benefit of PDO is that it has exception
?> class 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.
14
 Drop a MySQL Database
<html> // drop database
<head> $sql = "drop DATABASE MAUDB";
<title>Creating MySQL if (mysqli_query($conn, $sql)) {
Database</title> echo "Database drop successfully";
</head> } else {
<body> echo "Error creating database: " .
<?php mysqli_error($conn);
$dbhost = 'localhost'; }
$dbuser = 'root'; mysqli_close($conn);
$dbpass = ''; ?></body> </html>
$conn = mysqli_connect($dbhost,
$dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' .
mysqli_connect_error());
} 15
 Selecting a Database using PHP
 Once you get connection with MySQL server, it is required
to select a particular database to work with.
 This is because there may be more than one database
available with MySQL Server.
 PHP provides mysqli_select_db() function to select a
database.
» It returns TRUE on success or FALSE on failure.
Syntax: bool mysqli_select_db(connection ,db_name);
 db_name- MySQL Database name to be selected
 connection - opened connection.

16
Selecting a Database using PHP Example

<?php //select database

$dbhost = 'localhost'; $seldb=mysqli_select_db($conn,’MAUDB')
$dbuser = 'root'; ;
$dbpass = ''; if(! $seldb )
$conn = {
mysqli_connect($dbhost, die('Could not select a database: ' .
$dbuser, $dbpass); mysqli_connect_error());
if(! $conn ){ }
die('Could not connect: ' . else
mysqli_connect_error()); } echo 'Database MAUDB selected<br />';
mysqli_close($conn);
?>

17
 PHP MySQL Create Table
 A database table has its own unique name and consists of columns
and rows.
 The CREATE TABLE statement is used to create a table in
MySQL.
 We will create a table named " student ", with five columns: "id",
"firstname", "lastname", "email" and "reg_date":
$sql=“CREATE TABLE student (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
) ”;
18
 PHP MySQL Create Table
After the data type, you can specify other optional attributes for each column:
 NOT NULL - Each row must contain a value for that column, null values are
not allowed.
 DEFAULT value - Set a default value that is added when no other value is
passed.
 UNSIGNED - Used for number types, limits the stored data to positive
numbers and zero.
 AUTO INCREMENT - MySQL automatically increases the value of the field
by 1 each time a new record is added.
 PRIMARY KEY - Used to uniquely identify the rows in a table. The column
with PRIMARY KEY setting is often an ID number, and is often used with
AUTO_INCREMENT.
 Each table should have a primary key column (in this case: the "id" column). Its
value must be unique for each record in the table.

19
 PHP MySQL Create Table Example
 Example : MySQLi Object-oriented
<?php // sql to create table
$servername = "localhost"; $sql="CREATE TABLE student (
$username = "root"; id INT(6) UNSIGNED
AUTO_INCREMENT PRIMARY KEY,
$password = "";
firstname VARCHAR(30) NOT NULL,
$dbname = “MAUDB"; lastname VARCHAR(30) NOT NULL,
// Create connection email VARCHAR(50),
$conn = new mysqli($servername, reg_date TIMESTAMP
$username, $password); ) ";
// Check connection if ($conn->query($sql) === TRUE) {
if ($conn->connect_error) { echo "Table student created successfully";
die("Connection failed: " . $conn- } else {
>connect_error); echo "Error creating table: " . $conn->error;
}
}
$conn->close();
//select DB ?>
mysqli_select_db($conn,
20
$dbname);
 PHP MySQL Create Table Example
 Example : MySQLi Procedural
<?php // sql to create table
$servername = "localhost"; $sql = "CREATE TABLE student (
$username = "root"; id INT(6) UNSIGNED AUTO_INCREMENT
$password = ""; PRIMARY KEY,
$dbname = “MAUDB"; firstname VARCHAR(30) NOT NULL,
// Create connection lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
$conn =
reg_date TIMESTAMP
mysqli_connect($servername, )";
$username, $password); if (mysqli_query($conn, $sql)) {
// Check connection echo "Table student created successfully";
if (!$conn) { } else {
echo "Error creating table: " . mysqli_error($conn);
die("Connection failed: " . } mysqli_close($conn);
mysqli_connect_error()); ?>
}
//select database
mysqli_select_db($conn, 21
 PHP MySQL Create Table Example
 Example : MySQLi Procedural
<?php // sql to create table
$servername = "localhost"; $sql = "CREATE TABLE student (
$username = "root"; id INT(6) UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
$password = ""; firstname VARCHAR(30) NOT NULL,
$dbname = “MAUDB"; lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
try { reg_date TIMESTAMP )";
//Connect and select database // use exec()
$conn = new $conn->exec($sql);
PDO("mysql:host=$servername; echo "Table student created successfully";
dbname=$dbname", $username, } catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
$password); }
$conn = null;
?>

22
 Dropping table using PHP
• It is very easy to drop an existing MySQL table but you need to
be very careful while deleting any existing table because data
lost will not be recovered after deleting a table.
• SQL syntax to drop a table
Syntax: DROP TABLE table_name ;
• To drop an existing table in any database you can use the PHP
mysqli_query() function.
Syntax: mysqli_query(connection ,sql ); // Sql represent drop
table query

23
 Dropping table using PHP Example
 Example : MySQLi Procedural
<?php //Select database
$servername = "localhost"; mysqli_select_db($conn, $dbname);
$username = "root"; // sql to create table
$password = ""; $sql = "drop TABLE student";
$dbname = “MAUDB";
if (mysqli_query($conn, $sql)) {
// Create connection echo "Table student droped
$conn = successfully";
mysqli_connect($servername, } else {
$username, $password); echo "Error creating table: " .
// Check connection mysqli_error($conn);
if (!$conn) { }
die("Connection failed: " . mysqli_close($conn);
mysqli_connect_error()); ?>
}

24
 Inserting data into MySQL table
 After a database and a table have been created, we can start adding data in the
table.
 Here are some syntax rules to follow:
 The SQL query must be quoted in PHP
 String values inside the SQL query must be quoted
 Numeric values must not be quoted
 The word NULL must not be quoted
 To add a new records into MySQL table we can use SQL INSERT INTO
command.
 SQL syntax of INSERT INTO query
Syntax: INSERT INTO table_name ( field1, field2,..fieldN )
VALUES ( value1, value2,...valueN );
 To insert a data in any table you can use the PHP query(), mysqli_query() and
exec() function.
Syntax: mysqli_query( connection,sql ); // Sql represent insert data query
25
 Example: Insert a record in a table
 Example : MySQLi Object-oriented
<?php //Select database
$servername = "localhost"; mysqli_select_db($conn, $dbname);
$username = "root"; //insert data query
$password = ""; $sql = "INSERT INTO student (firstname,
$dbname = “MAUDB"; lastname, email)
VALUES ('John’, Abel',
// Create connection '[email protected]')";
$conn = new if ($conn->query($sql) === TRUE) {
mysqli($servername, $username, echo "New record created
$password); successfully";
// Check connection } else {
if ($conn->connect_error) { echo "Error: " . $sql . "<br>" . $conn->error;
die("Connection failed: " . $conn- }
>connect_error);
} $conn->close();
?>
26
 Example: Insert a record in a table
 Example : MySQLi Procedural
<?php //Select database
$servername = "localhost"; mysqli_select_db($conn, $dbname);
$username = "root"; $sql = "INSERT INTO student (firstname,
$password = ""; lastname, email)
$dbname = “MAUDB"; VALUES ('ahmed', 'abebe',
'[email protected]')";
// Create connection
$conn = if (mysqli_query($conn, $sql)) {
mysqli_connect($servername, echo "New record created
$username, $password); successfully";
// Check connection } else {
if (!$conn) { echo "Error: " . $sql . "<br>" .
die("Connection failed: " . mysqli_error($conn);
mysqli_connect_error()); }
} mysqli_close($conn);
?>
27
 Example: Insert a record in a table
 Example : PDO
<?php $sql = "INSERT INTO student (firstname,
$servername = "localhost"; lastname, email)
$username = "root"; VALUES ('abebe', 'alemu',
$password = ""; '[email protected]')";
$dbname = “MAUDB"; // use exec()
$conn->exec($sql);
try { echo "New record created successfully";
$conn = new } catch(PDOException $e) {
PDO("mysql:host=$servername;d echo $sql . "<br>" . $e->getMessage();
bname=$dbname", $username, }
$password);
$conn = null;
?>

28
 PHP MySQL Insert Multiple Records
 Multiple SQL statements must be executed with the mysqli_multi_query()
function and each SQL statement must be separated by a semicolon.
 Example: MySQLi Object-oriented
<?php $sql = "INSERT INTO student (firstname,
$servername = "localhost"; lastname, email) VALUES ('Hawa', 'Ahmed’,
$username = "root"; '[email protected]');";
$sql .= "INSERT INTO student (firstname,
$password = ""; lastname, email) VALUES ('Helen', 'Mohammed',
$dbname = “MAUDB"; '[email protected]');";
$sql .= "INSERT INTO student (firstname,
// Create connection lastname, email) VALUES ('Abel', 'Ephream',
$conn = new mysqli($servername, '[email protected]')";
$username, $password);
// Check connection if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
if ($conn->connect_error) { } else {
die("Connection failed: " . $conn- echo "Error: " . $sql . "<br>" . $conn->error;
>connect_error); }
}
//select Database $conn->close();
mysqli_select_db($conn,$dbname); ?>
29
 PHP MySQL Insert Multiple Records…..
 Multiple SQL statements must be executed with the mysqli_multi_query()
function and each SQL statement must be separated by a semicolon.
 Example: MySQLi Procedural
$sql = "INSERT INTO student (firstname,
<?php lastname, email) VALUES ('Hawa', 'Ahmed',
$servername = "localhost"; '[email protected]');";
$username = "root"; $sql.= "INSERT INTO student (firstname,
$password = ""; lastname, email) VALUES ('Helen',
$dbname = "junedb"; 'Mohammed', '[email protected]');";
$sql.= "INSERT INTO student (firstname,
// Create connection lastname, email) VALUES ('Abel', 'Ephream',
$conn = mysqli_connect($servername, '[email protected]')";
$username, $password);
// Check connection if (mysqli_multi_query($conn,$sql)) {
if (!$conn) { echo "New records created successfully";
die("Connection failed: " . } else {
$mysqli_connect_error); echo "Error: " . $sql . "<br>" .
} $mysqli_error($conn);
//select database }
mysqli_select_db($conn,$dbname); $conn->close();
?> 30
 PHP MySQL Insert Multiple Records…..
The PDO way is a little bit different, it use different functions.
 Example: PDO
// SQL statements
$conn->exec("INSERT INTO student (firstname,
<?php lastname, email) VALUES ('John', 'Alex',
$servername = "localhost"; '[email protected]')");
$username = "root"; $conn->exec("INSERT INTO student (firstname,
$password = ""; lastname, email) VALUES ('Hayat', 'Ahmed',
$dbname = “MAUDB"; '[email protected]')");
$conn->exec("INSERT INTO student (firstname,
try { lastname, email) VALUES ('Abebe', 'kebede',
'[email protected]')");
$conn = new
PDO("mysql:host=$servername;dbname // commit the transaction/save the transaction
=$dbname", $username, $password); $conn->commit();
// begin the transaction echo "New records created successfully";
$conn->beginTransaction(); } catch(PDOException $e) {
// roll back the transaction if something failed
$conn->rollback();
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
31
Example: Insert a record in a table from HTML form
<html><head>
<title>Insert a record</title>
</head><body>
<?php
// tests if a variable has a value (including 0, FALSE, or an empty string, but not NULL).
if(isset($_POST['add']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = ‘MAUDB';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(!$conn){
die('Could not connect: ' . mysqli_connect_error());
}
 Cont..

if ($_SERVER["REQUEST_METHOD"] == "POST"){
$student_Name = $_POST['student_Name'];
$student_Email = $_POST['student_Email'];
}
$sql = "INSERT INTO student (firstname, email)VALUES
('$student_Name',' ','$student_Email')";
mysqli_select_db($conn,$dbname);
$retval = mysqli_query( $conn,$sql );
if(!$retval ) {
die('Could not enter data: ' . mysqli_connect_error()); }
echo "Data entered successfully\n";
mysqli_close($conn);
} ?>
 Cont..

/* super global variable that returns the filename of the currently

executing script*/
<form method="post" action="<?php $_PHP_SELF ?>">
<p>Name: <input name="student_Name" type="text"></p>
<p>Email: <input name="student_Email" type="email"></p>
<input name="add" type="submit" value="Add Student"></td>
</tr>
</table>
</form>
</body>
</html>
 Example 2: Insert.html
<html><body>
<form action="insert.php" method="post">
<p>Firstname: <input type="text" name="First_name"></p>
<p>Lastname: <input type="text" name="Last_name"></p>
<p>Email: <input type="text" name=“email"></p>
<input type="submit" name="Add_row" value="Register">
</form>
</body></html>
 Insert.php
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($conn,’MAUDB');
$sql="INSERT INTO student(firstname,lastname, email)
VALUES('$_POST[‘First_name’]','$_POST[‘Last_name’]','$_POST[‘email’]')"
;
$retval = mysqli_query( $conn , $sql);
if(!$retval )
{
die('Could not enter data: ' . mysql_connect_error());
}
echo "1 record added";
mysqli_close($conn);
?>
 PHP MySQL Select Data From MySQL
Database
 The SQL SELECT statement is used to select data from one or more tables.
Syntax: SELECT field1, field2,...fieldN FROM table_name1, table_name2.
[WHERE Clause][OFFSET M ][LIMIT N];
 You can use one or more tables separated by comma.
 You can specify star (*) in place of fields. In this case SELECT will return all
the fields.
 You can limit the number of fields returned using LIMIT attribute (used to
limit output when we have large output)
 Assume we want to select records from 1-15 ( inclusive) from a table called
“students”.
E.g. $sql=‘SELECT * FROM student LIMIT 15’; //will return the first 15 records
 What if we want to select records from 10-15 (inclusive)? We can specify by
using OFFSET.
E.g. $sql=‘SELECT * FROM students LIMIT 5 OFFSET 9’; //return 5 records , start
on record 10(OFFSET 9)
37
Select data from DB using MySQLi Object-
oriented
 Selecting data from a MySQL database using MySQLi object-oriented style in
PHP involves… creating a connection to the database, preparing and
executing a SQL SELECT statement, and then processing the results.
<?php $sql = "SELECT id, firstname, lastname,
$servername = "localhost"; email FROM student";
$username = "root"; $result = $conn->query($sql);
$password = ""; if ($result->num_rows > 0) {
$dbname = “MAUDB"; // Output data of each row
// Create connection while($row = $result->fetch_assoc()) {
$conn = new mysqli($servername, echo "id: " . $row["id"]. “, Name: " .
$username, $password, $row["firstname"]. " " . $row["lastname"].
$dbname); " , Email: " . $row["email"]. "<br>";
// Check connection }
if ($conn->connect_error) { } else { echo "0 results";
die("Connection failed: " . $conn- }// Close connection
>connect_error); $conn->close();
} // SQL query to select data ?> 38
 Select data from DB using MySQLi
Procedural
 Using MySQLi in procedural style to select data from a MySQL database
involves similar steps to the object-oriented approach, but the syntax is slightly
different.
// SQL query to select data
<?php $sql = "SELECT id, firstname,
$servername = "localhost"; lastname, email FROM student";
$username = "root"; $result = mysqli_query($conn, $sql);
$password = ""; if (mysqli_num_rows($result) > 0) {
$dbname = "junedb"; // Output data of each row
// Create connection while($row = mysqli_fetch_assoc($result))
$conn = {
mysqli_connect($servername, echo "id: " . $row["id"]. " , Name: " .
$username, $password, $row["firstname"]. " " . $row["lastname"].
$dbname); " , Email: " . $row["email"]. "<br>";
// Check connection }} else { echo "0 results";}
if (!$conn) { mysqli_close($conn); // Close connection
die("Connection failed: " . ?>
mysqli_connect_error()); 39
 Select data from DB using MySQLi
Procedural
 You can also put the result in an HTML table:
<?php if (mysqli_num_rows($result) > 0) {
$servername = "localhost"; // Output data of each row
$username = "root";
while($row = mysqli_fetch_assoc($result)) {
$password = "";
$dbname = "junedb"; echo "<tr>";
// Create connection echo "<td style='border: 1px solid black; padding:
$conn = mysqli_connect($servername, $username, 8px;'>".$row["id"]."</td>";
$password, $dbname); echo "<td style='border: 1px solid black; padding:
// Check connection 8px;'>".$row["firstname"].“
if (!$conn) {
".$row["lastname"]."</td>";
die("Connection failed: " . mysqli_connect_error());
} echo "</tr>";
// SQL query to select data }
$sql = "SELECT id, firstname, lastname, email FROM } else {
student"; echo "<tr><td colspan='2' style='border: 1px solid black;
$result = mysqli_query($conn, $sql); padding: 8px; text-align: center;'>0 results</td></tr>";
// Start HTML output
}
echo "<html><body>";
echo "<table style='border-collapse: collapse; border: echo "</table>";
1px solid black;'>"; echo "</body></html>";
echo "<tr><th style='border: 1px solid black; padding: // Close connection
8px;'>ID</th> mysqli_close($conn);
<th style='border: 1px solid black; padding: ?>
8px;'>Name</th></tr>";

40
 Select data from DB using PDO
<?php if ($stmt->rowCount() > 0) {
$servername = "localhost"; // Output data of each row
$username = "root"; while ($row = $stmt-
>fetch(PDO::FETCH_ASSOC)) {
$password = "";
echo "<tr>";
$dbname = "junedb"; echo "<td style='border: 1px solid black;
try { padding: 8px;'>".$row["id"] . "</td>";
// Create a new PDO instance echo "<td style='border: 1px solid black;
$conn = new padding: 8px;'>" . $row["firstname"] . " " .
PDO("mysql:host=$servername;dbname= $row["lastname"] . "</td>";
echo "</tr>";
$dbname", $username, $password);
}
// SQL query to select data } else {
$sql = "SELECT id, firstname, echo "<tr><td colspan='2' style='border:
lastname, email FROM student"; 1px solid black; padding: 8px; text-align:
$stmt = $conn->prepare($sql); center;'>0 results</td></tr>";
$stmt->execute(); }
echo "</table>";
// Start HTML output
echo "</body></html>";
echo "<html><body>"; } catch (PDOException $e) {
echo "<table style='border- echo "Connection failed: " . $e-
collapse: collapse; border: 1px >getMessage();
solid black;'>"; }
echo "<tr><th style='border: // Close connection
$conn = null;
1px solid black; padding:
?>
8px;'>ID</th>
<th style='border: 1px solid 41
 Using WHERE clause
 We have seen SQL SELECT command to fetch data from MySQL table. We
can use a conditional clause called WHERE clause to filter out results.
 Using WHERE clause we can specify a selection criteria to select required
records from a table.
 Here is generic SQL syntax of SELECT command with WHERE

Syntax: SELECT field1, field2,...fieldN table_name1, table_name2...[WHERE

condition1 [AND [OR]] condition2..
 You can use one or more tables separated by comma to include various
condition using a WHERE clause.
 You can specify more than one conditions using AND or OR operators.
 A WHERE clause can be used along with DELETE or UPDATE SQL command
also to specify a condition.
$sql = "SELECT id, firstname, lastname FROM student WHERE lastname=‘Abel’”;
$result = mysqli_query($conn, $sql);//execute a query

42
 Updating data using PHP
 There may be a requirement where existing data in a MySQL table need
to be modified.
 The UPDATE statement is used to update existing records in a table
 This will modify any field value of any MySQL table
 SQL syntax of UPDATE command
Syntax:UPDATE table_name SET field1=new-value1, field2=new-
value2 [WHERE Clause];
 You can update one or more field all together and You can also
specify any condition using WHERE clause.
 You can update values in a single table at a time.
 The WHERE clause is very useful when you want to update selected
rows in a table.
 You can use SQL UPDATE command with or without WHERE CLAUSE
into PHP function query() in OOP, mysqli_query() in procedural and
prepare() in PDO.
43
 Updating data using MySQLi Object-
oriented style
<?php
$servername = $sql = "UPDATE student SET
"localhost"; firstname=‘mohamed',
$username = "root"; lastname=‘Awol' WHERE id=3";
$password = "";
$dbname = “MAUDB"; if ($conn->query($sql) ===
TRUE) {
// Create connection echo "Record updated
$conn = new successfully";
mysqli($servername, } else {
$username, $password, echo "Error updating
$dbname); record: " . $conn->error;
// Check connection }
if ($conn->connect_error) $conn->close();
{ ?>
die("Connection failed:
" . $conn- 44
 Updating data using MySQLi Procedural
style
<?php $sql = "UPDATE student SET
$servername = lastname=‘Abebe' WHERE id=3";
"localhost";
$username = "root"; if (mysqli_query($conn,
$password = ""; $sql)) {
$dbname = “MAUDB"; echo "Record updated
successfully";
// Create connection } else {
$conn = echo "Error updating
mysqli_connect($servernam record: " .
e, $username, $password, mysqli_error($conn);
$dbname); }
// Check connection
if (!$conn) { mysqli_close($conn);
die("Connection failed: ?>
" .
mysqli_connect_error()); 45
 Updating data using PDO style
<?php // Prepare statement
$servername = $stmt = $conn-
"localhost"; >prepare($sql);
$username = "root";
$password = ""; // execute the query
$dbname = “MAUDB"; $stmt->execute();
try { // echo a message to say
$conn = new the UPDATE succeeded
PDO("mysql:host=$serverna echo $stmt->rowCount() . "
me;dbname=$dbname", records UPDATED
$username, $password); successfully";
} catch(PDOException $e) {
$sql = "UPDATE student echo $sql . "<br>" . $e-
SET lastname='Abel' WHERE >getMessage();
id=3"; }
$conn = null;
?> 46
 PHP MySQL Delete Data
 If you want to delete a record from any MySQL table then you can
use SQL command DELETE FROM.
 SQL syntax of DELETE command
Syntax: DELETE FROM table_name [WHERE Clause];
 If WHERE clause is not specified then all the records will be
deleted from the given MySQL table.
 You can specify any condition using WHERE clause.
 You can delete records in a single table at a time.
 The WHERE clause is very useful when you want to delete
selected rows in a table.
 You can use SQL DELETE command with or without WHERE
CLAUSE into PHP function query() in OOP, mysqli_query() in
procedural and exec() in PDO. 47
Delete Data using MySQLi Object-oriented

<?php // sql to delete a record

$servername = "localhost"; $sql = "DELETE FROM
$username = "root"; student WHERE id=3";
$password = ""; if ($conn->query($sql) ===
$dbname = “MAUDB"; TRUE) {
// Create connection echo "Record deleted
$conn = new successfully";
mysqli($servername, } else {
$username, $password, echo "Error deleting
$dbname); record: " . $conn->error;
// Check connection }
if ($conn->connect_error)
{ $conn->close();
die("Connection failed: ?>
" . $conn->connect_error);
}
48
 Delete Data using MySQLi Procedural
<?php // sql to delete a record
$servername = "localhost"; $sql = "DELETE FROM
$username = "root"; student WHERE id=2";
$password = "";
$dbname = “MAUDB"; if (mysqli_query($conn,
$sql)) {
// Create connection echo "Record deleted
$conn = successfully";
mysqli_connect($servername } else {
, $username, $password, echo "Error deleting
$dbname); record: " .
// Check connection mysqli_error($conn);
if (!$conn) { }
die("Connection failed:
" . mysqli_close($conn);
mysqli_connect_error()); ?>
} 49
 Delete Data using PDO
<?php // sql to delete a
$servername = "localhost"; record
$username = "root"; $sql = "DELETE FROM
$password = ""; student WHERE id=5";
$dbname = “MAUDB"; // use exec() because no
try { results are returned
$conn = new $conn->exec($sql);
PDO("mysql:host=$servernam echo "Record deleted
e;dbname=$dbname", successfully";
$username, $password); } catch(PDOException $e) {
echo $sql . "<br>" . $e-
>getMessage();
}

$conn = null;
?>
50
 PHP MySQL like Clauses
 A WHERE clause with equal sign (=) works fine where we want to do an exact
match.
 The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column.
 SQL syntax of SELECT command
Syntax: SELECT field1, field2,...fieldN from table_name1,
table_name2...WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
 You can use LIKE clause along with WHERE clause in place of equal sign.
 When LIKE is used along with % sign then it will work like a meta character
search (used to define wildcards (missing letters) both before and after the
pattern).
 %et%-Return all the records from the table for which the student name
contains ”et”.
 %et-return all records from the table for which student name ends with
“et”.
 et%-return all records from the table for which student name begins with
“et”. 51
 PHP MySQL Delete Data like Clauses
example
<?php $sql = "SELECT id, firstname,
$servername = "localhost"; lastname FROM student where
$username = "root"; firstname like '%hn%'";
$result = mysqli_query($conn,
$password = "";
$sql);
$dbname = “MAUDB";
if (mysqli_num_rows($result) > 0)
// Create connection {
$conn = // output data of each row
mysqli_connect($servername, while($row =
$username, $password, mysqli_fetch_assoc($result)) {
$dbname); echo "id: " . $row["id"]. " ,
Name: " . $row["firstname"]. " " .
// Check connection
$row["lastname"]. "<br>";
if (!$conn) { }
die("Connection failed: " . } else {
mysqli_connect_error()); echo "0 results";
} }

mysqli_close($conn);
?> 52
 Using ORDER BY clause
We have seen SQL SELECT command to fetch data from MySQL table.
 When you select rows, the MySQL server is free to return them in
any order, unless you instruct it otherwise by saying how to sort the
result.
 But you sort a result set by adding an ORDER BY clause that names
the column or columns you want to sort by.
 SQL syntax of SELECT command along with ORDER BY
Syntax:SELECT field1, field2,...fieldN table_name1, table_name2
ORDER BY field1, [field2...] [ASC [DESC]]
 You can sort returned result on any field provided that filed is being
listed out.
 You can sort result on more than one field.
 You can use keyword ASC or DESC to get result in ascending or
descending order. By default its ascending order.
 You can use WHERE...LIKE clause in usual way to put condition.

53
 Using order Clauses example
<?php $sql = "SELECT id, firstname,
$servername = "localhost"; lastname FROM student ORDER BY
$username = "root"; firstname";
$result = mysqli_query($conn,
$password = "";
$sql);
$dbname = "junedb";
if (mysqli_num_rows($result) > 0)
// Create connection {
$conn = // output data of each row
mysqli_connect($servername, while($row =
$username, $password, mysqli_fetch_assoc($result)) {
$dbname); echo "id: " . $row["id"]. " ,
Name: " . $row["firstname"]. " " .
// Check connection
$row["lastname"]. "<br>";
if (!$conn) { }
die("Connection failed: " . } else {
mysqli_connect_error()); echo "0 results";
} }

mysqli_close($conn);
?> 54
 !! ?
n d ?
e ns
h e i o
T es t
u
Q
55