search by two given dates

[I-AM-OBODO]

Hi all,

i am try to get the result of all transaction made by a user between a given period of date i.e from 2013-01-10 to 2013-01-30 but don't know how to get it done. I only get the result of just a date even when there are other dates and transaction by the user.

 

thanks all

 

this is what i did:

 

<?php

if(isset($_POST['search'])){				

$s = $_POST['start'];
$end = $_POST['end'];

if($s == ''){
echo "invalid Entry";
}
else{
$s = date('Y-m-d', strtotime(str_replace('-','/', $s)));
$end = date('Y-m-d', strtotime(str_replace('-','/', $end)));

$r = mysql_query("SELECT * FROM transaction WHERE  username = '$_SESSION[username]' AND date LIKE '$s' AND $end ORDER BY date DESC") 
or die(mysql_error());


$num_rows = mysql_num_rows($r);
print "There are $num_rows records.<P>";
echo "<center><table border='1' bordercolor='#000000' cellpadding='1' cellspacing='1'>
<tr>
<th>Trans ID</th>
<th>Trans Ref</th>
<th>Sender Acct</th>
<th>Receiver Acct</th>
<th>Trans Status</th>
<th>Date</th>
</tr>";
while ($get_info = mysql_fetch_row($r)){ 
print "<tr>\n";
foreach ($get_info as $field) 
print "\t<td><font face=arial size=2/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
print "<br>";
mysql_close($link);
}

}			

?>

 

[requinix]

MySQL has the self-explanatory BETWEEN...AND operator.

`date` BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

[Love2c0de]

You didn't open any curly braces for your foreach loop. I'm not sure if that was the problem but I added them in.

 

Give it a try:

if(isset($_POST['search'])){						   

   $s = $_POST['start'];
   $end = $_POST['end'];

   if($s == ''){
       echo "invalid Entry";
   }
   else{
       $s = date('Y-m-d', strtotime(str_replace('-','/', $s)));
       $end = date('Y-m-d', strtotime(str_replace('-','/', $end)));

       $r = mysql_query("SELECT * FROM transaction WHERE  username = '$_SESSION[username]' AND date LIKE '$s' AND $end ORDER BY date DESC") or die(mysql_error());

       $num_rows = mysql_num_rows($r);
       print "There are $num_rows records.<P>";

       echo "<center><table border='1' bordercolor='#000000' cellpadding='1' cellspacing='1'>
           <tr>
               <th>Trans ID</th>
               <th>Trans Ref</th>
               <th>Sender Acct</th>
               <th>Receiver Acct</th>
               <th>Trans Status</th>
               <th>Date</th>
           </tr>";

       while ($get_info = mysql_fetch_row($r)){
           print "<tr>\n";

           foreach ($get_info as $field) {
               print "\t<td><font face=arial size=2/>$field</font></td>\n";
               print "</tr>\n";
           }
       }
           print "</table>\n";
           print "<br>";
           mysql_close($link);
   }

}  

 

Hope this helps you in the right direction.

 

Regards,

 

L2c.

[PFMaBiSmAd]

@Love2c0de,

 

The foreach() loop's purpose was to run the single print statement for all the <td>$field</td> values, followed by printing one </tr> tag. For a single statement, you don't need the {} and the code you posted would print the </tr> tag after every </td>$field</td> value.

[Love2c0de]

Ah my bad. Thank you very much for clearing that up. I thought the foreach was meant to loop inside the while loop. I thought he needed the curly braces because I thought he was trying to execute more than one line of code also.

 

Thanks again.

 

Kind regards,

 

L2c.

[I-AM-OBODO]

@all thanks but still not working. I tried the BETWEEN statements and did a "where date <= $s and >= $end. all to no avail.

[Barand]

"where date <= $s and >= $end. all to no avail.

 

either

where date >= '$s' and date <= '$end'

or

where date BETWEEN '$s' AND '$end'

[I-AM-OBODO]

Thank you all. Finally I got it working. I tried both the 'BETWEEN' and '>= <='. The problem was that I omitted the apostrophe sign on the end date. '$end'.

 

Thanks once again.