unable to show values between current date and one week before date

Question

Here I a have problem in mysql , that how to find value from current date to one week before. Here I have write a program , which should show date between current date and one week before date.

Here my php code:

<?php
$hostdb = 'localhost';
$namedb = 'my_db';
$userdb = 'root';
$passdb = 'root';

try {

  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8


$currDate = date('Y-m-d H:i:s');
$date = $currDate ;
// parse about any English textual datetime description into a Unix timestamp
$ts = strtotime($date);
// calculate the number of days since Monday
$dow = date('w', $ts);
//$offset = $dow - 3;
$offset = $dow - date('d');
if ($offset < 0) {
    $offset = 6;
}
// calculate timestamp for the Monday
$ts = $ts - $offset*86400;
// loop from Monday till Sunday
for ($i = 0; $i < 1; $i++, $ts += 86400){
    //print date("Y-m-d l", $ts) ."</br>" . "\n";
$week = date("Y-m-d H:i:s", $ts) ."</br>";

}


  $sql = "SELECT * FROM battery WHERE date BETWEEN " . $date .  " AND " . $week;


  $result = $conn->query($sql);

echo "<table border='1' style='font-family:arial;font-size12px;' cellspacing=1>
<tr>
<th>User Id</th>
<th>Firstname</th>
<th>LastName</th>
<th>EmailId</th>
<th>Date</th>
</tr>";

  while($row = $result->fetch(PDO::FETCH_ASSOC)) {
  echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['emailid'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";

  echo "</tr>";
  }

echo "</table>";

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();

}

?>

Here $date gives current date and $week gives one week before date. I am not able to find value between these two dates. Please , I need some help. Any help will be greatly appreciated.

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

I believe this answer here might help: MySQL Query - Records between Today and Last 30 Days

You can change last 30 days to last 7 days (or 5 if it only runs on a friday? :))

edited May 23 '17 at 12:21

Community

1
1

answered May 08 '13 at 10:08

lukeocodes

1,192
1
16
31

score 0 · Answer 2 · answered May 08 '13 at 10:11

0

You can use the datediff method in mysql to do that.

SELECT * FROM battery WHERE DATEDIFF(now(), `date`)<=6 AND DATEDIFF(now(), `date`)>=0

answered May 08 '13 at 10:11

Hugo Delsing

13,803
5
45
72

score 0 · Accepted Answer · answered May 08 '13 at 10:17

0

You can use DateTime and DateInterval classes in php.

http://www.php.net/manual/en/class.datetime.php http://www.php.net/manual/en/class.dateinterval.php

answered May 08 '13 at 10:17

cha

730
2
12
28

unable to show values between current date and one week before date

3 Answers3