Making a search that resembles phpmyadmin

[TerryMullins]

I am trying to make a search page that resembles the search that phpmyadmin uses. When the user completes this form, will pass the search to a master / detail page set, but I am not sure how to go about writting the query to work with the operator that the user selects.
Below is the html part of the code that I am working with.
Can someone point me in the right direction ?

Thanks,
Terry

<table border="0" cellspacing="5" cellpadding="5">
  <tr>
    <th>Field</th>
    <th>Operator</th>
    <th>Value</th>
  </tr>
  <tr>
    <td nowrap="nowrap"><label>Company Name:
      <input type="text" name="company_name" id="company_name" />
    </label></td>
    <td><label>
      <select name="company_operator" size="1" id="company_operator">
        <option value="=" selected="selected">=</option>
        <option value="&lt;&gt;">Not Equal</option>
        <option value="Like %...%">Like %...%</option>
      </select>
    </label></td>
    <td><label>
      <input type="text" name="company_value" id="company_value" />
    </label></td>
  </tr>
  <tr>
    <td><label>State:
      <input type="text" name="state" id="state" />
    </label></td>
    <td><select name="state_operator" size="1" id="state_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="state_value" id="state_value" /></td>
  </tr>
  <tr>
    <td><label>County:
      <input type="text" name="county" id="county" />
    </label></td>
    <td><select name="county_operator" size="1" id="county_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="county_value" id="county_value" /></td>
  </tr>
  <tr>
    <td><label>City:
      <input type="text" name="city" id="city" />
    </label></td>
    <td><select name="city_operator" size="1" id="city_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="city_value" id="city_value" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td><label>
      <input type="submit" name="button" id="button" value="Search" />
      </label></td>
  </tr>
  </table>

[jeepin81]

this should help point you in the right direction:

//get all of you form fields
$co_op = strtoupper($_POST['company_operator']);
$ci_op = strtoupper($_POST['city_operator']);


//clean data - 
$clean_co_op = mysql_real_escape_string($co_op);
$clean_ci_op = mysql_real_escape_string($ci_op);


//query your db

$search = "SELECT * FROM TABLE WHERE ucase(COMPANY_OPERATOR) LIKE '".$clean_co_op%."' OR ucase(CITY_OPERATOR) LIKE '".$clean_ci_op%."' OR ucase(ETC) LIKE '".$clean_etc%."' WHERE COUNTY = 'COUNTY'";

Depending on how much data you want to return or don't want to return you can adjust your LIKE/AND/OR/%/=.

There's lots of examples out there Dr. Google can show you.

Hope that helps,
j

[dagon]

you could download phpmyadmin, and look at their code.

[toma42]

Have you considered Zend_Search_Lucene?

http://framework.zend.com/manual/en/zend.search.lucene.html

[TerryMullins]

Thanks for the code snippet.
What I am really after is a way to make a search form that has all of the fields my customer's table so that the user can do a more advanced search.
I don't know how to go about writing the query & other code so that it will only query the database using the field(s) and operator(s) that user entered into the form.

[TerryMullins]

toma42;10970056 wrote:

Have you considered Zend_Search_Lucene?

http://framework.zend.com/manual/en/zend.search.lucene.html

I'm sorry, I took a look at the webpage but I am having a hard time figuring out what exactly I am looking at.

Thanks,
Terry

[TerryMullins]

I guess that you could call the form that I am trying to create is also known as a 'Query By Example' (or) QBE.

The idea is to allow the user to use the form to make more advanced queries from our database. This would allow them, for instance, to just search for the field 'STATE' and pick an operator like '=' and enter something like 'FL' for the value they wish to search by. This form would work whether the user used some, or all of the available fields to query with.

The advanced_query.php file goes like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Advanced Query</title>
</head>

<body>
<form name="form1" method="post" action="do_advanced_search.php">
<table border="0" cellspacing="5" cellpadding="5">
  <tr>
    <th>Field Name</th>
    <th>Operator</th>
    <th>Value</th>
  </tr>
  <tr>
    <td><input name="company_name" type="text" id="company_name" value="Company Name" readonly="readonly" /></td>
    <td><select name="company_operator" size="1" id="company_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="company_value" id="company_value" /></td>
  </tr>
  <tr>
    <td><input name="state" type="text" id="state" value="State" readonly="readonly"/></td>
    <td><select name="state_operator" size="1" id="state_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="state_value" id="state_value" /></td>
  </tr>
  <tr>
    <td><label>
      <input name="County" type="text" id="County" value="County" readonly="readonly" />
    </label></td>
    <td><select name="county_operator" size="1" id="county_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="county_value" id="county_value" /></td>
  </tr>
  <tr>
    <td><input name="city" type="text" id="city" value="City" readonly="readonly" /></td>
    <td><select name="city_operator" size="1" id="city_operator">
      <option value="=" selected="selected">=</option>
      <option value="&lt;&gt;">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="city_value" id="city_value" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><label>
      <input type="submit" name="button" id="button" value="Search" />
      </label></td>
    <td>&nbsp;</td>
  </tr>
  </table>
</form>  

</body>
</html>

The do_advanced.php below.

$connection=mysql_connect($servername,$dbusername,$dbpassword);
$db=mysql_select_db($dbname,$connection) or die(mysql_error());

$sql=mysql_query("SELECT * FROM smf_prospects where companyname '". $_POST['company_operator'] ."' '" . $_POST['company_value'] ."' AND state '" . $_POST['state_operator'] ."' '" . $_POST['company_value'] ."' AND county '". $_POST['county_operator'] ."' '" . $_POST['county_value'] . "');
echo $sql;
$result=mysql_query($sql,$connection) or die(mysql_error());

while($row=mysql_fetch_array($result)) {
	$comapanyname= $row['companyname'];
	$state=$row['state'];
	$county=$row['county'];
}
echo $companyname;
echo $state;
echo $county;

?>

I know that there is something wrong with the query, but I can't get the syntax.

I will post a working version of this once completed with it.

Thanks in advance.
Terry Mullins