2.2.1 Introduction to SQL Queries in PHP

Executing SQL queries within PHP scripts is a fundamental aspect of building dynamic and data-driven web applications. SQL (Structured Query Language) is the language used to interact with relational databases like MySQL. In this section, we’ll explore the basics of executing SQL queries within PHP scripts and how it enables developers to interact with and manipulate data in the connected MySQL database.

Importance of SQL Queries in PHP:

Retrieving Data:

One of the primary functions of SQL queries in PHP is to retrieve data from the database. SELECT queries are used to fetch specific columns or entire rows based on specified conditions. This is crucial for displaying information on web pages, such as user profiles, product details, or articles.

<?php
// Example of a SELECT query
$sqlSelect = "SELECT * FROM users";
$resultSelect = mysqli_query($connection, $sqlSelect);
?>

Inserting Data:

PHP enables the execution of SQL queries to insert new records into the database. This is essential for user registrations, form submissions, or any scenario where new data needs to be added to the system.

<?php
// Example of an INSERT query
$sqlInsert = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$resultInsert = mysqli_query($connection, $sqlInsert);
?>

Updating Data:

Updating existing records in the database is another common operation. PHP allows developers to execute UPDATE queries to modify specific values in the database.

<?php
// Example of an UPDATE query
$sqlUpdate = "UPDATE users SET email = 'updated_email@example.com' WHERE username = 'john_doe'";
$resultUpdate = mysqli_query($connection, $sqlUpdate);
?>

Deleting Data:

Deleting unwanted records from the database is achieved through DELETE queries. PHP facilitates the execution of such queries to maintain data integrity.

<?php
// Example of a DELETE query
$sqlDelete = "DELETE FROM users WHERE username = 'john_doe'";
$resultDelete = mysqli_query($connection, $sqlDelete);
?>

Basics of Executing SQL Queries in PHP:

Connection Establishment:

Before executing SQL queries, a connection to the MySQL database must be established using the mysqli_connect() function. This connection is essential for subsequent query execution.

<?php
// Example of establishing a connection
$hostname = "localhost";
$username = "root";
$password = "your_password";
$database = "your_database";

$connection = mysqli_connect($hostname, $username, $password, $database);

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Query Execution:

PHP provides functions like mysqli_query() for executing SQL queries. These functions take the database connection and the SQL query as parameters.

<?php
// Example of executing a query
$sqlSelect = "SELECT * FROM users";
$resultSelect = mysqli_query($connection, $sqlSelect);
?>

Result Handling:

After executing a query, developers need to handle the results. For SELECT queries, mysqli_fetch_assoc() is commonly used to retrieve rows as associative arrays.

<?php
// Example of handling SELECT query results
while ($row = mysqli_fetch_assoc($resultSelect)) {
    echo "Username: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}
?>