Comments Compare + Count

16 posts by 2 authors in: Forums > CMS Builder
Last Post: July 16, 2010   (RSS)

By ILLUME-MEDIA-LABS - July 12, 2010 - edited: July 12, 2010

Hi,

SO I have a question. My articles table n comments table are separate. How can I do query and connect comments with articles to do count...on the category page. For the article page...I did this and it works:

// load comments
list($countRecords, $countMetaData) = getRecords(array(
'tableName' => 'comments',
'allowSearch' => false,
'where' => "commentValue = '". mysql_real_escape_string( $_SERVER['REQUEST_URI']) ."' AND approved = 0",

));


<?php @$count = 0; ?><?php foreach ($countRecords as $record): ?><?php @$count++; ?><?php endforeach ?> <?php echo @$count; ?>

How would I write the code so the query compares the article url with the commentValue url and returns the total count?

Thanks!

Re: [Illume Magazine] Comments Compare + Count

By Jason - July 13, 2010

Hi,

There may be an easier way to do this. First, can you give me a list of all of the fields that appear in your articles and your comments tables?

Do these two tables only link on the commentValue field? If so, can you give me an example of what the information in this field looks like?

Let me know and we'll get something figured out.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Comments Compare + Count

By ILLUME-MEDIA-LABS - July 14, 2010

Hi Jason,

Heres what you requested. The comment value is the article url. ie: /zine/articleDetail.php?Clinton-Condemns-World-Cup-Terror-13111

Comments Table
Approved checkbox
Title textfield
Name textfield
Email textfield
Comment wysiwyg
commentValue textfield


Article Table
Title textfield
Category list
Add To Slideshow checkbox
External Link textfield
Hidden checkbox
Approved checkbox
Featured checkbox
Date date
Author textfield
Twitter Search Single Keyword textfield
Tag Cloud Multiple Keywords textfield
Pull Quote textbox
Summary textbox
Content wysiwyg
Article Image upload
Slideshow Image upload
In Pictures upload
File Attatchments upload
Photo Essay Images upload
MP3 URL textfield
Video Embed Code textbox
Video Caption textfield

Re: [Illume Magazine] Comments Compare + Count

By Jason - July 14, 2010

Hi,

You can get a count of the number of comments a particular article has during the foreach loop that is listing the articles.

The code you use would look something like this:

<?php foreach($articleRecords as $article): ?>
<?php
$commentValue = "/zine/articleDetail.php?".str_replace("'","-",str_replace(" ","-",$article['title']))."-".$article['num'];
$count = mysql_select_count_from("comments","commentValue='$commentValue'");
?>
.
.
** NORMAL OUTPUT **
.
.
<?php endforeach ?>

What this code does is, for each article you selected, construct what the commentValue for that article would look like IF that article had comments. It then uses the function mysql_select_count_from() to get a count of comments in the comments table that have that commentValue and store that number in the variable $count (note: if there are no comments in the comments table for that article, it will return the number 0).
You can then output the $count variable where ever you want to display the number of comments that article has.

You may need to change some names to match what's in your database, etc.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Illume Magazine] Comments Compare + Count

By Jason - July 14, 2010

Hi,

This is the only really important part of the code that I sent you:

<?php
$commentValue = "/zine/articleDetail.php?".str_replace("'","-",str_replace(" ","-",$article['title']))."-".$article['num'];
$count = mysql_select_count_from("comments","commentValue='$commentValue'");
?>
This code is meant to go inside your foreach loop. You may have to change the names of the variables ($article) for example to match either what you're using in your database or to match the variables you're using inside your .php page.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Comments Compare + Count

By ILLUME-MEDIA-LABS - July 14, 2010

I update the variable, but didnt work.

Re: [Illume Magazine] Comments Compare + Count

By Jason - July 14, 2010

Hi,

If you could attach the .php file you're working with, I can take a closer look.

thanks
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Comments Compare + Count

By ILLUME-MEDIA-LABS - July 14, 2010

Here is the file. Thank you jason!
Attachments:

categoryindex.php 18K

Re: [Illume Magazine] Comments Compare + Count

By Jason - July 14, 2010

Hi,

I've taken a look. There doesn't seem to be any errors in the code. What's happening though is that the number stored in $count is never being outputted. You need to use this where ever you want the number of comments to be outputted:

<?php echo $count; ?>

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/