[RESOLVED] COUNT query from form where fields may be blank question.

[dmacman1962]

Hi All,

I am trying to build a query (a search form) that the user can fill in any of several fields to search the database. I can get my COUNT query to work correctly (meaning the number of records returned) if I only specify one field (in the actual query, not all 6), but I need to let them pick any combination of 6 fields and I cannot seem to get how to do that.

Here is the query I tried, but it returned a COUNT of 384 (which I suspect is the number of records that the 5 empty fields matched and the 'State' field I actually filled in, matched (IE 384 records only had information in the State field and not the others, so I got 384 records as COUNT).

The actual number of records with that 'State' is 5186, but I got back 384.

Here is my query.

$query_count  = "SELECT State 
as totalCount 
FROM max8_copy
WHERE EmailAddress LIKE '%$EmailAddress%'
AND FirstName LIKE '%$FirstName%'
AND LastName LIKE '%$LastName%'
AND City LIKE '%$City%'
AND State LIKE '%$State%'
AND Zip LIKE '%$Zip%'
GROUP BY State";

I know this is possible, since almost any site does this, its just that I never did it before.

Thanks in advance for the help,
Don

[Piranha]

First thing is that if you only want the number of rows you should use "select count(something)", for example select count(*).

Second is that your query should not handle the occations where you don't want to search for something. Use if statements instead, something like this:

$sql = "select something from somewhere where "
if ($column1 != null || $column1 != "") {
   $sql .= "column1 = '$column1' and "
}
// Other if statements
// Remove the 4 last characters in the sql string
// Add everything you want that should be after where

[dmacman1962]

You just showed me a whole new approach that I had no idea was allowed, using a loop (test) within a query.

Thanks!.

I tried what you suggested for both my count, and my search queries.

count query

$query_count = "SELECT State,
COUNT(*) as totalCount
FROM max8_copy
WHERE EmailAddress LIKE '%$EmailAddress%'
OR FirstName LIKE '%$FirstName%'
OR LastName LIKE '%$LastName%'
OR City LIKE '%$City%'
OR State LIKE '%$State%'
OR Zip LIKE '%$Zip%'
GROUP BY State";

search query

$query = "SELECT * FROM max8_copy WHERE";
 		if($EmailAddress != NULL ||
 		$FirstName != NULL ||
 		$LastName != NULL ||
 		$City != NULL ||
 		$State != NULL ||
 		$Zip != NULL) { 
		$query .= " EmailAddress LIKE '%$EmailAddress%'
 		OR FirstName LIKE '%$FirstName%'
 		OR LastName LIKE '%$LastName%'
 		OR City LIKE '%$City%'
 		OR State LIKE '%$State%'
 		OR Zip LIKE '%$Zip%'
 		GROUP BY State";
		}

But I ended up with a cound of 65535 records (which was incorrect).

I follow (somewhat) your logic, but obviously, I am not getting it fully.

I will with your help and I appreciate your assistance.

regards,
Don

[dmacman1962]

I took your logic and applied it to the query_count and got it to work for the state queries, but when I add another field, I get the max record count (65535).

$query_count = "SELECT State, COUNT(*) as totalCount FROM max8_copy WHERE";
 	if($EmailAddress != NULL) {
		$query_count .= " EmailAddress LIKE '%$EmailAddress%',";
		}
	if($FirstName != NULL) {
		$query_count .= " FirstName LIKE '%$FirstName%',";
		}
	if($LastName != NULL) {
		$query_count .= " LastName LIKE '%$LastName%',";
		}		
	if($City != NULL) {
		$query_count .= " City LIKE '%$City%',";
		}	
	if($State != NULL) {
		$query_count .= " State LIKE '%$State%'";
		}	
		$query_count .= "GROUP BY State";

Thanks,
Don

[Piranha]

Try to print out the query before you run it, I think you will get a suprise at how it looks. Also try to print out the variables you insert into the query.

By the way, this code does not use a loop, or if statement, inside a query, it just builds a string. What you do to the string later doesn't have anything to do with the build of it.

[dmacman1962]

Ok, I worked with these queries and got them to work (mostly) but I have one issue left.

The count query gives me an accurate count now, but the query for the search fields gives me a low number of records. So when I put them together within my pagination code, I end up with a pagination (count) of 1575 records, but the search produces only 258 records, so my pagination 'runs out' of data before it should.

IE both queries are not ending up with the same number of rows.

In this search, I used EmailAddress ='yahoo' and state ='Illinois'.

And I think what is happening is it is only giving me the records where those 2 fields match and including all records where the other fields are blank, instead of all records that match just the EmailAddress and State and ignoring the other fields.

Here is the COUNT query:

 $query_count    = "SELECT COUNT(*) as totalCount FROM max8_copy WHERE";
 	if($EmailAddress != NULL || $EmailAddress != "") {
		$query_count .= " EmailAddress LIKE '%$EmailAddress%' AND";
		}
	if($FirstName != NULL || $FirstName != "") {
		$query_count .= " FirstName LIKE '%$FirstName%' AND";
		}
	if($LastName != NULL || $LastName != "") {
		$query_count .= " LastName LIKE '%$LastName%' AND";
		}		
	if($City != NULL || $City != "") {
		$query_count .= " City LIKE '%$City%' AND";
		}	
	if($State != NULL || $State != "") {
		$query_count .= " State LIKE '%$State%'";
		}  

  	if($Zip != NULL || $Zip != "") {
		$query_count .= " Zip LIKE '%$Zip%'";
		} 

And here is the search query:

 $query    = "SELECT * FROM max8_copy WHERE";
 		if($EmailAddress != NULL || $EmailAddress != ""
		|| $FirstName != NULL || $FirstName != ""
		|| $LastName != NULL || $LastName != ""
		|| $City != NULL || $City != ""
		|| $State != NULL || $State != "" 
		|| $Zip != NULL || $Zip != "") { 
$query .= " EmailAddress LIKE '%$EmailAddress%'
		AND FirstName LIKE '%$FirstName%'
		AND LastName LIKE '%$LastName%'
		AND City LIKE '%$City%'
		AND State LIKE '%$State%'
		AND Zip LIKE '%$Zip%'
		ORDER BY EmailAddress ASC
		LIMIT $limitvalue, $limit";
		}

Thanks for the help,
Don

[dmacman1962]

Never mind, I got this working (although, if you have a better way, I am open to suggestions).

$query    = "SELECT * FROM max8_copy WHERE";
		//build te queries based on what fields are filled in
 		if($EmailAddress != NULL || $EmailAddress != "") {
			$query .= " EmailAddress LIKE '%$EmailAddress%' AND";
			}
 		if($FirstName != NULL || $FirstName != "") {
			$query .= " FirstName LIKE '%$FirstName%' AND";
			}
 		if($LastName != NULL || $LastName != "") {
			$query .= " LastName LIKE '%$LastName%' AND";
			}
 		if($City != NULL || $City != "") {
			$query .= " City LIKE '%$City%' AND";
			}
 		if($State != NULL || $State != "") {
			$query .= " State LIKE '%$State%'";
			}			
 		if($Zip != NULL || $Zip != "") {
			$query .= "AND Zip LIKE '%$Zip%'";
			}
			$query .= " ORDER BY EmailAddress ASC LIMIT $limitvalue, $limit";

This gave me the query results for the search fields I needed without limiting the records for blank fields.

Thanks for you help,
Don

[Piranha]

Just make sure to use the same where-part of the query and it should work. Otherwise you will just count one thing and retreive another.