another one that is probably simple

[dmarchman]

I have on my index page the newest entry from my database, and at the bottom the user can click to go to a page that displays the entry as well as comments for that entry. The blog entry and the comments are being pulled from separate tables. Now I need to tweak the View/Post Comments Link on my index page to display the number of comments for that entry. I have messed around with the SQL and have made it display the total number of comments for ALL of the entries combined, but I need to sort it somehow by the id of the entry. Any help on this would be appreciated.

[dmarchman]

figured this out with the help of this excellent website

 

here's the tables in the db:

 

(art is the name of the table that holds my articles)

CREATE TABLE `art` (
 `id_art` int(11) NOT NULL auto_increment,
 `idtop_art` int(11) NOT NULL default '0',
 `title_art` varchar(100) NOT NULL default '',
 `desc_art` varchar(255) NOT NULL default '',
 `text_art` longtext NOT NULL,
 `date_art` datetime default NULL,
 PRIMARY KEY  (`id_art`)
)

 

(top is the name of the table that holds the names of the topics of the articles)

CREATE TABLE `top` (
 `id_top` int(11) NOT NULL auto_increment,
 `topic_top` varchar(100) NOT NULL default '',
 PRIMARY KEY  (`id_top`)
)

 

heres the SQL(switch to advanced mode in the recordset dialog)

SELECT *, count(*)
FROM art INNER JOIN top ON idtop_art = id_top
GROUP BY topic_top
ORDER BY topic_top ASC

 

from the bindings panel in dreamweaver i only need to drag out the name of the topic and the count dynamic text blocks to make it work. i could simplify the SQL by only selecting the fields i need to like this:

SELECT id_top, topic_top, count(*)
FROM art INNER JOIN top ON idtop_art = id_top
GROUP BY topic_top
ORDER BY topic_top ASC

this would make the search a bit faster, and i could pass the primary key to a sort page to only display articles of a given topic.

 

not too bad for someone at the end of his rope a couple of days ago..