Display all table contents. Or may be limited to last week

Databases
1

Hi,

I am designing an admin part of the web site.

I want to display all contents of a MySQL table. Or may be limit it to the last weeks data.

So I have two questions:

  1. What is the best way to store the date in a table. Is there an ‘auto date’ choice. i realise there is a ‘date’ choice in PHPMyAdmin - I would like to include the time also. I would like to store the time the person adds something to the basket. AND ALSO the time the customer buys. (So if they add stuff to basket Monday and pay on Friday this will show in the MySQL table).

  2. is there is quick way to display a table. I know I could do a MySQL query and echo out the fields. Is there a way in PHP code to write:

Display table customerorders

and how about limiting it to the last weeks, or days information:

Display table customerorders for last 7 days
Display table customerorders for last 1 days
Display table customerorders for last 2 days

Look forward to your reply,

Matt.

2

Use a DATETIME field instead of a DATE field.

3

whaaa?? :eek:

could you please explain this? and also why simple date interval arithmetic can lead to “inaccuracies”

4

great - now what about adding the time to the MySQL table too?!?

5

You’re on the right track, but this can be inaccurate. It’s better to use the DATE_SUB function to do the subtraction.


SELECT * 
FROM `table` 
WHERE `date` > DATE_SUB( CURRENT_DATE(), INTERVAL 7 DAY )

Intervals can also be expressed in terms of weeks or months. It’s all pretty powerful - here’s a link to the relevant MySQL manual pages.

Asking the mods to move this to the Database or MySQL forum. PHP does a lot of work there, but there are posters in those forums who are more expert in SQL who don’t frequent this forum as often.

6

OK - I’ll do that.

How can I add time too? To the closest second would be fine.

Matt.

7

when inserting a new record, if you dont specify a date, mySQL will automatically fill in the current date.

If you want to make double sure, tell it to insert CURRENT_DATE.

“INSERT INTO mytable(userid,date,object,quantity) VALUES (”.$userid.“,CURRENT_DATE,'”.$myobjectname.“',”.$number.“)”;

8

right - ok - but how do I add date and time to a table? I realise if you have text boxes for name, telephone you can add these to a table. But when there is no user-input how can I code it to add date and time to database? If you write something like

INSERT DATE value $DATE

then it will say invalid! Same goes for time.

Matt.

9

Well, first of all, there is a ‘datetime’ field type.

selecting all data from a table would be:
“SELECT * FROM table”;
selecting all data from the last week of the table would be
“SELECT * FROM table WHERE date > CURRENT_DATE - INTERVAL 7 DAY”