RE: [PHP] Conditional Select

> -----Original Message----- > From: Dan Shirah [mailto:mrsquash2@gmail.com] > Sent: Friday, January 19, 2007 3:10 PM > To: php-general@lists.php.net > 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">&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" 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