getting a result number for this kind of query

[darkcarnival]

hi,

im working a search item for my news page and so far i have fixed a few things but i have 1 issue left.

getting the number results.

now the query used to find topics <= to the time the user logged in will display all the topics.
so to fix that i had the read table check for those posts that were already marked as read. doing that gave the correct number of results to output. but now heres the problem.

the part that would say how many results were found will not match the actual list.

example:

without the read check a search find 20 results. after the read check was added, the real number was 3, but it still says 20.

so how can i output the real result number? if it sounds impossible, then say so and I'll have to not display the number results. if you know of a way to find out, please tell me as ive tried everything i can think of.

thanks.

[shoz]

Post the code snippet that makes the query and determines the number of results.

[darkcarnival]

i would but theres 2 queries.

one that searches based on the ladt time visited and another that checks to see if the post id is already marked as read in the read table.

now the second query is done from a loop that outputs the results based on the first query.

so its very hard to list the whole thing, esp when the thing is in like 3 different functions.

[shoz]

It'll be difficult to fix your error without seeing your code. However, I'll try to give you some suggestions to help you find the problem.

Chances are that the value for the number of results is being determined before you do the read check. Find that line and remove it. Then depending on how the read check and display of the results is done, you can do one of the following.

If the read check function returns an array of results to display then use [a href=\"http://www.php.net/count\" target=\"_blank\"]count()[/a] to determine the number of entries. If the display of the results is done as soon as it's determined whether or not the topic has been read, then you can setup a counter and display the number of results after all the results have been displayed.

Based on what you've said you may want to use a query similar to the following to retrieve and count the topics that have not been read.
[code]
SELECT
t.*
FROM
topics_table AS t
LEFT JOIN
read_table AS r
ON t.topic_id=r.topic_id
WHERE t.date <= $date
AND
ISNULL(r.topic_id)
ORDER BY t.date DESC
LIMIT etc
[/code]
You can then use [a href=\"http://www.php.net/mysql_num_rows\" target=\"_blank\"]mysql_num_rows[/a] to determine the count.

Without some snippets to look at, I'm not sure I can give anymore productive advice.

[darkcarnival]

well first the reg query is done followed by a num check from that.

the read check is performed in a loop like so:

[code]function search_results_newposts(){

global $db, $table2, $search_result, $search_result2, $logged_user;
//output any topics.
while ($row = mysql_fetch_assoc($search_result)) {

$db->run = "select * from ebb_read WHERE Topic='$row[tid]' and user='$logged_user'";
$read_ct = $db->num_results();
$db->close();
if ($read_ct == 0){
$searchresults .= "<tr bgcolor=\"$table2\">
<td class=\"td\" width=\"50%\"><a href=\"viewtopic.php?bid=$row[bid]&amp;tid=$row[tid]\">$row[Topic]</a></td><td class=\"td\" width=\"50%\">$row[author]</td></tr>";
}
}
//output any posts
while ($row2 = mysql_fetch_assoc($search_result2)){

$db->run = "SELECT * FROM ebb_topics where tid='$row2[tid]'";
$topic_r = $db->result();
$db->close();
//see if post is new.
$db->run = "select * from ebb_read WHERE Topic='$row2[tid]' and user='$logged_user'";
$read_ct2 = $db->num_results();
$db->close();
if ($read_ct2 == 0){
$searchresults .= "<tr bgcolor=\"$table2\">
<td class=\"td\" width=\"50%\"><a href=\"viewtopic.php?bid=$row2[bid]&amp;tid=$row2[tid]&amp;pid=$row2[pid]#$row2[pid]\">$topic_r[Topic]</a></td><td class=\"td\" width=\"50%\">$row2[re_author]</td></tr>";
}
}
return $searchresults;
}[/code]

I almost think getting a exact number will be impossible wit the way i have the whole thing created. I mean i can live without a num listed for this part of the search if i have to ;)

[shoz]

The post you've made gives the impression that there are actually 4 queries being dealt with. The 2 that give $search_result and $search_result2 and the 2 that look through these results to compare with the "ebb_read" table.

Anyway, you should be able to get a count by using a counter.
[code]
function search_results_newposts(){

global $db, $table2, $search_result, $search_result2, $logged_user;
//output any topics
$count = 0;
while ($row = mysql_fetch_assoc($search_result)) {

$db->run = "select * from ebb_read WHERE Topic='$row[tid]' and user='$logged_user'";
$read_ct = $db->num_results();
$db->close();
if ($read_ct == 0){
//increment count
$count++;
$searchresults .= "<tr bgcolor=\"$table2\">
<td class=\"td\" width=\"50%\"><a href=\"viewtopic.php?bid=$row[bid]&tid=$row[tid]\">$row[Topic]</a></td><td class=\"td\" width=\"50%\">$row[author]</td></tr>";
}
}
//output any posts
while ($row2 = mysql_fetch_assoc($search_result2)){

$db->run = "SELECT * FROM ebb_topics where tid='$row2[tid]'";
$topic_r = $db->result();
$db->close();
//see if post is new.
$db->run = "select * from ebb_read WHERE Topic='$row2[tid]' and user='$logged_user'";
$read_ct2 = $db->num_results();
$db->close();
if ($read_ct2 == 0){
//increment count
$count++;
$searchresults .= "<tr bgcolor=\"$table2\">
<td class=\"td\" width=\"50%\"><a href=\"viewtopic.php?bid=$row2[bid]&tid=$row2[tid]&pid=$row2[pid]#$row2[pid]\">$topic_r[Topic]</a></td><td class=\"td\" width=\"50%\">$row2[re_author]</td></tr>";
}
}
$result_info = array($searchresults, $count);
return $result_info;
}

[/code]
When you call the function you'd do something similar to the following
[code]
$result_info = search_results_newposts();
$search_results = $result_info[0];
$count = $result_info[1];
[/code]

[darkcarnival]

success!

this works perfectly :D

thanks a bunch shoz for the help.

mark this as solved.