PHP MySQL: Querying Data from Database

PHP MySQL: Querying Data from a Database

Querying data from a MySQL database using PHP involves executing SQL SELECT statements. The retrieved data can be displayed or processed further in your PHP application.

Steps to Query Data

1. Connect to the MySQL Database
2. Write and Execute the SQL Query
3. Fetch and Process the Results
4. Close the Database Connection

Code Example: Querying Data

1. Establish a Database Connection

Use the mysqli or PDO extension for database connectivity. Below is an example using mysqli.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

2. Execute a SELECT Query

Write and execute an SQL query using the query method.

<?php
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
?>

3. Process the Result Set

You can fetch results row by row using fetch_assoc, fetch_row, or fetch_array.

Fetch with fetch_assoc (Associative Array)

<?php
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found";
}
?>

Fetch with fetch_row (Numerical Array)

<?php
if ($result->num_rows > 0) {
    while ($row = $result->fetch_row()) {
        echo "ID: " . $row[0] . " - Name: " . $row[1] . " - Email: " . $row[2] . "<br>";
    }
}
?>

Fetch with fetch_array (Both Associative and Numerical Array)

<?php
if ($result->num_rows > 0) {
    while ($row = $result->fetch_array()) {
        echo "ID: " . $row[0] . " - Name: " . $row["name"] . " - Email: " . $row[2] . "<br>";
    }
}
?>

4. Free Result and Close Connection

Always free the result set and close the connection after use:

<?php
$result->free();
$conn->close();
?>

Complete Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Execute SQL query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found";
}

// Free result and close connection
$result->free();
$conn->close();
?>

Using Prepared Statements

Prepared statements are more secure and help prevent SQL injection. Below is an example:

<?php
$conn = new mysqli("localhost", "root", "", "example_db");

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

// Prepare and execute a query
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

$id = 1; // Example dynamic input
$stmt->execute();

// Fetch result
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Key Notes

1. Escape User Input: Always escape or validate user inputs to prevent SQL injection (use mysqli_real_escape_string or prepared statements).
2. Error Handling: Use error handling to catch and debug issues during database operations.
3. Optimize Queries: Index columns are frequently used in the WHERE clause for better performance.
4. Secure Credentials: Avoid hardcoding database credentials in your scripts; use environment variables or a configuration file.

Let me know if you'd like help with more advanced examples!