PHP MySQL Select Data

PHP MySQL Select Data Tutorial

In PHP MySQL Select Data allows you to retrieve and display records in your PHP applications. You can use mysqli or PDO for this purpose.

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.

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.

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.

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.

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