> -----Original Message-----
> From: Dan Shirah [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 19, 2007 3:10 PM
> To: [email protected]
> 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
> class="smallbold">Request Id:</span></td>
> <td width="128" class="tblcell"><input type="Text"
> value="<?php echo $_POST['request_id']; ?>" name="request_id"
> size="8" maxlength=""></td>
> <td width="187" align="right"><span class="smallbold">Date
> Entered:</span></td>
> <td width="254" class="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
> class="smallbold">Status:</span></td>
> <td width="128" class="tblcell"><input type="Text"
> value="<?php echo $_POST['status']; ?>" name="status"
> size="8" maxlength=""></td>
> <td width="187" align="right"><span
> class="smallbold">Request Type:</span></td>
> <td width="254" class="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">
> <input type="submit" name="reset" value="Reset">
> </td>
> </tr>
> </table>
> <table align="center" border="0" cellpadding="0" cellspacing="0"
> width="780">
> <tr><td> </td></tr>
> <tr>
> <td height="13" align="center" class="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"
> class="tblhead"><div align="center">Request
> ID </div></td>
> <td width="224" height="13" align="center"
> class="tblhead"><div align="center">Date/Time
> Entered </div></td>
> <td width="156" height="13" align="center"
> class="tblhead"><div align="center">Status</div></td> <td
> width="156" height="13" align="center" class="tblhead"><div
> align="center">Request Type </div></td> <td width="156"
> height="13" align="center" class="tblhead"><div
> align="center">Last Processed By</div></td> </tr> </table>
> <?php $database = "database"; $host = "host"; $user =
> "username"; $pass = "password";
> // Connect to the datbase
> $connection = mssql_connect($host, $user, $pass) 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'
> class='tblcell'><div align='center'>$id</div></td>"; echo
> "<td width='224' height='13' align='center'
> class='tblcell'><div align='center'>$dateTime</div></td>";
> echo "<td width='156' height='13' align='center'
> class='tblcell'><div align='center'>Open</div></td>"; echo
> "<td width='156' height='13' align='center'
> class='tblcell'><div align='center'>Payment Type</div></td>";
> echo "<td width='156' height='13' align='center'
> class='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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
