mysql query to retrieve record based on dates

Question

I have a table with a single field date. I need to retrieve the records from the database based on 2dates using single field in the table. Is it possible to do it?

I have a form in which user as to enter startdate from enddate. all the records between those 2 dates as to be retrieved. But there is a single date in the date field in the database.Is it possible to retrieve it.

Here is the code.

<?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 }?>

change where p.date between p.date='$date' and p.date='$date1' to where p.date between '$date' and '$date1' — Abhik Chakraborty
– Abhik Chakraborty, Commented Sep 17, 2014 at 8:26
I m getting the records. But the sum is wrong..Is this query proper to find the sum of amount — user3675208
– user3675208, Commented Sep 17, 2014 at 8:33
"SELECT sum(amount) FROM payment where date between '$date' and '$date1'" — user3675208
– user3675208, Commented Sep 17, 2014 at 8:33
The query is correct but need to make sure that you have proper dates in the DB i.e. date or datetime type with yyyy-mm-dd format and you are also passing the same format values as $date and $date1 — Abhik Chakraborty
– Abhik Chakraborty, Commented Sep 17, 2014 at 8:35

Jacob A. · Accepted Answer · 2014-09-17 09:02:19Z

1

1.) use mysqli, yours is deprecated

2.) change your where-clause to:

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

edited Sep 17, 2014 at 9:02

answered Sep 17, 2014 at 8:29

Jacob A.

2703 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

besciualex · Accepted Answer · 2014-09-17 09:35:41Z

1

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

For your second query:

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

I would recommend use a single query for the whole process. I updated your first one to this:

$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";

edited Sep 17, 2014 at 9:35

answered Sep 17, 2014 at 9:02

besciualex

1,8921 gold badge15 silver badges21 bronze badges

2 Comments

user3675208 Over a year ago

payment table contians id,amount,date

besciualex Over a year ago

And how do you corelate the payments table with each user ? What is the foreign key used in this process ? Is payment.id = member.id ?

Raheel · Accepted Answer · 2014-09-17 08:30:01Z

0

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

answered Sep 17, 2014 at 8:30

Raheel

9,06410 gold badges65 silver badges108 bronze badges

1 Comment

user3675208 Over a year ago

it only find the sum of starting and ending date.. doesnt some the amount of other records

Collectives™ on Stack Overflow

mysql query to retrieve record based on dates

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related