CODEWITHSUNDEEP
phpmysql
user3675208
user3675208

Reputation: 45

mysql query to retrieve record based on dates

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

Upvotes: 1

Views: 212

Answers (3)

besciualex
besciualex

Reputation: 1892

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

Upvotes: 1

Jacob A.
Jacob A.

Reputation: 270

1.) use mysqli, yours is deprecated

2.) change your where-clause to:

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

Upvotes: 1

Raheel
Raheel

Reputation: 9024

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

Upvotes: 0

Related Questions