-1

I m using the 'BETWEEN' statement in php data objects to show posts made between 2 specific dates, but however, 'date from' is not working, although 'date to' is working properly, if I only set 'date from', nothing is displayed, but if I only set 'date to', correct posts are displayed, also if I set both 'date from' and 'date to', still correct posts are displayed, the problem is only when I select only 'date from', This is my code

$dateFrom = $_POST['dateFrom'];
$dateTo = $_POST['dateTo'];
$sql = "SELECT * FROM Main_Posts_table WHERE DATE(time) BETWEEN '$dateFrom' AND '$dateTo' ";
$data = $connect->query($sql);
echo "<center><table class = 'main_table'>";
foreach($data as $row){
echo "<tr><td><span id = 'time_of_post'>".$row['time']."</span><br/><br/>".$row['posts']."<br/><br/><span id = 'comment_button'>comments &#8609;</span></td></tr>";

}
echo "</table></center>";
user7324674
  • 49
  • 1
  • 6
  • what's the datatype of `time` column? – hassan Mar 13 '17 at 08:20
  • the type is datetime @hassan – user7324674 Mar 13 '17 at 08:20
  • Please show sample Data and Output you get – Jens Mar 13 '17 at 08:21
  • 3
    Possible duplicate of [SQL Server : fetching records between two dates?](http://stackoverflow.com/questions/13099364/sql-server-fetching-records-between-two-dates) – RST Mar 13 '17 at 08:24
  • This is mysql not sql server, I tagged it mysql – user7324674 Mar 13 '17 at 08:28
  • seems a logical issue .. if you don't set dateTo then you are searching between a valid date and null .. null is converted in the beginning mysql date .. and then the query not retunr values because the end date is less the start date – ScaisEdge Mar 13 '17 at 08:31
  • I did thought about the same, so that means, I will have to make a separate if-else block where dateto is not set and datefrom is set, then in $sql, I will set 'datefrom' to $datefrom and instead of $dateto, I can set a custom value like 2060-4-4, is this the only way ? @scaisEdge – user7324674 Mar 13 '17 at 08:39
  • yes both the way are correct .. you can also check if the $dateTo is null then assign tan high value for your need . – ScaisEdge Mar 13 '17 at 08:44
  • pdo with no prepared statements... useless – Masivuye Cokile Mar 13 '17 at 10:50
  • Since the input type was date, that is why I didn't bother about prepared statements, do I need to prepare the statements even if the input type is date ? @Masivuye Cokile – user7324674 Mar 14 '17 at 11:51

1 Answers1

0

The following code works:

if(empty($_POST['dateTo']) && !empty($_POST['dateFrom'])){
    $sql = "SELECT * FROM Main_Posts_table WHERE DATE(time) BETWEEN '$dateFrom' AND '2080-12-31' ";
}
else{
    $sql = "SELECT * FROM Main_Posts_table WHERE DATE(time) BETWEEN '$dateFrom' AND '$dateTo' ";
}

If dateto is not set, it is set to null, a value less than datefrom, so I created a separate if else block, to check for a case where only datefrom is set, and then I set dateto to a very high value, if anyone can think of a shorter code, please post

user7324674
  • 49
  • 1
  • 6