PHP - Unable To Use Select Distinct With Join

Hi I have been trying to populate my data from 3 tables and display like this:

                  DATE             $acronym1                        $acronym2
POSTS   1/9/2011          10 (this is no of posts)                  3
POSTS   31/8/2011         20                                                10
and so on. the threads and user table will be similar as well.
 I am not able to populate the distinct dates since the dateline contains time as well. So when I try to print out the just dates(without time) , it prints  with the dates repeated. it prints something like this:
1/9/2011 1
1/9/2011 1
and so on..
How can populate and print the the date and number of posts in the above format. Here is my complete code below:


Code: [Select]
<?php
error_reporting(-1);
//ini_set('display_errors',1);
include("db.php");
//$thirty_days_ago = strtotime("-30 days");
$limit = strtotime("-1 month");
$sql=mysql_query(("SELECT * from new_site"),$con) or die(mysql_error());
while($row=mysql_fetch_assoc($sql))
{
   $include=$row['include'];
   $forumurl=$row['forumurl'];
   $url=$row['url'];
   $acronym=$row['acronym'];
   include("$include");
   //echo $include."<br>";
   $configdbname=$config['Database']['dbname'];
   $configdbconport=$config['MasterServer']['servername'].":".$config['MasterServer']['port'];
   $configusername=$config['MasterServer']['username'];
   $configpassword=$config['MasterServer']['password'];
   $configprefix=$config['Database']['tableprefix'];
   /* Connect to the required database */
   $con2=mysql_connect($configdbconport, $configusername, $configpassword);
   if (!$con2)
   {
       die('Could not connect: ' . mysql_error());
   }
   mysql_select_db($configdbname, $con2);
   $postdate=mysql_query("SELECT DISTINCT dateline,postid from post WHERE dateline >='$limit' ORDER by dateline DESC") or die(mysql_error());
   while($postdate_results=mysql_fetch_assoc($postdate))
   {
      $postdate_record=$postdate_results['dateline'];
     // echo $postdate."<br>";
      $postdate_formatted=date('M dS  Y ',$postdate_results['dateline']);
      $post_count=mysql_query("SELECT * from post WHERE dateline >='$postdate_record'");
      while($post_count_results=mysql_fetch_assoc($post_count))
      {
         //$postdate_formatted=date('M dS  Y ',$post_dateline_results['dateline']);
         $posts=mysql_num_rows($post_count) or die(mysql_error());
         //echo $acronym.":POSTS:".$posts."<br>";
         echo '<table border="1">';
         echo "<tr>";
         echo "<th>Category</th>";
         echo "<th>".$acronym."</th>";
         echo "</tr>";
         echo "<tr>";
         echo "<td>POSTS:DATE:".$postdate_formatted."</td>";
         echo "<td>".$posts."</td>";
   echo "</tr>";
      }
     
   $threaddate=mysql_query("SELECT * from thread WHERE dateline >='$limit' ORDER by dateline DESC") or die(mysql_error());
   while($threaddate_results=mysql_fetch_assoc($threaddate))
   {
      $threaddate_record=$threaddate_results['dateline'];
            $threaddate_formatted=date('M dS  Y ',$threaddate_results['dateline']);
            $thread_count=mysql_query("SELECT * from thread WHERE dateline='$threaddate_record'");
           while($thread_count_results=mysql_fetch_assoc($thread_count))
           {
              $threads=mysql_num_rows($thread_count) or die(mysql_error());
             //echo $acronym.":THREADS:".$threads."<br>";
             echo "<tr>";
             echo "<td>THREADS:DATE:".$threaddate_formatted."</td>";
             echo "<td>".$threads."</td>";
             echo "</tr>";
             $userdate=mysql_query("SELECT * from user WHERE joindate >='$limit' ORDER by joindate DESC") or die(mysql_error());
             while($userdate_results=mysql_fetch_assoc($userdate))
    {
        $userdate_record=$userdate_results['joindate'];
        $userdate_formatted=date('M dS  Y ',$userdate_results['joindate']);
                 $user_count=mysql_query("SELECT * from user WHERE joindate='$userdate_record'");
                 while($user_count_results=mysql_fetch_assoc($user_count))
                 {
                    $users=mysql_num_rows($user_count) or die(mysql_error());
                  //echo $acronym.":USERS REGISTERED:".$users."<br>";
                    echo "<tr>";
                    echo "<td>REGISTERED USERS::DATE:".$userdate_formatted."</td>";
                    echo "<td>".$users."</td>";
                   echo "</tr>";
       }
              }

            }
         }
  
       }
     }

echo "</table>";
?>

How can I stop duplication in the below code? Where do I implement the DISTINCT function?


$sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name .  "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'";
   
//-run  the query against the mysql query function
     $result=mysql_query($sql);
   
//-create  while loop and loop through result set
     while($row=mysql_fetch_array($result)){
             $First_Name  =$row['First_Name'];
             $Last_Name=$row['Last_Name'];
             $Resource_ID=$row['Resource_ID'];
   
//-display the result of the array
     echo "<ul>\n";
     echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"   .$First_Name . " " . $Last_Name .  "</a></li>\n";
     echo "</ul>";
     }
     }

Hi am having a few problems solving this code with select distinct clause. None of what i tryed works.
Can anyone help please thanks.

this is just some of the query's i tryed
$query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id;");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip;");
$query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id;");
$query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id;");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline;");

and again but without ";"

$query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip");
$query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id");
$query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id");
$query7 = yasDB_select("SELECT DISTINCT ip FROM useronline");


this is the code am working on.
Code: [Select]
$query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;");
$visitors_online = $query7->fetch_array(MYSQLI_ASSOC);
$visitors_online = $query7->num_rows;
$query7->close();

visitors online : <?php echo $visitors_online;?><br/>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=343149.0

So, I've been trying to get this query working and can't quite get it to work.  I'm trying to get an "array" of distinct browsers from the database, but it's only showing one of them.  There are 3 unique browsers in the table and only "Chrome 30" gets returned.  Here is the query:

SELECT DISTINCT `browser` AS `unique_browsers`,
     COUNT(DISTINCT `ip`) AS `unique_visitors`,
     COUNT(DISTINCT `country`) AS `unique_countries`,
     COUNT(`id`) AS `total_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Chrome%') AS `chrome_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Internet Explorer%') AS `ie_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Firefox%') AS `firefox_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Safari%') AS `safari_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Opera%') AS `opera_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` NOT LIKE '%Chrome%' AND `browser` NOT LIKE '%Internet Explorer%' AND `browser` NOT LIKE '%Firefox%' AND `browser` NOT LIKE '%Safari%' AND `browser` NOT LIKE '%Opera%') AS `unknown_count`
FROM `table`
GROUP BY `browser` 

Everything works properly except the line: