Jump to content

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.

Link to comment
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..

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.