php forms using multiple where conditions and mysql

rrsimons · February 4, 2012

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:

<?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:

<div>

<tr>

<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>

</td>

<td align="right">County or Parish: </td>

</tr>

<tr>

<td width="63" align="right">Lease: </td>

</tr>

<tr>

<td width="111" align="right">Operator Name: </td>

</tr>

<tr>

</tr>

</table>

</form>

My Repeat Region starts here

<tr>

<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>

</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>

<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>

<?php } // Show if not last page ?></td>

<?php } // Show if not last page ?></td>

</tr>

</table>

MadTechie · February 4, 2012

please use PHP/CODE tags as its a pain to read that post,

without reading the whole thing (it hurts my eyes)

i would suggest the following

<?php 

$WHERE = "State='".$_GET['state']."' ";


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

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

//etc

$SQL = "SELECT * FROM table WHERE $WHERE";

of course that's just the outline to give you the idea

rrsimons · February 4, 2012

Thanks for the reply and tip...it's exactly what I was looking for...sorry about not using PHP/CODE and I'll use it the next time I post anything.

MadTechie · February 7, 2012

No worries,

also can you please use the Topic Solved button (bottom left) ( i have one it for you on this one)

Sign In

php forms using multiple where conditions and mysql

Recommended Posts

rrsimons

Link to comment

Share on other sites

MadTechie

Link to comment

Share on other sites

rrsimons

Link to comment

Share on other sites

MadTechie

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information