How to check duplicate dates?

Question

I dont want MYSQL shows duplicate dates.

My date array:

Array
(
    [0] => Array
        (
            [date] => 2020-05-04 00:00:00
        )

    [1] => Array
        (
            [date] => 2020-05-05 00:00:00
        )

    [2] => Array
        (
            [date] => 2020-05-03 00:00:00
        )

    [3] => Array
        (
            [date] => 2020-05-06 00:00:00
        )

    [4] => Array
        (
            [date] => 2020-05-04 00:00:00
        )

    [5] => Array
        (
            [date] => 2020-05-05 00:00:00
        )

    [6] => Array
        (
            [date] => 2020-05-05 00:00:00
        )
)

My PHP and MYSQLI code:

$c = Database::$database->query("
    SELECT 
        `date`
    FROM `payments` 
    WHERE 
        `currency` = '{$this->settings->payment->currency}' 
    AND 
        WEEK(`date`) = WEEK(CURRENT_DATE())")->fetch_all(MYSQLI_ASSOC);


foreach ($c as $key => $value) {
    $current_date[] = $value['date'];
    $current_sum[] = Database::$database->query("SELECT COUNT(*) AS sum FROM `payments` WHERE `currency` = '{$this->settings->payment->currency}' AND `date` = '{$value['date']}'")->fetch_assoc();
}

What Im trying to do?

Sales

Left side is my current what is broken, and right side is what I'm trying to get.

Workflow:

I have to get all dates (not duplicates) of current week. Then I have to get all SUM(*) AS sum of every date (Not duplicates) of current week.

My JS:

var optionslinechart = {
            chart: {
                toolbar: {
                    show: false
                },
                height: 170,
                type: 'line'
            },
            dataLabels: {
                enabled: false
            },
            stroke: {
                curve: 'smooth',
                width: [ 8 ]
            },
            xaxis: {
                show: false,
                type: 'datetime',
                categories: <?= json_encode($data->current_date, true); ?>,
                labels: {
                    show: false,
                },
                axisBorder: {
                    show: false,
                },
            },
            grid: {
                show: false,
                padding: {
                    left: 15,
                    right: 15,
                    bottom: 20
                }
            },
            colors:['#ffffff'],
            series: [
                {
                    name: 'SOD',
                    data: <?= json_encode(nestedToSingle($data->current_sum), true); ?>,
                }
            ],
            tooltip: {
                x: {
                    format: 'dd/MM/yy HH:mm'
                }
            }
        };

        var chartlinechart = new ApexCharts(
            document.querySelector("#chart-widget1"),
            optionslinechart
        );
        chartlinechart.render();

Answer 1

Instead of all that code, you could just use something like this...

SELECT p.date
     , COUNT(*) total
  FROM payments p
 WHERE p.currency = :currency
   AND p.date BETWEEN CURDATE() - INTERVAL WEEKDAY(CURDATE()) AND CURDATE() - INTERVAL WEEKDAY(CURDATE()) + INTERVAL 6 DAY -- untested
 GROUP 
    BY p.date

Answer 2

use DISTINCT keyword

SELECT 
DISTINCT
        `date`
    FROM `payments` 
    WHERE 
        `currency` = '{$this->settings->payment->currency}' 
    AND 
        WEEK(`date`) = WEEK(CURRENT_DATE())