dynamic time/date variable to sort records by

[furry]

I posted a question on this some months ago but didn't get a good answer.

I have a very basic personal weblog on my website using a PHP script and
MySql db to post store and retrieve my blog entries. Currently I'm using
date("d/m/y") to populate a date text field (updated) and I also have an
automatic TIMESTAMP field (time). When retrieving the data, I sort by time
desc and all is dandy.

One problem. Whenever I need to edit the data in an already created blog
record, this automatically changes the time field (presumably because it's
of type TIMESTAMP). This screws up the sorting of my blogs.

Can anyone suggest a way whereby I can store today's date in a format that
is sortable in descending order, allowing me to display records as I wish
and also allowing me to edit records without messing up the sort order ?

Thanks in advance !

[Alvaro G Vicario]

*** furry wrote/escribió (Wed, 16 Jun 2004 14:12:00 +0100):

> One problem. Whenever I need to edit the data in an already created blog
> record, this automatically changes the time field (presumably because it's
> of type TIMESTAMP). This screws up the sorting of my blogs.

Exactly, that's the behaviour of TIMESTAMP fields. Anyway, please note that
if you have more than one fields of TIMESTAMP type only the first one gets
updated.

> Can anyone suggest a way whereby I can store today's date in a format that
> is sortable in descending order, allowing me to display records as I wish
> and also allowing me to edit records without messing up the sort order ?

You have DATE and DATETIME.

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

[furry]

I figured this out myself.

All I needed was to get a timestamp formatted date and post this to a text
based field in my table.

Here's the code to generate a timestamp using PHP:

$timestamp = mktime
(date('H'),date('i'),date('s'),date('n'),date('j') ,date('Y'));


Works a treat


"Alvaro G Vicario" <alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote in
message news:wr3bghpq5w6d.1to31z04aro43.dlg@40tude.net...
*** furry wrote/escribió (Wed, 16 Jun 2004 14:12:00 +0100):

> One problem. Whenever I need to edit the data in an already created blog
> record, this automatically changes the time field (presumably because it's
> of type TIMESTAMP). This screws up the sorting of my blogs.

Exactly, that's the behaviour of TIMESTAMP fields. Anyway, please note that
if you have more than one fields of TIMESTAMP type only the first one gets
updated.

> Can anyone suggest a way whereby I can store today's date in a format that
> is sortable in descending order, allowing me to display records as I wish
> and also allowing me to edit records without messing up the sort order ?

You have DATE and DATETIME.

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

[Alvaro G Vicario]

*** furry wrote/escribió (Wed, 16 Jun 2004 15:36:23 +0100):

> Here's the code to generate a timestamp using PHP:
>
> $timestamp = mktime
> (date('H'),date('i'),date('s'),date('n'),date('j') ,date('Y'));

I normally leave leave the work to MySQL:

.... date_field=NOW()

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

[eclipsboi]

On Wed, 16 Jun 2004 16:10:02 +0200, Alvaro G Vicario
<alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote:

>*** furry wrote/escribió (Wed, 16 Jun 2004 14:12:00 +0100):

>> One problem. Whenever I need to edit the data in an already created blog
>> record, this automatically changes the time field (presumably because it's
>> of type TIMESTAMP). This screws up the sorting of my blogs.

>
>Exactly, that's the behaviour of TIMESTAMP fields. Anyway, please note that
>if you have more than one fields of TIMESTAMP type only the first one gets
>updated.

From MySQL.com
([url]http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html):[/url]
.. . .
You can set any TIMESTAMP column to a value different from the current
date and time by setting it explicitly to the desired value. This is
true even for the first TIMESTAMP column. You can use this property
if, for example, you want a TIMESTAMP to be set to the current date
and time when you create a row, but not to be changed whenever the row
is updated later:

Let MySQL set the column when the row is created. This initializes it
to the current date and time.
When you perform subsequent updates to other columns in the row, set
the TIMESTAMP column explicitly to its current value:

UPDATE tbl_name
SET timestamp_col = timestamp_col,
other_col1 = new_value1,
other_col2 = new_value2, ...

.. . . .

Basically, instead of altering the database, it's saying to reuse the
old timestamp.