johram pong johram pong - 1 month ago 4
MySQL Question

Date in string format show two records in advance

My query is working fine but I wish to show counts of today's date + add 2 more days in advance and order data by today's date followed by tomorrow and day after?

since there are already too many records so the varchar row

meeting_date
cannot be changed in another format

Here is MySQL query

date is stored as month/date/year as in 11/01/2016.

$today = date("m/d/Y");

$query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND (meeting_date LIKE '%".$today."%') ORDER BY id DESC";
$total_pages = mysql_fetch_array(mysql_query($query));


Appreciate your time and help.

Answer

You need to do like this:

// get 3 days used in the query
$today = date("m/d/Y");
$day1 =  date("m/d/Y", time() + 86400 ); 
$day2 =  date("m/d/Y", time() + 86400*2 ); 

// updated query without LIKE
 $query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND (meeting_date = '$today' OR meeting_date = '$day1' meeting_date = '$day2') ORDER BY id DESC";

After you need to sort array with php.

Comments