How to eliminate repeated results

Question

I am doing a query but it returns repeated dates

<select name="option_date">
        <?php
            $sql_product_id = "SELECT * FROM `pat_order_product` WHERE `product_id` = $id_product";
            $result_product_id = mysql_query($sql_product_id,$link);
            while($row_product_id = mysql_fetch_array($result_product_id)){
                $product_options = str_replace('\\','', $row_product_id["product_options"]);
                $data = $product_options;
                $books = json_decode($data, true);
                $product_option_date = $books[0]['value']['name'];
        ?>
                <option value="<?php echo $product_option_date; ?>"><?php echo $product_option_date; ?></option>
        <?php } ?></select>

And when I execute the query, it results in these repeated dates

<select name="option_date">
    <option value="26 Oct 2019">26 Oct 2019</option>
    <option value="26 Oct 2019">26 Oct 2019</option>
    <option value="26 Oct 2019">26 Oct 2019</option>
    <option value="09 Nov 2019">09 Nov 2019</option>
    <option value="09 Nov 2019">09 Nov 2019</option>
</select>

What I'm looking for is that I only get one result from each date

26 Oct 2019

09 Nov 2019

One thing to look for in code is the way you seem to pass a value round from on variable to another and do nothing with it - `$product_options` - `$data` - `json_decode($data, true)`. If you don't need to reuse a variable, consider do you really need to create it in the first place. I accept that in some cases it improves code readability, but that can only be determined on a case by case basis. — Nigel Ren, Oct 03 '19 at 06:08

score 4 · Answer 1 · answered Oct 03 '19 at 05:33

You can loop over the results, and generate an array of values. Using array_unique() on that array, you are ensured to only get unique values.

You should also be using a prepared statement when dealing with user-input in a query (this means using mysqli_ instead of the highly outdated and insecure mysql_ API).

<?php 
// Create a MySQLi connection and set exception handling mode
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli("localhost", "user", "pass", "database");

// Get the product-options and put the dates in an array using a prepared statement
$result = [];
$sql = "SELECT product_options FROM pat_order_product WHERE product_id = ?";
$stmt = $link->prepare($sql);
$stmt->bind_param("s", $id_product);
$stmt->execute();
$stmt->bind_result($product_options);
while ($stmt->fetch()) {
    $data = str_replace('\\', '', $product_options);
    $books = json_decode($data, true);
    $result[] = $books[0]['value']['name'];
}

// Loop the results with array_unique() to get unique results, and print it
?>
<select name="option_date">
    <?php 
    foreach (array_unique($result) as $value) {
        ?>
        <option value="<?php echo $value; ?>"><?php echo $value; ?></option>
        <?php
    }
    ?>
</select>

Useful reading material

score 2 · Accepted Answer · answered Oct 03 '19 at 05:08

The date you're displaying is embedded in unstructured data in the product_options field, so it will be difficult to accurately get the response you're hoping for using SQL.

Instead, in your PHP you'll need to iterate through the dates to provide a list of distinct dates.

One way to do this is to use the value you need to be distinct as the key in a dictionary. The value is irrelevant. Because of how dictionaries work, you will only get the values once.

$distinct_dates = [];
while($row_product_id = mysql_fetch_array($result_product_id)){
    $product_options = str_replace('\\','', $row_product_id["product_options"]);
    $data = $product_options;
    $books = json_decode($data, true);
    $product_option_date = $books[0]['value']['name'];
    $distinct_dates[$product_option_date] = 1;
}

Then, when you're ready to emit the select box, you can do this:

<select name="option_date">
<?php foreach(array_keys($distinct_dates) as $date) { ?>
    <option value="<?php echo $date; ?>"><?php echo $date; ?></option>
<? } ?>
</select>

How to eliminate repeated results

2 Answers2