9 Replies - 562 Views - Last Post: 10 January 2013 - 12:57 PM Rate Topic: -----

#1 g37752  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 195
  • Joined: 24-July 12

mysql date range not working properly?

Posted 09 January 2013 - 11:06 PM

my database field format is datetime, and HTML form input type is 'text'. how come I cannot extract the correct range?

$start=$_POST['start'].' 00:00:00';
$end=$_POST['end'].' 23:59:59';
$start=date('Y-m-d H:i:s',strtotime($start));
$end=date('Y-m-d H:i:s',strtotime($end));
...
$sql="select * from table where paytime BETWEEN '".$end."' AND '".$start."'";


I also tried <= and >=

neither returns correct results?

Is This A Good Question/Topic? 0
  • +

Replies To: mysql date range not working properly?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3404
  • View blog
  • Posts: 9,625
  • Joined: 08-June 10

Re: mysql date range not working properly?

Posted 09 January 2013 - 11:24 PM

what does the SQL string look like, when you put in the data?
Was This Post Helpful? 0
  • +
  • -

#3 g37752  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 195
  • Joined: 24-July 12

Re: mysql date range not working properly?

Posted 09 January 2013 - 11:42 PM

when i write to the database, i do

date_default_timezone_set('Asia/Shanghai'); 
$date=date('Y-m-d H:i:s');

$sql = "INSERT INTO table (paytime,...) VALUES ('$date',..)";

This post has been edited by g37752: 09 January 2013 - 11:46 PM

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3404
  • View blog
  • Posts: 9,625
  • Joined: 08-June 10

Re: mysql date range not working properly?

Posted 09 January 2013 - 11:49 PM

well, but that doesn’t answer my question.
Was This Post Helpful? 0
  • +
  • -

#5 g37752  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 195
  • Joined: 24-July 12

Re: mysql date range not working properly?

Posted 09 January 2013 - 11:54 PM

View PostDormilich, on 09 January 2013 - 11:49 PM, said:

well, but that doesn’t answer my question.

sorry, what do you mean?
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3404
  • View blog
  • Posts: 9,625
  • Joined: 08-June 10

Re: mysql date range not working properly?

Posted 10 January 2013 - 12:24 AM

I mean the output from var_dump($sql);
Was This Post Helpful? 0
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: mysql date range not working properly?

Posted 10 January 2013 - 02:06 AM

View Postg37752, on 10 January 2013 - 06:06 AM, said:

... BETWEEN '".$end."' AND '".$start."'";


Think about that for a second. Which comes first, the beginning or the end?


http://dev.mysql.com...perator_between

dev.mysql.com said:

* expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0.

Was This Post Helpful? 1
  • +
  • -

#8 g37752  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 195
  • Joined: 24-July 12

Re: mysql date range not working properly?

Posted 10 January 2013 - 12:39 PM

i got it to work by removing 1 condition. I don't understand why.

this does not work:

$sql="select * from table where status='success' OR status='cancel' AND paytime BETWEEN '".$end."' AND '".$start."'";


but this works:

$sql="select * from table where status='success' AND paytime BETWEEN '".$end."' AND '".$start."'";


i actually use >= and <=

so whats wrong with the 1st $sql?

This post has been edited by g37752: 10 January 2013 - 12:41 PM

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2891
  • View blog
  • Posts: 9,599
  • Joined: 12-December 12

Re: mysql date range not working properly?

Posted 10 January 2013 - 12:46 PM

You need to be aware of the order of precedence in expressions.

You could add brackets to change the order within your SQL:

$sql="select * from table where (status='success' OR status='cancel') AND paytime BETWEEN '".$end."' AND '".$start."'";


So this means either 'success' or 'cancel', AND paytime between.. . Without the brackets it means 'success' OR ('cancel' and paytime between..).

This post has been edited by andrewsw: 10 January 2013 - 12:51 PM

Was This Post Helpful? 1
  • +
  • -

#10 g37752  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 195
  • Joined: 24-July 12

Re: mysql date range not working properly?

Posted 10 January 2013 - 12:57 PM

that was what i suspected, just didnt think it would work that way..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1