Essential Database CRUD Operations in PHP u0026 MySQL Quiz Quiz

Sharpen your understanding of basic PHP and MySQL CRUD operations with this quiz, featuring questions on creating, reading, updating, and deleting records. Improve your grasp of SQL queries, PHP integration, and essential database concepts for web development.

1. Identifying the Correct SQL Statement for Inserting Data

   Which SQL statement correctly inserts a new row into a table named 'users' with columns 'name' and 'email'?

   1. INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
   2. DELETE FROM users WHERE name='John';
   3. SELECT name, email FROM users WHERE name='John';
   4. UPDATE users SET name='John', email='john@example.com';

   Explanation: The INSERT INTO statement is used to add new rows to a table. UPDATE modifies existing rows, not adding new ones, while DELETE is used for removal, and SELECT retrieves data. Option A properly uses the INSERT syntax and provides both column names and values.

2. Recognizing the Purpose of the SELECT Statement in PHP u0026 MySQL

   In PHP and MySQL, what is the main purpose of issuing a SELECT statement like 'SELECT * FROM posts'?

   1. To delete all posts from the table.
   2. To insert a new record into the table.
   3. To modify existing records in the table.
   4. To retrieve all records from the 'posts' table.

   Explanation: SELECT is used to fetch data from a database table. The asterisk * means all columns will be retrieved. DELETE removes data, INSERT adds new data, and UPDATE modifies current entries, so the other options do not match the SELECT statement's purpose.

3. Updating Records in a Database Table

   Which SQL statement properly updates the email of a user with id 1 in the 'users' table?

   1. DELETE FROM users WHERE email='old@example.com';
   2. UPDATE users SET email='new@example.com' WHERE id=1;
   3. SELECT email FROM users WHERE id=1;
   4. INSERT INTO users (email) VALUES ('new@example.com');

   Explanation: UPDATE changes existing data, and a WHERE clause specifies which row to change. INSERT adds a row, DELETE removes data, and SELECT is for viewing. Only option A correctly updates the specified user's email.

4. Deleting a Record Using SQL

   What does the SQL statement 'DELETE FROM comments WHERE id=5;' do?

   1. Adds a new comment with id 5.
   2. Updates the comment with id 5.
   3. Retrieves the comment with id 5.
   4. Removes the comment with id of 5 from the 'comments' table.

   Explanation: DELETE FROM specifies a row for removal by the WHERE clause. INSERT is used for adding data, UPDATE for changing values, and SELECT for fetching data, making the other options incorrect.

5. Connecting PHP to a MySQL Database

   Which PHP function is commonly used to establish a connection to a MySQL database?

   1. mysql_insert()
   2. mysqli_query()
   3. mysqli_connect()
   4. connect_sql()

   Explanation: mysqli_connect() is the standard function for connecting PHP to a MySQL database. mysql_insert() and connect_sql() are not valid PHP built-in functions, and mysqli_query() only executes SQL queries after a connection is made.

6. Preventing SQL Injection in PHP Database Queries

   What method should you use in PHP to safely include user input in SQL queries to prevent SQL injection?

   1. Using echo statements
   2. Directly concatenating user input into the query
   3. Prepared statements with parameter binding
   4. Typing input in uppercase letters

   Explanation: Prepared statements separate SQL logic from data, preventing injections. Concatenating user input exposes you to risks, echo does not affect SQL safety, and letter casing is unrelated. Only parameter binding ensures queries are executed safely.

7. Fetching Query Results in PHP

   After running a SELECT query in PHP, which function retrieves a row as an associative array?

   1. mysqli_num_rows()
   2. mysqli_exec()
   3. mysqli_fetch_assoc()
   4. mysqli_insert_id()

   Explanation: mysqli_fetch_assoc() fetches a row as an associative array mapping columns to values. mysqli_exec() does not exist, mysqli_num_rows() counts rows, and mysqli_insert_id() gets the ID from the last insert, not for fetching result rows.

8. Primary Key Usage in Database Tables

   Why should every table in a relational database ideally have a primary key column?

   1. To uniquely identify each row in the table.
   2. To increase the table's storage space.
   3. To define foreign key constraints of other tables.
   4. To store backup data for each row.

   Explanation: The primary key ensures each row has a unique identifier, which is essential for managing data. It does not store backups or directly define foreign keys (but can be referenced), and it is not meant for storage expansion.

9. Executing an SQL Query in PHP

   Which PHP function is typically used to send an SQL statement to a MySQL database for execution after a connection is established?

   1. mysqli_fetch_query()
   2. connect_query()
   3. sql_send()
   4. mysqli_query()

   Explanation: mysqli_query() is the correct function for executing SQL queries after connecting to MySQL. connect_query() and sql_send() are not standard PHP functions, and mysqli_fetch_query() does not exist in default PHP extensions.

10. Detecting Errors After Database Operations

    How can you check for errors after attempting an SQL operation using PHP’s MySQLi extension?

    1. By running an UPDATE query
    2. By checking the $_POST variable
    3. By using mysqli_error()
    4. By opening a new database connection

    Explanation: mysqli_error() returns a description of the most recent error involving the database connection. $_POST is related to form data, new connections don't indicate errors, and UPDATE only changes data without checking for errors by itself.