Connecting PHP and MySQL Exercise Solutions | KalviGavani

Chapter 9 — Connecting PHP and MySQL: Exercise Solutions Guide

How to Use This Page

1. Attempt every exercise on your own before reading the solutions. 2. Compare your method with the answer shown — focus on the logic, not just the final output. 3. Practise writing connection and query code by hand; the exam often asks complete programs.

Key Concepts (Quick Recap)

• mysqli_connect($host, $user, $pass, $db) — establishes a connection to a MySQL database server.
• mysqli_connect_error() — returns a description of the last connection error.
• mysqli_query($conn, $sql) — executes a SQL query on the database.
• mysqli_fetch_array($result) — fetches a result row as an associative array, numeric array, or both.
• mysqli_fetch_assoc($result) — fetches a result row as an associative array (column names as keys).
• mysqli_fetch_row($result) — fetches a result row as a numeric (indexed) array.
• mysqli_num_rows($result) — returns the number of rows in a result set.
• mysqli_close($conn) — closes the database connection.
• CRUD — Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE).
• Prepared statements use mysqli_prepare(), mysqli_stmt_bind_param(), and mysqli_stmt_execute() to prevent SQL injection.
• Always check for connection and query errors before proceeding.

I. Choose the Best Answer (MCQs)

Explanation: mysqli_connect() is the improved MySQL function (the older mysql_connect() is deprecated and removed in PHP 7+). The "i" stands for "improved."

Explanation: For SELECT/SHOW/DESCRIBE queries, mysqli_query() returns a mysqli_result object. For INSERT/UPDATE/DELETE, it returns TRUE on success.

Explanation: mysqli_fetch_assoc() returns a row with column names as keys (e.g., $row['name']). mysqli_fetch_row() uses numeric indices; mysqli_fetch_array() can return both.

Explanation: It counts rows returned by a SELECT query. For INSERT/UPDATE/DELETE, use mysqli_affected_rows($conn) instead.

Explanation: INSERT adds new rows. Syntax: INSERT INTO table (col1, col2) VALUES ('val1', 'val2').

Explanation: mysqli_close($conn) frees the connection resource. It's good practice to close connections when done.

Explanation: mysqli_affected_rows($conn) returns the number of rows changed by the last INSERT, UPDATE, or DELETE. mysqli_num_rows() is for SELECT results only.

Explanation: The type string "ss" means both parameters are strings. Common type characters: s = string, i = integer, d = double, b = blob.

Explanation: On failure, mysqli_connect() returns FALSE. Use mysqli_connect_error() to get the error message.

Explanation: UPDATE table SET col='new' WHERE condition modifies existing rows. ALTER changes table structure, not data.

II. Fill in the Blanks

1. The function mysqli_connect() is used to establish a connection between PHP and MySQL.

2. mysqli_query() executes a SQL query on the database.

3. mysqli_fetch_assoc() fetches a result row as an associative array with column names as keys.

4. mysqli_num_rows() returns the number of rows in a SELECT result set.

5. mysqli_affected_rows() returns the number of rows changed by INSERT, UPDATE, or DELETE.

6. mysqli_close() closes the database connection.

7. The four CRUD operations stand for Create, Read, Update, and Delete.

8. In prepared statements, mysqli_prepare() creates a prepared statement from a SQL template.

9. The type specifier "i" represents an integer in mysqli_stmt_bind_param().

10. mysqli_connect_error() returns the error description when a connection fails.

III. True or False

1. mysql_connect() is the recommended function to connect PHP to MySQL. — False. mysql_connect() is deprecated and removed in PHP 7+. Use mysqli_connect() or PDO.

2. mysqli_query() returns TRUE for successful INSERT, UPDATE, and DELETE queries. — True. For non-SELECT queries, it returns TRUE on success; for SELECT, it returns a result object.

3. mysqli_fetch_assoc() returns both numeric and associative arrays. — False. It returns only an associative array. Use mysqli_fetch_array() for both.

4. You must always close a database connection with mysqli_close(). — True. It's good practice to close connections, though PHP auto-closes them when the script ends.

5. Prepared statements help prevent SQL injection attacks. — True. They separate SQL logic from data, so user input cannot alter the query structure.

6. mysqli_num_rows() can be used after an INSERT query to get the number of inserted rows. — False. mysqli_num_rows() works only with SELECT results. Use mysqli_affected_rows() for INSERT/UPDATE/DELETE.

7. The WHERE clause is optional in an UPDATE statement. — True. But without it, ALL rows will be updated, which is usually unintended.

8. mysqli_fetch_row() returns an associative array. — False. It returns a numeric (indexed) array. Use mysqli_fetch_assoc() for associative.

9. The die() function stops the script and displays an error message. — True. It is often used with mysqli_connect() or mysqli_query() for error handling.

10. A SELECT query with no matching rows returns FALSE. — False. It returns a result object with zero rows. Use mysqli_num_rows() to check if rows exist.

IV. Match the Following

Set A

Set B

V. Short Answer Questions

<?php
$host = "localhost";
$user = "root";
$pass = "";
$db   = "school";

$conn = mysqli_connect($host, $user, $pass, $db);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!";
?>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");
$sql = "SELECT * FROM student";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    echo "Roll: " . $row["rollno"] . " - Name: " . $row["name"] . "<br>";
}
mysqli_close($conn);
?>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");

