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)) { // loop multible rows (if any) printing the results
echo $rowї'foo'];
}
// ...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.