Help on select date based on yesterday

mysql date

[chrissim]

hi,

The coding below able to pull data based on today date but what if i want another option to pull my data based on 2 or 3 days ago result

$time = date( 'd-m-Y' ); // Get the current date, in the format of: 12-12-2006
$timestamp = time();

SELECT * FROM uniquehits where date = '$time'

[Matthew1980]

Hi there Chrissim,

Check this function out: [uk2.php.net ].

Place it into date("d-m-Y", strtotime("-1 day")); and this should provide the offset you are asking for ;-p

Have a play with that :)

Oh, and make sure that when you retrieve the date from the table that the format matches that as you are asking for

Cheers,
MRb

[Readie]

where date = '$time'

Just a note: "date" is a data type, you should rename this column to something like d_date.

[chrissim]

Thx guys...i got the script to work now:)

[Matthew1980]

Hi there chrissim,

Cool! Though, Readie does have a valid point there, whenever you assign names to vars or field names in tables, always spare a thought for the reserved words, it might be an idea to check php.net of for a good listing: [uk2.php.net ]

Happy coding!

Cheers,
MRb

[rocknbil]

I know this is a PHP forum, so you're familiar with php dates . . . but if you're using mySQL, you will find it much more powerful to use inherent mySQL date and time functions.

// today
SELECT * FROM uniquehits where `date` = curdate()

// yesterday

SELECT * FROM uniquehits where `date` = date_sub(curdate(), interval 1 day)

// last 7 days

SELECT * FROM uniquehits where `date` >= date_sub(curdate(), interval 7 day) and `date` <= curdate()

// format it mm/dd/yyyy

SELECT date_format('%m/%d/%Y',`date`), * FROM uniquehits where `date` = curdate()

--> formatted date will be first index in returned row

5.0 date and time functions [dev.mysql.com] (mostly the same in all versions . . . )