PHP : MySQL Select Data

The SELECT statement reads data from a table. In PHP you run the query with mysqli_query(), and then read the rows one by one from the result.

---

Syntax

SELECT column1, column2 FROM table_name;

-- all columns
SELECT * FROM table_name;

Write only the columns you need. SELECT * is fine for learning, but it is slow for big tables.

---

Fetch data using PHP

• Connect to the database using db-connection.php.
• Run the query with mysqli_query(). For SELECT it returns a result object.
• Use mysqli_num_rows() to know how many rows were found.
• Read each row with mysqli_fetch_assoc() in a while loop. It gives the row as an array, and null when there are no more rows.

Suppose the users table has the 3 records that we inserted in the previous chapter.

<?php
require_once('db-connection.php');

$result = mysqli_query($conn, "SELECT id, name, mobile, email FROM users");

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row["id"] . " - " . $row["name"] . " - " . $row["mobile"] . " - " . $row["email"] . "<br>";
    }
} else {
    echo "No records found.";
}

mysqli_close($conn);
?>

Output:

---

Show the data in an HTML table

In a real page you show data in a table. Always use htmlspecialchars() when you print data, to be safe from XSS (see PHP Security Tips).

<?php
require_once('db-connection.php');

$result = mysqli_query($conn, "SELECT id, name, email FROM users");

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

while ($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>" . $row["id"] . "</td>";
    echo "<td>" . htmlspecialchars($row["name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["email"]) . "</td>";
    echo "</tr>";
}

echo "</table>";
?>

---

WHERE, ORDER BY and LIMIT

These parts of the SELECT query help you to get only the data you want:

Clause	Use	Example
WHERE	Filter the rows	WHERE name = "Ram"
ORDER BY	Sort the rows	ORDER BY name ASC (or DESC)
LIMIT	Get only some rows	LIMIT 10

SELECT id, name FROM users WHERE id > 1 ORDER BY name DESC LIMIT 2;

Next chapter: PHP : MySQL UPDATE Statement.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.