Hope someone can help, very new to all this, been teaching myself for entertainment more than anything but I can't seem to get this page to work. I had it working before with one search filter but when I add more it doesn't seem to filter at all. Trying to get the page to display all records when no search added, then the user can search 1 or more filters and the relevant records will show. 3 of the filters are a date range, each range having too text fields to and from...
希望有人可以提供帮助,对所有这些都很陌生,一直在教我自己的娱乐,但我似乎无法让这个页面起作用。我之前使用过一个搜索过滤器,但是当我添加更多时,它似乎根本没有过滤。当没有添加搜索时,尝试让页面显示所有记录,然后用户可以搜索1个或多个过滤器并显示相关记录。 3个过滤器是一个日期范围,每个范围都有太多的文本字段来往...
My code is as follows..
我的代码如下..
<?php require_once('Connections/tidentchase.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;
}
}
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
mysql_select_db($database_tidentchase, $tidentchase);
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
$newdatefrom_Recordset1 = "-1";
if (isset($_GET['newdatefrom']))
{
$newdatefrom_Recordset1 = $_GET['newdatefrom'];
}
$newdateto_Recordset1 = "-1";
if (isset($_GET['newdateto']))
{
$newdateto_Recordset1 = $_GET['newdateto'];
}
$closeddatefrom_Recordset1 = "-1";
if (isset($_GET['closeddatefrom']))
{
$closeddatefrom_Recordset1 = $_GET['closeddatefrom'];
}
$closeddateto_Recordset1 = "-1";
if (isset($_GET['closeddateto']))
{
$closeddateto_Recordset1 = $_GET['closeddateto'];
}
$cancelleddatefrom_Recordset1 = "-1";
if (isset($_GET['cancelleddatefrom']))
{
$cancelleddatefrom_Recordset1 = $_GET['cancelleddatefrom'];
}
$cancelled_Recordset1 = "-1";
if (isset($_GET['cancelled']))
{
$cancelled_Recordset1 = $_GET['cancelled'];
}
$closed_Recordset1 = "-1";
if (isset($_GET['closed']))
{
$closed_Recordset1 = $_GET['closed'];
}
$fileno_Recordset1 = "-1";
if (isset($_GET['fileno']))
{
$fileno_Recordset1 = $_GET['fileno'];
}
$borrower_Recordset1 = "-1";
if (isset($_GET['borrower']))
{
$borrower_Recordset1 = $_GET['borrower'];
}
$cancelleddateto_Recordset1 = "-1";
if (isset($_GET['cancelleddateto']))
{ // SHOW SEARCH CRITERIA
$cancelleddateto_Recordset1 = $_GET['cancelleddateto'];
$query_Recordset1 = sprintf("SELECT fileno, dateopen, borrower, cancelled, datecancelled, closed, dateclosed FROM filedata WHERE filedata.fileno = ".$fileno_Recordset1." AND filedata.borrower = ".$borrower_Recordset1." AND filedate_closed = ".$closed_Recordset1." AND filedata_cancelled = ".$cancelled_Recordset1." AND filedata.dateopen BETWEEN ".$newdatefrom_Recordset1." AND ".$newdateto_Recordset1." AND filedata.datecancelled BETWEEN ".$cancelleddatefrom_Recordset1." AND ".$cancelleddateto_Recordset1." AND filedata.dateclosed BETWEEN ".$closeddatefrom_Recordset1." AND ".$closeddateto_Recordset1."", GetSQLValueString($newdatefrom_Recordset1, "date"),GetSQLValueString($newdateto_Recordset1, "date"),GetSQLValueString($cancelleddatefrom_Recordset1, "date"),GetSQLValueString($cancelleddateto_Recordset1, "date"),GetSQLValueString($closeddatefrom_Recordset1, "date"),GetSQLValueString($closeddateto_Recordset1, "date"));
}
else // SHOW ALL RECORDS
{
$query_Recordset1 = "SELECT fileno, dateopen, borrower, cancelled, datecancelled, closed, dateclosed FROM filedata";
}
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $tidentchase) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
}
else
{
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>All Files</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css">
</head>
<body>
<H1>TRIDENTCHASEDASHBOARD</H1>
<div>
<ul>
<li><a href="home.php">HOME</a></li>
</ul>
</div>
<div>
<P>ALL FILES</P>
<form name="form1" method="post" action="">
<label for="fileno">File Number</label>
<input type="text" name="fileno" id="fileno">
<label for="borrower">Borrower</label>
<input type="text" name="borrower" id="borrower">
<input type="checkbox" name="cancelled" id="cancelled">
<label for="cancelled">Cancelled</label>
<input type="checkbox" name="closed" id="closed">
<label for="closed">Closed</label>
<label for="newdatefrom">New Date From</label>
<input type="text" name="newdatefrom" id="newdatefrom">
<label for="newdateto">New Date to</label>
<input type="text" name="newdateto" id="newdateto">
<label for="closeddatefrom">Closed Date From</label>
<input type="text" name="closeddatefrom" id="closeddatefrom">
<label for="closeddateto">Closed Date To</label>
<input type="text" name="closeddateto" id="closeddateto">
<label for="cancelleddatefrom">Cancelled date from</label>
<input type="text" name="cancelleddatefrom" id="cancelleddatefrom">
<label for="cancelleddateto">Cancelled Date To</label>
<input type="text" name="cancelleddateto" id="cancelleddateto">
<input type="submit" name="search" id="search" value="Submit">
</form>
</div>
<p>To update any file click on the file number below</p>
<table border="2" cellpadding="2" cellspacing="2">
<tr>
<td>fileno</td>
<td>dateopen</td>
<td>borrower</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['fileno']; ?></td>
<td><?php echo $row_Recordset1['dateopen']; ?></td>
<td><?php echo $row_Recordset1['borrower']; ?></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
mysql_free_result($allfiles);
?>
</html>
I've either got it completely wrong or I'm close.. I think
我要么完全错了,要么就近了......我想
Thanks in advance!
提前致谢!
Ben
1 个解决方案
#1
0
You need to build the sql statement as you process the input fields. Instead of this:
您需要在处理输入字段时构建sql语句。而不是这个:
$newdatefrom_Recordset1 = "-1";
if (isset($_GET['newdatefrom']))
{
$newdatefrom_Recordset1 = $_GET['newdatefrom'];
}
You will need to do this:
你需要这样做:
$fld = ""; $whr = ""; // initialise variables
$newdatefrom_Recordset1 = "";
if (isset($_GET['newdatefrom'])) {
if ( $fld != '' ) $fld .= ","; //--- build field list
if ( $whr != '' ) $whr .= " or "; //--- build sql where joiner
$fld .= 'dateopen';
$whr .= "dateopen='".$_GET['newdatefrom']."'";
}
//--- repeat for each field you want to search on
//--- apply any rules i.e. date range and values etc
//--- when ready to run query then
$qry = "SELECT ".$fld." WHERE ".$whr;
#1
0
You need to build the sql statement as you process the input fields. Instead of this:
您需要在处理输入字段时构建sql语句。而不是这个:
$newdatefrom_Recordset1 = "-1";
if (isset($_GET['newdatefrom']))
{
$newdatefrom_Recordset1 = $_GET['newdatefrom'];
}
You will need to do this:
你需要这样做:
$fld = ""; $whr = ""; // initialise variables
$newdatefrom_Recordset1 = "";
if (isset($_GET['newdatefrom'])) {
if ( $fld != '' ) $fld .= ","; //--- build field list
if ( $whr != '' ) $whr .= " or "; //--- build sql where joiner
$fld .= 'dateopen';
$whr .= "dateopen='".$_GET['newdatefrom']."'";
}
//--- repeat for each field you want to search on
//--- apply any rules i.e. date range and values etc
//--- when ready to run query then
$qry = "SELECT ".$fld." WHERE ".$whr;