I have a page that shows all outstanding tickets, what I'm trying to have it
do is let users search by several categories. I want to be able to filter
the results based on the user input.

For instance, say I have an employee that wants to find ticket #2. He
enters "2" in the Request ID field and also enters "01/01/06" in the Date
Requested field. How would I return the ticket that matches both Request ID
and Date Requested fields? I have been able to return a result with only 1
search criteria entered, but get no results with more than 1.

Below is the code I am working on.

else {
/* if the "submit" variable exists, the form has been submitted - look for
and process form data */
// display result
$request_id = $_POST['request_id'];
$date_entered = $_POST['date_entered'];
$status = $_POST['status'];
$request_type = $_POST['request_type'];


?>
<form name="submitForm" action="<?php echo $_SERVER['PHP_SELF']; ?>"
method="post">
<table align="center" width="780" cellpadding="2" cellspacing="2"
border="0">
<tr>
<td width="185" align="right"><span cl***="smallbold">Request
Id:</span></td>
<td width="128" cl***="tblcell"><input type="Text" value="<?php echo
$_POST['request_id']; ?>" name="request_id" size="8" maxlength=""></td>
<td width="187" align="right"><span cl***="smallbold">Date
Entered:</span></td>
<td width="254" cl***="tblcell"><input type="Text" value="<?php echo
$_POST['date_entered']; ?>" name="date_entered" size="15" maxlength=""></td>
</tr>
<tr>

<td width="185" height="27" align="right"><span
cl***="smallbold">Status:</span></td>
<td width="128" cl***="tblcell"><input type="Text" value="<?php echo
$_POST['status']; ?>" name="status" size="8" maxlength=""></td>
<td width="187" align="right"><span cl***="smallbold">Request
Type:</span></td>
<td width="254" cl***="tblcell"><input type="Text" value="<?php echo
$_POST['request_type']; ?>" name="request_type" size="15" maxlength=""></td>
</tr>
</table>
<FORM ACTION="a href="javascript:clearForm()" METHOD="POST"
name="logoutform">
<table align="center" border="0" cellpadding="0" cellspacing="0"
width="780">
<tr>
<td colspan="2">
<input type="submit" name="submit" value="Search">&nbsp;
<input type="submit" name="reset" value="Reset">
</td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0"
width="780">
<tr><td>&nbsp;</td></tr>
<tr>
<td height="13" align="center" cl***="tblhead"><div
align="center"><strong>Process
Payments </strong></div></td>
</tr>
<tr>
<td colspan="6"><hr color="#006600" /></td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0"
width="780">
<tr>
<td width="88" height="13" align="center" cl***="tblhead"><div
align="center">Request
ID </div></td>
<td width="224" height="13" align="center" cl***="tblhead"><div
align="center">Date/Time
Entered </div></td>
<td width="156" height="13" align="center" cl***="tblhead"><div
align="center">Status</div></td>
<td width="156" height="13" align="center" cl***="tblhead"><div
align="center">Request Type </div></td>
<td width="156" height="13" align="center" cl***="tblhead"><div
align="center">Last Processed By</div></td>
</tr>
</table>
<?php
$database = "database";
$host = "host";
$user = "username";
$p*** = "p***word";
// Connect to the datbase
$connection = mssql_connect($host, $user, $p***) or die ('server
connection failed');
$database = mssql_select_db("$database", $connection) or die ('DB
selection failed');
// Query the table and load all of the records into an array.

**Note my SQL statement below, can I break in and out of PHP like this to
verify if multiple variables are set?*
*
$sql = "SELECT
child_support_payment_request.credit_card_id,
credit_card_payment_request.credit_card_id,
date_request_received
FROM child_support_payment_request,
credit_card_payment_request
WHERE child_support_payment_request.credit_card_id =
credit_card_payment_request.credit_card_id" ?>
<?php if ($request_id !== '') {
"AND credit_card_payment_request.credit_card_id = $request_id"
}
?>
<?php if ($dateTime !== '') {
"AND date_request_received = $dateTime";
}
?>
<?php
$result = mssql_query($sql) or die(mssql_error());
echo "<table width='780' border='1' align='center' cellpadding='2'
cellspacing='2' bordercolor='#000000'>";

while ($row = mssql_fetch_array($result)) {
$id = $row['credit_card_id'];
$dateTime = $row['date_request_received'];
echo "<tr>";
echo "<td width='88' height='13' align='center' cl***='tblcell'><div
align='center'>$id</div></td>";
echo "<td width='224' height='13' align='center' cl***='tblcell'><div
align='center'>$dateTime</div></td>";
echo "<td width='156' height='13' align='center' cl***='tblcell'><div
align='center'>Open</div></td>";
echo "<td width='156' height='13' align='center' cl***='tblcell'><div
align='center'>Payment Type</div></td>";
echo "<td width='156' height='13' align='center' cl***='tblcell'><div
align='center'>Last Processed By</div></td>";
echo "</tr>";
}
echo "</table>";
?>
<?php } ?>

> -----Original Message-----
> From: Dan Shirah [private.php?do=newpm&u=]
> Sent: Friday, January 19, 2007 3:10 PM
> To: php-EMAIL REMOVED
> Subject: [PHP] Conditional Select
>
> I have a page that shows all outstanding tickets, what I'm
> trying to have it do is let users search by several
> categories. I want to be able to filter the results based on
> the user input.
>
> For instance, say I have an employee that wants to find
> ticket #2. He enters "2" in the Request ID field and also
> enters "01/01/06" in the Date Requested field. How would I
> return the ticket that matches both Request ID and Date
> Requested fields? I have been able to return a result with
> only 1 search criteria entered, but get no results with more than 1.
>
> Below is the code I am working on.
>
> else {
> /* if the "submit" variable exists, the form has been
> submitted - look for and process form data */
> // display result
> $request_id = $_POST['request_id'];
> $date_entered = $_POST['date_entered']; $status =
> $_POST['status']; $request_type = $_POST['request_type'];
>
>
> ?>
> <form name="submitForm" action="<?php echo $_SERVER['PHP_SELF']; ?>"
> method="post">
> <table align="center" width="780" cellpadding="2" cellspacing="2"
> border="0">
> <tr>
> <td width="185" align="right"><span
> cl***="smallbold">Request Id:</span></td>
> <td width="128" cl***="tblcell"><input type="Text"
> value="<?php echo $_POST['request_id']; ?>" name="request_id"
> size="8" maxlength=""></td>
> <td width="187" align="right"><span cl***="smallbold">Date
> Entered:</span></td>
> <td width="254" cl***="tblcell"><input type="Text"
> value="<?php echo $_POST['date_entered']; ?>"
> name="date_entered" size="15" maxlength=""></td>
> </tr>
> <tr>
>
> <td width="185" height="27" align="right"><span
> cl***="smallbold">Status:</span></td>
> <td width="128" cl***="tblcell"><input type="Text"
> value="<?php echo $_POST['status']; ?>" name="status"
> size="8" maxlength=""></td>
> <td width="187" align="right"><span
> cl***="smallbold">Request Type:</span></td>
> <td width="254" cl***="tblcell"><input type="Text"
> value="<?php echo $_POST['request_type']; ?>"
> name="request_type" size="15" maxlength=""></td>
> </tr>
> </table>
> <FORM ACTION="a href="javascript:clearForm()" METHOD="POST"
> name="logoutform">
> <table align="center" border="0" cellpadding="0" cellspacing="0"
> width="780">
> <tr>
> <td colspan="2">
> <input type="submit" name="submit" value="Search">&nbsp;
> <input type="submit" name="reset" value="Reset">
> </td>
> </tr>
> </table>
> <table align="center" border="0" cellpadding="0" cellspacing="0"
> width="780">
> <tr><td>&nbsp;</td></tr>
> <tr>
> <td height="13" align="center" cl***="tblhead"><div
> align="center"><strong>Process
> Payments </strong></div></td>
> </tr>
> <tr>
> <td colspan="6"><hr color="#006600" /></td> </tr>
> </table> <table align="center" border="0" cellpadding="0"
> cellspacing="0"
> width="780">
> <tr>
> <td width="88" height="13" align="center"
> cl***="tblhead"><div align="center">Request
> ID </div></td>
> <td width="224" height="13" align="center"
> cl***="tblhead"><div align="center">Date/Time
> Entered </div></td>
> <td width="156" height="13" align="center"
> cl***="tblhead"><div align="center">Status</div></td> <td
> width="156" height="13" align="center" cl***="tblhead"><div
> align="center">Request Type </div></td> <td width="156"
> height="13" align="center" cl***="tblhead"><div
> align="center">Last Processed By</div></td> </tr> </table>
> <?php $database = "database"; $host = "host"; $user =
> "username"; $p*** = "p***word";
> // Connect to the datbase
> $connection = mssql_connect($host, $user, $p***) or die
> ('server connection failed');
> $database = mssql_select_db("$database", $connection) or
> die ('DB selection failed');
> // Query the table and load all of the records into an array.
>
> **Note my SQL statement below, can I break in and out of PHP
> like this to verify if multiple variables are set?*
> *
> $sql = "SELECT
> child_support_payment_request.credit_card_id,
> credit_card_payment_request.credit_card_id,
> date_request_received
> FROM child_support_payment_request,
> credit_card_payment_request
> WHERE child_support_payment_request.credit_card_id =
> credit_card_payment_request.credit_card_id" ?> <?php if
> ($request_id !== '') {
> "AND credit_card_payment_request.credit_card_id = $request_id"
> }
> ?>
> <?php if ($dateTime !== '') {
> "AND date_request_received = $dateTime";
> }
> ?>
> <?php
> $result = mssql_query($sql) or die(mssql_error());
> echo "<table width='780' border='1' align='center' cellpadding='2'
> cellspacing='2' bordercolor='#000000'>";
>
> while ($row = mssql_fetch_array($result)) {
> $id = $row['credit_card_id'];
> $dateTime = $row['date_request_received']; echo "<tr>";
> echo "<td width='88' height='13' align='center'
> cl***='tblcell'><div align='center'>$id</div></td>"; echo
> "<td width='224' height='13' align='center'
> cl***='tblcell'><div align='center'>$dateTime</div></td>";
> echo "<td width='156' height='13' align='center'
> cl***='tblcell'><div align='center'>Open</div></td>"; echo
> "<td width='156' height='13' align='center'
> cl***='tblcell'><div align='center'>Payment Type</div></td>";
> echo "<td width='156' height='13' align='center'
> cl***='tblcell'><div align='center'>Last Processed
> By</div></td>"; echo "</tr>"; } echo "</table>"; ?> <?php } ?>
>


Your logic is correct but here is the correct syntax:

<?php

$sql =
"SELECT child_support_payment_request.credit_card_id,
credit_card_payment_request.credit_card_id,
date_request_received
FROM child_support_payment_request,
credit_card_payment_request
WHERE child_support_payment_request.credit_card_id =
credit_card_payment_request.credit_card_id";

if ($request_id !== '') {
$sql .= " AND credit_card_payment_request.credit_card_id = $request_id";
}
if ($dateTime !== '') {
$sql .= " AND date_request_received = $dateTime";
}

$result = mssql_query($sql) or die(mssql_error());

?>

The key here is the use of ".=" to *append* to the query string (don't
forget the space before "AND" or the query will break)


Hope that helps,

Brad