php - mysql查询根据日期检索记录

Question

我有一个只有一个字段日期的表格。我需要使用表中的单个字段基于 2dates 从数据库中检索记录。有可能吗？

我有一个表单，用户可以在其中输入开始日期和结束日期。要检索的这两个日期之间的所有记录。但是数据库中的日期字段中只有一个日期，是否可以检索。

这是代码。

<?php
$id="";
$date=$_REQUEST["date"];
$date1=$_REQUEST["date"];


$username = "root";
$password = "";
$hostname = "localhost"; 
$db = "abc";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
mysql_select_db($db,$dbhandle) or die('cannot select db');
 $query="SELECT m.id, m.fathername, m.mothername, m.address,m.mobile,m.phone, m.fatheroccupation, m.placef, m.motheroccupation, m.placem,m.income,m.childname1,m.class1,m.schoolname1,m.gross,m.organisation,m.member,m.economy,p.month,p.date,p.amount
  FROM member AS m 
   INNER JOIN payment AS p 
   ON m.id = p.id where p.date between p.date='$date' and p.date='$date1'";

$run= mysql_query($query);






while($row=mysql_fetch_array($run))
{
$id=$row[0];
$fathername=$row[1];
$mothername=$row[2];
$address=$row[3];
$mobile=$row[4];
$phone=$row[5];
$fatheroccupation=$row[6];
$placef=$row[7];
$motheroccupation=$row[8];
$placem=$row[9];
$income=$row[10];
$childname1=$row[11];
$class1=$row[12];
$schoolname1=$row[13];

$gross= $row[14];
$organisation=$row[15];
$member=$row[16];
$economy= $row[17];


$month=$row[18];
$date=$row[19];

$amount=$row[21];


?>

<tr align='center'>
<td><?php echo $id; ?></td>

<td><?php echo $fathername; ?></td>



<td><?php echo $mothername; ?></td>
<td><?php echo $address; ?></td>
<td><?php echo $mobile;?></td>
<td><?php echo $phone;?></td>
<td><?php echo $fatheroccupation; ?></td>
<td><?php echo $placef; ?></td>
<td><?php echo $motheroccupation; ?></td>
<td><?php echo $placem; ?></td>
<td><?php echo $income; ?></td>
<td><?php echo $childname1; ?></td>
<td><?php echo $class1; ?></td>
<td><?php echo $schoolname1; ?></td>


<td><?php echo $gross; ?></td>
<td><?php echo $organisation;?></td>
<td><?php echo $member; ?></td>
<td><?php echo $economy; ?></td>


<td><?php echo $month; ?></td>
<td><?php echo $date; ?></td>
<td><?php echo $date1; ?></td>
<td><?php echo $amount; ?></td>



</tr>
<?php } ?>
<?php

        $result = mysql_query("SELECT sum(amount) FROM payment where  date='$date' and date='$date1'") or die(mysql_error());
        while ($rows = mysql_fetch_array($result)) {
    ?>


            <span style="font-size:22px; margin-bottom:20px;">  &nbsp;Total Amount Contributed:&nbsp;</span><?php echo $rows['sum(amount)']; ?></div>

    <?php }?>

Answer 1

1.) 使用 mysqli，你的已弃用

2.) 将您的 where-clause 更改为:

WHERE p.date BETWEEN '$date' AND '$date1'