Executing Simple Queries

Executing SQL Queries and Fetching Results in PHP

Interacting with databases is an essential part of modern web development. PHP makes this process easier by offering built-in extensions such as MySQLi and PDO. In this topic, we will focus on MySQLi, which supports both object-oriented and procedural approaches.

While building PHP applications, we often need to execute SQL commands. Fortunately, PHP provides the mysqli_query() function to send queries to the database. This function is easy to use and works with both query types that return data (like SELECT) and those that modify data (like INSERT or UPDATE).

Executing an SQL Query

To run a query, we must provide a valid database connection and a SQL statement. Depending on the coding style, the syntax varies slightly.

Syntax

Object-Oriented Style:

$mysqli->query("SQL Query", resultmode);

Procedural Style:

mysqli_query(connection, "SQL Query", resultmode);

The first parameter is always the connection object or resource. The second is the actual SQL query string. Optionally, you may include the resultmode to control how the result is returned.

Sometimes, developers use mysqli::real_query() to execute a statement without retrieving data immediately. Others prefer mysqli::multi_query() for sending multiple queries at once. Nevertheless, mysqli::query() remains the most popular, as it handles both execution and result buffering in one step.

Fetching Results from the Database

The return value of mysqli_query() depends on the type of SQL command. For read-type queries like SELECT, SHOW, or DESCRIBE, the function returns a mysqli_result object. On the other hand, write-type queries such as INSERT, UPDATE, or DELETE return true when successful. If something goes wrong, the function returns false.

Once the result is available, the next step is to fetch the data.

Using fetch_assoc() to Retrieve Data

PHP provides the fetch_assoc() method to get each row as an associative array. This is useful because the data can be accessed using column names, making the code easier to understand and maintain.

Example:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");
$sql = "SELECT id, firstname, lastname FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Output:

ID: 1 - Name: Amit Kumar  
ID: 2 - Name: Riya Sharma

This approach is efficient. It allows developers to loop through the result set and display data dynamically. Moreover, the fetched data can be placed within an HTML table or formatted layout to create interactive pages.

Summary

To sum up, executing simple queries in PHP using MySQLi involves calling the mysqli_query() function. This function sends the query to the database and returns either a result object or a success flag. When working with data retrieval commands like SELECT, the returned result must be looped through using fetch_assoc(). This allows you to extract each row and use it in your application. By using this method, developers can build dynamic, data-driven web pages efficiently.