Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,256
Total Threads: 106,575
Total Posts: 592,698
There are 86 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

A column that counts the number of index entries on a specific index?

Thread title: A column that counts the number of index entries on a specific index?
Reply    
    Thread tools Search this thread Display Modes  
06-07-2012, 01:12 PM
#1
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old  A column that counts the number of index entries on a specific index?

Bare with me as I try to explain.

I have two tables: Topics and Comments. topic.id is indexed to comment.topicid, for every comment that is indexed to topic.id I want to +1 a column in my comments table, called comments.replies.

I can do this incredibly easy with PHP, however, I'm left wondering if it's at all possible with just MySQL?

Here's my relationship diagram for better understanding: http://i.imgur.com/1nICb.png

06-07-2012, 03:02 PM
#2
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

Select all the topics you want, left join all the relevant comments, group them by topic ID and make one of your rows a count of that ID.

Code:
SELECT 
	t.id.
	t.author,
	... (additional info you want from the topic table here)
	c.forumtopicid,
	COUNT(t.id) as reply_count
FROM
	forum_topics t
LEFT JOIN 
	forum_comments c ON c.forumtopicid=t.id
GROUP BY
	t.id
WHERE
	t.category=1
If this query does not make sense see what it looks like without the GROUP BY statement and it might help.

Thanked by:
Dan (06-13-2012)
06-07-2012, 04:13 PM
#3
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

Is this a better method than a trigger?

Reply With Quote
06-07-2012, 04:24 PM
#4
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

A trigger? I'm not familiar with the term.

Reply With Quote
06-07-2012, 04:31 PM
#5
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

Reply With Quote
06-07-2012, 05:06 PM
#6
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

Right. Those are bad for this because they require too much maintenance. You'll have to have one when posts are added, deleted, or moved. Furthermore if the trigger should for some reason fail you'll have to recompile all your data to make it valid again. Preforming counts on the spot requires no maintenance and has less ways to fail.

Reply With Quote
Thanked by:
Dan (06-13-2012)
06-13-2012, 09:06 PM
#7
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

I looked into doing it straight from the SQL and wouldn't this be a better method?

$db->query("UPDATE `forum topics` SET `replies` = replies +1 WHERE `id` = '103' LIMIT 1");

Reply With Quote
06-13-2012, 10:00 PM
#8
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

It poses the exact same problems, the counts can get messed up and need to be reset. The only advantage to updating a count like that would be speed on large counts. The method you just gave would be better from a coding standpoint and easier to implement on shared servers but may take slightly longer. I doubt the time difference would ever really matter though. .

Reply With Quote
Reply    


Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed