How do I count all the values in a table and echo a some certain value if that value repeats itself-CodePudding

I have a database of reservations that contains all of bookings, now I want to limit the dates if 1 date repeats itself certain number of times (for testing purposes, 5). I wrote a query that selects all the dates but figured it just counts all the dates not the dates with same values. How do I make it do that?

$sql2 = "SELECT date FROM tbl_reservations ORDER BY date ASC";
$res2 = mysqli_query($conn, $sql2);

if($res2 == TRUE)
{
    $count2 = mysqli_num_rows($res2); 

    if ($count2 > 5)
    {
        while ($rows2 = mysqli_fetch_assoc($res2))
        {
            $books = $rows2['date']; echo"\"$books\",";
        }
    }
}
?>

CodePudding user response：

You can write below query to select all dates (which were repeated specific amount of times, for example 5):

SELECT COUNT(date) AS numberofreservations, date FROM tbl_reservations GROUP BY date HAVING numberofreservations >= 5

Then you can check if the entered date is contained in the above selection, it means that date is full.

CodePudding user response：

You'll need to group your reservations by date, and then use the count function to give you the number of records for each date.

SELECT COUNT(date) AS numberofreservations, date FROM tbl_reservations GROUP BY date

It may also be an idea to change the name of the date field to something like reservation_date, since date is a field type, and using that as a field name can lead to confusion (and some database engines will require the field name to be quoted, if it's a reserved word)