PHP and MySQL Database Connection Practice Questions with Solutions

Introduction

PHP and MySQL work together to build dynamic websites and database-driven applications. PHP handles application logic, while MySQL stores information such as student records, employee details, and product data. In this chapter, you will practise connecting PHP to MySQL, executing queries, retrieving records, and handling database errors using MySQLi. PHP and MySQL Database Connection practice questions with solutions to help you to understand the concepts.

Before Starting: Database Setup

For these practice questions, you can use XAMPP, WAMP, or another PHP and MySQL environment.

Step 1: Start the required services

  • Start Apache.
  • Start MySQL.
  • Open phpMyAdmin in your browser.

Step 2: Create a database

Create a database named:

school_db

Step 3: Create a students table

Run this SQL query in phpMyAdmin:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    marks INT NOT NULL
);

Step 4: Insert sample records

INSERT INTO students (name, email, course, marks)
VALUES
('Rahul', 'rahul@gmail.com', 'PHP', 85),
('Priya', 'priya@gmail.com', 'Python', 90),
('Amit', 'amit@gmail.com', 'Java', 75),
('Neha', 'neha@gmail.com', 'PHP', 95),
('Karan', 'karan@gmail.com', 'Python', 80);

You will use this database and table throughout the practice questions.

Question 1: Connect PHP to MySQL Using MySQLi

Problem Statement

Create a PHP program that connects to a MySQL database named school_db using MySQLi. Display a success message when the connection is established.

PHP Solution

<?php

$host = "localhost";
$username = "root";
$password = "";
$database = "school_db";

$conn = new mysqli($host, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

echo "Database Connected Successfully!";

$conn->close();

?>

Sample Output

Database Connected Successfully!

Explanation

The mysqli class creates a connection between PHP and MySQL.

The connection requires four important values:

  • $host: MySQL server address. In a typical local XAMPP setup, it is localhost.
  • $username: MySQL username. The default XAMPP username is commonly root.
  • $password: MySQL password. It is commonly blank in a default local XAMPP installation.
  • $database: Name of the database you want to access.

The following statement creates the connection:

$conn = new mysqli($host, $username, $password, $database);

The connect_error property checks whether the connection failed.

Finally, close() closes the database connection.

Concepts Covered

  • PHP and MySQL connection
  • MySQLi
  • Database credentials
  • Connection error handling
  • Closing database connections

Question 2: Connect PHP to MySQL Using PDO

Problem Statement

Create a PHP program that connects to the school_db database using PDO. Display a success message if the connection is established and handle connection errors using try-catch.

PHP Solution

<?php

$host = "localhost";
$database = "school_db";
$username = "root";
$password = "";

try {

    $conn = new PDO(
        "mysql:host=$host;dbname=$database;charset=utf8mb4",
        $username,
        $password
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    echo "Database Connected Successfully!";

} catch (PDOException $e) {

    echo "Connection Failed: " . $e->getMessage();

}

?>

Sample Output

Database Connected Successfully!

Explanation

PDO stands for PHP Data Objects. It provides a consistent way to connect PHP applications to different database systems.

The following statement creates a PDO connection:

$conn = new PDO(
    "mysql:host=$host;dbname=$database;charset=utf8mb4",
    $username,
    $password
);

The connection string specifies the database driver, host, database name, and character encoding.

The try block contains the connection code. If a database error occurs, the catch block handles the exception.

The following setting enables exception-based error reporting:

$conn->setAttribute(
    PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION
);

Concepts Covered

  • PDO connection
  • Database connection string
  • Exception handling
  • try-catch
  • MySQL database connectivity

Question 3: Display All Records from a MySQL Database

Problem Statement

Create a PHP program that connects to school_db, retrieves all records from the students table, and displays student names, email addresses, courses, and marks.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM students";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while ($row = $result->fetch_assoc()) {

        echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
        echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
        echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
        echo "Marks: " . (int) $row["marks"] . "<br><br>";

    }

} else {

    echo "No Records Found.";

}

$conn->close();

?>

Sample Output

Name: Rahul
Email: rahul@gmail.com
Course: PHP
Marks: 85

Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90

Name: Amit
Email: amit@gmail.com
Course: Java
Marks: 75

Name: Neha
Email: neha@gmail.com
Course: PHP
Marks: 95

Name: Karan
Email: karan@gmail.com
Course: Python
Marks: 80

Explanation

The SQL query retrieves all records from the students table:

