Help with Query By Example (wildcard "%") Form

TerryMullins · December 19, 2010

I am trying to make a simplified query by example search form for my website.

I would like to allow my users to do more complicated queries from one my prospects table.

The user will be able to search by 1 or more field(s), select the operator for each field & enter the search value for each field.

The advanced_search .php file is below:

<!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="<>">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="<>">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="<>">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="<>">Not Equal</option>
      <option value="Like %...%">Like %...%</option>
    </select></td>
    <td><input type="text" name="city_value" id="city_value" /></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td><label>
      <input type="submit" name="button" id="button" value="Search" />
      </label></td>
    <td> </td>
  </tr>
  </table>
</form>  
</body>
</html>

The do_advanced_search.php is 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;

?>

Any help would be greatly appreciated.

I will poet a completed version of this once it is finished.

Thank you,

Terry Mullins

TerryMullins · December 20, 2010

At this point, I have modified the do_advanced_search.php to this.

$company_operator=$_POST['company_operator'];
$company_value=$_POST['company_value'];
$state_operator=$_POST['state_operator'];
$state_value=$_POST['state_value'];
$county_operator=$_POST['county_operator'];
$county_value=$_POST['county_value'];
$city_operator=$_POST['city_operator'];
$city_value=$_POST['city_value'];

echo "Value of \$company_operator: $company_operator <br>" ;
echo "Value of \$company_value: $company_value <br>";
echo "Value of \$state_operator: $state_operator <br>";
echo "Value of \$state_value: $state_value <br>";
echo "Value of \$county_operator: $county_operator <br>";
echo "Value of \$county_value: $county_value <br>";
echo "Value of \$city_operator: $city_operator <br>";
echo "Value of \$city_value: $city_value <br>";

$sql="Select * FROM smf_prospects WHERE companyname $company_operator `$company_value` AND state $state_operator `$state_value` AND county $county_operator `$county_value` AND city $city_operator `$city_value`";
echo $sql;

If the user was to enter 'FL' into the State Value, (( wants all records from the database that are in Florida), this is what the do_advanced_search.php file returns.

Value of $company_operator: =

Value of $company_value:

Value of $state_operator: =

Value of $state_value: FL

Value of $county_operator: =

Value of $county_value:

Value of $city_operator: =

Value of $city_value:

Select * FROM smf_prospects WHERE companyname = `` AND state = `FL` AND county = `` AND city = ``

Of course, if you query the database with this, you will get an empty dataset.

How can I make this work to where it will only include the values that the user is actually searching for ?

In otherwords, the above example should have parsed to...

Select * FROM smf_prospects WHERE state = `FL`

Thank you,

Terry Mullins

TerryMullins · December 20, 2010

I am sorry that I didn't put this into the last post, but I also changed the advanced_search.php to 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="!=">Not Equal</option>
      <option value="LIKE">Like</option>
      <option value="LIKE "% `. $_POST['company_value'] .` %"">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="!=">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="!=">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="!=">Not Equal</option>
      <option value="LIKE">Like</option>
    </select></td>
    <td><input type="text" name="city_value" id="city_value" /></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td><label>
      <input type="submit" name="button" id="button" value="Search" />
      </label></td>
    <td> </td>
  </tr>
  </table>
</form>  
</body>
</html>

The this form looks like the jpg that I attached.

[attachment deleted by admin]

Sign In

Help with Query By Example (wildcard "%") Form

Recommended Posts

TerryMullins

Link to comment

Share on other sites

TerryMullins

Link to comment

Share on other sites

TerryMullins

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information