仅显示超过20个参与者的行

Question

I have a problem i hope you can help me out with.

To break it down, i have a table in the database where i create events, it's called "dansetimer" and for that i have a table called for the participants on the events, called "transak"

I want to count how many events within a given time, had more than 20 participants.

I have made this for a start, and it's also very good, but i cant see how to count, how many events i end up with, of course i can just count the list manually, but i really want to get PHP to count it.

    $hentgg = mysql_query("SELECT * FROM dansetimer WHERE dato BETWEEN $fra AND $til"); 
    if(mysql_num_rows($hentgg)) {
    while($visgg = mysql_fetch_array($hentgg)) {

    $result=mysql_query("SELECT count(*) as total FROM transak WHERE produkt = '$visgg[id]'");
    $data=mysql_fetch_assoc($result);

    if($data['total'] > 20) {
       echo $visgg[title];  
    }

    }
    } else {
       echo "Ingen resultater";
    }   

With the above code i'm able to retrieve the events and get them listed.

With this code:

    $result=mysql_query("SELECT count(*) as total FROM transak WHERE produkt = '$visgg[id]'");
    $data=mysql_fetch_assoc($result);

I can count how many participants there are on the event, and with the if statement, testing if there are more than 20, i can hide the events, that dont have more participants.

But do you have a great idea for how i can write "Within the given time, you held 23 events with more than 20 participants" or something like that.

Answer 1

You could keep an extra counter in the loop, so you can see how many events have over 20 participants:

// At the start of your script
$large_events = 0;
...
// In your loop:
if($data['total'] > 20) {
   echo $visgg[title];  
   $large_events++;
}

But I think it's easier, and probably faster too, to let SQL give you already some more details. In your solution it will query the participants for each event, so if someone has 1000 events, each with 20 participants or less, your database has to do 1001 queries, end the end result is 'Ingen resultater'.

So, change the query to this:

SELECT *
FROM
  ( SELECT 
        dt.*,
        (SELECT count(*) FROM transak WHERE produkt = dt.id) AS total_participants 
    FROM dansetimer dt 
    WHERE dato BETWEEN $fra AND $til
  )
WHERE
    total_participants > 20
ORDER BY
    dato,
    title

This query returns only events with more than 20 participant, and it will return the number of participants per event. So you can just fetch this query result into a simple array and display each item to show the title, number of participants and other information. And the length of the array is the number of 'large' events.

PS: Did you know that the mysql_* functions are old and are removed in PHP 7? I think it's time for an upgrade to PDO.