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);
?>
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>
</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> </td>
<td> </td>
<td> </td>
<td align="left"><input name="search" type="submit" value="Search" /></td>
</tr>
</table>
</form>
Code: Select all
<table width="100%" border="1" align="center" cellpadding="2" cellspacing="2">
<tr>
<td align="right"> </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>