1

I'm getting two dates from PHP form in the dd-mm-yyyy format. (Say 01/06/2013 and 30/06/2013) Now I'm using following code to display the datewise result among the date range as given above, but it's not working for me as the dates stored in DB are in UNIX Timestamp format(transaction_date bigint(12)). How should I display the datewise results then? Can anyone help me in resolving this issue?

if($form_data['from_date']!='' && $form_data['to_date']!='') {
        $from_time  = explode("/", $form_data['from_date']);
        $to_time    = explode("/", $form_data['to_date']);

        $start_date = mktime( 0,0,0,$from_time[1],$from_time[0],$from_time[2] ) ;
        $end_date   = mktime( 23,59,59,$to_time[1],$to_time[0],$to_time[2] ) ;

        $sql  =" SELECT COUNT(*) `total count`, SUM(transaction_status = 'success') `success`, ";
        $sql .=" SUM(transaction_status = 'inprocess') `inprocess`, SUM(transaction_status = 'fail') `fail`, ";
        $sql .=" SUM(transaction_status = 'cancelled') `cancelled` FROM user_transaction ";
        $sql .=" WHERE  transaction_date >= '".$start_date."' AND transaction_date <=  '".$end_date."' GROUP BY transaction_date ";

        $this->mDb->Query( $sql);

        $queryResult = $this->mDb->FetchArray();
    }

Thanks in advance.

PHPLover
  • 1
  • 51
  • 158
  • 311

2 Answers2

2

Use FROM_UNIXTIME(transaction_date) to get it as date type. Info

mirkobrankovic
  • 2,389
  • 1
  • 21
  • 24
0

Make change in your last line of query and replace with below code:

$sql .=" WHERE  DATE_FORMAT(transaction_date,'%d-%m-%Y') >= '".$start_date."' AND DATE_FORMAT(transaction_date,'%d-%m-%Y') <=  '".$end_date."' GROUP BY FROM_UNIXTIME(transaction_date)";

And this link will be more helpful to you MySQL convert datetime to Unix timestamp.

Community
  • 1
  • 1
Code Lღver
  • 15,573
  • 16
  • 56
  • 75