Trouble with some SQL in my script.

[sixteh]

I want to count the number of comments (stored in a database) where cid = x and sid = y then label each of the comments with their ID # relative to that set. For instance, if I have 5 comments where cid = 1 and sid = 1, with the following id's: 1, 3, 6, 7, and 9. When I display each comment, however, I want it to show the comment #. With my current code (it just displays the comment id), Comment id #1 will show 1, #3 will show #3, etc. Instead, I want it to show comment #1 as #1, but #3 as #2, its relative position among the group. However, I'm getting a syntax error with the first problem (probably just some retardation on my part):

SELECT * COUNT(id) FROM comments WHERE cid=1 AND sid=1

 

In addition, I was considering adding a rating script to rate each on a scale of 1 to 5. I already have the actual rating implemented, but I'd prefer to use something more secure for rating than cookies since a user can just clear their cookies to vote again. Is there any efficient way to label each user's account with which id's they've voted for? I'm building my website around the MyBB database.

 

Thanks.

[amites]

get that * out of your query,

 

might like to take a gander at http://www.tizag.com/mysqlTutorial/mysqlcount.php

[phpSensei]

<?php

$counter = 1;
$query = "SELECT  COUNT(id) FROM comments WHERE cid='1' AND sid='1'";
$result = mysql_query($query);

  while($row = mysql_fetch_array($result)){

    echo  $counter . " " . $row['comment_title'] . "<br>";

    $counter++;
   }

?>

[sixteh]

Okay...the count thing works now.

However, can you help with the rating system? Thanks.

[teng84]

$query = "SELECT  COUNT(id) as score FROM comments WHERE cid='1' AND sid='1' order by score desc";

this should give you the result from highest to lowest..

[sixteh]

Sorry, what I'm trying to fix with the rating system is that I want it to register who voted for what in some way other than cookies.

[sixteh]

Bump - Thanks for the help so far, but I'm still stumped on the rating system; I still need a way to save who voted for who by user account instead of cookies.

[revraz]

Have a voting table, and mark the user's id when they voted.

[sixteh]

So for each set of fields in my table do I add an extra field or am I supposed to create a new table altogether?