CODEWITHSUNDEEP
phpsql
user17595824
user17595824

Reputation:

How do I count all the values in a table and echo a some certain value if that value repeats itself certain number of times?

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\",";
        }
    }
}
?>

Upvotes: 0

Views: 48

Answers (2)

Gamer In The Game
Gamer In The Game

Reputation: 254

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.

Upvotes: 1

Lars Lind Nilsson
Lars Lind Nilsson

Reputation: 1148

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)

Upvotes: 0

Related Questions