On Thursday 15 November 2001 01:38, Olav Drageset wrote:
> Professionals!
> I cannot get timestamp to contain a time value different from zero.
> According to another mailing this shall happen when something is done
> wrong I am completely new to databases. I am initiating the database
> tables from a php page.
> Can anyone tel me what I am doing wrong??
> Thank you.
>
> Vreating table is like this:
> $sql = "CREATE TABLE domains (";
> //En record for hvert navnedomene
> $sql .= "dNbr INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,";
> $sql .= "dName CHAR(25) NOT NULL,";
> $sql .= "payPrYr SMALLINT DEFAULT 0 NOT NULL,";
> $sql .= "nextPayDay DATE NULL,";
> $sql .= "members SMALLINT DEFAULT 0 NOT NULL, ";
> $sql .= "noPayMembers SMALLINT DEFAULT 0 NOT NULL,";
> $sql .= "timeChanged TIMESTAMP (14),";
> $sql .= "timeCreated TIMESTAMP (14),";
> $sql .= "INDEX domain(dName))";
The above can be re-written as:
//En record for hvert navnedomene
$sql = "CREATE TABLE domains (
dNbr INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
dName CHAR(25) NOT NULL,
payPrYr SMALLINT DEFAULT 0 NOT NULL,
nextPayDay DATE NULL,
members SMALLINT DEFAULT 0 NOT NULL,
noPayMembers SMALLINT DEFAULT 0 NOT NULL,
timeChanged TIMESTAMP (14),
timeCreated TIMESTAMP (14),
INDEX domain(dName))";
Which IMHO is clearer.
> if (!$sql_result = mysql_query($sql,$connection) )
> {echo "<P>query1: "; echo mysql_error();}
> else {echo"<P>query1: domains ok";
>
>
> this is one variant of writing to table:
>
> $tid = "NOW(YY-MM-DD HH:MM:SS)";
> $sql = "INSERT INTO domains ( dName, payPrYr, nextPayDay,timeChanged,
> timeCreated ) ";
> $sql .= "VALUES ('$domainName', '$payPerYear', '$nxtPayDay', '$tid',
> '$tid' );";
> if (!$sql_result = mysql_query($sql,$connection) )
> {echo "<P>query1: "; echo mysql_error();}
> else {
Note you probably DON'T want timeCreated to be a TIMESTAMP field as
everytime to update the record and do not specify a value for it, it's
value will be changed to the time at which the record was updated. You
should probably be using a DATETIME field for this.
If you're using a timestamp field then there is no need to specify a
value for it when using INSERT, UPDATE or REPLACE.
Thus:
$sql = "INSERT INTO
domains(
dName,
payPrYr,
nextPayDay,
timeCreated)
VALUES(
'$domainName',
'$payPerYear',
'$nxtPayDay',
'$tid')";
Note you probably DON'T want timeCreated to be a TIMESTAMP field as
everytime to update the record and do not specify a value for it, it's
value will be changed to the time at which the record was updated. You
should probably be using a DATETIME field for this.
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
We'll cross that bridge when we come back to it later.
*/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
