PHP DATABASE MANAGEMENT.pptx

Introduction
• Database is one of the most common
application that reside in the server.
• Therefore Common Gateway Interface e.g PHP
provides a link between user-side applications
and server-side applications such Database
Management System e.g. MySQL.
• The main component for storing data in a
system is a DBMS.

Database Connection
PHP 5 and later can work with a MySQL database using:
• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)
• Earlier versions of PHP used the MySQL extension. However, this
extension was deprecated in 2012.
• Both MySQLi and PDO have their advantages:
• PDO will work on 12 different database systems, whereas 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.

Connecting to server
• <?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";
?>

Create a Database
<?php
// Check connection
if (!$conn) {
}
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Create a Database (Example)
<?php
$username = “root";
$password = ” ";
// Check connection
if (!$conn) {
}
// Create database
$sql = "CREATE DATABASE school";
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
?>

CREATING A TABLE
• <?php
$dbname = “school";
$conn = mysqli_connect($servername,
$username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " .
mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE admission (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30),
lastname VARCHAR(30),
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP)";
echo "Table admission created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
?>

Insert data
$sql = "INSERT INTO admission(firstname, lastname,
email) VALUES (‘Peter', ‘Juma',
‘pjuma@gmail.com')";
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

• HTML form to send data to a database via PHP
<body>
<form method=“GET" action= “student.php">
First Name<input name=“fname" value="" />
Last name<input name=“lname" value="" />
E-mail <input name=“email" value="" />
<input type="submit“ value=“ADD”>
</form>

Insert data from HTML form
$fnam=GET[“fname”];
$lnam=GET[“lname”];
$em=GET[“email”];
$sql = "INSERT INTO admission (firstname, lastname,
email) VALUES (‘$fnam', ‘$lnam', ‘$em')";
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

Select data
//list records
$sql = "SELECT id, firstname, lastname FROM admission";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " .
$row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

Delete record
Connect to the server
$sql = "DELETE FROM admission WHERE id=3";
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
?>

Update records
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE
id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>

Update records (Procedural)
$sql = "UPDATE admission SET lastname='Doe' WHERE
id=2";
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
?>

Group Assignment (15 marks)
Develop a web application with the following
features:
• User-side validation/manipulation (JavaScript)
(5 marks)
• Use the application to manage a database
(insert data, update record, delete record and
list records) (10 marks)

PHP DATABASE MANAGEMENT.pptx

More Related Content

Similar to PHP DATABASE MANAGEMENT.pptx

Recently uploaded

PHP DATABASE MANAGEMENT.pptx