Executing SQL Queries and Retrieving Data in PHP

Structured Query Language (SQL) is a powerful language used for managing databases. In a PHP application, it is common to interact with a database by executing SQL queries and retrieving data. In this article, we will explore how to effectively execute SQL queries and retrieve data in PHP.

Establishing a Database Connection

Before executing any SQL queries, it is important to establish a connection with the database. PHP provides various methods for connecting to different database systems such as MySQL, PostgreSQL, SQLite, etc. Here is an example of connecting to a MySQL database:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";
?>

Replace your_username, your_password, and your_database with the appropriate values for your MySQL server.

Executing SQL Queries

Once the database connection is established, you can execute SQL queries using various methods provided by PHP. The most commonly used method is query(), which executes a SQL statement and returns a result set. Here's an example:

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Username: " . $row["username"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

In the example above, we execute a SELECT statement to retrieve all users from the users table. The result set is then iterated using a while loop, and the username of each user is printed.

Retrieving Data

To retrieve data from a result set, we can use various methods provided by PHP. Here are some commonly used methods:

• fetch_assoc(): Returns the current row of the result set as an associative array.
• fetch_row(): Returns the current row of the result set as a numeric array.
• fetch_object(): Returns the current row of the result set as an object.

Let's modify the previous example to retrieve more user details:

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Username: " . $row["username"] . "<br>";
        echo "Email: " . $row["email"] . "<br>";
        // ...
    }
} else {
    echo "No users found";
}

$conn->close();
?>

In the modified example, we access other columns like "email" from the result set using the associative array syntax.

Conclusion

Executing SQL queries and retrieving data is a fundamental operation in PHP when working with databases. By establishing a database connection, executing queries, and retrieving data, you can effectively interact with your database in your PHP applications. Remember to always sanitize user inputs and use prepared statements to prevent SQL injection attacks.