MySQL Select Data

Retrieve and display data from MySQL database

📖 What is SELECT?

The SELECT statement retrieves data from database tables. It's the most commonly used SQL command for reading and displaying information stored in your MySQL database. 


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

Key Concepts

🎯

Select All

Retrieve all columns from table 

// Get everything
SELECT * FROM users
📋

Specific Columns

Choose only needed columns 

// Select specific
SELECT name, email FROM users
🔄

Fetch Results

Loop through retrieved data 

// Get rows
while($row = $result->fetch_assoc())
📊

Display Data

Show results to users 

// Output data
echo $row['name'];

🔹 Select All Data

Retrieve all columns and rows from a table using the asterisk (*) wildcard. This is useful when you need complete information from the table. 

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

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

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

// Select all data
$sql = "SELECT * 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 "0 results";
}

$conn->close();
?>

Output:

ID: 1 - Name: John Doe - Email: [email protected]

ID: 2 - Name: Jane Smith - Email: [email protected]

ID: 3 - Name: Bob Johnson - Email: [email protected]

🔹 Select Specific Columns

Choose only the columns you need instead of selecting all data. This improves performance and reduces memory usage, especially with large tables. 

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

// Select only name and email columns
$sql = "SELECT name, email FROM users";
$result = $conn->query($sql);

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

$conn->close();
?>

Output:

User List:

Name: John Doe - Email: [email protected]

Name: Jane Smith - Email: [email protected]

🔹 Display in HTML Table

Format query results in an HTML table for better presentation. This creates a clean, organized display of your database data. 

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

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

echo "<table border='1' cellpadding='10'>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Age</th></tr>";

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row["id"] . "</td>";
        echo "<td>" . $row["name"] . "</td>";
        echo "<td>" . $row["email"] . "</td>";
        echo "<td>" . $row["age"] . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='4'>No records found</td></tr>";
}

echo "</table>";
$conn->close();
?>

Output:

ID Name Email Age
1 John Doe [email protected] 30
2 Jane Smith [email protected] 25

🔹 Fetch Methods

MySQL provides different methods to fetch data from result sets. Each method returns data in a different format based on your needs. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");
$sql = "SELECT name, email FROM users LIMIT 1";
$result = $conn->query($sql);

// Method 1: fetch_assoc() - Associative array
$row = $result->fetch_assoc();
echo "Assoc: " . $row["name"] . "<br>";

// Reset result pointer
$result->data_seek(0);

// Method 2: fetch_row() - Numeric array
$row = $result->fetch_row();
echo "Row: " . $row[0] . "<br>";

// Reset result pointer
$result->data_seek(0);

// Method 3: fetch_array() - Both associative and numeric
$row = $result->fetch_array();
echo "Array: " . $row["name"] . " or " . $row[0] . "<br>";

$conn->close();
?>

Output:

Assoc: John Doe

Row: John Doe

Array: John Doe or John Doe

🔹 Count Results

Check how many rows were returned by your query before processing them. This helps with pagination and empty result handling. 

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

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

// Count total rows
$total = $result->num_rows;
echo "Total users: $total<br><br>";

if ($total > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . "<br>";
    }
} else {
    echo "No users in database";
}

$conn->close();
?>

Output:

Total users: 3


John Doe

Jane Smith

Bob Johnson

💡 Best Practices

  • Select only columns you need, avoid SELECT *
  • Always check if result has rows before fetching
  • Close database connection after use
  • Use LIMIT to restrict large result sets
  • Handle empty results gracefully

🧠 Test Your Knowledge

Which method returns an associative array?