Hi Richard,
> What I'm trying to achieve I would have thought would have been very
> simple, but I must be stupid, LOL!
You are almost there!
> The blogger writes an entry and visitors leave comments for a
> particlular blog entry. The comment entry form will appear under
> each blog entry, and after the associated comments if there are any.
>
> I want to display on the web page all the blog entries for a
> particular day followed directly underneath each blog entry by any
> comments, or none at all if no comments have been made.
>
> blogs
>
> bID (primary key)
> timeAdded
> title
> entry
>
> comments
>
> cID (primary key)
> bID (foreign key)
> timeAdded
> name
> comment
>
> What I can't get my head round is how to write the mysql query so
> that a particular blog entry is displayed with its associated
> comments displayed directly underneath, followed by the next blog
> entry and associated comments.
>
> As an aside (I think I can work out how to do it) the blog entries
> for a particular day will be listed last entry first and the
> associated comments listed first comment first.
SELECT * FROM comments LEFT JOIN blogs ON comments.bID=blogs.bID ORDER BY
blogs.timeAdded DESC, comments.timeAdded ASC
By the way, I would have used:
blogs[bl_PK, bl_TimeAdded, bl_Title, bl_Entry]
comments[co_PK, co_bl_PK, co_TimeAdded, co_Name, co_Comment, co_Email,
co_IP]
which allows to reduce the SQL to:
SELECT * FROM comments LEFT JOIN blogs ON co_bl_PK=bl_PK ORDER BY
bl_TimeAdded DESC, co_TimeAdded ASC (no need anymore to include the
tablenames with the fieldnames as the naming is not ambiguous anymore)
Store the IP for in case people behave badly and you will need to create a
blacklist...
Or of course:
commenter[cm_PK, cm_Email, cm_IP, cm_UserName, cm_PassHash] and replace
co_Name, co_Email and co_IP with co_cmPK and make a double join.
Anyway, after fetching all the rows into two-dimensional array $Rows make
something like:
$ActualBlogID=0;
foreach ($Rows as $Row) {
if ($Row["bl_PK"]<>$ActualBlog) {
echo '<tr><th
colspan="2"><h1>'.$Row["bl_title"].'</h1><p>'.$Row["bl_Entry"].'</p></th></t
r>';
$ActualBlog = $Row["bl_PK"];
}
echo '<tr><td>'.$Row["TimeAdded"].'<br
/>'.$Row["Name"].'</td><td>'.$Row["Comment"].'</td></tr>';
}
However, as the number of comments per blog entry will increase, you will
want to do the following:
show only the x most recent comments under a blog entry and then the next
one. This will require limiting, end hence you will need more than one SQL
statement:
Retrieve the blogs (no join needed)
For each retrieved blog {
display the <a href=detailpage>blogtitle</a> and entry,
mark its PK value.
Retrieve the comments "WHERE co_bl_PK=bl_PK ORDER BY co_TimeAdded DESC
LIMIT 0,5"
Display all the retrieved comments
}
and include a link on the blog linking to this single blog plus all its
comment entries.
Thus you will have a front page with an overview of all blogs and few
comments, and derived pages with one blog entry plus all the related
comments.
Marc
------------------------ Yahoo! Groups Sponsor --------------------~-->
Most low income homes are not online. Make a difference this holiday season!
http://us.click.yahoo.com/5UeCyC/BWHMAA/TtwFAA/HKFolB/TM
--------------------------------------------------------------------~->
Community email addresses:
Post message: [email protected]
Subscribe: [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
List owner: [EMAIL PROTECTED]
Shortcut URL to this page:
http://groups.yahoo.com/group/php-list
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-list/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
