Selecting Distinct Rows in MySQL with PHP

When working with databases, you often encounter duplicate rows in a table (especially if you’re selecting all rows from a table).

To filter out these duplicates and get unique values, you can use the DISTINCT keyword in MySQL. In this blog post, we’ll explore how to use SELECT DISTINCT with PHP and MySQL to retrieve distinct rows from a database. We’ll walk through examples and cover all the steps necessary for implementation.

Table of Contents

1. Introduction to SELECT DISTINCT
2. Why Use DISTINCT?
3. How to Implement SELECT DISTINCT in PHP with MySQL
4. Code Example for SELECT DISTINCT in PHP
5. Conclusion

1. Introduction to SELECT DISTINCT

The SELECT DISTINCT statement in MySQL is used to retrieve unique values from a column or combination of columns in a table. Without this keyword, SQL will return all matching rows, even if they are duplicates. By using DISTINCT, only the first occurrence of each value is returned, and all subsequent duplicates are ignored.

Basic Syntax:

SELECT DISTINCT column1, column2 FROM table_name;

• column1, column2: The columns from which you want to retrieve distinct values.
• table_name: The name of the table.

For example, if you have a list of customers and their cities, and you want to know which unique cities they come from, you would use DISTINCT to avoid duplicates.

2. Why Use DISTINCT?

There are many use cases where you’d want to retrieve distinct values. Some examples include:

• Unique Categories: If you have a product database with categories, and you want to list all distinct categories.
• Unique Users: If you’re analyzing user data and want to know which countries or cities users are from, without duplicates.
• Data Analysis: For reporting purposes, retrieving only distinct values helps summarize large datasets.

Using SELECT DISTINCT ensures that your data retrieval is efficient and that your query results are clean without unnecessary duplicates.

3. How to Implement SELECT DISTINCT in PHP with MySQL

Step 1: Establish a Connection to the Database

Before executing any SQL query, you need to connect to your MySQL database. In PHP, you can use the mysqli extension to handle the connection.

Here’s an example of how to set up the database connection:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

This code creates a connection to the database using the server name, username, password, and database name.

Step 2: Write the SELECT DISTINCT SQL Query

Now, let’s say you have a table called customers with columns id, name, and city. To get the unique cities from the customers table, you would write the following SQL query:

SELECT DISTINCT city FROM customers;

This query returns a list of distinct cities where customers are located.

Step 3: Execute the Query in PHP

You can execute the SQL query using the mysqli_query() function in PHP:

$sql = "SELECT DISTINCT city FROM customers";
$result = $conn->query($sql);

Step 4: Fetch and Display the Results

Once the query has been executed, the results can be retrieved and displayed. You’ll loop through the results and print each distinct value:

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "City: " . $row["city"]. "<br>";
    }
} else {
    echo "0 results";
}

In this example, the loop will display each unique city found in the customers table. If no rows are returned, a message stating “0 results” will be displayed.

Step 5: Close the Database Connection

To keep your application efficient and secure, always close the database connection when you’re done:

$conn->close();

4. Code Example for SELECT DISTINCT in PHP

Now that we’ve gone through the steps, let’s put it all together in a complete PHP script that retrieves and displays distinct values from a MySQL table.

Full PHP Code:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

$sql = "SELECT DISTINCT city FROM customers";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each distinct city
    while($row = $result->fetch_assoc()) {
        echo "City: " . $row["city"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Expected Output:

The script will output something like:

City: New York
City: London
City: Berlin

If all customers in your table are from the same city, the result might look like:

City: New York

5. Conclusion

Using SELECT DISTINCT in MySQL with PHP is an effective way to filter out duplicate rows and retrieve only unique values from your database. This method can be especially useful when working with large datasets where you want to reduce redundancy and present more meaningful, summarized results.

In this tutorial, we covered:

• How to write a SELECT DISTINCT query in MySQL.
• How to implement SELECT DISTINCT using PHP and MySQL.
• A complete PHP example to retrieve distinct values from a table.

With this knowledge, you can now add data filtering to your PHP projects, providing users with cleaner and more useful data views.

Additional Resources:

• PHP mysqli_query Documentation
• MySQL DISTINCT Clause Documentation