It shows up exactly the way I want, but there are two problems.
The entry ID in the href is blank
I’ve tried to put the exact post date in the title=“” attribute, and that’s also blank.
I can’t for the life of me figure out how to get those two to show up! Probably because I usually code in ColdFusion, and this type of thing would be built into a tag or two.
Any pointers in the right direction would be appreciated.
I find this very interesting! I’m in the middle of a site design and was thinking of doing this exact same thing for blog postings. I’m curious how are you outputting the results to the html page?
Sorry for such a newbie question but I am new to php.
Figured it out! (and no, I did not stay up all night working on it) But I think I am finally starting to “get” this array thing in PHP.
I was right about needing a third row in the spot I indicated in my last post. I ended up with this:
$dates[$row->postyear][] = $row->postdate;
Being a CF person, I’m used to using the cfdump tag for debugging. I wondered if someone had come up with a snippet that does something similar, so I Googled and found this: http://dbug.ospinto.com/
It’s the coolest thing EVER and allowed me to figure out exactly where I was going wrong. Anyway…
Final (working) code:
$query = mysql_query("
SELECT blog_id
, category_id
, entry_author_id
, entry_id
, entry_name
, DATE_FORMAT(FROM_UNIXTIME(entry_date),'%W, %M %e, %Y') as postdate
, FROM_UNIXTIME(entry_date, '%Y') as postyear
FROM blog_entries
WHERE entry_author_id = 1
AND entry_status = 'published'
ORDER BY entry_date DESC
LIMIT 5, 15
");
while($row = mysql_fetch_object($query)) {
$set[$row->postyear][] = $row->entry_name;
$ids[$row->postyear][] = $row->entry_id;
$dates[$row->postyear][] = $row->postdate;
}
foreach ($set as $postyear => $rows) {
echo "<h4>{$postyear}</h4>\
";
echo "<ul>";
foreach ($rows as $key => $entry_name) {
echo "\
\ <li><a href=\\"index.php?entry={$ids[$postyear][$key]}\\" title=\\"Posted: {$dates[$postyear][$key]}\\">{$entry_name} ></a></li>";
}
echo "\
</ul>\
";
}
In the middle of trying this now but am getting nowhere. I see that I have to construct my sql query differently because my blogs are broken down into categories so I have to pull information from the categories table in order to try to construct the links as so:
//do a left join to grab only those columns from categories table and articles table to construct the links.
$query = mysql_query("SELECT articles.aid
, articles.catid
, articles.title
, articles.reported_date
, categories.catdir
, categories.details
, categories.catid
, DATE_FORMAT(FROM_UNIXTIME(reported_date),'%W, %M %e, %Y') as postdate
, FROM_UNIXTIME(reported_date, '%Y') as postyear
FROM categories
LEFT JOIN articles
ON articles.catid
WHERE articles.catid = categories.catid
ORDER BY reported_date DESC
LIMIT 5, 15") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
$id = $row['aid'];//id of article and corresponds to primary key in articles table
$cDir = $row['catdir'];//directory designation in categories table
$cPage = $row['details'];//details page in categories table
$title= $row['title'];//title of article in articles table
# Tracking what happens is often good for debugging purposes
# And this part will tell you everything is fine - you do get your comments...
echo '<h3><a href="'.$cDir.'/'.$cPage.'?aid='.$id.'">'.$title.'</a></h3>';
}
Up to this point, everything works fine, the while loop lists everything accordingly.
Implementing this code gives me strange results again, my array is a bit different
Got it working! I had to take out the FROM_UNIXTIME and just went with a straight DATE_FORMAT function in the SQL. I assumed “FROM_UNIXTIME” may have something to do with the time on your server which is probably a UNIX server.
After that the years worked. Then it was short order to solve my links issues. Anyway, here’s my final result.
//do a left join to grab only those columns from categories table and articles table to construct the links.
$query = mysql_query("SELECT articles.aid
, articles.catid
, articles.title
, articles.reported_date
, categories.details
, categories.catdir
, categories.catid
, DATE_FORMAT (reported_date,'%W, %M %e, %Y') as postdate
, DATE_FORMAT (reported_date, '%Y') as postyear
FROM articles
LEFT JOIN categories
ON categories.catid
WHERE articles.catid = categories.catid
ORDER BY reported_date DESC
LIMIT 5, 30") or die(mysql_error());
while($row = mysql_fetch_object($query)) {
$set[$row->postyear][] = $row->title;
$ids[$row->postyear][] = $row->aid;
$cDir[$row->postyear][] = $row->catdir;
$cPage[$row->postyear][] = $row->details;
$dates[$row->postyear][] = $row->postdate;
}
foreach ($set as $postyear => $rows) {
echo "<h4>{$postyear}</h4>\
";
echo "<ul>";
foreach ($rows as $key => $entry_name) {
echo "\
\ <li><a href=\\"{$cDir[$postyear][$key]}/{$cPage[$postyear][$key]}?aid={$ids[$postyear][$key]}\\" title=\\"Posted: {$dates[$postyear][$key]}\\">{$entry_name} ></a></li>";
}
echo "\
</ul>\
";
}
Couldn’t have done it without your starting point!
But I think I am finally starting to “get” this array thing in PHP.