SELECT * FROM students;

The query() method executes the SQL statement.

The num_rows property checks how many records were returned.

The fetch_assoc() method retrieves one record at a time as an associative array.

For example:

$row["name"]

retrieves the student’s name from the current record.

The htmlspecialchars() function protects HTML output by escaping special characters. This is important when displaying database values on a webpage.

Concepts Covered

  • SELECT query
  • Retrieving database records
  • query() method
  • fetch_assoc() method
  • num_rows property
  • Safe HTML output

Question 4: Display a Specific Student Record Using an ID

Problem Statement

Create a PHP program that retrieves the details of a student whose ID is 2 from the students table. Display the student’s name, email, course, and marks.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$studentId = 2;

$stmt = $conn->prepare(
    "SELECT id, name, email, course, marks FROM students WHERE id = ?"
);

$stmt->bind_param("i", $studentId);

$stmt->execute();

$result = $stmt->get_result();

if ($row = $result->fetch_assoc()) {

    echo "Student ID: " . (int) $row["id"] . "<br>";
    echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
    echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
    echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
    echo "Marks: " . (int) $row["marks"];

} else {

    echo "Student Not Found.";

}

$stmt->close();
$conn->close();

?>

Sample Output

Student ID: 2
Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90

Explanation

The program retrieves a particular student using the ID.

The SQL query uses a question mark as a placeholder:

SELECT id, name, email, course, marks FROM students WHERE id = ?

The prepare() method creates a prepared statement.

The following statement binds the student ID to the placeholder:

$stmt->bind_param("i", $studentId);

Here, i means the parameter is an integer.

Prepared statements help protect applications against SQL injection by separating SQL instructions from supplied values.

Concepts Covered

  • SELECT with WHERE
  • Prepared statements
  • prepare() method
  • bind_param() method
  • Fetching a single record
  • SQL injection prevention

Question 5: Insert a New Student Record into MySQL

Problem Statement

Create a PHP program that inserts a new student into the students table with the following details:

FieldValue
NameAnjali
Emailanjali@gmail.com
CourseJava
Marks88

Display a success message after inserting the record.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$name = "Anjali";
$email = "anjali@gmail.com";
$course = "Java";
$marks = 88;

$stmt = $conn->prepare(
    "INSERT INTO students (name, email, course, marks)
     VALUES (?, ?, ?, ?)"
);

$stmt->bind_param("sssi", $name, $email, $course, $marks);

if ($stmt->execute()) {

    echo "Student Record Inserted Successfully!";

} else {

    echo "Error: " . $stmt->error;

}

$stmt->close();
$conn->close();

?>

Sample Output

Student Record Inserted Successfully!

Explanation

The INSERT INTO query adds a new record to the students table.

The prepare() method creates a prepared statement with four placeholders.

The bind_param() method connects the PHP variables to those placeholders.

The type string is:

"sssi"

It represents:

  • s: String for student name.
  • s: String for email.
  • s: String for course.
  • i: Integer for marks.

The execute() method runs the prepared statement.

Concepts Covered

  • INSERT query
  • Adding database records
  • Prepared statements
  • Parameter binding
  • execute() method

Question 6: Update an Existing Student Record

Problem Statement

Create a PHP program that updates the marks of the student whose ID is 3.

Change the student’s marks from 75 to 82.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$studentId = 3;
$newMarks = 82;

$stmt = $conn->prepare(
    "UPDATE students SET marks = ? WHERE id = ?"
);

$stmt->bind_param("ii", $newMarks, $studentId);

if ($stmt->execute()) {

    if ($stmt->affected_rows > 0) {
        echo "Student Record Updated Successfully!";
    } else {
        echo "No Record Changed.";
    }

} else {

    echo "Error: " . $stmt->error;

}

$stmt->close();
$conn->close();

?>

Sample Output

Student Record Updated Successfully!

Explanation

The UPDATE query modifies an existing database record.

UPDATE students SET marks = ? WHERE id = ?

It updates the marks of the student whose ID matches the supplied value.

The bind_param("ii", ...) statement binds two integer values.

The affected_rows property tells us how many rows were changed by the statement.

If the supplied marks are already the same as the stored marks, or the ID does not match a record, no rows may be changed.

Concepts Covered

  • UPDATE query
  • Modifying database records
  • WHERE condition
  • Prepared statements
  • affected_rows property

Question 7: Delete a Student Record from MySQL

