PHP MySQL Select Data

Welcome to The Coding College! In this tutorial, we’ll explore how to use PHP to retrieve data from a MySQL database. Fetching data is one of the most fundamental database operations when building web applications.

How to Select Data in PHP from MySQL

To select data, we use the SQL SELECT statement. This statement allows us to retrieve data from specific columns or all columns in a table, optionally applying filters using the WHERE clause.

Steps to Select Data:

  1. Connect to the database using MySQLi or PDO.
  2. Write the SQL SELECT query.
  3. Execute the query.
  4. Fetch the results.
  5. Process and display the results.

Method 1: Using MySQLi (Object-Oriented)

Example: Fetch and Display All Rows

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// SQL query to fetch all rows
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);

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

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

Explanation:

  • $conn->query($sql): Executes the SQL query.
  • $result->num_rows: Returns the number of rows retrieved.
  • $result->fetch_assoc(): Fetches each row as an associative array.

Example: Fetch Data with a Condition

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// SQL query to fetch rows where age is greater than 25
$sql = "SELECT id, username, email FROM Users WHERE age > 25";
$result = $conn->query($sql);

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

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

Method 2: Using MySQLi (Prepared Statements)

Prepared Statements are more secure and should always be used when dealing with user inputs.

Example: Fetch Data with User Input

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Prepare the SQL query with placeholders
$sql = "SELECT id, username, email FROM Users WHERE age > ?";
$stmt = $conn->prepare($sql);

// Bind the parameter
$min_age = 25; // Example value
$stmt->bind_param("i", $min_age);

// Execute the prepared statement
$stmt->execute();

// Get the result
$result = $stmt->get_result();

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

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

Explanation:

  • Prepared Statements protect against SQL injection by separating the query structure from the parameter values.
  • Use bind_param() to bind parameters with specific types (i for integers, s for strings).

Method 3: Using PDO (PHP Data Objects)

Example: Fetch All Rows with PDO

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query
    $sql = "SELECT id, username, email FROM Users";

    // Execute the query
    $stmt = $conn->query($sql);

    // Fetch and display results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Example: Fetch Data with Conditions Using PDO

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query with a placeholder
    $sql = "SELECT id, username, email FROM Users WHERE age > :age";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Bind the parameter
    $stmt->bindParam(':age', $min_age, PDO::PARAM_INT);

    // Set the parameter value
    $min_age = 25;

    // Execute the prepared statement
    $stmt->execute();

    // Fetch and display results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Fetching Data in Different Formats

With PDO, you can fetch data in various formats:

  • Associative Array: 
$row = $stmt->fetch(PDO::FETCH_ASSOC);
  • Numerical Array: 
$row = $stmt->fetch(PDO::FETCH_NUM);
  • Both: 
$row = $stmt->fetch(PDO::FETCH_BOTH);
  • Object: 
$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->username;

Best Practices for Selecting Data

  1. Use Prepared Statements: Always use prepared statements to avoid SQL injection.
  2. Check Query Results: Validate if rows are returned before processing results.
  3. Handle Exceptions: Use try-catch blocks in PDO for better error handling.
  4. Close Connections: Always close statements and database connections to free resources.

Conclusion

Fetching data from a MySQL database in PHP is a key skill for any developer. By using MySQLi or PDO, you can write secure and efficient database queries. For more in-depth tutorials and coding insights.

Leave a Comment