Total Records

raouleduke

I am just trying to get a total number of CUSTNO's out of my database, for some reason it is picking up the first CUSTNO as the Total_Results number. I think maybe I just need a fresh pair of eyes to look at this, this seems like it should be an easy fix, but I can get my head to think about it.

$query1 = "SELECT COUNT(*) as Num FROM COONRAPIDSRECOMMEND WHERE CLOSEDATE > '2004-01-01'";   

						  if($selectmake =='1'){
						  $query1.=" AND MAKE IN (\"".$selectedmakestr."\")"; 
						  }
						  if($selectmodel =='1'){
						  $query1.=" AND MODEL IN (\"".$selectedmodelstr."\")"; 
						  }
						  if($selectyear =='1'){ 
						  $query1.=" AND YEAR IN (\"".$selectedyearstr."\")"; 
						  }
						  if($selectcity =='1'){ 
						  $query1.=" AND CITY IN (\"".$selectedcitystr."\")"; 
						  }
						  if($selectstate =='1'){
						  $query1.=" AND STATE IN (\"".$selectedstatestr."\")"; 
						  }
						  if($selectzip =='1'){
						  $query1.=" AND ZIP IN (\"".$selectedzipstr."\")";
						  }
						  $query1.=" GROUP BY CUSTNO";

					print $query1;
					$total_results = mysql_result(mysql_query($query1),0);

					$total_price = @round($total_results*.85,2);

bradgrafelman

Couple of things about your post...

You didn't tell us what the problem is. What is it saying? Are you getting any error messages?
I see that you at least print out your query so you can see it... can you post that here for us so we can inspect it as well?
Is MySQL trying to tell you something? Don't try to save space when you lose the ability to debug; write out the steps in querying the database! For example:

print $query1 . '<br>';
$exec = mysql_query($query1) or die('Error: ' . mysql_error());
$total_results = mysql_result($exec, 0);

raouleduke

The query seems to run fine, this is the print out I am getting

SELECT COUNT(*) as Num FROM COONRAPIDSRECOMMEND WHERE CLOSEDATE > '2004-01-01' AND YEAR IN ("2003","2002","2001") GROUP BY CUSTNO

But it's just not giving me the right result. I am just trying to count the number of DISTINCT CUSTNO's in the database within this criteria. I seems that it is giving me the first CUSTNO it finds as the $total_results, not the total number of CUSTNO's.

Eric_T

Have you tried

$total_results = mysql_result($query1,0);

kburger

Since you're using GROUP BY, you're not going to get a single result. You're going to get a count of the number of rows in the table each unique 'CUSTNO' has, for each 'CUSTNO' that matches your WHERE clause. Make sense? You're going to need a loop to process that result, assuming that's actually the query you're trying to do. I'm guessing that's not the query you want. I'm guessing you want to leave off the GROUP BY. Then you'll get a single total of the number of rows that match your WHERE clause.

Actually, now that I re-read your post it looks to me like what you want is the number of rows returned by your query, not the SUM itself.

$numberofcustomersmatchingquery = mysql_num_rows($result);

bradgrafelman

Isn't that what he wants, though?

I am just trying to count the number of DISTINCT CUSTNO's in the database within this criteria.

kburger

Rather than me go back and try to edit my previous post, let me just try to summarize in a new post. This should work.

$query1 = "SELECT whatever FROM COONRAPIDSRECOMMEND WHERE CLOSEDATE > '2004-01-01'";   

if($selectmake =='1'){
	$query1.=" AND MAKE IN (\"".$selectedmakestr."\")";
}
// -- SNIP -- //

$query1.=" GROUP BY CUSTNO";

$result = mysql_query($query1) or die("Query failed: " . mysql_error());
$total_results = mysql_num_rows($result);
$total_price = @round($total_results*.85,2);