help with search query criteria logic

[$php_mysql$]

could someone help correct the logic? here is my function for search

 

function SearchResults($post) {
        $searchdetails = array();

$criteria = "";
if($post['searchquery'] != ""){
   	$criteria .= " s.title LIKE '%".$post['searchquery']."%'";
    		
}

if(strtolower($post['category']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " OR "; 
    }
    $criteria .= " s.category ='".$post['category']."'";
}

 if(strtolower($post['state']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " OR "; 
    }
    $criteria .= " s.state ='".$post['state']."'";
}

if(strlen($criteria)) {
    $sql    = "SELECT * FROM `tbl` AS s  WHERE ".$criteria;
    $rs     = executeSql($sql); 
    while($row = mysql_fetch_array($rs)) {
	$searchdetails[] = $row;
    }
}
return $searchdetails;
    }			

 

my search form like this

 

<form action="search_ads.php" name="search" method="POST">
		Search: <input type="text" name="searchquery" /> 
		Category: <select name="category" size="1">
		<option value="All">All Categories</option>
		<option value="Events">Events</option>
		</select>
		State: <select name="state" size="1">
		<option value="All">All States</option>
		<option value="South_carolina">South Caroline</option>
		</select>
<input type="submit" name="search" value="Search"/>
</form>

 

 

[trq]

Have you got a specific question / problem? We are not here to write code for people.

[$php_mysql$]

yes i do have a question, with  following function below

 

function SearchResults($post) {
        $searchdetails = array();

$criteria = "";
if($post['searchquery'] != ""){
    if($post['searchquery'] == 'searchquery') {
	$criteria .= " s.title LIKE '%".$post['searchquery']."%'";
    }
}

if(strtolower($post['category']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " OR "; 
    }
    $criteria .= " s.category ='".$post['category']."'";
}

 if(strtolower($post['state']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " OR "; 
    }
    $criteria .= " s.state ='".$post['state']."'";
}

if(strlen($criteria)) {
    $sql    = "SELECT * FROM `tbl` AS s  WHERE ".$criteria;
    $rs     = executeSql($sql); 
    while($row = mysql_fetch_array($rs)) {
	$searchdetails[] = $row;
    }
}
return $searchdetails;
    }			

 

say i type a keyword which is in DB and in a table, so if i type the keyword, select a wrong category but a state which is right then results still show according to the matching state but categpry is wrong.

 

so what i want is, even if it exist in that state table but if category is wrong then it should return result 0

 

also when im typing a keyword which exist in table and select all category and all state nothing is showing

[$php_mysql$]

any helo here?

[Nodral]

Change the OR statements to AND.

 

Therefore you will only SELECT if all critera are true.  With OR it will select as long as one out of the criteria are true.

[$php_mysql$]

thanks, so now i did this

 

function SearchResults($post) {
        $searchdetails = array();

$criteria = "";
if($post['searchquery'] != ""){
    if($post['searchquery'] == 'searchquery') {
	$criteria .= " s.title LIKE '%".$post['searchquery']."%'";
    }
}

if(strtolower($post['category']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " AND "; 
    }
    $criteria .= " s.category ='".$post['category']."'";
}

 if(strtolower($post['state']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " AND "; 
    }
    $criteria .= " s.state ='".$post['state']."'";
}

if(strlen($criteria)) {
    $sql    = "SELECT * FROM `tbl` AS s  WHERE ".$criteria;
    $rs     = executeSql($sql); 
    while($row = mysql_fetch_array($rs)) {
	$searchdetails[] = $row;
    }
}
return $searchdetails;
    }		

 

so now if i type a word boomer but in a category title does not have anything in similar instead it got  wang but when i hit subit it show up wang tho it is not matching with boomer

[Nodral]

Put some sort of test into your script which will echo out the SELECT statement which you are running.

 

See if you can fathom where the error is coming from after doing that, if not just post it on here and I'll have another look

[$php_mysql$]

here is my form

 

<form action="search_ads.php" name="search" method="POST">
		Search: <input type="text" name="searchquery" /> 
		Category: <select name="category" size="1">
		<option value="All">All Categories</option>
		<option value="Events">Events</option>
		<option value="Career">Career</option>
		<option value="Realestate">Realestate</option>
		</select>
		State: <select name="state" size="1">
		<option value="All">All States</option>
		<option value="State1">State1</option>
		<option value="State2">State2 Pradesh</option>
		<option value="State3">State3</option>
		</select>
<input type="submit" name="search" value="Search"/>
</form>

 

here is my function

 

function SearchResults($post) {
        $searchdetails = array();

$criteria = "";
if($post['searchquery'] != ""){
    if($post['searchquery'] == 'searchquery') {
	$criteria .= " s.title LIKE '%".$post['searchquery']."%'";
    }
}

if(strtolower($post['category']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " AND "; 
    }
    $criteria .= " s.category ='".$post['category']."'";
}

 if(strtolower($post['state']) != 'all') {
    if(strlen($criteria) > 0) {
    $criteria .= " AND "; 
    }
    $criteria .= " s.state ='".$post['state']."'";
}

if(strlen($criteria)) {
    $sql    = "SELECT * FROM `tbl` AS s  WHERE ".$criteria;
    $rs     = executeSql($sql); 
    while($row = mysql_fetch_array($rs)) {
	$searchdetails[] = $row;
    }
}
return $searchdetails;
    }			

 

and here is how i pull data

 

if(count($_POST)) {		
	$sost = $_POST;
	//printr($sost);
	$getResults = SearchResults($sost);
}

 

so am trying to achieve is

 

1. if a user types a query in search form and selects no category or state then it show similar results from all category and state

 

2. if user types query and selects a category but no state so anything similar to the query in that category from all states show

 

3. if user types query selects a state but not category then results matching in the query from category in of that state shows

 

4, if a user types query and selects a category and a state if similar results exist shows else dnt show

 

 

this is what im trying to achieve but something is not right somewhere

[Nodral]

You need to do the following

 

if(strlen($criteria)) {	    
$sql    = "SELECT * FROM `tbl` AS s  WHERE ".$criteria;
// insert the next line to help you debug
echo $sql;

$rs     = executeSql($sql); 	    
while($row = mysql_fetch_array($rs)) {		
$searchdetails[] = $row;	    
}	
}	
return $searchdetails;    
}	

 

Does the SELECT statement read as you expect it to? 

Is this why you are not getting the information as you would expect?

 

Try this, and then post the SELECT statement you see.  This will then indicate where your error could be.

[$php_mysql$]

ok seems like it not fetching the search query input data i get this

 

SELECT * FROM `tbl` AS s WHERE s.category ='Realestate' AND s.state ='state1'

[Nodral]

Ok, you seem to be over complicating this by passing an array to your function.  You'd be better to just pass it as 2 variables.

 

eg

function SearchResults($category, $state){
//
//
//
//
//
//
//
}

 

 

 

 

[$php_mysql$]

ccoring to you does the function look  ok for the purpose im trying to achieve?

[$php_mysql$]

ok so now i get this after i lil change

 

$criteria = "";

if($post['searchquery'] != ""){

$criteria .= " s.title LIKE '%".$post['searchquery']."%'";

}

 

the output is

 

SELECT * FROM `tbl` AS s WHERE s.title LIKE '%search%' AND s.category ='All' AND s.state ='All'

 

this is without selecting any category or state

 

so instead of showing ALL how do i make a change that it shows

 

SELECT * FROM `tbl` AS s WHERE s.title LIKE '%search%' AND s.category ='categoryl' AND s.state ='state'

 

so that it searches entire category and entire state for resuls?

 

 

[$php_mysql$]

succeeded at last :-)