PHP And Databases

PHP is widely used to build dynamic web applications, and its interaction with databases is a key feature that allows developers to store, retrieve, and manage data efficiently. PHP supports multiple database management systems (DBMS), such as MySQL, PostgreSQL, SQLite, and more. PHP’s database handling functions include both procedural and object-oriented styles, offering flexibility to developers. This article covers common interview questions and answers related to PHP and databases.

How can PHP interact with a database?

Answer:
PHP can interact with a database through several extensions, such as:

• MySQLi (MySQL Improved): Used for interacting with MySQL databases.
• PDO (PHP Data Objects): Provides a database access abstraction layer, supporting multiple databases (MySQL, PostgreSQL, SQLite, etc.).
• SQLite: A lightweight, self-contained database engine that doesn’t require a server.

Example using MySQLi:

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

Example using PDO:

$dsn = "mysql:host=localhost;dbname=database";
$pdo = new PDO($dsn, "username", "password");

What is the difference between MySQLi and PDO in PHP?

Answer:

• MySQLi: Designed specifically for MySQL databases. It supports both procedural and object-oriented styles.
• PDO (PHP Data Objects): Provides a database abstraction layer, allowing you to work with multiple databases using the same API. It only supports the object-oriented approach.

Key differences:

• Database support: MySQLi only supports MySQL databases, while PDO supports multiple databases (MySQL, PostgreSQL, SQLite, etc.).
• Prepared statements: Both MySQLi and PDO support prepared statements, but PDO's implementation is more versatile.
• Object-oriented: PDO is only object-oriented, while MySQLi supports both procedural and object-oriented programming.

How do you connect to a MySQL database using MySQLi in PHP?

Answer:
You can connect to a MySQL database using the mysqli_connect() function in procedural style or by creating a mysqli object in object-oriented style.

Procedural example:

$conn = mysqli_connect("localhost", "username", "password", "database");
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

Object-oriented example:

$conn = new mysqli("localhost", "username", "password", "database");
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

How do you connect to a database using PDO in PHP?

Answer:
To connect to a database using PDO, you need to define the Data Source Name (DSN), which specifies the database type, host, and database name. Then, you create a new PDO object with the DSN, username, and password.

Example:

$dsn = "mysql:host=localhost;dbname=database";
$username = "username";
$password = "password";
try {
   $pdo = new PDO($dsn, $username, $password);
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   echo "Connected successfully";
} catch (PDOException $e) {
   echo "Connection failed: " . $e->getMessage();
}

What are prepared statements, and why are they important?

Answer:
Prepared statements are a way to execute SQL queries with placeholders instead of embedding user input directly in the query string. This protects against SQL injection attacks, as user input is treated as data rather than part of the SQL query. Both PDO and MySQLi support prepared statements.

Example using MySQLi:

$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);  // "s" indicates the type (string)
$stmt->execute();
$result = $stmt->get_result();

Example using PDO:

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
$users = $stmt->fetchAll();

What is SQL Injection, and how can it be prevented in PHP?

Answer:
SQL Injection is a security vulnerability that occurs when an attacker manipulates SQL queries by injecting malicious SQL code through user input. This can result in unauthorized access to or manipulation of the database.

Prevention:

• Prepared Statements: Always use prepared statements with parameterized queries to avoid directly embedding user input into SQL queries.
• Input Validation: Sanitize and validate all user inputs before processing.
• Escaping Data: When using non-prepared queries, use functions like mysqli_real_escape_string() to escape user input.

Example using prepared statements (PDO):

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);  // Prevents SQL injection

How do you execute a SELECT query using PDO?

Answer:
To execute a SELECT query using PDO, you can prepare the query, bind parameters (if necessary), and then execute the query. After execution, you can fetch the results using fetch() or fetchAll().

Example:

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
// Fetch a single row
$user = $stmt->fetch(PDO::FETCH_ASSOC);
echo $user['name'];
// Fetch all rows
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
   echo $user['name'];
}

How do you insert data into a MySQL database using MySQLi and PDO in PHP?

Answer:
MySQLi example:

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);  // "ss" means two strings
$stmt->execute();

PDO example:

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([
   'name' => $name,
   'email' => $email
]);

How can you update data in a database using PDO?

Answer:
To update data using PDO, you can use a prepared statement to execute an UPDATE query.

Example:

$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->execute(['name' => 'John Doe', 'id' => 1]);

How do you handle errors and exceptions in database operations using PDO?

Answer:
PDO allows you to handle errors and exceptions using the try-catch block. By setting the error mode to PDO::ERRMODE_EXCEPTION, any database error will throw an exception, which can be caught and handled.

Example:

try {
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
   $stmt->execute(['id' => 1]);
} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}

How do you delete data from a database using MySQLi and PDO?

Answer:
MySQLi example:

$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);  // "i" means integer
$stmt->execute();

PDO example:

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => $id]);

How do you handle database transactions in PHP using PDO?

Answer:
PDO supports database transactions, which allow you to group multiple SQL queries together so that they either all succeed or all fail. This ensures data integrity.

Example:

try {
   $pdo->beginTransaction();
   $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id");
   $stmt->execute(['amount' => 100, 'id' => 1]);
   $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id");
   $stmt->execute(['amount' => 100, 'id' => 2]);
   $pdo->commit(); // Commit the transaction
} catch (PDOException $e) {
   $pdo->rollBack(); // Roll back the transaction if an error occurs
   echo "Transaction failed: " . $e->getMessage();
}

What is the difference between fetch() and fetchAll() in PDO?

Answer:

• fetch(): Fetches a single row from the result set.
• fetchAll(): Fetches all rows from the result set.

Example:

// Fetching a single row
$stmt = $pdo->query("SELECT * FROM users LIMIT 1");
$user = $stmt->fetch(PDO::FETCH_ASSOC);
// Fetching all rows
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

What are database transactions, and how can they be useful in PHP?

Answer:
Database transactions allow multiple SQL queries to be executed as a single unit. If all queries are successful, the transaction is committed, making the changes permanent. If any query fails, the transaction can be rolled back, undoing all changes. This ensures data consistency and integrity, especially in cases where multiple related changes are made to the database.

What is a PDO statement, and how is it used?

Answer:
A PDO statement is an instance of the PDOStatement class, representing a prepared SQL query. It is used to execute queries and fetch results in a secure and efficient way, especially when working with prepared statements to prevent SQL injection.

Example:

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);