...

View Full Version : SQL Between two dates



RxDx
02-27-2010, 05:51 PM
I have two dates, current one, and the date of expiry(which is 14 days after current day).
I want all articles between current date and date of expiry to return to my page.
I have tried <= operator and BETWEEN, however nothing returns to my page.
below is the code :


$result = mysql_query ("SELECT * FROM db WHERE date BETWEEN '$curdate' AND '$expdate'", $db)

What seems to be wrong?

bdl
02-27-2010, 06:05 PM
What value do the variables $curdate and $expdate hold?

Do they actually have any value at this point in the script?

Does the SQL statement work in MySQL using valid date values?

Does the variable $db hold a valid MySQL connection resource?

Is there an error returned?

This should answer most of these questions:


$sql= "SELECT * FROM db WHERE date BETWEEN '{$curdate}' AND '{$expdate}'";
echo "<p>SQL statement: {$sql}</p>";
$result = mysql_query ($sql, $db) OR die(mysql_error());

RxDx
02-27-2010, 07:02 PM
$curdate = 27.02.2010
$expdate = 13.03.2010

Yes, in MySQL everything is fine. Gives me back everything between those 2 dates.
Holds valid, for sure.

No error, just no results.

The code you provided gives Resource id #7

bdl
02-27-2010, 08:12 PM
Those aren't valid date values. A valid value would be '2010-02-27'.

What datatype did you use for your date fields?

MySQL Reference Manual (http://dev.mysql.com/doc/refman/5.1/en/) : Date and Time types (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html)


The code you provided gives Resource id #7

Where is this shown?

RxDx
02-27-2010, 08:19 PM
I used varchar, because I need data in this format only.

Resourse id 7 is shown after I echo the result of this :

$sql= "SELECT * FROM db WHERE date BETWEEN '{$curdate}' AND '{$expdate}'";
echo "<p>SQL statement: {$sql}</p>";
$result = mysql_query ($sql, $db) OR die(mysql_error());

bdl
02-27-2010, 08:27 PM
Nothing in what I provided should be giving you "Resource id #7". What is below that? Are you attempting to echo $result?

RxDx
02-27-2010, 08:36 PM
Exactly. I echoed the result and it showed me Resource id 7.

PS. Strange thing, now I can't get SQL query in Phpmyadmin to work with dates, however it worked just 1 hour ago...

bdl
02-27-2010, 09:23 PM
Exactly. I echoed the result and it showed me Resource id 7.

Right. What that's telling you is that the variable $result is a reference to a valid MySQL result resource, that you can further use within the scope of the current MySQL connection to reference back to that specific resultset, and retrieve data from it. If you echo'ed the value of $db, it should also show you a resource number, one that references the currently open connection.

Please read the PHP manual entry for mysql_query() (http://us2.php.net/mysql_query), the entries for mysql_fetch_assoc() (http://us2.php.net/mysql_fetch_assoc), mysql_result() (http://us2.php.net/mysql_result), and while you're at it, have a look at the Language Reference (http://us2.php.net/langref) section on Resource Types (http://us2.php.net/manual/en/language.types.resource.php).

Inigoesdr
02-28-2010, 09:29 PM
RxDx, if you insist on keeping your VARCHAR date field, you can do something like this:


// example values:
// $curdate = '27.02.2010';
// $expdate = '13.03.2010';

$curdate_valid = implode('-', array_reverse(explode('.', $curdate)));
$expdate_valid = implode('-', array_reverse(explode('.', $expdate)));
$result = mysql_query ("SELECT * FROM db WHERE STR_TO_DATE(`date`, '%d.%m.%Y') BETWEEN '{$curdate_valid}' AND '{$expdate_valid}'") or die(mysql_error());



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum