Page 1 of 1

php forms using multiple where conditions and mysql

Posted: Sat Feb 04, 2012 6:36 pm
by rrsimons
Hello everyone,

I'm a newbie with PHP and mySQL and need some assistance with writing a php script that searches a mySQL database using a form. The form has five fields that I want to search from and one is a required field (State). I need to filter or narrow down the search by either two or more fields. The problem I am having is if I used multiple WHERE clauses using the AND condition I have to enter valid information in all five fields and if I use the OR condition then my search does not produce the desired outcome (too many results). I "think" I need to use the AND condition but I need to be able to leave some of the fields blank (except for the State field) and narrow my search with using anywhere from 2-5 search fields.

Also, another requirement is to be able to enter partial information in the search field "without" having to enter a wildcard in the search field. Any assistance is very much appreciated and thanks in advance for your help.

Form Fields:
State SELECT FIELD
Lease TEXT FIELD
Operator Name TEXT FIELD
County or Parish TEXT FIELD
Well No TEXT FIELD

I have a table called well_permits and it is structure is as follows:

date DATE
state TEXT
county VARCHAR
api VARCHAR
permit_no VARCHAR
operator VARCHAR
phone VARCHAR
contact VARCHAR
lease VARCHAR
well_no VARCHAR
permit_for VARCHAR
welltype VARCHAR
wellspot VARCHAR
lat FLOAT
lon FLOAT
depth VARCHAR

This is what I have for the connecting to my database and selecting the fields:

Code: Select all

<?php require_once('../../../Connections/Wldatabase.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

//Variable to store Unique_ID aka API which will be passed to the well-search-results.php page
$var_api_rs_search = $_Get['api'];

$maxRows_rs_search = 20;
$pageNum_rs_search = 0;
if (isset($_GET['pageNum_rs_search'])) {
  $pageNum_rs_search = $_GET['pageNum_rs_search'];
}
$startRow_rs_search = $pageNum_rs_search * $maxRows_rs_search;

$var_state_rs_search = "%";
if (isset($_GET['state'])) {
  $var_state_rs_search = $_GET['state'];
}
$var_lease_rs_search = "%";
if (isset($_GET['lease'])) {
  $var_lease_rs_search = $_GET['lease'];
}
$var_well_no_rs_search = "%";
if (isset($_GET['well_no'])) {
  $var_well_no_rs_search = $_GET['well_no'];
}
$var_operator_rs_search = "%";
if (isset($_GET['operator'])) {
  $var_operator_rs_search = $_GET['operator'];
}
$var_county_rs_search = "%";
if (isset($_GET['County'])) {
  $var_county_rs_search = $_GET['County'];
}
mysql_select_db($database_Wldatabase, $Wldatabase);
$query_rs_search = sprintf("SELECT DISTINCT * FROM well_permits WHERE (well_permits.`state` LIKE %s AND well_permits.county LIKE %s) OR (well_permits.lease LIKE %s) OR (well_permits.operator LIKE %s) OR (well_permits.well_no LIKE %s) ORDER BY well_permits.county", GetSQLValueString($var_state_rs_search, "text"),GetSQLValueString($var_county_rs_search, "text"),GetSQLValueString($var_lease_rs_search, "text"),GetSQLValueString($var_operator_rs_search, "text"),GetSQLValueString($var_well_no_rs_search, "text"));
$query_limit_rs_search = sprintf("%s LIMIT %d, %d", $query_rs_search, $startRow_rs_search, $maxRows_rs_search);
$rs_search = mysql_query($query_limit_rs_search, $Wldatabase) or die(mysql_error());
$row_rs_search = mysql_fetch_assoc($rs_search);
?>
This is my form:

Code: Select all

<form action="search.php" method="GET" name="frmsearch" target="_self">

<input name="api" type="hidden" value="" />
<div>

<table width="900" border="0" align="center" cellpadding="2" cellspacing="2">
    <tr>
       <td colspan="6">
           <p style="text-align:left">Select a State then enter at least one search criteria.  State is a required field.</p>
          * Denotes a required field.<br>
   </td>
    </tr>
    <tr>
       <td align="right">* State: </td>
       <td>
          <select name="state" size="1" dir="ltr" lang="en">
     <option value="AL">AL</option>
          <option value="AR">AR</option>
          <option value="CA">CA</option>
          <option value="CO">CO</option>
          <option value="IL">IL</option>
          <option value="IN">IN</option>
          <option value="KS">KS</option>
          <option value="KY">KY</option>
          <option value="LA">LA</option>
          <option value="MI">MI</option>
          <option value="MS">MS</option>
          <option value="MT">MT</option>
          <option value="ND">ND</option>
          <option value="NE">NE</option>
          <option value="NM">NM</option>
          <option value="NY">NY</option>
          <option value="OH">OH</option>
          <option value="OK">OK</option>
          <option value="OS">OS</option>
          <option value="PA">PA</option>
          <option value="SD">SD</option>
          <option value="TX">TX</option>
          <option value="UT">UT</option>
          <option value="WV">WV</option>
          <option value="WY">WY</option>
          &nbsp;</select>
      </td>
      <td align="right">County or Parish: </td>
      <td align="left"><input name="County" type="text" value="" size="35" maxlength="40" /></td>
    </tr>
    <tr>
      <td width="63" align="right">Lease: </td>
      <td width="239"><input name="lease" type="text" value="" /></td>
      <td align="right">Well No: </td>
      <td><input name="well_no" type="text" value="" /></td>
    </tr>
    <tr>
      <td width="111" align="right">Operator Name: </td>
      <td width="261"><input name="operator" type="text" value="" /></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td align="left"><input name="search" type="submit" value="Search" /></td>
  </tr>
</table>
</form>
My Repeat Region starts here:

Code: Select all

<table width="100%" border="1" align="center" cellpadding="2" cellspacing="2">
  <tr>
    <td align="right">&nbsp;</td>
    <th align="center">Operator</th>
    <th align="center">Lease</th>
    <th align="center">Well Number</th>
    <th align="center">County</th>
    <th align="center">State</th>
  </tr>
  <tr>  
  <?php do { ?>
    <td align="center"><a href="results.php?recordID=<?php echo $row_rs_search['api']; ?>">Select</a></td>
    <td align="left"><?php echo $row_rs_search['operator']; ?></td>
    <td align="left"><?php echo $row_rs_search['lease']; ?></td>
    <td align="center"><?php echo $row_rs_search['well_no']; ?></td>
    <td align="center"><?php echo $row_rs_search['county']; ?></td>
    <td align="center"><?php echo $row_rs_search['state']; ?></td>
  </tr>
  <?php } while ($row_rs_search = mysql_fetch_assoc($rs_search)); ?>
</table>


<p align="center">Number of Wells Located: <?php echo ($startRow_rs_search + 1) ?> to <?php echo min($startRow_rs_search + $maxRows_rs_search, $totalRows_rs_search) ?> of <?php echo $totalRows_rs_search ?></p>


<table border="0" align="center">
  <tr>
    <td align="center"><?php if ($pageNum_rs_search > 0) { // Show if not first page ?>
        <a href="<?php printf("%s?pageNum_rs_search=%d%s", $currentPage, 0, $queryString_rs_search); ?>">First</a>
        <?php } // Show if not first page ?></td>
    <td align="center"><?php if ($pageNum_rs_search > 0) { // Show if not first page ?>
        <a href="<?php printf("%s?pageNum_rs_search=%d%s", $currentPage, max(0, $pageNum_rs_search - 1), $queryString_rs_search); ?>">Previous</a>
        <?php } // Show if not first page ?></td>
    <td align="center"><?php if ($pageNum_rs_search < $totalPages_rs_search) { // Show if not last page ?>
        <a href="<?php printf("%s?pageNum_rs_search=%d%s", $currentPage, min($totalPages_rs_search, $pageNum_rs_search + 1), $queryString_rs_search); ?>">Next</a>
        <?php } // Show if not last page ?></td>
    <td align="center"><?php if ($pageNum_rs_search < $totalPages_rs_search) { // Show if not last page ?>
        <a href="<?php printf("%s?pageNum_rs_search=%d%s", $currentPage, $totalPages_rs_search, $queryString_rs_search); ?>">Last</a>
        <?php } // Show if not last page ?></td>
  </tr>
</table>

Re: php forms using multiple where conditions and mysql

Posted: Sat Feb 04, 2012 7:48 pm
by califdon
I don't have the time today to study your entire script, but there are 2 approaches I can think of to do what you describe:
  1. For each of the search columns, test whether you have a non-null value and if it is null, substitute the value "%", which is the wildcard in an SQL LIKE comparison; when using all ANDs, that will allow any value for a column where the input was null; you would have to independently check for how many fields were null.
  2. Alternatively, you could build your SQL statement, field by field, in PHP so that you only include the columns for which there are values, so your WHERE clause would be custom built for each query.
I think I would probably do the second approach. If the database is very large, the query might run a trifle faster.

In any case, you definitely must use ANDs, but if no search parameter is given for a given column, you want to match all entries in that column (in other words, you don't want filter out any rows based on that column).

Re: php forms using multiple where conditions and mysql

Posted: Sun Feb 05, 2012 8:27 am
by rrsimons
Thanks for your response califdon...I like your second approach but I'm unsure of how to code it. I have tried the following with no joy...it would not produce any results...the syntax is correct because I don't get any errors. I truly appreciate your help!

Code: Select all

$var_state_rsSearch = "state='".$_GET['state']."' ";


if (isset($_GET['county'])) {
  $var_state_rsSearch .= "AND county='".$_GET['county']."' ";
  $var_county_rsSearch = $_GET['county'];
}

if (isset($_GET['lease'])) {
  $var_state_rsSearch .= "AND lease='".$_GET['lease']."' ";
  $var_lease_rsSearch = $_GET['lease'];
}

if (isset($_GET['operator'])) {
  $var_state_rsSearch .= "AND operator='".$_GET['operator']."' ";
  $var_operator_rsSearch = $_GET['operator'];
}

if (isset($_GET['well_no'])) {
  $var_state_rsSearch .= "AND well_no='".$_GET['well_no']."' ";
  $var_well_no_rsSearch = $_GET['well_no'];
}


mysql_select_db($database_Wldatabase, $Wldatabase);
$query_rsSearch = sprintf("SELECT * FROM well_permits WHERE $var_state_rsSearch ORDER BY county ASC", GetSQLValueString($var_state_rs_search, "text"),GetSQLValueString($var_county_rs_search, "text"),GetSQLValueString($var_lease_rs_search, "text"),GetSQLValueString($var_operator_rs_search, "text"),GetSQLValueString($var_well_no_rs_search, "text"));

Re: php forms using multiple where conditions and mysql

Posted: Sun Feb 05, 2012 12:17 pm
by califdon
I'm having a little problem following your sprintf() logic, as applied to this situation. I think that you don't need to use both that and the GetSQLValueString() function if you construct the SQL statement as you are now doing. It would greatly simplify your code and I think you may be duplicating the purpose of those techniques. Since you will be dealing with each field separately, you can (and must) recognize whether each field is numeric or alpha, and use quotes appropriately. But once done, I don't see a need to use those functions. Also, in PHP, there is no need to type-cast numerics, etc. That's all automatic.

One other important point: in order to see MySQL error messages, you need to do something like the following:

Code: Select all

$sql_str = "SELECT * FROM Something WHERE This = 'That' ORDER BY id";
$result = mysql_query($sql_str) or die(mysql_error());
If there is a MySQL error, the mysql_query will return a boolean FALSE and the script will halt ("die") and echo the MySQL error to the browser; if there is no error, the or will be ignored. Important: this is for debugging and should be removed when you go public, since an error could reveal useful data about your server to a potential hacker.

A final suggestion: it's useful in debugging a database issue to construct your SQL statement string, then immediately echo the string to the browser. You would be amazed at how many simple problems this reveals immediately--typos, misplaced quotes, omitted keywords--that might otherwise waste hours of your time trying to figure out. Of course, the echo statement should be removed as soon as it's working properly.

Re: php forms using multiple where conditions and mysql

Posted: Sun Feb 05, 2012 7:20 pm
by rrsimons
Thanks again for your guidance califdon. I didn't realize that GetSQLValueString() function was redundant and have removed it as you suggested and yes the code looks much cleaner...so thanks for that.

The following code works but with one caveat, none of the fields can be left blank which does not meet one of the requirements (i.e. not having to fill in all of the fields.) Albiet, the more fields filled in the narrower the search but a user may not have all of the information needed. Any suggestions on how I can use multiple AND condition but not require all of the form fields to filled in but left blank instead?

The following syntax works if all of the fields are filled in with valid data:

Code: Select all

$var_state_rsSearch = "state='".$_GET['state']."' ";


if (isset($_GET['county'])) {
  $var_state_rsSearch .= "AND county='".$_GET['county']."' ";
  $var_county_rsSearch = $_GET['county'];
}

if (isset($_GET['lease'])) {
  $var_state_rsSearch .= "AND lease='".$_GET['lease']."' ";
  $var_lease_rsSearch = $_GET['lease'];
}

if (isset($_GET['operator'])) {
  $var_state_rsSearch .= "AND operator='".$_GET['operator']."' ";
  $var_operator_rsSearch = $_GET['operator'];
}

if (isset($_GET['well_no'])) {
  $var_state_rsSearch .= "AND well_no='".$_GET['well_no']."' ";
  $var_well_no_rsSearch = $_GET['well_no'];
}

mysql_select_db($database_Wldatabase, $Wldatabase);
$query_rsSearch = sprintf("SELECT * FROM well_permits WHERE $var_state_rsSearch ORDER BY county ASC");

echo $var_state_rsSearch; 
Thanks Again for your help! I truly appreciate it!

Re: php forms using multiple where conditions and mysql

Posted: Sun Feb 05, 2012 10:14 pm
by califdon
I'm not sure if I understand your question. If one of the fields involved in the if isset($_GET['xxx']... lines is left blank, it will just not be a factor in your query, which is exactly what you want. Can you describe what is not working as you want it to? You now don't need the sprintf() at all. That was only for the purpose of inserting variables into a string, for which there is now no need to do. The line below the mysql_select_db(...) line should be merely:

Code: Select all

$query_rsSearch = "SELECT * FROM well_permits WHERE $var_state_rsSearch ORDER BY county ASC";
And of course that must be followed by the mysql_query() line.

Re: php forms using multiple where conditions and mysql

Posted: Mon Feb 06, 2012 11:50 am
by rrsimons
Aaahhh....yes and that is where the issue lies. Apparantly my code is not working as it should because the if isset($_GET['xxx']... does not leave out the blank fields but passes it as NULL instead.

When I submit the form with only 2 or 3 fields filled out the following is the result:

Code: Select all

state LIKE 'KY' AND county LIKE 'ADAIR' AND lease LIKE '' AND operator LIKE 'MAXIMUM ENERGY DEVELOPMENT, LLC' AND well_no LIKE '' 
The above result is the outcome of filling out the state=KY, county=ADAIR, and operator=MAXIMUM ENERGY DEVELOPMENT, LLC fields and leaving the lease and well_no fields blank. Because the lease and well_no fields are passed as NULL the AND condition fails.

If I were to fill out all five fields with "valid" info then I get the following result.

Code: Select all

state LIKE 'KY' AND county LIKE 'ADAIR' AND lease LIKE 'LUTTRELL, CAROLYN' AND operator LIKE 'MAXIMUM ENERGY DEVELOPMENT, LLC' AND well_no LIKE '3'
Thanks for your patience and bearing with me...I truly appreciate it califdon!

Re: php forms using multiple where conditions and mysql

Posted: Mon Feb 06, 2012 12:39 pm
by califdon
It would appear that the result of isset($_GET['xxx'] is TRUE in those cases, which suggests to me that there must be at least a space character, since the isset() function returns FALSE if the argument is Null. Look at the URL string in your browser address bar to see what is actually being sent to the script. It should look something like:[text]...search.php?state=TX&county=Bexar&lease=&well_no=&operator=[/text]
From this you should be able to see if there is a space after the equal sign of any of the arguments. You could also change from using isset() to testing whether the field name is not equal to a zero-length string:

Code: Select all

if ($_GET['lease'] != "") {
  $var_state_rsSearch .= "AND lease='".$_GET['lease']."' ";
  $var_lease_rsSearch = $_GET['lease'];
}

Re: php forms using multiple where conditions and mysql

Posted: Mon Feb 06, 2012 10:33 pm
by rrsimons
Hello califdon. I was able to solve my search problem using the !empty() function. Below is the code that makes the magic! Thanks for your guidance and patience...very much appreciated!

Code: Select all


$var_state_rs_search = "state LIKE '".$_GET['state']."' ";

if (!empty($_GET['County'])) {
  $var_state_rs_search .= "AND County LIKE '".$_GET['County']."%' ";
  $var_county_rs_search = $_GET['County'];
}
else {
	$var_state_rs_search .= "";
}
if (!empty($_GET['lease'])) {
  $var_state_rs_search .= "AND lease LIKE '".$_GET['lease']."%' ";
  $var_lease_rs_search = $_GET['lease'];
}
else {
	$var_state_rs_search .= "";
}
if (!empty($_GET['operator'])) {
  $var_state_rs_search .= "AND operator LIKE '".$_GET['operator']."%' ";
  $var_operator_rs_search = $_GET['operator'];
}
else {
	$var_state_rs_search .= "";
}
if (!empty($_GET['well_no'])) {
  $var_state_rs_search .= "AND well_no LIKE '".$_GET['well_no']."%' ";
  $var_well_no_rs_search = $_GET['well_no'];
}
else {
	$var_state_rs_search .= "";
}

mysql_select_db($database_Wldatabase, $Wldatabase);
$query_rs_search = ("SELECT * FROM well_permits WHERE $var_state_rs_search ORDER BY county ASC");

Re: php forms using multiple where conditions and mysql

Posted: Mon Feb 06, 2012 10:58 pm
by califdon
Excellent. I should have thought of that. Glad you have it fixed.