Page 1 of 1

SELECT Unread Threads

Posted: Tue Jul 13, 2004 5:29 am
by Dingbats
I'm making a forum and the only part that's left is marking the unread threads (the threads in which there are new posts). I use a system which a guy who has written another forum does.
When you read a thread, the time, your username and the id of the thread are inserted into a table called f_read, or updated if you have read the thread before. In the f_threads table, there's a column called lastpost, which stores the time of the last post in the thread. When you view a forum, all the threads in that forum whose lastpost > the time you read them should be selected and marked with a star.
This is the query I use for selecting them:

Code: Select all

<?php
$sqlNEW = "SELECT f_threads.id FROM f_threads LEFT OUTER JOIN f_read ON f_threads.id = f_read.thread AND f_read.user = '".$_COOKIE['user']."' WHERE ((f_threads.lastpost > f_read.time) OR f_read.time = NULL) AND forum = ".$_GET['id']." AND (f_threads.lastpost > '".$rowU['forumsince']."')";
?>
$rowU['forumsince'] is the time you last clicked 'Mark all threads as read'.

The problem with this query is that it selects all the threads in the forum. I have no idea what is wrong.
Does anyone know?

Posted: Tue Jul 13, 2004 9:05 am
by kettle_drum
On another note, i would place single quotes around $_GET['id'] as somebody can inject sql at the moment.

Posted: Tue Jul 13, 2004 9:45 am
by Weirdan
Do you have MySQL as a backend? If so, you need to use IS NULL instead of =NULL, because the expression <anything>=NULL always results in NULL.

Posted: Tue Jul 13, 2004 9:51 am
by Weirdan
echo your query before passing it to the mysql_query, then copy/paste it to some sql client (MySQLcc, PHPMyAdmin etc) and see what it gives you

Posted: Thu Jul 15, 2004 9:00 am
by Dingbats
Thanks, I'll try.
Edit: The IS NULL doesn't change anything. When I echo the result I get "Resource id #19". :S

Posted: Thu Jul 15, 2004 10:16 am
by JAM
Example code only:

Code: Select all

$result = mysql_query('select foo from bar');
 echo $result;
...will produce the resource id # message...

Code: Select all

$result = mysql_query('select foo from bar');
 while($row = mysql_fetch_assoc($result)) &#123; // loop multible rows (if any) printing the results
  echo $row&#1111;'foo'];
 &#125;
 // ...or...
 echo mysql_result($result, 0); // printing the result for hit 1
 echo mysql_result($result, 5); // printing the result for hit 6
...will give you a more desirable result. Read up on mysql_fetch_array/mysql_fetch_assoc and mysql_result for more ideas.

Posted: Thu Jul 15, 2004 10:20 am
by Dingbats
It says:
"Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 19 in c:\program\easyphp\www\sbg\forumview.php on line 64" when I try "echo mysql_result($resNEW, 0);". And the same for other numbers.

Posted: Thu Jul 15, 2004 10:30 am
by feyd
sounds like you have returned zero rows.

Posted: Thu Jul 15, 2004 7:38 pm
by JAM
feyd wrote:sounds like you have returned zero rows.
...and if you take this knowledge and combine it with mysql_num_rows() you get the "hitrecord" of the previously used query.

Short description of functions and usage. rewrite to fit your needs.

Code: Select all

if (mysql_num_rows($query_result) > 0) {
  echo 'Found: '. mysql_num_rows($query_result) .' rows.';
  while ($row = mysql_fetch_array($query_result)) {
    echo $row[0]; // for single & multiple field selects...
// or...
    echo mysql_result($query_result, 0); // for single field selects only...
  }
} else {
  echo 'No hits.';
}

Posted: Fri Jul 16, 2004 1:05 pm
by Dingbats
Sure it would work, but why would I want to do that? I just want the "while($row = mysql_fetch_array($result))" part. I don't want to output how many rows the query returned.

Posted: Fri Jul 16, 2004 2:16 pm
by feyd
I believe he was suggesting more that You output "No hits" when there are no results returned.

Posted: Sat Jul 17, 2004 1:37 pm
by JAM
Dingbats wrote:Sure it would work, but why would I want to do that? I just want the "while($row = mysql_fetch_array($result))" part. I don't want to output how many rows the query returned.
I wasn't telling you, I was demonstrating. I post examples for others to read, learn (by for example looking the functions up in the manual) and use them in their own way.

Youre welcome.