timestamp

boombanguk

Hi,

I'm confused about all this timestamp business. I'm using Flash->PHP->Mysql, and I want to save the time/day/month/year along with other details as an entry in my database, how would I go about doing that?

thanks for any advice.
boombanguk

bradgrafelman

If you just want the current datetime inserted, you could define a column as a TIMESTAMP type. From the MySQL manual on the Overview of Date and Time Types:

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself.

NogDog

Alternatively, if for some reason you do not want that sort of automated functionality, you can define the database column as a DATETIME type, then in your SQL assign its value as "NOW()".

boombanguk

bradgrafelman wrote:
If you just want the current datetime inserted, you could define a column as a TIMESTAMP type. From the MySQL manual on the Overview of Date and Time Types:

I've created a TIMESTAMP type, but when i've used that before, it never put the time into that variable, so I don't understand how to get that to work?

NogDog

If, during an INSERT or UPDATE you either set the first timestamp-type field's value to NULL or do not assign it at all, it should automatically be set to the current date and time, unless you have specifically defined it not to:

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

With a constant DEFAULT value, the column has the given default. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated, otherwise not.

In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to get auto-update without also having the column auto-initialized.)

Above from: http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html

Note that it only applies to the first timestamp-type field in the table. All others will need to be explicitly set by your queries. Also, there is nothing stopping you from explicitly setting the first one if you so desire.

NogDog

PS: If by any chance your MySQL server is running in MAXDB mode, the timestamp stuff will not apply, and any TIMESTAMP type column will be treated just like a DATETIME column.

boombanguk

ok thanks.

and how would I do a search for the most recent entries in the database?

bradgrafelman

Add an "ORDER BY date_column DESC" onto the end of your query.

If you just want the latest 5 entries, also add a "LIMIT 5" after that.

boombanguk

ok got that working, thanks.

but how would I get it so that it sends back to flash the data in pages? so for example lets say there are 5 entries per my page, but I want it to display the 2nd lot of 5 entries (5-10) which are still all ordered according to DESC date?

bradgrafelman

Search the board for "pagination" or read the MySQL manual for the SELECT syntax and you'll note that the "LIMIT" can include an offset, so "LIMIT 5, 5" would show entries 5-10.

boombanguk

this is the code im using, but its only giving me the first result, not the rest

$startRow = 1;
$endRow = 5;
$type = trim($entryType);
$query = mysql_query("SELECT * FROM $table WHERE type = '$type' ORDER BY date DESC LIMIT $startRow,$endRow");
$query2 = mysql_num_rows($query);
if($query2<=0)
{
return "&error=" . mysql_error();
} else {
while ($row = mysql_fetch_array($query)){
echo "&Field1=".$row[0];
echo "&Field2=".$row[1];
echo "&Field3=".$row[2];
echo "&Field4=".$row[3];
echo "&Field5=".$row[4];
echo "&Field6=".$row[5];
echo "&Field7=".$row[6];
echo "&Field8=".$row[7];
echo "&Field9=".$row[8];
echo "&Field10=".$row[9];
}