Mutiple options for search box

[Alicia]

Hi I am trying to create a search box with 5 optional textfield and can you give me a idea why my form and search query is not working ? in the submitted form, my php mysql query as below that returns either not accurate result or nothing found. =(

 

 

My form

<form name="form1" method="post" action="wheretofind.php">
<table cellpadding="5" cellspacing="0" border="0" height="100">
<tr>
  <td>Company Name </td>
	<td> :</td>
  <td><input name="keyword" type="text" value="Any"/></td>
</tr>
<tr>
  <td>Occupation</td>
	<td> :</td>
	<td>
		<select name="professional">
		  <option value="Any" selected="selected">Any</option>
			<option value="Engineer">Engineer</option>
			<option value="Technician">Technician</option>

			<option value="Other">Other</option>
        </select>		</td>
</tr>
<tr>
  <td>City</td>
  <td> </td>
  <td><input name="city" type="text" value="Any" onkeypress="return submitenter(this,event)" onclick="this.value='';" id="city" /></td>
    </tr>
<tr>
  <td>State</td>
  <td> </td>
  <td><input name="state" type="text" value="Any" onkeypress="return submitenter(this,event)" onclick="this.value='';" id="state" /></td>
    </tr>
<tr>
  <td>Country</td>
	<td>: </td>
	<td>
		<select name="country">
		  <option value="Any" selected="selected">Any</option>
			<option value="AF">Afghanistan</option>
			<option value="AL">Albania</option>
			<option value="DZ">Algeria</option>
			<option value="AS">American Samoa</option>
	  </select>		</td>
</tr>
<tr>
	<td colspan="3"><input type="submit" name="submit" value="Search" /></td>
</tr>
</table>
</form>

 

 

submitted form php mysql script

  $search_query = 'mysql_query("select * from Students where';
          
          if(($keyword !='Any') || ($keyword !=''))
          	{ 
            $search_query .="NickName LIKE \'%$keyword%\' or DescriptionMe Like \'%$keyword%\'";
            }
            
           if(($professional !='Any') || ($professional !=''))
          	{ 
            $search_query .="AND Current_Pro = \'$profession\'";
            }
                 if(($city !='Any') || ($city !=''))
          	{ 
            $search_query .="AND City = \'$city\'";
            }
     	  if(($state !='Any') || ($state !=''))
          	{ 
            $search_query .="AND State = \'$state\'";
            }
          if($country !='Any')
          	{ 
            $search_query .="AND Country = \'$country\'";
            }
            
       $search_query .= 'ORDER BY `Students`.`NickName` ASC ") or die(mysql_error())';

 

Please advise and thanks.

[Brian W]

What is the entire code of the page "wheretofind.php"?

[Alicia]

the rest is not php script anymore.

 

as long as the sql query is correct, i can fetch any records the users want from the search result right ?

[premiso]

You do realize you cannot put a function in a string and expect it to be called.

 

Try this:

 

  $search_query = 'select * from Students where';
         
          if(($keyword !='Any') || ($keyword !=''))
             {
            $search_query .="NickName LIKE \'%$keyword%\' or DescriptionMe Like \'%$keyword%\'";
            }
           
           if(($professional !='Any') || ($professional !=''))
             {
            $search_query .="AND Current_Pro = \'$profession\'";
            }
                 if(($city !='Any') || ($city !=''))
             {
            $search_query .="AND City = \'$city\'";
            }
          if(($state !='Any') || ($state !=''))
             {
            $search_query .="AND State = \'$state\'";
            }
          if($country !='Any')
             {
            $search_query .="AND Country = \'$country\'";
            }
           
       $search_query .= 'ORDER BY `Students`.`NickName` ASC';
      $result = mysql_query($search_query) or die(mysql_error());
     while ($row = mysql_fetch_assoc($result)) {
            print_r($row);
    }

 

And see if that works for you.

[Brian W]

wow, can't believe I missed that. owch

[Alicia]

Not working..

 

This is the error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE \'%key%\' or DescriptionMe Like \'%key%\'AND Occupation = \'\'AND City = \' at line 1

[Brian W]

Try:

  $search_query = 'select * from Students where ';
         
          if(($keyword !='Any') || ($keyword !=''))
             {
            $search_query .="NickName LIKE \'%$keyword%\' or DescriptionMe Like \'%$keyword%\' ";
            }
           
           if(($professional !='Any') || ($professional !=''))
             {
            $search_query .="AND Current_Pro = \'$profession\' ";
            }
                 if(($city !='Any') || ($city !=''))
             {
            $search_query .="AND City = \'$city\' ";
            }
          if(($state !='Any') || ($state !=''))
             {
            $search_query .="AND State = \'$state\' ";
            }
          if($country !='Any')
             {
            $search_query .="AND Country = \'$country\' ";
            }
           
       $search_query .= 'ORDER BY `Students`.`NickName` ASC';
      $result = mysql_query($search_query) or die(mysql_error());
     while ($row = mysql_fetch_assoc($result)) {
            print_r($row);
    }

All I did was add spaces in, I think your query looked like this

select * from Students whereNickName LIKE ........

where and NickName got bunched cuz you didn't have a space.

[Alicia]

No.. still the same

 

[Brian W]

Change this line:

 $result = mysql_query($search_query) or die(mysql_error());

to

 $result = mysql_query($search_query) or die(mysql_error()."<br>".$search_query);

 

post the error message in its entirety after

[Alicia]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'%key%\' or DescriptionMe Like \'%key%\' AND Current_Pro = \'\' AND City = \'\'' at line 1

select * from Students where NickName LIKE \'%key%\' or DescriptionMe Like \'%key%\' AND Current_Pro = \'\' AND City = \'\' AND State = \'\' AND Country = \'\'ORDER BY `Students`.`NickName` ASC

[premiso]

Remove the slashes around the ' they are not needed and are causing the error.

[Alicia]

thanks premiso

 

no error now but results are not accurate.. why when maybe i search for only country, it doesnt show all records with the country i selected ?

 

Is it the Any default value causing the prob ?

[Alicia]

or shall i change on the conditional statement

 

something like this one ?

 

($keyword !='Any') || ($keyword !='')

[Brian W]

Try that, it can't do any harm (I think)

What did you input for country name?

Btw, if the rest of that page you have the query on is just HTML, than you must be using register_globals on. This is not safe and is also unusable in PHP 5

[Alicia]

not working though =( any guru can assist ?

[Alicia]

anyone can assist ?