This slide show will brief about database handling

Lecture 7 - Recap
1. Superglobal variables and Form handling
2. File handling
3. File upload
4. File creation
5. File read
6. File write
7. Directory/folder operations
1

IT1100 Internet and Web technologies
Lecture 08
Database handling
2

Content
1. Introduction
2. The database connection
3. Basic CRUD operations
– Create
– Read
– Update
– Delete 3

1. Introduction
• Database is an external resource, hosted in a
database server, and managed by a DBMS
• The database server is considered as a
separate tire
4

1. Introduction
• MySQL database server is the de facto
standard for PHP applications
• There are multiple ways to connect to a
database using PHP
• PHP can perform CRUD operations on a
database using SQL
5

1. Introduction
phpMyAdmin
• How to run phpMyAdmin
• Create/Delete/Rename Database
• Create/Delete/Rename/Drop Table
• Insert/Update/Delete Data
6

1. Introduction
Ways to connect to DB using PHP
1. MySQL extension
• Support only PHP versions before v7
• Procedural
2. MySQLi (improved)
• Support since PHP version 7
• Support both procedural and OOP
• Support prepared statements
3. PHP Data Objects (PDO)
• A lightweight, consistent interface for accessing databases in PHP.
• Support many DB servers
• Only OOP
• Support prepared statements 7

Configurations
• It is a good idea to keep the DB
configurations in a dedicated file config.php
//The connection object
//$con= new mysqli(“Server”, “UN”, “PW”, “DB”);
$con=new mysqli("localhost","root",“123","test");
8
1 2 3 4

Configurations
• Check for errors before continue
// Check connection
if ($con->connect_error)
{
die("Connection failed: " . $con->connect_error);
}
9
$con->connect_error - Returns a string that describes the error. NULL
if no error occurred

2. The connection
Configurations
• The configuration file can be linked when
needed
• index.php (or any other page/file)
//Linking the configuration file
require 'config.php';
10

2. The connection
Configurations
11
<?php
$con=new mysqli("localhost","root","","MyDB");
// Check connection
if($con->connect_error){
}
?>
config.php

3. The Create statement
• To create data, an insert SQL statement is
used
$sql= "INSERT INTO myTable (stuID, stuName) VALUES (1, ‘SLIIT’)";
12
Single quotes
for strings,
within double
quotes

• Execute the statement
$sql = "INSERT INTO myTable (stuID, stuName) VALUES (1, ‘SLIIT’)";
• $con->query($sql)
– This returns a Boolean value (true or false) to indicate
the (un)successful execution of the statement in the
DB server.
13

• Check for errors before in SQL command
execution.
if ($con->query($sql))
{
echo “Inserted successfully";
}
else
{
echo “Error: ”. $con->error;
}
14

• Do not forget to close the connection
– After executing any operation
$con->close();
15

<?php
$sql= "INSERT INTO myTable (stuID, stuName)VALUES(11111,'SLIIT')";
if($con->query($sql)){
echo "Inserted successfully";
}
else{
echo "Error:". $con->error;
}
$con->close();
?>
16
<?php
$con=new mysqli("localhost","root",“ ","MyDB");
// Check connection
}
?>

Problems in data INSERT method
• Can insert One Record at a time
• User need access rights to internal .PHP
pages stored in webserver (ex. /htdocs/…)
17

Solution1
Use a HTML Form
<!doctype html>
<html>
<head> </head>
<body>
<form method="post" action="form_process.php">
<h3>Input Student Data </h3>
Student ID :<input type="text" name="stuID"><BR />
Student Name :<input type="text" name="stuName"><BR />
<input type="submit" value="Submit">
<input type="reset" value="Reset">
</form>
</body>
</html>
18
<?php
$ID = $_POST["stuID"];
$Name = $_POST["stuName"];
$sql= "INSERT INTO myTable(stuID, stuName)VALUES($ID,$Name)";
}
else{
}
$con->close();
?>
5
4
2
3
1
6
7

Solution1
Use a PHP Form
<?php
?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<h3>Input Student Data </h3>
Student ID :<input type="text" name="stuID"><BR />
Student Name :<input type="text" name="stuName"><BR />
<input type="submit" value="Submit" name="btnSubmit">
<input type="reset" value="Reset">
</form>
<?php
if(isset($_POST["btnSubmit"])){
$stuID = $_POST["stuID"];
$stuName = $_POST["stuName"];
$sql= "INSERT INTO myTable(stuID,stuName)VALUES($stuID,'$stuName')";
}
else{
}
}
$con->close();
?>
19
6
5
3
2
1
7
4

4. Read (Select) statement
• When reading data from a DB, we use a
select statement, which returns a dataset as
the result.
$sql = “select ID, name from myTable”
20

Result set
• We execute the select SQL statement, then
assign the result set into a variable ($result ).
$result = $con->query($sql);
21

Result set - availability
• If only there are results, we can read them
if ($result->num_rows > 0)
{
//read data
}
else
{
echo “no results”;
} 22

Result set – read data
• We read the dataset row by row using a loop
• There are multiple functions to fetch a row from a
dataset
• fetch_all — Fetches all result rows as an associative array, a numeric array, or both
• fetch_array — Fetch a result row as an associative, a numeric array, or both
• fetch_assoc — Fetch a result row as an associative array
• fetch_field_direct — Fetch meta-data for a single field
• fetch_field — Returns the next field in the result set
• fetch_fields — Returns an array of objects representing the fields in a result set
• fetch_object — Returns the current row of a result set as an object
• fetch_row — Get a result row as an enumerated array
23

• Lets use fetch_assoc(), which return the row
as an associative array
while($row = $result->fetch_assoc())
{
//Read and utilize the row data
}
24

• Column names can be used as the indexes to
read the cell data in the fetched row
echo $row["ID"]. " – " . $row["Name"] . "<BR />";
EX: show the data inside a table, on the page
25

Complete function
• Column names can be used as the indexes to
read the cell data in the fetched row
echo $row[“stuID"]. " – " . $row[“stuName"] . "<BR />";
EX: show the data inside a table, on the page
26

Complete Code
<?php
$sql = "select stuID, stuName from myTable";
if($result->num_rows > 0){
//read data
while($row = $result->fetch_assoc()){
//Read and utilize the row data
echo $row["ID"]. " – " . $row["Name"] . "<BR />";
}
}
else
{
echo "no results";
}
$con->close();
?>
27
<?php
$con=new mysqli("localhost","root","","MyDB");
// Check connection
}
?>

4. Read
Complete function
<?php
function readData()
{
global $con;
$sql = "SELECT stuID, stuName FROM myTable";
if ($result->num_rows > 0)
{
while($row = $result->fetch_assoc())
{
echo "ID: " . $row["ID"]. " - Name: " . $row["Name"]. "<br>";
}
}
else
{
echo "No results";
}
$con->close();
}
readData();
?> 28
<?php
$con=new mysqli("localhost","root","","test");
if($con->connect_error)
{
die("Connection failed: ". $con->connect_error);
}
?>

Summary
1. Introduction
3. Basic CRUD operations
– Create
– Read
– Update
– Delete
29

This slide show will brief about database handling

More Related Content

Similar to This slide show will brief about database handling

Recently uploaded

This slide show will brief about database handling