Is there anyway to achieve this?

[takn25]

Hi, I have table called done in mysql and there is a row which is filled with the users id and another row with todays date. Now what I want to know which three users participated the most with filling out stuff on the  website according to todays date.

For instance

 

First user filled out 5

2nd user filled out 3

3rd user filled out 2          You get the idea.

 

I dont know what type of query should I write to extract the data based on the users id and how many times they participated on the particular day.

 

Another example of the table is  1st column    user id = 20 time= todays date

                                                    2nd column  user id= 20  time = todays date    Showing the user participated twice on the day. 

 

Thanks any help is appreciated.

[mens]

Try:

SELECT COUNT(*) as `filled` FROM `done` WHERE DATE(`date`) = DATE(NOW()) GROUP BY `user_id`

 

Hope that helps.

[drisate]

It would look like this

 

<?php
// If the voting form has been submited
if ($_POST){
    $today = date("d-m-Y"); // Todays date
    
    //check if user already voted
    $count = mysql_num_rows (mysql_query("SELECT * FROM vote WHERE iser_id='$_SESSION[id]' and date = '$today'"));
    
    if ($count=="0"){
        // INSERT
        $insert = mysql_query("INSERT INTO vote (id, date, user_id, voted) VALUES ('', '$date', '$_SESSION[id]', '1')");
    }else{
        // UPDATE
        $insert = mysql_query("UPDATE vote SET voted=voted+1 WHERE date='$date' and user_id='$_SESSION[id]'");
    }

}
?>

[PFMaBiSmAd]

You would use COUNT(*) to get the count for each id (I would also assign that to an alias name for referencing). You would use GROUP BY id to consolidate the rows for each id. You would use WHERE DATE(time) = CURDATE() to select just the rows that match the current date.

 

Should work -

SELECT id, COUNT(*) as cnt FROM your_table WHERE DATE(time) = CURDATE() GROUP BY id

 

You would access the count value as $row['cnt']

 

[takn25]

Ok I got some thing to work but havent really got the hang of it just yet. Its grouping the results and showing them but I am not able to ORDER the results for instance from the most to the least.  I mean what am I suppose to ORDER it by ?

 

$query = "SELECT id, COUNT(*) as kb FROM part GROUP BY id";

 

$result = mysql_query($query) or die(mysql_error());

 

// Print out result

while($row = mysql_fetch_array($result)){

echo "There are ". $row['kb'];

echo "<br />";

}

 

 

Thanks again!

 

 

 

[DavidAM]

Order by the count:

 

$query = "SELECT id, COUNT(*) as kb FROM part GROUP BY id ORDER BY COUNT(*) DESC";

 

or since you aliased the count as 'kb':

 

$query = "SELECT id, COUNT(*) as kb FROM part GROUP BY id ORDER BY kb DESC";

 

* DESC indicates descending so the highest count will be the first row returned.

[takn25]

Thanks!