Jump to content

Archived

This topic is now archived and is closed to further replies.

dmarchman

another one that is probably simple

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.