PHP and Databases

Databases are structured repositories that store and organize vast amounts of data in a way that allows for efficient retrieval and manipulation. Popular database management systems include MySQL, SQLite, PostgreSQL, and more. For our examples, let’s consider MySQL, a widely used relational database management system.

PHP offers two primary methods to connect to databases: PDO (PHP Data Objects) and mysqli (MySQL Improved). Both methods provide ways to establish a connection and interact with the database. Let’s demonstrate how to connect to a MySQL database using PDO:

<?php
$host = “localhost”;
$username = “your_username”;
$password = “your_password”;
$database = “your_database”;

try {
$conn = new PDO(“mysql:host=$host;dbname=$database”, $username, $password);
echo “Connected successfully!”;
} catch (PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
}
?>

Once connected to the database, you can execute SQL queries to perform various operations, such as inserting, updating, or retrieving data. Here’s an example of executing a simple SELECT query using PDO:

<?php
// Assuming the connection is established

$stmt = $conn->prepare(“SELECT id, name, age FROM users WHERE age > :age”);
$stmt->bindParam(‘:age’, 18, PDO::PARAM_INT);
$stmt->execute();

while ($row = $stmt->fetch()) {
echo “ID: {$row[‘id’]}, Name: {$row[‘name’]}, Age: {$row[‘age’]} <br>”;
}
?>

Prepared statements are a crucial security measure to protect against SQL injection attacks. They allow you to separate SQL code from user input, making it safe to handle dynamic queries. Prepared statements are available in both PDO and mysqli. Here’s an example of a prepared statement using PDO:

<?php
// Assuming the connection is established

$name = $_POST[‘name’];
$email = $_POST[’email’];

$stmt = $conn->prepare(“INSERT INTO users (name, email) VALUES (:name, :email)”);
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:email’, $email);

if ($stmt->execute()) {
echo “User added successfully!”;
} else {
echo “Error in adding user.”;
}
?>