How to Select Data Using PHP and MySQL?

php select data from mysql

In this article, You will understand everything you need to know about how to use PHP to select data from MySQL—using procedural and object-oriented methods. You will also learn more about PDO (PHP Data Objects), which is a great alternative for working with databases.

If you need to fetch data from a MySQL database in your PHP applications, you’ll rely heavily on SQL’s SELECT statement. The SELECT statement enables you to retrieve specific data from tables and use it in your application.

Basic Syntax of the SQL SELECT Statement

The below syntax show you how to write the SELECT statement in SQL:

SELECT column1, column2, ... FROM table_name;

This simple command retrieves data from the specified columns of a table. If you want to fetch everything, you can use the * wildcard:

SELECT * FROM table_name;

This command helps you to pull all data from the table, which can be handy, but for some reason, you will often want to be more specific.

Let’s move on to the following section to see how it works with PHP.

Select Data Using MySQL in PHP

You can start running SQL queries. Here’s how you can select data using the mysqli extension:

$db_server_name= "localhost";
$db_username = "username";
$db_password = "password";
$db_dbname = "database_name";

$condb = new mysqli($db_server_name, $db_username , $db_password , $db_dbname );

if ($condb->connect_error) {
   die("Unable to Establish Database Access:". $condb->connect_error);
}

$sql = "SELECT * FROM users";
$dbresult= $condb->query($sql);

