How to Select Data from MySQL Using PHP: Simple Guide

To select data from MySQL using PHP, use mysqli_connect to connect to the database, then run a SELECT query with mysqli_query. Fetch the results using mysqli_fetch_assoc to access each row's data.

Syntax

Here is the basic syntax to select data from MySQL using PHP:

• mysqli_connect: Connects to the MySQL server.
• mysqli_query: Executes the SQL SELECT statement.
• mysqli_fetch_assoc: Fetches each row as an associative array.
• mysqli_close: Closes the database connection.

php

<?php
$connection = mysqli_connect('hostname', 'username', 'password', 'database');

if (!$connection) {
    die('Connection failed: ' . mysqli_connect_error());
}

$sql = 'SELECT column1, column2 FROM table_name';
$result = mysqli_query($connection, $sql);

if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        // Access data using $row['column1'], $row['column2']
    }
} else {
    echo 'No results found';
}

mysqli_close($connection);
?>

Example

This example connects to a MySQL database, selects all users from a users table, and prints their names and emails.

php

<?php
$connection = mysqli_connect('localhost', 'root', '', 'testdb');

if (!$connection) {
    die('Connection failed: ' . mysqli_connect_error());
}

$sql = 'SELECT name, email FROM users';
$result = mysqli_query($connection, $sql);

if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo 'Name: ' . $row['name'] . ', Email: ' . $row['email'] . "<br>";
    }
} else {
    echo 'No users found';
}

mysqli_close($connection);
?>

Output

Name: Alice, Email: [e-mail]
Name: Bob, Email: [e-mail]
Name: Carol, Email: [e-mail]

Common Pitfalls

Common mistakes when selecting data from MySQL using PHP include:

• Not checking if the connection was successful before running queries.
• Not verifying if the query returned any rows before fetching data.
• Using wrong column names that do not exist in the table.
• Not closing the database connection after finishing.

Always handle errors to avoid silent failures.

php

<?php
// Wrong: No connection check
$connection = mysqli_connect('localhost', 'root', '', 'testdb');
$result = mysqli_query($connection, 'SELECT name FROM users');

// Right: Check connection and query result
$connection = mysqli_connect('localhost', 'root', '', 'testdb');
if (!$connection) {
    die('Connection failed: ' . mysqli_connect_error());
}
$result = mysqli_query($connection, 'SELECT name FROM users');
if (!$result) {
    die('Query failed: ' . mysqli_error($connection));
}
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['name'] . "<br>";
    }
} else {
    echo 'No data found';
}
mysqli_close($connection);
?>

Quick Reference

Remember these key functions when selecting data from MySQL using PHP:

Function	Purpose
mysqli_connect	Connect to MySQL database
mysqli_query	Run SQL query
mysqli_fetch_assoc	Fetch a row as an associative array
mysqli_num_rows	Get number of rows in result
mysqli_close	Close the database connection

Key Takeaways

Always check if the database connection is successful before running queries.

Use mysqli_query to run your SELECT statement and mysqli_fetch_assoc to read each row.

Verify that your query returned results before trying to access data.

Close the database connection with mysqli_close when done.

Handle errors gracefully to avoid silent failures.