Page 1 of 1
Changing Group heading based on SQL date field
Posted: Mon Feb 09, 2004 3:04 pm
by ruthsimon
I have a MySQL table for user comments with the following fields:
ID
Date
Time
UserName
UserComment
I want to display the all the records for each date (most recent date first) on the page under a single heading for each date. For example:
January 30, 2004
UserName, UserComment
UserName, UserComment
UserName, UserComment
January 28, 2004
UserName, UserComment
January 27, 2004
UserName, UserComment
UserName, UserComment
etc.
Any suggestions on MySQL/php?
Thanks, Ruth
Posted: Mon Feb 09, 2004 5:50 pm
by qads
what do you have in time field? ..well..the time

, but in what format? timestamp?
Posted: Mon Feb 09, 2004 8:22 pm
by DuFF
"SELECT ID, Date, Time, UserName, UserComment FROM table_name ORDER BY Date, Time";
That will organize it by dates, making it much easier.
Posted: Mon Feb 09, 2004 11:34 pm
by ruthsimon
Thanks,
I don't have any problem with date formatting.
I'm using similar SQL, but I'm trying to figure out how to nest the while statements so that I only display the date one time, then all the records with that date, and then the next occuring date.
Here's the code:
Code: Select all
//Query to select all user comments
$blog_display_query = "SELECT * FROM post
ORDER BY post_date DESC, post_time DESC";
//Run the query
$blog_display_result = mysql_query($blog_display_query)
or die ("couldn't execute blog_display query");
//Query to count number of user comments per day
$date_count_query = "SELECT post_date,
COUNT(post_date) AS num_entry FROM post GROUP BY post_date
ORDER BY post_date DESC";
//Run the query
$date_count_result = mysql_query($date_count_query)
or die ("couldn't execute date_count query");
//Get the rows from the comment count query
while ($date_count_row = mysql_fetch_array ($date_count_result,MYSQL_ASSOC)) {
extract($date_count_row);
//display the date heading
echo "$post_date, <hr>";
//Get the rows for the rows for the comment records
while ($blog_display_row = mysql_fetch_array
($blog_display_result,MYSQL_ASSOC)) {
//display all the records for the day
for ($i=1; $i < $num_entry; $i++) {
extract ($blog_display_row);
echo "$post_heading<br><br>
$post_entry<br><br>
posted by <b>$post_name</b> at $post_time<br>";
}
}
}
This doesn't work at all. Any idea how to get closer?
Posted: Tue Feb 10, 2004 12:37 am
by mahara
I think you should create two resultset; first to get all posting date only by using this query.
Code: Select all
SELECT DISTINCT date AS post_date FROM post ORDER BY date DESC
Then through the second query, you can get all posting foreach date in first resultset and then get all posting which is posted only on that day,
Code: Select all
SELECT * FROM post WHERE date = '$postDate' ORDER BY time DESC
which '$postDate' variable is variable returned by first query.
This can be implemented like this.
Code: Select all
<?php
$connID = mysql_connect('localhost', 'user', 'password');
mysql_select_db('your_db', $connID);
$firstSQL = "SELECT DISTINCT date AS post_date FROM post ORDER BY date DESC";
$firstResultSet = mysql_query($firstSQL, $connID) or die('bla... bla... bla...');
while ($firstRows= mysql_fetch_array($firstResultSet , MYSQL_ASSOC))
{
$postDate = $firstRows['post_date'];
echo "<strong>$postDate</strong><br/>\n";
$secondSQL = "SELECT * FROM post WHERE date = '$postDate' ORDER BY time DESC";
$secondResultSet = mysql_query($secondSQL, $connID or die('bla... bla... bla...');
while ($secondRows= mysql_fetch_array($secondResultSet, MYSQL_ASSOC))
{
echo $secondRows['userName'] . ' (' . $secondRows['userTime'] . '), ' . $secondRows['userComments'] . "<br/>\n";
}
mysql_free_result($secondResultSet); // This is an optional.
}
mysql_free_result($firstResultSet); // This is an optional.
mysql_close($connID); // This is an optional.
?>
Hope helps.
Posted: Tue Feb 10, 2004 12:58 pm
by ruthsimon
Thanks, that worked great.
I was moving towards that type of solution, but was hoping to come up with something that didn't have to run the query so many times over on a single page (for example, if there are comments each day for 30 days running, the second query has to run 30 times to complete the script).
Maybe there's a more efficient method? Ruth
Posted: Mon Feb 16, 2004 3:50 am
by mahara
Only two queries required

.
Code: Select all
<?php
// Main task of this function is to convert an ordinary DB resultset's form into
// an associative array, so that you can access it by using:
// $rows[$specifiedRowIndex][$fieldName];
//
function DBFetchRowSet($_queryResultID, $_rowOffset = 0, $_type = MYSQL_ASSOC)
{
$result = array();
while ($array = mysql_fetch_array($_queryResultID, $_type))
{
$result[$_rowOffset] = $array;
$_rowOffset++;
}
reset($result);
return($result);
}
$connID = mysql_connect('localhost', 'user', 'password');
mysql_select_db('db', $connID) or die('bla... bla... bla...');
$SQL = "SELECT DISTINCT date, COUNT(date) AS date_count
FROM posts
GROUP BY date
ORDER BY date DESC";
$firstResult = mysql_query($SQL, $connID) or die('bla... bla... bla...');
$SQL = "SELECT *
FROM posts
ORDER BY date DESC, time DESC";
$secondResult = mysql_query($SQL, $connID) or die('bla... bla... bla...');
// Note: first row index I use here is 1, not 0 like a default array.
$secondRow = DBFetchRowSet($secondResult, 1, MYSQL_ASSOC);
while ($firstRow = mysql_fetch_array($firstResult, MYSQL_ASSOC))
{
static $postsCount = 0;
echo '<strong>' . $firstRow['date'] . "</strong><br/>\n";
for ($i = $postsCount + 1; $i <= $postsCount + $firstRow['date_count']; $i++)
{
echo $secondRow[$i]['user_name'] . ' (' . $secondRow[$i]['time'] . '), ' . $secondRow[$i]['user_comment'] . ".<br/>\n";
}
echo "<br/>\n";
$postsCount += $firstRow['date_count'];
}
mysql_free_result($firstResult); // This is an optional.
mysql_free_result($secondResult); // This is an optional.
mysql_close($connID); // This is an optional.
?>
Hope helps.