Grouping data from mysql

[graham23s]

Hi Guys,

 

in my database i log the ip and the date the ip's visited!

 

like:

 

INSERT INTO `fcp_real_visitors` (`id`, `ip`, `date`) VALUES
(1, 'xx,xxx.xx.xxx', '2008-04-21 13:02:52'),
(2, 'xx,xxx.xx.xxx', '2008-04-21 13:10:43'),
(3, 'xx,xxx.xx.xxx', '2008-04-21 13:17:10'),
(4, 'xx,xxx.xx.xxx', '2008-04-21 13:34:02'),
(5, 'xx,xxx.xx.xxx', '2008-04-21 17:17:24'),
(6, 'xx,xxx.xx.xxx', '2008-04-21 19:39:39'),

 

etc, my code to display is:

 

<?php
  case "visitor-statistics":
  
      // UNIQUE
      $qU = "SELECT DISTINCT(ip) FROM `fcp_real_visitors` GROUP BY `date` DESC LIMIT 50";
      $rU = mysql_query($qU) or die (mysql_error());
  
      print("<table width='95%' border='0' cellpadding='5' cellspacing='1' class='tbl_login' />\n");
      print("<tr>\n");
      print("<td colspan='3' align='left' class='c3'><b>Visitor statistics breakdown.</b></td>\n");
      print("</tr>\n"); 
      print("<tr>\n");
      print("<td align=\"center\" class='c3'><b>Date</b></td><td align=\"center\" class='c3'><b>Unique Visitors</b></td><td align=\"center\" class='c3'><b>Action</b></td>\n");
      print("</tr>\n");
      print("<tr class=\"c5\">\n");
      print("<td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td>\n");
      print("</tr>\n");  
      
      // LOOP
      while ($aU = mysql_fetch_array($rU))
      {
       // VARS
       $uDate = $aU['date'];

       // FANCY DATE
       $newDate = date("F j, Y", strtotime($uDate));
       
       // ALTERNATE COLOUR
       $row_color = ($row_color == "class=\"no_color\"") ? "class=\"c1\"" : "class=\"no_color\"";     
       
       // PRINT ROWS
       print("<tr $row_color ><td align=\"center\">$newDate</td><td align=\"center\"><b>$catVW</b></td></tr>\n");   
      
      }
      
      // END TABLE
      print("</table>");
  
  break; 
?>

 

so basically i wanted to display:

 

DATE  !  UNIQUE VISITORS ON THAT DAY

 

2008-11-23    130

2008-11-22    124

 

kinda thing but my query isn't right! any help woul dbe apppreciated!

 

thanks guys

 

Graham

[Mchl]

I think you need

SELECT date, COUNT(*) AS uniqueVisits FROM `fcp_real_visitors` GROUP BY DATE(`date`) DESC LIMIT 50

[graham23s]

Thanks mate thats perfect

 

Graham