MySQL MIN
Filter

MySQL MIN

by Soeng Souy /
Share

MySQL MIN() Function

The MIN() function in MySQL is used to retrieve the smallest value from a column in a table. It is commonly used in aggregation queries with GROUP BY or without grouping. Below is a detailed step-by-step guide on using MIN() for a website or application:


1. Understand the Syntax

The basic syntax for the MIN() function is:

SELECT MIN(column_name)
FROM table_name
[WHERE condition];

2. Sample Data Setup

Let's assume we have a table called products:

product_idproduct_nameprice
1Laptop800
2Smartphone600
3Tablet400
4Smartwatch200

3. Using MIN() Without Conditions

To find the lowest price in the products table:

SELECT MIN(price) AS lowest_price
FROM products;

Result:

lowest_price
200

4. Using MIN() With a Condition

You can add a WHERE clause to filter results. For example, find the lowest price for products costing more than $300:

SELECT MIN(price) AS lowest_price
FROM products
WHERE price > 300;

Result:

lowest_price
400

5. Using MIN() With GROUP BY

If you want to find the minimum price grouped by a category, you need a category column in your table. For example:

product_idcategoryprice
1Electronics800
2Electronics600
3Gadgets400
4Gadgets200

Query:

SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category;

Result:

categorylowest_price
Electronics600
Gadgets200

6. Integrate Into a Website

To display MIN() results on a website, follow these steps:

a. Write the Query

Write the desired SQL query, such as finding the lowest price in a specific category.

b. Connect to MySQL Database

Use your preferred backend language (e.g., PHP, Python, Node.js) to connect to the database. Example in PHP:

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

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

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

// Query to find the minimum price
$sql = "SELECT MIN(price) AS lowest_price FROM products";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data
    while ($row = $result->fetch_assoc()) {
        echo "Lowest Price: " . $row["lowest_price"];
    }
} else {
    echo "No results found.";
}

$conn->close();
?>

c. Display on Frontend

Format the output (e.g., using HTML or a framework like React, Angular, etc.):

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Lowest Price</title>
</head>
<body>
    <h1>Product Prices</h1>
    <div id="prices">
        <!-- PHP Script above outputs here -->
    </div>
</body>
</html>

7. Testing and Optimization

  • Test queries in tools like MySQL Workbench or phpMyAdmin.
  • Use indexes on columns used in WHERE or GROUP BY for better performance.
  • Secure your queries against SQL injection by using prepared statements.

By following these steps, you can successfully use the MIN() function to retrieve and display the smallest value from your database on your website!

Tag
Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close