Problem Statement

Create a PHP program that deletes the student record whose ID is 5 from the students table. Display a confirmation message when the record is deleted.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$studentId = 5;

$stmt = $conn->prepare(
    "DELETE FROM students WHERE id = ?"
);

$stmt->bind_param("i", $studentId);

if ($stmt->execute()) {

    if ($stmt->affected_rows > 0) {
        echo "Student Record Deleted Successfully!";
    } else {
        echo "Student Record Not Found.";
    }

} else {

    echo "Error: " . $stmt->error;

}

$stmt->close();
$conn->close();

?>

Sample Output

Student Record Deleted Successfully!

Explanation

The DELETE query removes a record from the database.

DELETE FROM students WHERE id = ?

The WHERE condition ensures that only the student with the matching ID is deleted.

The bind_param() method safely binds the student ID.

The affected_rows property checks whether a record was actually deleted.

Important: Always use a suitable WHERE condition when deleting records. Without it, a DELETE query can remove every record from the table.

Concepts Covered

  • DELETE query
  • Removing database records
  • WHERE condition
  • Prepared statements
  • affected_rows property

Question 8: Count Total Students in MySQL

Problem Statement

Create a PHP program that counts the total number of student records in the students table and displays the result.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$sql = "SELECT COUNT(*) AS total_students FROM students";

$result = $conn->query($sql);

if ($result) {

    $row = $result->fetch_assoc();

    echo "Total Students: " . $row["total_students"];

} else {

    echo "Error: " . $conn->error;

}

$conn->close();

?>

Sample Output

Total Students: 4

Explanation

The SQL COUNT() function counts the number of records in a table.

SELECT COUNT(*) AS total_students FROM students;

The alias total_students gives a readable name to the calculated value.

The fetch_assoc() method retrieves the result as an associative array.

The output depends on which records currently exist in your database. If you have completed the previous insert and delete questions in order, the original five records become four after Question 7, and Question 8 displays four.

Concepts Covered

  • SQL COUNT function
  • Aggregate queries
  • Column aliases
  • Fetching query results

Question 9: Search Students by Course Using Prepared Statements

Problem Statement

Create a PHP program that displays all students enrolled in the PHP course.

Use a prepared statement to search for students whose course is PHP.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$course = "PHP";

$stmt = $conn->prepare(
    "SELECT id, name, email, marks FROM students WHERE course = ?"
);

$stmt->bind_param("s", $course);

$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows > 0) {

    while ($row = $result->fetch_assoc()) {

        echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
        echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
        echo "Marks: " . (int) $row["marks"] . "<br><br>";

    }

} else {

    echo "No Students Found in PHP Course.";

}

$stmt->close();
$conn->close();

?>

Sample Output

Name: Rahul
Email: rahul@gmail.com
Marks: 85

Name: Neha
Email: neha@gmail.com
Marks: 95

Explanation

The program searches for students based on their course.

The SQL query uses a placeholder:

SELECT id, name, email, marks FROM students WHERE course = ?

The following statement binds the course name:

$stmt->bind_param("s", $course);

The letter s indicates that the parameter is a string.

The while loop retrieves and displays every matching student record.

If you have changed or deleted records while completing earlier questions, your output may be different.

Concepts Covered

  • SELECT with WHERE
  • Searching database records
  • Prepared statements
  • Fetching multiple records
  • Displaying database results

Question 10: Build a Student Management System Using PHP and MySQL

Problem Statement

Create a simple Student Management System that performs the following operations:

  1. Connect to the school_db database.
  2. Insert a new student record.
  3. Retrieve all student records.
  4. Display student details in an HTML table.

Use the following student information:

FieldValue
NameMohit
Emailmohit@gmail.com
CoursePython
Marks92

PHP Solution

Save the following code as student_management.php.

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

// Insert Student Record

$name = "Mohit";
$email = "mohit@gmail.com";
$course = "Python";
$marks = 92;

$stmt = $conn->prepare(
    "INSERT INTO students (name, email, course, marks)
     VALUES (?, ?, ?, ?)"
);

$stmt->bind_param("sssi", $name, $email, $course, $marks);

