mysql查询，根据日期检索记录 - mysql query to retrieve record based on dates

我有一个只有一个字段日期的表。我需要使用表中的单个字段从数据库中检索基于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 }?>

1.）使用mysqli，您的已弃用

2.）将您的where条款更改为：

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

$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 '$date' and '$date1'";

对于您的第二个查询：

$result = mysql_query("SELECT sum(amount) 
                       FROM payment 
                       WHERE (date BETWEEN '$date' and '$date1') AND ( id = '$id')");

我建议在整个过程中使用单个查询。我把你的第一个更新为：

$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, SUM(p.amount)
        FROM member AS m 
        INNER JOIN payment AS p ON m.id = p.id 
        WHERE p.date between '$date' and '$date1'
        GROUP BY m.id";

$result = mysql_query("SELECT sum(amount) FROM payment where  date between '$date' and     '$date1') or die(mysql_error());