unable to print distinct dates and display in a specific format..

[php_begins]

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:

 

 

<?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>";
?>

[Pikachu2000]

SELECT DISTINCT DATE_FORMAT(date_field, '%Y'-%m-%d') . . . etc.

[php_begins]

thanks,is the post count(mysql_num_rows) part of my code correct? it still returns 1 for the date..i feel i might have messed up my code in the loop somewhere..

[Pikachu2000]

I guess I should have asked earlier, what format is the date/time in the database stored in?

[php_begins]

It is in a datetime format..Like this 1186945212..

[DavidAM]

That looks like a unix timestamp not a datetime (which is an SQL data type). Hopefully, you have that defined as an integer and not a varchar (or other string type).

 

There are a couple of issues with your code that I would cleanup. Do not use 'select *' and mysql_num_rows() to determine how many rows exist, use 'select count(*)' instead. You are wasting a lot of time and resources that way. Also, do not run queries in a loop if you can avoid it. I would suggest writing that (first) process along these lines:

 

$sql = "SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) AS FmtDate, COUNT(postid) AS PostCnt
FROM post WHERE dateline >='$limit' 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) DESC";
$res = mysql_query($sql);
if ($res !== false) {
  while ($row = mysql_fetch_assoc($res)) {
    echo 'Date: ' . $row['FmtDate'] . '  Posts: ' . $row['PostCnt'];
  }
}

 

That's just off the top of my head. You will have to add all of your HTML and stuff where the "echo" is; but that should give you an idea.

 

I don't know why you were using ">=", unless it was because of the TIME portion of the timestamp. The query above should give you post counts by date, regardless of time. Also, I do not see where you are using the $acronym variables to limit the queries; so I'm not sure how you would incorporate that.

 

This concept should work for your other tables, look into JOINs and aggregate functions (in mySql) to see how to work that. If you get stuck  on the other queries, post your work and we can take a look.

[php_begins]

That looks like a unix timestamp not a datetime (which is an SQL data type). Hopefully, you have that defined as an integer and not a varchar (or other string type).

 

There are a couple of issues with your code that I would cleanup. Do not use 'select *' and mysql_num_rows() to determine how many rows exist, use 'select count(*)' instead. You are wasting a lot of time and resources that way. Also, do not run queries in a loop if you can avoid it. I would suggest writing that (first) process along these lines:

 

$sql = "SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) AS FmtDate, COUNT(postid) AS PostCnt
FROM post WHERE dateline >='$limit' 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) DESC";
$res = mysql_query($sql);
if ($res !== false) {
  while ($row = mysql_fetch_assoc($res)) {
    echo 'Date: ' . $row['FmtDate'] . '  Posts: ' . $row['PostCnt'];
  }
}

 

That's just off the top of my head. You will have to add all of your HTML and stuff where the "echo" is; but that should give you an idea.

 

I don't know why you were using ">=", unless it was because of the TIME portion of the timestamp. The query above should give you post counts by date, regardless of time. Also, I do not see where you are using the $acronym variables to limit the queries; so I'm not sure how you would incorporate that.

 

This concept should work for your other tables, look into JOINs and aggregate functions (in mySql) to see how to work that. If you get stuck  on the other queries, post your work and we can take a look.

 

THanks so much..the above code works good...but it shows only the last 30 dates where there have been posts..I want to display all last 30 dates and if there are no posts on that particular date, it should echo the post count as 0..