PHP MySQL Select Data

📄 PHP MySQL Select Data Tutorial

Selecting data from a MySQL database allows you to retrieve and display records in your PHP applications. You can use mysqli or PDO for this purpose.

1️⃣ Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Select data
$sql = "SELECT id, name, email, age FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row["id"] .
" - Name: " . $row["name"] .
" - Email: " . $row["email"] .
" - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Select data

$sql = "SELECT id, name, email, age FROM users";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

while($row = mysqli_fetch_assoc($result)) {

echo "ID: " . $row["id"] .

" - Name: " . $row["name"] .

" - Email: " . $row["email"] .

" - Age: " . $row["age"] . "<br>";

}

} else {

echo "0 results";

}

mysqli_close($conn);

✅ mysqli_fetch_assoc() fetches each row as an associative array.

2️⃣ Using `mysqli` Object-Oriented

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

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

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

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

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

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

}

$sql = "SELECT id, name, email, age FROM users";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"] .

" - Name: " . $row["name"] .

" - Email: " . $row["email"] .

" - Age: " . $row["age"] . "<br>";

}

} else {

echo "0 results";

}

$conn->close();

✅ $result->fetch_assoc() retrieves each row as an associative array.

3️⃣ Using PDO

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT id, name, email, age FROM users");
$stmt->execute();

// Fetch all rows as associative array
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
echo "ID: " . $user['id'] .
" - Name: " . $user['name'] .
" - Email: " . $user['email'] .
" - Age: " . $user['age'] . "<br>";
}

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null; // Close connection
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT id, name, email, age FROM users");

$stmt->execute();

// Fetch all rows as associative array

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {

echo "ID: " . $user['id'] .

" - Name: " . $user['name'] .

" - Email: " . $user['email'] .

" - Age: " . $user['age'] . "<br>";

}

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null; // Close connection

✅ fetchAll(PDO::FETCH_ASSOC) retrieves all rows as an associative array.

4️⃣ Using Prepared Statements for Select (Secure)

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

$name = "Vipul";

// Prepare statement
$stmt = $conn->prepare("SELECT id, name, email, age FROM users WHERE name = ?");
$stmt->bind_param("s", $name);
$stmt->execute();

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

$stmt->close();
$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

$name = "Vipul";

// Prepare statement

$stmt = $conn->prepare("SELECT id, name, email, age FROM users WHERE name = ?");

$stmt->bind_param("s", $name);

$stmt->execute();

// Get result

$result = $stmt->get_result();

while($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"] .

" - Name: " . $row["name"] .

" - Email: " . $row["email"] .

" - Age: " . $row["age"] . "<br>";

}

$stmt->close();

$conn->close();

✅ Prepared statements protect against SQL injection.

5️⃣ Key Points

Use mysqli_fetch_assoc() or fetch_assoc() for associative arrays.
Use PDO fetch modes for flexible data retrieval:
- PDO::FETCH_ASSOC → associative array
- PDO::FETCH_NUM → numeric array
- PDO::FETCH_OBJ → object
Prepared statements should be used for user-supplied input.
Always close connections and statements after use.

PHP MySQL Select Data