Web Applications Development using PHP & MYSQL
V Semester UT-V
M NAGA V VARA PRASAD, Assistant Professor, CS, BVRC (III Chem)
UT-V Syllabus
Interacting with MYSQL using PHP: MYSQL Versus MYSQLi Functions, Connecting to MYSQL with PHP, Working with
MYSQL Data.
Creating a Student Details: Planning and Creating Database Tables, Creating Menu, Creating Record Addition Mechanism,
Viewing Records, Creating the Record Deletion Mechanism, Adding Sub – entities to a Record.
Explain the difference between MySQL and MySQLi functions in PHP?
MySQL vs MySQLi in PHP
1. MySQL and MySQLi are PHP extensions used to connect and interact with databases.
2. MySQL is deprecated and not available in newer PHP versions.
3. MySQLi (improved MySQL) provides more functions, better performance, and higher security.
4. Recommended to use MySQLi instead of MySQL.
Feature MySQL MySQLi
Version Added in PHP 2.0, deprecated in PHP 5.5 Added in PHP 5.5, works with MySQL 4.1.3+
Interfaces Procedural only Procedural + Object-Oriented
Prepared Statements Not supported Supported
Stored Procedures Not supported Supported
Transactions Via SQL queries only Supported through API
Security Weaker, prone to SQL injection Stronger, supports prepared statements
Extension Directory ext/mysql ext/mysqli
Write PHP code to connect to a MySQL database using mysqli_connect( ) ?
Explain the steps to connect PHP with a MySQL database?
Explanation of Steps:
1. Connect to MySQL Server
• mysqli_connect ($servername, $username, $password);
• localhost → MySQL is on the same computer(Servername).
• root → default username.
• root → default password.
2. Create Database
• CREATE DATABASE myDB;
This ensures the myDB database is created only if it does not already exist.
3. Execute a query
• mysqli_query($conn, $sql) runs the query.
• If successful → "Database created successfully".
• If failed → error message is displayed.
4. Select Database
mysqli_select_db($conn, "myDB");
Makes myDB the active database.
5. Create Table
CREATE TABLE Student
(
id INT(3) PRIMARY KEY,
name VARCHAR(30) NOT NULL,
marks INT(3)
);
• id: Student Number (Primary Key).
• name: Student Name (Required).
• marks: Student Marks.
6. Execute a query
• mysqli_query($conn, $sql) runs the query.
• If successful → "Database created successfully".
• If failed → error message is displayed.
7. Close Connection
$conn->close( );
Example: PHP Program
<?php
$servername = "localhost";
$username = "root";
$password = "root";
// Step 1: Connect to MySQL (Procedural style)
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn)
{
echo "Connection failed: " . mysqli_connect_error( );
}
echo "Connected successfully<br>";
// Step 2: Create Database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql))
{
echo "Database created successfully<br>";
}
else
{
echo "Error creating database: " . mysqli_error($conn);
}
// Step 3: Select Database
mysqli_select_db($conn, "myDB");
// Step 4: Create Table
$sql = “CREATE TABLE Student
(
id INT(3) PRIMARY KEY,
name VARCHAR(30) NOT NULL,
marks INT(3)
)”;
if (mysqli_query($conn, $sql))
{
echo "Table Student created successfully<br>";
}
else
{
echo "Error creating table: " . mysqli_error($conn);
}
// Step 5: Close Connection
mysqli_close($conn);
?>
Output:
How to insert and retrieve data from database through PHP?
• Insert a record into the Employee table
• Retrieve and display all records
1. Connect to Database – using mysqli_connect( ).
2. Insert Record – using INSERT INTO Student (...) VALUES (...).
3. Check Insertion – if query runs successfully, print confirmation.
4. Retrieve Records – using SELECT * FROM Student.
5. Count Records – mysqli_num_rows( ) gives how many records are found.
6. Display Records – mysqli_fetch_assoc( ) (or) mysqli_fetch_array( ) fetches each row.
7. Close Connection – free up resources.
Example:
<?php
// Step 1: Database connection details
$servername = "localhost";
$username = "root";
$password = "root";
$database = "mydb";
// Step 2: Create a connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Step 3: Check connection
if (!$conn)
{
echo "Sorry, connection failed: " . mysqli_connect_error();
}
else
{
echo "Connection was successful<br><br>";
}
// ---------------- INSERT DATA ---------------- //
// SQL query to insert a record
$sql = "INSERT INTO Student (Id, Name, Marks)
VALUES (1, 'Ravi', 85), (2, 'Kiran', 90), (3, 'Latha', 78)";
// Execute the query
if (mysqli_query($conn, $sql))
{
echo "Record inserted successfully<br><br>";
}
else
{
echo "Error inserting record: " . mysqli_error($conn) . "<br><br>";
}
// ---------------- RETRIEVE DATA ---------------- //
// SQL query to fetch all records
$sql = "SELECT * FROM Student";
$result = mysqli_query($conn, $sql);
// Count number of records
$num = mysqli_num_rows($result);
// Display rows using while loop
if ($num > 0)
{
while ($row = mysqli_fetch_assoc($result))
{
echo $row['id'] . " -- " . $row['name'] . " -- " . $row['marks'] . "<br>";
}
}
echo "<br>$num records found in the Database“;
// Step 4: Close connection
mysqli_close($conn);
?>
Output:
Write PHP code to update a record in MySQL?
Explanation Steps:
1. Connect to MySQL – Using mysqli_connect( ) with database name (myDB).
2. SQL Query – Updates name and marks of the Student whose id = 1.
UPDATE Student SET name = 'Raju', marks = 90 WHERE id = 1;
3. mysqli_affected_rows( ) – Tells how many rows were updated.
• If 0, then either the record doesn’t exist or no change was needed.
4. Close connection – Frees up resources.
Example:
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$database = "myDB";
// Step 1: Connect to MySQL
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn)
{
echo "Connection failed: " . mysqli_connect_error( );
}
// Step 2: Update Student Record
$sql = "UPDATE Student SET name = 'Raju', marks = 90 WHERE id = 1“;
if (mysqli_query($conn, $sql))
{
if (mysqli_affected_rows($conn) > 0)
{
echo “Student record updated successfully";
}
else
{
echo "No matching record found to update";
}
}
else
{
echo "Error updating record: " . mysqli_error($conn);
}
// Step 3: Close Connection
mysqli_close($conn);
?> 1 -- Raju – 90
Output: 2 -- Kiran – 90
3 -- Latha -- 78
Write PHP code to delete a record in MySQL?
Explanation Steps:
1. Connect to MySQL → Uses mysqli_connect( ) with database myDB.
2. Delete Query →
• DELETE FROM Student WHERE id = 3;
Removes the student with number 3.
3. mysqli_affected_rows( ) →
• Returns >0 if a record was deleted.
• Returns 0 if no such student exists.
4. Close Connection → Ends the session cleanly.
Example:
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$database = "myDB";
// Step 1: Connect to MySQL
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn)
{
echo "Connection failed: " . mysqli_connect_error( );
}
// Step 2: Student number to delete (example: 3)
$Stu_Id = 3;
// Step 3: SQL query to delete record
$sql = "DELETE FROM Student WHERE id = $Stu_Id";
if (mysqli_query($conn, $sql))
{
if (mysqli_affected_rows($conn) > 0)
{
echo “Student with id $Stu_Id deleted successfully";
}
else
{
echo "No Student found with id $Stu_Id";
}
}
else
{
echo "Error deleting record: " . mysqli_error($conn);
}
// Step 4: Close connection
mysqli_close($conn);
?> 1 -- Raju – 90
Output: 2 -- Kiran – 90
2 records found in the Database
Develop a menu-driven PHP application to perform the following operations on a student table with fields id,
name, and marks:
1. Create Table – Create a table named student if it does not already exist.
2. Add Record – Insert a new student record (id, name, marks).
3. View Records – Display all student records in tabular format.
4. Delete Record – Delete a student record based on student id.
Write the PHP code to implement the above functionalities.
Explanation Steps:
1. Save this as mysqli_menu.php in htdocs/phpprog/.
2. Open browser → http://localhost/phpprog/mysqli_menu.php.
3. First click Create Table.
4. Use Add Student form → Enter ID, Name, Marks → click Add Record.
5. Click View Records to see all students.
6. Enter ID under Delete Student → click Delete Record.
Example:
<?php
// Step 1: Database Connection
$conn = mysqli_connect("localhost", "root", "root", "testdb");
if ($conn->connect_error)
{
echo "Connection failed: " . $conn->connect_error;
}
// Step 2: Handle Menu Actions
if (isset($_POST['action']))
{
$action = $_POST['action'];
// 1. Create Table
if ($action == "create")
{
$sql = "CREATE TABLE Student
(
id INT PRIMARY KEY,
name VARCHAR(50),
marks INT
)";
if ($conn->query($sql) === TRUE)
{
echo "<p>Table 'student' is ready.</p>";
}
else
{
echo "<p>Error creating table: " . $conn->error . "</p>";
}
}
// 2. Add Record
if ($action == "add")
{
if (!empty($_POST['add_id']) && !empty($_POST['add_name']) && !empty($_POST['add_marks']))
{
$id = (int) $_POST['add_id'];
$name = mysqli_real_escape_string($conn, $_POST['add_name']);
$marks = (int) $_POST['add_marks'];
$sql = "INSERT INTO student (id, name, marks) VALUES ($id, '$name', $marks)";
if ($conn->query($sql) === TRUE)
{
echo "<p>New record inserted successfully.</p>";
}
else
{
echo "<p>Error inserting record: " . $conn->error . "</p>";
}
}
else
{
echo "<p>Please fill in all fields (ID, Name, Marks).</p>";
}
}
// 3. View Records
if ($action == "view")
{
$sql = "SELECT * FROM student";
$res = $conn->query($sql);
if ($res && $res->num_rows > 0)
{
echo "<h3>Student Records</h3>";
echo "<table border='1' cellpadding='5'>
<tr><th>ID</th><th>Name</th><th>Marks</th></tr>";
while ($row = $res->fetch_assoc( )) {
echo "<tr><td>".$row['id']."</td><td>".$row['name']."</td><td>".$row['marks']."</td></tr>";
}
echo "</table>";
}
else
{
echo "<p>No records found.</p>";
}
}
// 4. Delete Record
if ($action == "delete")
{
if (!empty($_POST['del_id']))
{
$id = (int) $_POST['del_id'];
$sql = "DELETE FROM student WHERE id=$id";
if ($conn->query($sql) === TRUE)
{
echo "<p>Record with ID $id deleted successfully.</p>";
}
else
{
echo "<p>Error deleting record: " . $conn->error . "</p>";
}
}
else
{
echo "<p>Please enter an ID to delete.</p>";
}
}
}
// Step 3: Close Connection
$conn->close( );
?>
<!-- HTML Menu -->
<h2>🎓 Student Records Menu</h2>
<form method="post">
<button name="action" value="create">1. Create Table</button><br><br>
<h3>Add Student</h3>
ID: <input type="number" name="add_id"><br>
Name: <input type="text" name="add_name"><br>
Marks: <input type="number" name="add_marks"><br>
<button name="action" value="add">2. Add Record</button><br><br>
<button name="action" value="view">3. View Records</button><br><br>
<h3>Delete Student</h3>
ID: <input type="number" name="del_id"><br>
<button name="action" value="delete">4. Delete Record</button>
</form>
Output:
Output:
