Simple Date Command

nick2005

Hi,

I'm relatively new to MYSQL code but I'm beggining to get familar with it.

I want to create a basic updates page. But I'm not sure how I can get the database to recognise dates.

Basically I want to display, say the 10 most recently updated pages on my site. I have a field on everyone of the programmes called 'date' and the date is produced in dd/mm/yyyy format.

How can I create a function that displays the ten most recently updated programmes and what has been updated?

If your not sure what I'm on about - have a look at the programme pages here:
http://www.uktvindex.net/progs/show.php?prog_id=184
Notice the 'last updated' variable.

Any help is appreciated.

has27

Hello -

A good start would be to use a timestamp rather than dd/mm/yyyy which isn't really very specific - say 40 pages were updated today, then they'd all have 28/02/2005 and there'd be no way to tell the most recently updated.

You can get a timestamp using time().

Now, when you're using this, you can do a nice simple query to pull them out using the particularly useful LIMIT. If your table is called mytable and the column with the timestamp is called mytimestamp:

select * from TABLE ORDER BY timestamp LIMIT 10

You might need to use

select * from mytable ORDER BY mytimestamp desc LIMIT 10

to get the most recent (I can never remember which way round they're pulled out!).

nick2005

Hi,

Thanks for your help. Seems the coding is much easier than I expected.

You talk about including a timestamp. Currently I do have a script which will include the date and the time will this do?

The actual coding seems relatively simple.

has27

Hello -

Timestamp is

time()

in php.

You can either include this in your form by having

(so a hidden field whose value is populated by php)

or (a better way, I think) when you're inserting your rows into your database with your INSERT command, use something like

$mytimestamp=time();
mssql_query("INSERT INTO mytable(name, timestamp) VALUES('$_POST[name]', '$mytimestamp');");

Hope this helps.

nick2005

Hi,

Because I'm not the only one who edits the database it is probably best to display the date on the form I use to update the site. The form that displays all the data for the relevant programme.

But when I've included <?php echo(time()); ?> as the value of the box and it is not hidden all I get displayed is: 1109682976

Is my server incompatible?

has27

Nope, that's what you'd expect. See http://uk2.php.net/manual/en/function.time.php
for more info. It's the number of seconds since unix epoch (a fixed date sometime in the past).

If you look at it now you'll see the right hand few digits have gone up - this is because more seconds have passed. So you can select on this field and order it like a number to get the most recent posts.

nick2005

Ah I see, I'm with you now.

Thanks alot for your help. I'll put a link to the resulting updates page on here. To show you how I got on.