Why won't this query work?

[KW802]

OK, I'm banging my head on this one....

PHP Code:

$new_users = $DB_site->query(" 
    SELECT username, email, languageid, userid, joindate
    FROM " . TABLE_PREFIX . "user 
    WHERE '" . date('mdY', joindate) . "' = '10062004' AND 
    usergroupid IN ($ids) 
"); 


When running the above I get no hits even though I know for a fact that there are records in the user table with a join date of 10/06/2004. When I change to be not equal (!=) I get all users in the table, even those that registered on 10/06/2004!

Where'd I screw up? :ermm:

[Velocd]

date() function returns a human readable representation of a timestamp.

So comparing its return value to a timestamp (joindate) is erroneous.

The query you are probably looking for:

PHP Code:

$new_users = $DB_site->query("
    SELECT username, email, languageid, userid, joindate
    FROM " . TABLE_PREFIX . "user
    WHERE joindate >= ".strtotime('2004-10-06')." 
    AND joindate < ".strtotime('2004-10-07')."
    AND usergroupid IN ($ids)
"); 


[KW802]

Originally Posted by Velocd

date() function returns a human readable representation of a timestamp.

So comparing its return value to a timestamp (joindate) is erroneous.

OOOOoohhhhhhh...... I get it! Even though I'm seeing the result of the date function returning the value I want the WHERE clause is still comparing the real value of joindate versus my criteria. OK, that's a little different from some environments I'm used to.

OK, off to some quick trial & error.....

Thanks!

Kevin

[KW802]

Excellent! Works exactly like I wanted. It's a deviation of the code that ChurchMedia posted for his Birthday Thread hack. This will make life just a little easier in welcoming new visitors to the site.

Thanks again,
Kevin