CODEWITHSUNDEEP
phpmysql
David19801
David19801

Reputation: 11448

mysql collection of visitor days?

I have a mysql table - table1. It has ID (autoinc), dt (datetime), name (varchar) columns. When a visitor visits they can enter their name in the database. On some days their are no visitors.

From this, i'm trying to find if their is some way to make a list in php of all days for which their was at least 1 visitor.

Any ideas?

Upvotes: 0

Views: 180

Answers (2)

dmcnelis
dmcnelis

Reputation: 2923

Select distinct date(dt) from table1

So then, in php you would do something like:

$result = mysql_query("select distinct date(dt) from table1");
while($row = mysql_fetch_array($result)){
   echo $row[0] . "\n";
}

This would print each date on a different line.

Should give you a list of the unique dates that data was written to the table.

Updated to use date() instead of day() *Updated to fix the missing parenthesis *

Upvotes: 3

mhitza
mhitza

Reputation: 5715

To extract only those dates.

SELECT DATE_FORMAT(dt, '%Y-%m-%d') AS the_date,
       COUNT(*) AS visitors

  FROM table

 GROUP BY the_date

HAVING visitors > 0;

Upvotes: 0

Related Questions