if ($dbresult->num_rows > 0) {
    while($row = $dbresult->fetch_assoc()) {
        echo "rec_id: " . $row["user_id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}

This example retrieves all rows from the users table and displays the id and name fields. The fetch_assoc() function retrieves a result row from database as an associative array.

Procedural Method to Select Data From MySQL within PHP

You can connect to the database and fetch data in a similar way. Here is an example:

$condb= mysqli_connect($db_server_name, $db_username, $db_password, $db_dbname);

if (!$condb) {
    die("Unable to Establish Database Access:" . mysqli_connect_error());
}

$sql = "SELECT * FROM users";
$dbresult= mysqli_query($condb, $sql);

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

mysqli_close($condb);

This syntax might be simple, but it does the job perfectly. Just do not forget to close the connection yourself once you are done fetching the data.

Using PDO to Select Data

PDO is considered more secure because it supports prepared statements, helping protect against SQL injection. Here’s an example using PDO:

try {
    $condb = new PDO("mysql:host=$db_server_name;dbname=$db_name", $db_username, $db_password);
    $condb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $condb->prepare("SELECT id, name FROM users");
    $stmt->execute();
    
    $dbresult= $stmt->fetchAll();
    
    foreach ($dbresultas $row) {
        echo "id: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$condb= null;

Let’s see in the following example how to handle results from the query.

Fetching Results from the Query

After executing the SELECT statement, you will want to handle the results. PHP helps you to use several ways to fetch the results:

  • fetch_assoc(): Returns an associative array.
  • fetch_array(): Retrieve both an associative array and a numeric array.
  • fetch_object(): Returns an object.

Here’s an example of using these methods:

while ($row = $dbresult->fetch_assoc()) {
    echo $row["id"] . " " . $row["name"] . "<br>";
}

while ($row = $dbresult->fetch_array()) {
    echo $row[0] . " " . $row[1] . "<br>";
}

while ($row = $dbresult->fetch_object()) {
    echo $row->id . " " . $row->name . "<br>";
}

Each of these methods provides a different way of accessing the data depending on how you want to structure your result handling.

Select Data Using Object-Oriented in MySQL Using PHP

The object-oriented method with MySQLi allows you to use classes and objects, which can feel more organized. Here’s an example:

$condb= new mysqli($db_server_name, $db_username, $db_password, $db_dbname);

if ($condb->connect_error) {
    die("Unable to Establish Database Access:". $condb->connect_error);
}

$sql = "SELECT * FROM users";
$dbresult= $condb->query($sql);

if ($dbresult->num_rows > 0) {
    while($row = $dbresult->fetch_assoc()) {
        echo "rec_id: " . $row["user_id"]." - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}

$condb->close();

It’s more organized and readable, especially if your project has complex database interactions.

Let’s see how to display the data in an HTML table.

Displaying Data in an HTML Table

You can display it in an HTML table for a neat presentation once you have the data:

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

while($row = $dbresult->fetch_assoc()) {
    echo "<tr><td>" . $row["id"]. "</td><td>" . $row["name"]. "</td></tr>";
}

echo "</table>";

This code will create a simple table to display the results in a tabular format.

Let’s summarize it.

Wrapping Up

Getting data from a MySQL database with PHP might seem a bit confusing at first, but once you understand the steps, it will be easy. So if you like using of mysqli or PDO, getting the basics down will help you create secure database-driven projects.

Here is a quick recap of what we explained:

  • SQL SELECT Syntax
  • Procedural style
  • Object-oriented style
  • Select data using PDO

FAQs

How do I fetch data from MySQL using PHP?

To fetch data from MySQL in PHP, use mysqli or PDO. Here’s a basic mysqli example:

$condb = new mysqli("localhost", "username", "password", "database_name");

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

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

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

$condb->close();

Q2: What is the difference between MySQLi and PDO in PHP?

  • MySQLi works only with MySQL.
  • PDO supports many databases like MySQL, PostgreSQL, and SQLite.
  • PDO uses prepared statements, which improve security against SQL injection.
  • MySQLi offers both procedural and object-oriented styles.

How do I display MySQL data in an HTML table using PHP?

You loop through the results and build table rows. Example:

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

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

echo "</table>";

How do I connect to MySQL using PDO in PHP?

Use a try-catch block to connect and handle errors.

try {
    $conn = new PDO("mysql:host=localhost;dbname=database_name", "username", "password");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

What is a prepared statement in PHP?

A prepared statement lets you run the same SQL query many times with different values. It protects against SQL injection. Example using PDO:

$stmt = $conn->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->bindParam(':id', $id);
$id = 1;
$stmt->execute();
$result = $stmt->fetchAll();

foreach ($result as $row) {
    echo $row['id'] . " - " . $row['name'];
}

How do I fetch data from MySQL using PHP?

To fetch data from MySQL in PHP, use mysqli or PDO. Here’s a simple mysqli example:

$condb = new mysqli("localhost", "username", "password", "database_name");

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

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

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

$condb->close();

Similar Reads

Mastering PHP’s Do-While Loop: Examples and Explanation

The PHP do-while loop is a type of loop that executes a block of code at least once, and then…

PHP filter_var_array: How to Validate Multiple Inputs

The filter_var_array() appeared to make input validation simple and sanitization in PHP. It handles multiple inputs with different filters was…

PHP Spread Operator: Understand How (…) Syntax Works

Before the spread operator, PHP developers had to pass array items to a function using extra steps. They often had…

PHP array_all Function: How it Works with Examples

PHP 8.4 released a new built-in function, array_all checks to check if all array values meet a condition. It gives…

PHP $GLOBALS: Access Global Variables with Examples

When you start working with PHP, you’ll soon need access to variables from multiple places in your code. For this,…

PHP is_readable: Check File Accessibility

The PHP is_readable helps you validate file readability before any operation like read or include files. What Is PHP is_readable? The is_readable() function…

PHP Boolean: Assigning True or False to a Variable

You can assign the boolean data type to PHP variables or use it as a direct value. This enables you…

PHP Strings: Types, Variables & Syntax Tips

If you start working with PHP, it won't take you that long to figure out that strings are everywhere. Besides…

PHP echo vs print: Key Differences & Usage Guide

Echo and print are foundational in displaying output in PHP, and though they might be similar in some ways, they…

PHP sizeof: How to Count Elements in Arrays with Examples

The sizeof function in PHP exists to help developers know how many items an array holds. Understand the sizeof Function…

Previous Article

Get Last Insert ID in PHP with MySQL PDO and MySQLi

Next Article

PHP MySQL WHERE: How to Filter Data in MySQL

Write a Comment

Leave a Comment

Your email address will not be published. Required fields are marked *


Subscribe to Get Updates

Get the latest updates on Coding, Database, and Algorithms straight to your inbox.
No spam. Unsubscribe anytime.