How to Interact with Databases Using PHP: Connections, Queries, and Error Handling

In web development, databases are the core of dynamic, data‑driven applications, and PHP—a widely used server‑side scripting language—plays an essential role in storing, retrieving, and managing data. This article explores how PHP achieves seamless database interaction.

Connecting to the Database

PHP supports many database management systems, including MySQL , PostgreSQL , and SQLite . Establishing a connection is the first step, which can be done using mysqli_connect() or the PDO (PHP Data Objects) extension.

<code>// Using MySQLi

// Database connection
$connection = mysqli_connect("localhost", "username", "password", "database_name");

// Using PDO

// Database connection
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database_name", "username", "password");
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
</code>

Executing Queries

After establishing a connection, you can run SQL statements. PHP offers functions like mysqli_query() or prepared statements for safe and efficient queries.

<code>// Using MySQLi

// Database interaction
$query = "SELECT * FROM users";

// Execute query
$result = mysqli_query($connection, $query);

// Using PDO

// Database interaction
$query = "SELECT * FROM users";

// Prepare query
$stmt = $pdo->prepare($query);

// Execute query
$stmt->execute();
</code>

Fetching Data

You can retrieve data using functions such as mysqli_fetch_assoc() for MySQLi or fetch() for PDO.

<code>// Using MySQLi

// Database interaction
$result = mysqli_query($connection, "SELECT * FROM products");

// Process data
while ($row = mysqli_fetch_assoc($result)) {
    // Handle row data
}

// Using PDO

// Database interaction
$stmt = $pdo->prepare("SELECT * FROM products");
$stmt->execute();

// Process data
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Handle row data
}
</code>

Inserting and Updating Data

PHP allows you to add, modify, or delete records using SQL INSERT , UPDATE , and DELETE statements.

<code>// Using MySQLi

// Database interaction
$insertQuery = "INSERT INTO products (name, price) VALUES ('Product A', 19.99)";

// Execute query
mysqli_query($connection, $insertQuery);

// Using PDO

// Database interaction
$insertQuery = "INSERT INTO products (name, price) VALUES ('Product B', 24.99)";

// Execute query
$pdo->exec($insertQuery);
</code>

Closing the Database Connection

To maintain optimal performance and resource management, close the database connection after use.

<code>// Using MySQLi

mysqli_close($connection);

// Using PDO (connection automatically closes at script end)
</code>

Error Handling

Robust error handling is crucial for detecting and managing database errors. PHP provides functions like mysqli_error() and PDO’s exception handling.

<code>// Using MySQLi

if (mysqli_error($connection)) {
    // Handle the error
    $error_message = mysqli_error($connection);
    error_log($error_message);
    echo $error_message;
}

// Using PDO

try {
    $statement = $pdo->prepare('SELECT * FROM users');
    $statement->execute();
    $results = $statement->fetchAll();
} catch (PDOException $e) {
    $error_message = $e->getMessage();
    error_log($error_message);
    echo $error_message;
}
</code>

Security Issues

Prevent SQL injection and other threats by using prepared statements, input validation, and proper authentication mechanisms to ensure data security.

PHP’s database interaction capabilities enable developers to build data‑driven web applications, and mastering these techniques is essential for creating secure, reliable systems.