Is there any easier way to count rows in multiple columns

[rcorlew]

I have this script that when it is executed will count the rows in 3 columns with 3 different queries. Then depending on the final query it will run a 4th to fetch array. Here is the code and any suggestions would help if there is anything wrong with doing this this way.

 

At the bottom of the page I do a mysql_free_result for the queries.

 

<?php
if(isset($var) {
echo "<table id='myForum'>
  				<tr>
    				<th align='center'>Viewing the Forum Info</th>
  				</tr>";

	$con = mysql_connect($HOST, $USER, $PASS);
	if (!$con) { 
  			// Since the entire script depends on connection, die if connection fails 
  		die("Error connecting to MySQL database!"); 
			} 
	mysql_select_db($NAME, $con);   

	$result = mysql_query("SELECT * FROM users",$con); 	 

	$usercount = mysql_num_rows($result) or die;

	$result2 = mysql_query("SELECT * FROM threads",$con) or die; 

	$thread_count = mysql_num_rows($result2) or die;

	$result3 = mysql_query("SELECT * FROM f_topics",$con) or die; 

	$cat_count = mysql_num_rows($result3) or die;

	$result4 = mysql_query("SELECT * FROM threads ORDER BY id DESC LIMIT 1",$con) or die;

	while($row4 = mysql_fetch_array($result4)) {
		$lastpostby = "$row4[poster]";
		$lastthreadposted = "$row4[id]";
		$lastreply = "$row4[ref]";
		$lastpostdate = "$row4[date]";
		$lastposttime = "$row4[time]";
		} 
			if($lastreply == 0){
				$lastthread = $lastthreadposted;
			}
			if($lastreply != 0){
				$lastthread = $lastreply;
			}
  			echo"<tr>
    				<td valign='top'>There are $usercount users registered for this <br />
				There are $thread_count posts in $cat_count Topics.<br />
				The last post was on $lastpostdate at $lastposttime by $lastpostby<br /> 
				You can view it <a href='forum.php?function=viewtr&thread=$lastthread'>here</a><br />
			</td>
  				</tr>
   ";

echo "</table>";
  }
}
?>

[btherl]

Those are three tables, not three columns

 

Better is to do it like this:

 

$result = mysql_query("SELECT count(*) FROM users", $con) or die(mysql_error());
$usercount = mysql_result($result, 0, 0);

 

When you give mysql a count(*) query instead of just select *, then it is able to do significant optimizations, because it knows you only want the count and not the actual results themselves.

[fenway]

When you give mysql a count(*) query instead of just select *, then it is able to do significant optimizations, because it knows you only want the count and not the actual results themselves.

That true without a WHERE clause... otherwise, there's a good chance that the only benefit is on sending the "resultset" back to the client.