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
Changing Group heading based on SQL date field
Moderator: General Moderators
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:
This doesn't work at all. Any idea how to get closer?
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>";
}
}
}-
mahara
- Forum Commoner
- Posts: 37
- Joined: Wed Nov 13, 2002 1:08 am
- Location: Bandung, Jawa Barat, Indonesia
I think you should create two resultset; first to get all posting date only by using this query.
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,
which '$postDate' variable is variable returned by first query.
This can be implemented like this.
Hope helps.
Code: Select all
SELECT DISTINCT date AS post_date FROM post ORDER BY date DESCCode: Select all
SELECT * FROM post WHERE date = '$postDate' ORDER BY time DESCThis 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.
?>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
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
-
mahara
- Forum Commoner
- Posts: 37
- Joined: Wed Nov 13, 2002 1:08 am
- Location: Bandung, Jawa Barat, Indonesia
Only two queries required
.
Hope helps.
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.
?>