$name = "Kumar";
$age  = 17;
$mark = 450;

$sql = "INSERT INTO student (name, age, total_mark) VALUES ('$name', $age, $mark)";

if (mysqli_query($conn, $sql)) {
    echo "Record inserted successfully!";
} else {
    echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");
$stmt = mysqli_prepare($conn, "INSERT INTO student (name, age) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "si", $name, $age);

$name = "Priya";
$age  = 16;
mysqli_stmt_execute($stmt);

$name = "Ravi";
$age  = 17;
mysqli_stmt_execute($stmt);

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");

$rollno = 105;
$sql = "DELETE FROM student WHERE rollno = $rollno";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully!";
} else {
    echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

VI. Detailed Answer Questions

<?php
// Connection
$conn = mysqli_connect("localhost", "root", "", "school");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

// CREATE (Insert)
$sql = "INSERT INTO student (name, age, mark) VALUES ('Arun', 16, 480)";
mysqli_query($conn, $sql);
echo "Record inserted.<br>";

// READ (Select)
$sql = "SELECT * FROM student";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    echo "Roll: {$row['rollno']} | Name: {$row['name']} | Age: {$row['age']} | Mark: {$row['mark']}<br>";
}

// UPDATE
$sql = "UPDATE student SET mark = 500 WHERE name = 'Arun'";
mysqli_query($conn, $sql);
echo "Record updated.<br>";

// DELETE
$sql = "DELETE FROM student WHERE rollno = 3";
mysqli_query($conn, $sql);
echo "Record deleted.<br>";

mysqli_close($conn);
?>

$conn = mysqli_connect("localhost", "root", "", "school");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!<br>";

$sql = "SELECT * FROM student";
$result = mysqli_query($conn, $sql);

if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}

mysqli_close($conn);

<!DOCTYPE html>
<html>
<body>
<h2>Student Registration</h2>
<form action="insert.php" method="POST">
    <label>Name:</label>
    <input type="text" name="name" required><br>
    <label>Age:</label>
    <input type="number" name="age" required><br>
    <label>Total Marks:</label>
    <input type="number" name="mark" required><br>
    <input type="submit" value="Register">
</form>
</body>
</html>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$name = mysqli_real_escape_string($conn, $_POST['name']);
$age  = intval($_POST['age']);
$mark = intval($_POST['mark']);

$sql = "INSERT INTO student (name, age, mark) VALUES ('$name', $age, $mark)";

if (mysqli_query($conn, $sql)) {
    echo "Student registered successfully!";
    echo "<br><a href='insert.html'>Add another</a>";
} else {
    echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

<?php
$conn = mysqli_connect("localhost", "root", "", "school");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

// Step 1: Prepare the statement with placeholders
$stmt = mysqli_prepare($conn, "INSERT INTO student (name, age, mark) VALUES (?, ?, ?)");

if (!$stmt) {
    die("Prepare failed: " . mysqli_error($conn));
}

// Step 2: Bind parameters (s=string, i=integer, i=integer)
mysqli_stmt_bind_param($stmt, "sii", $name, $age, $mark);

// Step 3: Set values and execute
$name = "Divya";
$age  = 16;
$mark = 490;
mysqli_stmt_execute($stmt);

// Execute again with different values
$name = "Karthik";
$age  = 17;
$mark = 475;
mysqli_stmt_execute($stmt);

echo "Records inserted successfully!";

// Close statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

Common Mistakes

1. Using mysql_connect() instead of mysqli_connect() — the old mysql_ functions are deprecated and removed in PHP 7+. Always use mysqli_.

2. Not checking for connection errors — always test the connection with if (!$conn) before proceeding.

3. Concatenating user input directly into SQL queries — this is vulnerable to SQL injection. Use prepared statements or mysqli_real_escape_string().

4. Confusing mysqli_num_rows() and mysqli_affected_rows() — mysqli_num_rows() is for SELECT results; mysqli_affected_rows() is for INSERT/UPDATE/DELETE.

5. Forgetting the WHERE clause in UPDATE or DELETE — without WHERE, all rows in the table will be modified or deleted.

6. Not closing connections and result sets — always call mysqli_close() and mysqli_free_result() to free resources.

7. Using mysqli_fetch_assoc() on a non-SELECT result — this only works on SELECT/SHOW results, not on TRUE returned by INSERT/UPDATE/DELETE.

Exam Checklist

• [ ] Can write the connection code with error handling (mysqli_connect + die)
• [ ] Know the difference between mysqli_fetch_assoc, mysqli_fetch_row, and mysqli_fetch_array
• [ ] Can write CRUD operations (INSERT, SELECT, UPDATE, DELETE) in PHP
• [ ] Can create an HTML form that submits data to a PHP script for database insertion
• [ ] Understand prepared statements and can write mysqli_prepare/bind/execute code
• [ ] Know type specifiers (s, i, d, b) for mysqli_stmt_bind_param()
• [ ] Can use mysqli_real_escape_string() for input sanitization
• [ ] Understand when to use mysqli_num_rows() vs mysqli_affected_rows()
• [ ] Can display database records in an HTML table using a while loop
• [ ] Know the complete flow: connect → query → fetch → display → close