PHP MySQL Interview Questions & Answers

What is the difference between mysqli_fetch_array() and mysqli_fetch_object()?

Both functions read one row from a MySQL result, but the format of the row is different.

The examples use the users table of our MySQL chapters. Suppose the user with id 1 is Ram.

<?php
require_once('db-connection.php');

$sql = "SELECT name, email FROM users WHERE id = 1";

$row = mysqli_fetch_array(mysqli_query($conn, $sql));
echo $row["name"] . " / " . $row[1] . "<br>";

$obj = mysqli_fetch_object(mysqli_query($conn, $sql));
echo $obj->name . " / " . $obj->email;
?>

Output:

In the first line, $row[1] is the second column (email), because the numeric keys start from 0. Most developers prefer mysqli_fetch_assoc(), because it does not keep every value two times. See all the options in ways to fetch data from a result set.

How to Fix Incorrect format parameter error when importing large database in PHPMyAdmin?

This error appears when you import a big SQL file in phpMyAdmin. It is usually not a problem with the file, but with the limits of PHP on your server.

Common causes:

• The file is bigger than upload_max_filesize or post_max_size.
• The import takes longer than max_execution_time.
• The script needs more memory than memory_limit allows.

Fix 1: compress the SQL file. phpMyAdmin can import a .zip or .gz file directly, and it is much smaller.

Fix 2: increase the limits in php.ini (or .user.ini), then restart the server.

upload_max_filesize = 256M
post_max_size = 256M
memory_limit = 512M
max_execution_time = 600

See how to increase the upload size and the execution time.

Fix 3: remove the phpMyAdmin time limit by adding this line in its config.inc.php file.

$cfg['ExecTimeLimit'] = 0;

Fix 4: import with the command line. This is the best way for very big files, because PHP limits do not apply.

mysql -u username -p database_name < backup.sql

In how many ways can we fetch data from a MySQL result set in PHP?

With the MySQLi extension there are five main functions to read rows from a result set.

This example uses the users table from our MySQL chapters. Suppose the user with id 1 is “Ram”.

<?php
require_once('db-connection.php');

$sql = "SELECT id, name FROM users WHERE id = 1";

$row = mysqli_fetch_assoc(mysqli_query($conn, $sql));
echo $row["name"] . "<br>";

$row = mysqli_fetch_row(mysqli_query($conn, $sql));
echo $row[1] . "<br>";

$row = mysqli_fetch_object(mysqli_query($conn, $sql));
echo $row->name;
?>

Output:

fetch_assoc() is the most used, because the code is easy to read. Use fetch_all() only when the result is small, as it loads every row in memory.

How to encrypt and decrypt data stored in a MySQL table?

MySQL has two built-in functions: AES_ENCRYPT() and AES_DECRYPT(). The column that stores the encrypted data must be of type VARBINARY or BLOB.

INSERT INTO users (name, secret)
VALUES ('Robin', AES_ENCRYPT('my secret', 'strong-key'));

SELECT AES_DECRYPT(secret, 'strong-key') AS secret
FROM users WHERE name = 'Robin';

This works, but it has weak points. The key goes inside the SQL, so it can be seen in query logs. And in MySQL 8 the default mode is AES-128-ECB, which is not a strong mode.

A better way is to encrypt the data in PHP before saving it, so MySQL only stores the encrypted text. The sodium extension is built in PHP 7.2 and above.

<?php
$key = sodium_crypto_secretbox_keygen();                         // save this key safely
$nonce = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);

$cipher = sodium_crypto_secretbox("my secret", $nonce, $key);    // save $nonce and $cipher in the database

echo sodium_crypto_secretbox_open($cipher, $nonce, $key);
?>

Output:

What is SQL injection and how to prevent it in PHP?

SQL injection is an attack where a visitor types SQL code in a form or URL, and your script runs it as part of the query. The attacker can read, change or delete data of your database.

It happens when you put user input directly inside the SQL text.

<?php
// Unsafe: the email comes from the form and goes directly in SQL
$email = $_POST["email"];
$sql = "SELECT * FROM users WHERE email = '$email'";
?>

If the visitor types ' OR '1'='1, the condition is always true and the query returns every user.

How to prevent it: use prepared statements. The SQL and the data are sent separately, so the data can never become a part of the SQL.

<?php
$stmt = $conn->prepare("SELECT id, name FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
?>

• Use prepared statements with mysqli or PDO for every query that has user data.
• Never build SQL by joining strings with variables.
• Give the database user only the permissions it needs.
• Do not show database errors to visitors.

Full examples are in PHP MySQL Prepared Statements.

What is the difference between mysqli and PDO in PHP?

Both MySQLi and PDO (PHP Data Objects) are used to connect PHP with a database. Both support prepared statements, so both can protect from SQL injection.

<?php
$pdo = new PDO("mysql:host=localhost;dbname=student;charset=utf8mb4", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT name FROM users WHERE email = :email");
$stmt->execute(["email" => "ram@example.com"]);

echo $stmt->fetchColumn();
?>

Output:

Use PDO if you may change the database in future, or you like named parameters. Use MySQLi if you work only with MySQL and want its extra features. See MySQLi prepared statements and MySQLi procedural vs object oriented.