MCQs on PHP and MySQL (Database Interaction) | PHP Intermediate

Integrating MySQL with PHP is essential for building dynamic, data-driven websites. Learn how to interact with databases efficiently, perform CRUD operations, and ensure secure connections to protect against SQL injection.

PHP and MySQL (Database Interaction) – 30 Multiple Choice Questions

1. Introduction to MySQL and PHP

1. What is MySQL primarily used for in web development?
   • A) File storage
   • B) Data storage and retrieval
   • C) User authentication
   • D) Email management
2. Which PHP function is used to connect to a MySQL database using MySQLi?
   • A) connect_mysql()
   • B) mysqli_connect()
   • C) mysql_connect()
   • D) open_connection()
3. Which of the following PHP extensions is used to interact with MySQL databases?
   • A) PDO
   • B) MySQLi
   • C) Both A and B
   • D) None of the above
4. What does MySQL stand for?
   • A) My Structured Query Language
   • B) My Standard Query Language
   • C) Managed Structured Query Language
   • D) MySQL Query Language
5. Which MySQL function is used to select a database after a connection is made?
   • A) mysql_select_db()
   • B) select_database()
   • C) db_select()
   • D) connect_db()
6. What is the default port number for MySQL?
   • A) 3307
   • B) 443
   • C) 3306
   • D) 8080
7. Which PHP function returns the last inserted ID in a MySQL database using MySQLi?
   • A) mysqli_insert_id()
   • B) get_last_id()
   • C) mysqli_last_insert()
   • D) last_insert_id()
8. Which method is used to fetch data as an associative array from a MySQLi query?
   • A) fetch_assoc()
   • B) fetch_array()
   • C) fetch_result()
   • D) get_data()

2. MySQL Connection using MySQLi and PDO

9. Which of the following statements correctly establishes a MySQLi connection in PHP?
   • A) $conn = new MySQLi(“localhost”, “username”, “password”, “database”);
   • B) $conn = mysqli_connect(“localhost”, “username”, “password”, “database”);
   • C) $conn = mysqli_connect(“localhost”, “database”, “username”, “password”);
   • D) $conn = new mysqli(“localhost”, “database”, “username”, “password”);
10. What is the main difference between MySQLi and PDO in PHP?
    • A) MySQLi supports only MySQL databases, PDO supports multiple database types.
    • B) PDO is slower than MySQLi.
    • C) MySQLi supports only prepared statements, PDO does not.
    • D) There is no difference.
11. Which PHP function is used to set the PDO error mode to exceptions?
    • A) set_error_mode(PDO_EXCEPTION);
    • B) setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    • C) set_error_mode(PDO::ERRMODE_EXCEPTION);
    • D) PDO->setErrorMode(PDO::ERRMODE_EXCEPTION);
12. How do you fetch data using PDO in PHP?
    • A) fetch_all()
    • B) fetch_array()
    • C) fetch()
    • D) fetch_assoc()
13. Which of the following is the correct syntax for creating a PDO connection to MySQL?
    • A) $pdo = new PDO(“mysql=localhost;dbname=mydatabase”, “username”, “password”);
    • B) $pdo = new PDO(“mysql=localhost;user=myuser;password=password;”);
    • C) $pdo = PDO(“localhost”, “mydatabase”, “username”, “password”);
    • D) $pdo = new PDO(“mysql;mydatabase”, “username”, “password”);
14. What is the main benefit of using PDO over MySQLi?
    • A) PDO can only connect to MySQL databases.
    • B) PDO supports multiple database types, whereas MySQLi supports only MySQL.
    • C) PDO is slower.
    • D) MySQLi supports multiple databases.
15. Which of the following PHP functions can be used to close a PDO connection?
    • A) close()
    • B) PDO::close()
    • C) unset($pdo)
    • D) $pdo = null

3. CRUD Operations (Create, Read, Update, Delete)

16. What SQL query is used to insert data into a table in MySQL?
    • A) SELECT INTO
    • B) UPDATE
    • C) INSERT INTO
    • D) ADD
17. Which of the following is the correct syntax for a SELECT query in SQL?
    • A) SELECT column_name FROM table_name;
    • B) SELECT FROM table_name;
    • C) SELECT * FROM table_name WHERE condition;
    • D) All of the above
18. What SQL query is used to update existing data in a MySQL table?
    • A) MODIFY
    • B) CHANGE
    • C) UPDATE
    • D) ALTER
19. Which PHP function is used to execute a MySQL INSERT query using MySQLi?
    • A) mysqli_query()
    • B) mysqli_exec()
    • C) mysqli_execute()
    • D) mysqli_insert()
20. What does DELETE SQL query do in MySQL?
    • A) Deletes a table
    • B) Removes rows from a table
    • C) Deletes all data from a table but keeps the table structure
    • D) Deletes only specific rows based on conditions
21. How do you perform a SELECT query using MySQLi and fetch the results?
    • A) mysqli_fetch_assoc()
    • B) mysqli_fetch_all()
    • C) mysqli_fetch_array()
    • D) All of the above
22. Which of the following is the correct SQL query for deleting a record in MySQL?
    • A) REMOVE FROM table_name;
    • B) DELETE FROM table_name WHERE condition;
    • C) DROP FROM table_name;
    • D) DELETE table_name;
23. What SQL query is used to retrieve the first record from a table in MySQL?
    • A) SELECT FIRST FROM table_name;
    • B) SELECT * FROM table_name LIMIT 1;
    • C) SELECT TOP 1 FROM table_name;
    • D) SELECT 1 FROM table_name;

4. Prepared Statements

24. What is the purpose of using prepared statements in MySQL?
    • A) To speed up SQL queries
    • B) To prevent SQL injection attacks
    • C) To allow more complex SQL queries
    • D) Both A and B
25. Which PHP function is used to prepare an SQL query in MySQLi?
    • A) mysqli_prepare()
    • B) mysqli_execute()
    • C) mysqli_stmt()
    • D) mysqli_query()
26. What method is used to bind parameters to a prepared statement in MySQLi?
    • A) bind_param()
    • B) bind_value()
    • C) execute_stmt()
    • D) prepare_stmt()
27. How do you execute a prepared statement in MySQLi?
    • A) execute()
    • B) execute_stmt()
    • C) run()
    • D) query()
28. What is the primary reason to use prepared statements in PHP with MySQL?
    • A) Better performance
    • B) To avoid SQL syntax errors
    • C) To prevent SQL injection vulnerabilities
    • D) To allow SELECT statements

5. Database Security and Preventing SQL Injection

29. Which of the following is the best practice to prevent SQL injection in PHP?
    • A) Using $_GET and $_POST data directly in queries
    • B) Using prepared statements with parameterized queries
    • C) Escaping user input manually
    • D) All of the above
30. What PHP function helps to sanitize user input to prevent SQL injection?
    • A) mysqli_real_escape_string()
    • B) sanitize_input()
    • C) filter_var()
    • D) sanitize()

Answer Key