if ($stmt->execute()) {
    echo "Student Added Successfully!<br><br>";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();

// Retrieve Student Records

$sql = "SELECT id, name, email, course, marks FROM students";

$result = $conn->query($sql);

?>

<h2>Student Management System</h2>

<table border="1" cellpadding="8" cellspacing="0">

    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Course</th>
        <th>Marks</th>
    </tr>

    <?php

    if ($result && $result->num_rows > 0) {

        while ($row = $result->fetch_assoc()) {

    ?>

    <tr>
        <td><?php echo (int) $row["id"]; ?></td>
        <td><?php echo htmlspecialchars($row["name"]); ?></td>
        <td><?php echo htmlspecialchars($row["email"]); ?></td>
        <td><?php echo htmlspecialchars($row["course"]); ?></td>
        <td><?php echo (int) $row["marks"]; ?></td>
    </tr>

    <?php

        }

    } else {

        echo "<tr><td colspan='5'>No Records Found.</td></tr>";

    }

    ?>

</table>

<?php

$conn->close();

?>

Sample Output

<text color=”secondary” size=”xs”>Illustrative output after running the program with the original sample records and completing the earlier insert and delete exercises.</text>

Student Added Successfully!

Student Management System

ID   Name    Email             Course    Marks
1    Rahul   rahul@gmail.com   PHP       85
2    Priya   priya@gmail.com   Python    90
3    Amit    amit@gmail.com    Java      82
4    Neha    neha@gmail.com    PHP       95
6    Mohit   mohit@gmail.com   Python    92

Explanation

This project combines the main database operations covered in this chapter.

1. Database connection

The new mysqli() statement connects PHP to MySQL.

2. Insert student record

A prepared statement inserts Mohit’s information into the students table.

3. Retrieve records

The SELECT query retrieves all student records.

4. Display records

The while loop processes each record and displays it inside an HTML table.

5. Protect displayed values

The htmlspecialchars() function escapes text before displaying it in HTML.

The program demonstrates how PHP can communicate with MySQL and display database information on a webpage.

Important: This is a learning example. If you refresh the page, Mohit’s record will be inserted again. A production application should use a form submission flow with validation and prevent accidental duplicate submissions.

Concepts Covered

  • PHP and MySQL connection
  • INSERT query
  • SELECT query
  • Prepared statements
  • Fetching database records
  • HTML table
  • Safe output using htmlspecialchars()
  • Basic student management system

Key Takeaways

  • PHP can connect to MySQL using MySQLi or PDO.
  • MySQLi and PDO both support prepared statements.
  • Use SELECT to retrieve database records.
  • Use INSERT to add new records.
  • Use UPDATE to modify existing records.
  • Use DELETE to remove records.
  • Use WHERE to target specific records.
  • Use fetch_assoc() to retrieve records as associative arrays.
  • Use COUNT() to count database records.
  • Prepared statements help protect against SQL injection.
  • Use htmlspecialchars() when displaying database values in HTML.
  • Always handle connection errors and avoid exposing sensitive database details in production.

Frequently Asked Questions (FAQs)

1. What is PHP and MySQL database connectivity?

PHP and MySQL database connectivity allows a PHP application to communicate with a MySQL database. PHP can insert, retrieve, update, and delete stored information.

2. How can we connect PHP to MySQL?

You can connect PHP to MySQL using MySQLi or PDO. Both provide methods for creating database connections and executing SQL queries.

3. What is the difference between MySQLi and PDO?

MySQLi is designed specifically for MySQL databases, while PDO supports multiple database systems through different drivers. Both support prepared statements.

4. What is the use of new mysqli() in PHP?

The new mysqli() statement creates a connection between a PHP application and a MySQL database.

5. What is a prepared statement in PHP?

A prepared statement separates an SQL query from the values supplied to it. It helps prevent SQL injection when parameters are bound correctly.

6. What is the use of bind_param() in PHP?

The bind_param() method binds PHP variables to placeholders in a prepared MySQLi statement.

For example:

$stmt->bind_param("si", $name, $marks);

Here, s represents a string and i represents an integer.

7. What is the use of fetch_assoc() in PHP?

The fetch_assoc() method retrieves a database result row as an associative array. Column names are used as array keys.

8. How can we insert data into MySQL using PHP?

You can use an SQL INSERT INTO statement with a prepared statement and execute it using the execute() method.

9. How can we update and delete records using PHP?

Use the SQL UPDATE statement to modify existing records and the DELETE statement to remove records. Prepared statements should be used when values come from application input.

10. How can we prevent SQL injection in PHP?

Use prepared statements with bound parameters instead of directly joining user input into SQL queries. Also validate input and use a database account with only the permissions the application requires.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Scroll to Top