select * from table where date('2007-04-18') between field1

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m_ramanaji
Forum Newbie
Posts: 1
Joined: Mon Apr 23, 2007 1:23 am

select * from table where date('2007-04-18') between field1

Post by m_ramanaji »

I wanted php script for the following MySQL code:

mysql>select price from table where date("2007-04-18') between field1 and filed2;

If I give 2007-04-18 and submit, it should show results of that.

id price field1 field2

1 1200 2007-04-01 2007-04-11
2 1500 2007-04-12 2007-04-30

It should results of 1500


I required necessary script for above.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Just so I'm clear - you want to return all entries in which field1 is before the passed date & field2 is after the passed date?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

Something like...

Code: Select all

<?php
$date = '2007-04-18';

$query = "SELECT price FROM table WHERE field_1 <= '" . mysql_real_escape_string ( $date ) . "' AND field_2 >= '" . mysql_real_escape_string ( $date ) . "';";

?>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

printf gave you the correct syntax. The issue is that the BETWEEN operator applies to a particular field (ONE field, that is) and tests whether that field's value is between two values. It doesn't look at TWO fields.
Post Reply