下面是我从MYSQL数据库中搜索日期范围的代码。我可以搜索日期,它会显示表格标题(Product Desc、New或Own和Date),但它不会显示数据库中的任何结果?
关于我需要如何编辑代码以使其显示所选日期范围的结果,有什么想法吗?
<?php
if(!isset($_POST['find']))
{
?>
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>">
<table width = "450" align = "center">
<tr>
<td><b><i>Please enter date in the field below (i.e. 11-09-2012)</i></b></td>
</tr>
<tr>
<td>
From :
<input type = "text" name = "small">
To :
<input type = "text" name = "large"></td>
</tr>
<tr>
<td align = "center">
<input type = "submit" name = "find" value = "SEARCH">
<input type = "reset" value = "CLEAR FORM">
</td>
</tr>
</table>
</form>
<?php
}
else
{
$small = trim($_POST['small']);
$large = trim($_POST['large']);
$connection = mysql_pconnect("localhost", "user_name", "password") or die("Connection failed. ".myslq_error());
mysql_select_db("stock") or die("Unable to select db. ".mysql_error());
//Add 1 to the upper range, $large, else it won't make it inclusive
$query = "SELECT * FROM main_stock WHERE curr_timestamp BETWEEN DATE_FORMAT(curr_timestamp,'%".$small."') AND DATE_FORMAT(curr_timestamp, '%".($large+1)."') ORDER BY curr_timestamp";
$result = mysql_query($query) or die(mysql_error());
echo "<table width = '500' align = 'center'>";
echo "<tr><b>";
echo "<td>Product Description</td>";
echo "<td>New or Own?</td>";
echo "<td>Date</td>";
echo "</b></tr>";
while($record = mysql_fetch_object($result))
{
echo "<tr>";
echo "<td>".$record->product_desc."</td>";
echo "<td>".$record->newown."</td>";
$year_part_of_date = explode('-', $record->curr_timestamp);
echo "<td>".$year_part_of_date[0]."</td>";
//if you want the full date replace the $year_part_of_date[0] with $record->date
echo "</tr>";
}
echo "</table>";
}
?>
我猜您的查询无效。
假设curr_timestamp
是YYYY-MM-DD HH:MM:SS
格式的MySQL日期时间字段,为了便于比较,您需要将输入更改为这种格式。如果你能详细说明$small
和$large
的格式,那将很有帮助。
您可能希望您的查询是这种格式的
SELECT * FROM main_stock
WHERE curr_timestamp BETWEEN '$small_formatted' AND '$large_formatted'
ORDER BY curr_timestamp ASC
其中$small_formatted
和$large_formatted
是使用类似date('Y-m-d H:i:s', $input_timestamp)
的适当格式化时间
- 切换逻辑的顺序。您不希望您的流程是
else
- 看起来您正在将程序样式和对象样式相结合。选择一个
- 应避免选择全部(
SELECT *
)。始终指定列列表 - 日期格式不正确。您应该设置输入日期的格式以匹配数据库格式(
YYYY-MM-DD
)。我假设curr_timestamp
是YYYY-MM-DD
- 您需要停止使用
mysql_
函数,因为它们已被弃用 - 使用准备好的语句来防止SQL注入
我还没有测试过,但这修复了我上面列出的问题:
<?php
if(isset($_POST['find']))
{
$link = mysqli_connect("localhost", "user_name", "password", "stock");
if (mysqli_connect_error()) {
die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
$stmt = mysqli_prepare($link, 'SELECT * FROM main_stock WHERE curr_timestamp ' .
"BETWEEN DATE_FORMAT(?, '%m-%d-%Y') " .
"AND DATE_FORMAT(?, '%m-%d-%Y') ORDER BY curr_timestamp");
mysqli_bind_param($stmt, 'ss', $_POST['small'], $_POST['large']) or die(mysqli_error($dbh));
$result = mysqli_stmt_execute($stmt) or die(mysqli_error($link));
echo "<table width = '500' align = 'center'>";
echo "<tr><b>";
echo "<td>Product Description</td>";
echo "<td>New or Own?</td>";
echo "<td>Date</td>";
echo "</b></tr>";
while($record = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $record[product_desc] . "</td>";
echo "<td>" . $record[newown] . "</td>";
$year_part_of_date = explode('-', $record[curr_timestamp]);
echo "<td>".$year_part_of_date[0]."</td>";
//if you want the full date replace the $year_part_of_date[0] with $record->date
echo "</tr>";
}
echo "</table>";
mysqli_close($link);
}
else
{
?>
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>">
<table width = "450" align = "center">
<tr>
<td><b><i>Please enter date in the field below (i.e. 11-09-2012)</i></b></td>
</tr>
<tr>
<td>
From :
<input type = "text" name = "small">
To :
<input type = "text" name = "large"></td>
</tr>
<tr>
<td align = "center">
<input type = "submit" name = "find" value = "SEARCH">
<input type = "reset" value = "CLEAR FORM">
</td>
</tr>
</table>
</form>
<?php
}
?>