johram pong johram pong - 1 year ago 65
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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download