Page 1 of 1

count the numbers. count the numbers...

Posted: Fri Oct 27, 2006 10:48 am
by mikeeeeeeey
Hey guys, happy Friday!

Having a wee problem with counting some mysql_num_rows() at the moment.

well, I'm not really. I'm getting all the numbers I need but it's more a problem of displaying it in the right place.

Check tha code:

Code: Select all

$tbSQL   = "SHOW TABLES";
$tbQuery = mysql_query($tbSQL);
									
$tNum    = 0;
$num     = 0;
									
if ($num >= 2 || $num == 0)
{
	echo "There are " . $num . " results for your search.<br/><br/>";
}
elseif ($num == 1)
{
	echo "There is " . $num . " result for your search.<br/><br/>";
}
									
while ($tb = mysql_fetch_array($tbQuery))
{
	$sql[$tNum]   = "SELECT * FROM " . $tb[0] . " WHERE title LIKE '%" . $search . "%' OR sub_section LIKE '%" . $search . "%' OR summary LIKE '%" . $search . "%' OR article LIKE '%" . $search . "%' ORDER BY title ASC";
        $query[$tNum] = mysql_query($sql[$tNum]);
	$num         += (int)mysql_num_rows($query[$tNum]);
										
	echo "<table>";
	echo "<tr><td style=\"font-weight:bold;font-size:16px\">Results from " . ucwords(str_replace("_"," ", $tb[0])) . "</td></tr>";
	echo "<tr><td>";

	while($row[$tNum] = mysql_fetch_array($query[$tNum]))
	{
		echo "<table style=\"padding:20px;border-bottom:solid 1px #FF0F21\">";
		echo "<tr><td style=\"color:#FF0F21;font-weight:bold;text-decoration:none\">" . str_replace($search,"<span style=\"background-color:#999999\">". $search . "</span>", $row[$tNum]['sub_section']) . "</td></tr>";
		echo "<tr><td style=\"color:#999999;text-decoration:none;padding-left:30px\"><a href=\"test.php?s=" . $tb[0] . "&sub=" . $row[$tNum]['sub_section'] . "&ID=" . $row[$tNum]['ID'] . "\">" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row[$tNum]['title']) . "</a></td></tr>";
		echo "<tr><td><strong>" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row[$tNum]['summary']) . "</strong></td></tr>";
		echo "<tr><td>" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row[$tNum]['article']) . "</td></tr>";
		echo "</table>";
	}
	echo "</td></tr>";
	echo "</table>";
	$tNum ++;
}
see I want to display the search results where it's trying to output them, but because $num doesn't get populated until after the while loops have finished, it returns zero everytime.

help!

thanks in advance you nice people :)

Posted: Fri Oct 27, 2006 10:54 am
by malcolmboston
either

echo mysql_num_rows () before the while loop <--- very easy

or

use Output control <--- can be tricky and unreliable but allows for "semi-real-time" output

Posted: Fri Oct 27, 2006 10:56 am
by hawleyjr
You have a cuople options.

Echo the count after the loop.

Add a count query before the loop:

Code: Select all

$sql[$tNum]   = "SELECT COUNT(id) FROM " . $tb[0] . " WHERE title LIKE '%" . $search . "%' OR sub_section LIKE '%" . $search . "%' OR summary LIKE '%" . $search . "%' OR article LIKE '%" . $search . "%' ORDER BY title ASC";
Don't output the results in the loop save it to a string and then output it after you output the count

:)

Posted: Fri Oct 27, 2006 10:58 am
by mikeeeeeeey
If I use mysql_num_rows before the while loop, won't I have to run all the sql before that aswell, which means making two more while loops for before the mysql_num_rows?

Posted: Fri Oct 27, 2006 11:00 am
by mikeeeeeeey
It works if you output it after all the loops have finished, but as far as a searching page goes, I've never seen one with the number of results at the after the results. :?

Posted: Fri Oct 27, 2006 12:02 pm
by timvw
Since hawlyjr already gave a couple of valid solutions (which you don't seem to understand) i'll give another one:

instead of echo-ing all the html in your loop... Append all the data to a $string... Preprend the numbers... And output... (Hmmz, perhaps this should be considered as the manual implementation of the output-buffering solution hawlyjr referred too)

Posted: Fri Oct 27, 2006 5:53 pm
by RobertGonzalez
Untested of course, but it might work for you.

Code: Select all

<?php
$sql = "SHOW TABLES";
if (!$result = mysql_query($sql))
{
	die('Could not show table: ' . mysql_error());
}

$table_count = mysql_num_rows($result);

$table_array = array();
while ($row = mysql_fetch_array($result))
{
	$table_array[] = $row;
}

$num = 0;
for ($i = 0; $i < $table_count; ++$i)
{
	$sql = "SELECT * FROM " . $table_array[$i][0] . " WHERE title LIKE '%" . $search . "%' OR sub_section LIKE '%" . $search . "%' OR summary LIKE '%" . $search . "%' OR article LIKE '%" . $search . "%' ORDER BY title ASC";
	if (!$result = mysql_query($sql))
	{
		echo '<h1>This query (' . $sql . ') had problems: ' . mysql_error());
	}
	
	$num += mysql_num_rows($result);

	echo "<table>";
	echo "<tr><td style=\"font-weight:bold;font-size:16px\">Results from " . ucwords(str_replace("_"," ", $table_array[$i][0])) . "</td></tr>";
	echo "<tr><td>";

	while ($row = mysql_fetch_array($result))
	{
		echo "<table style=\"padding:20px;border-bottom:solid 1px #FF0F21\">";
		echo "<tr><td style=\"color:#FF0F21;font-weight:bold;text-decoration:none\">" . str_replace($search,"<span style=\"background-color:#999999\">". $search . "</span>", $row['sub_section']) . "</td></tr>";
		echo "<tr><td style=\"color:#999999;text-decoration:none;padding-left:30px\"><a href=\"test.php?s=" . $table_array[$i][0] . "&sub=" . $row['sub_section'] . "&ID=" . $row['ID'] . "\">" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row['title']) . "</a></td></tr>";
		echo "<tr><td><strong>" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row['summary']) . "</strong></td></tr>";
		echo "<tr><td>" . str_replace($search,"<span style=\"background-color:#90EE90\">". $search . "</span>", $row['article']) . "</td></tr>";
		echo "</table>";
	}
	echo "</td></tr>";
	echo "</table>";
}
?>