Jump to content


Photo

another one that is probably simple


  • Please log in to reply
1 reply to this topic

#1 dmarchman

dmarchman
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 22 August 2005 - 07:32 PM

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.

#2 dmarchman

dmarchman
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 25 August 2005 - 01:51 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users