5. Databases and PHP

Introduction to databases

A database is a collection of data that is organized and stored so that it can be easily retrieved, updated, and managed. Databases play a crucial role in web development as they provide a way to store and retrieve large amounts of data. This data can be anything from user information, website content, product information, and more.

There are different types of databases, including relational databases, NoSQL databases, and object-oriented databases. The most commonly used type of database in web development is the relational database, which uses tables to store data and relationships between data.

When working with databases in PHP, developers can use a variety of tools, including PDO (PHP Data Objects), MySQLi, and others. These tools allow for easy and efficient interaction with databases, allowing developers to retrieve, insert, update, and delete data with just a few lines of code.

Overall, understanding how to work with databases is an important part of web development and is a key aspect of PHP programming.

MySQL and PHP

MySQL is a popular relational database management system that is commonly used with PHP. PHP provides several extensions to interact with MySQL, including the MySQLi extension and the PDO (PHP Data Objects) extension. Both extensions provide methods for connecting to a MySQL database, executing SQL statements, and retrieving the results.

MySQLi extension is a set of functions that provide an interface for interacting with a MySQL database. With the MySQLi extension, you can perform a wide range of database operations, including creating tables, inserting, updating, and deleting data, and executing queries.

The PDO extension provides an object-oriented interface for working with databases. It allows you to write database-agnostic code, as it provides a unified API for working with multiple databases, including MySQL.

Both MySQLi and PDO are popular and widely used, and the choice between them depends on your personal preferences and project requirements. However, it's generally recommended to use PDO for new projects as it provides better security features and error handling compared to MySQLi.

Connecting to a database

To connect to a database in PHP using the MySQL extension, you can use the mysqli_connect() function.

The function takes the following parameters:

mysqli_connect(host, username, password, database_name, port);

Here's an example of connecting to a database:

<?php

$host = "hostname";
$username = "username";
$password = "password";
$database = "database_name";

// Create connection using mysqli
$conn = mysqli_connect($host, $username, $password, $database);

// Check if the connection is successful
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Close the connection
mysqli_close($conn);
?>

The mysqli_connect() function returns a connection resource that can be used to perform operations on the database. If the function fails to connect to the database, it will return FALSE. In this case, you can use the mysqli_connect_error() function to get the error message.

Once you are done working with the database, you should close the connection by calling the mysqli_close() function.

Querying a database

Querying a database is a process of retrieving data from a database. In PHP, the database can be queried using the SQL (Structured Query Language) commands. There are several functions in PHP that allow us to execute SQL statements and fetch the results from the database. The most commonly used functions are:

mysql_query(): This function is used to execute a SQL query.

<?php

$conn = mysql_connect("hostname", "username", "password");

mysql_select_db("database_name", $conn);

$result = mysql_query("SELECT * FROM table_name", $conn);

mysqli_query(): This function is an improved version of mysql_query(). It provides an interface for database communication.

<?php

$conn = mysqli_connect("hostname", "username", "password");

$result = mysqli_query($conn, "SELECT * FROM table_name");

PDO (PHP Data Objects): This is a database-independent PHP extension that provides a set of functions for database communication.

<?php

try {
    // Create a new PDO instance with error handling
    $conn = new PDO("mysql:host=hostname;dbname=database_name", "username", "password");

    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Run the query
    $result = $conn->query("SELECT * FROM table_name");

    // Check if the query was successful and fetch the results
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "Row data: " . implode(", ", $row) . "<br>";
    }

} catch (PDOException $e) {
    // Handle any errors that occur during connection or query execution
    echo "Error: " . $e->getMessage();
}

// Close the connection
$conn = null;
?>

Once the SQL query is executed, the result can be fetched using functions such as mysql_fetch_array(), mysqli_fetch_array(), or PDO fetch methods. The result can then be displayed or used in further processing.

It's important to note that the mysql_ functions are now deprecated and it is recommended to use the improved mysqli_ functions or the PDO extension for database communication in PHP.

Prepared statements

Prepared statements are a feature in database systems that allow developers to execute the same SQL statement repeatedly with different parameters. This is useful when you have to perform similar database operations multiple times, such as inserting data into a table, updating records, or fetching data from a database.

A prepared statement is created by sending a template of an SQL statement to the database server, where the server parses and compiles the statement. The parsed statement can then be executed multiple times with different parameters, which are passed as arguments to the prepared statement. The benefits of using prepared statements are that they help to prevent SQL injection attacks and improve the performance of database operations.

In PHP, you can use the mysqli or PDO extension to execute prepared statements. Here's an example using the mysqli extension:

<?php

// Connect to the database
$db = mysqli_connect('hostname', 'username', 'password', 'database');

// Check the connection
if (!$db) {
    die("Connection failed: " . mysqli_connect_error());
}

// Prepare the statement
$stmt = mysqli_prepare($db, "INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)");

// Check if preparation was successful
if ($stmt === false) {
    die("Error preparing statement: " . mysqli_error($db));
}

// Assign values to the variables
$first_name = "John";
$last_name = "Doe";
$email = "johndoe@example.com";

// Bind the parameters to the statement
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

// Execute the statement
mysqli_stmt_execute($stmt);

// Check if the statement executed successfully
if (mysqli_stmt_affected_rows($stmt) > 0) {
    echo "Record inserted successfully!";
} else {
    echo "Error inserting record.";
}

// Close the statement and connection
mysqli_stmt_close($stmt);
mysqli_close($db);

?>

And here's an example using the PDO extension:

<?php

try {
    // Connect to the database
    $db = new PDO('mysql:host=host;dbname=database', 'username', 'password');
    
    // Set error mode to exceptions for better error handling
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare the statement
    $stmt = $db->prepare("INSERT INTO users (first_name, last_name, email) VALUES (:first_name, :last_name, :email)");
    
    // Assign values to the variables before binding
    $first_name = "John";
    $last_name = "Doe";
    $email = "johndoe@example.com";
    
    // Bind the parameters to the statement
    $stmt->bindParam(':first_name', $first_name);
    $stmt->bindParam(':last_name', $last_name);
    $stmt->bindParam(':email', $email);
    
    // Execute the statement
    $stmt->execute();

    echo "Record inserted successfully!";
} catch (PDOException $e) {
    // Handle any PDO exceptions (e.g., connection or SQL errors)
    echo "Error: " . $e->getMessage();
}

?>

In both examples, the statement is first prepared, then the parameters are bound to the statement, and finally the statement is executed. By using prepared statements, you can ensure that your database operations are secure and efficient.