insert timestamp problem

phence

I know there was a similar post probally answering this question, but i couldn't pull out an answer for my code.

this query will returns 0's in my mysql database. The datatype is set to timestamp.

what am i not doing?

$query1 = "INSERT INTO listings VALUES ('$id', '$trading', '$notice', '$for_', '$wac_1', '$wac_2', '$overdue_time', '$enter_time', '$lst_period','$pic_upload', '$user_id')";

the two vars '$enter_time' and '$overdue_time' are timestamps

miles_rich

it would be better if you actually named the fields you were insterting the data into like...


$query = "INSERT INTO aq_info (cust_name, cust_road, cust_city, cust_post, cust_telone, cust_teltwo, cust_fax, stamp) VALUES ('$custname', '$custroad', '$custcity', '$custpost', '$custtelone', '$custteltwo', '$custfax', '$timestamp')";

as to the other problem i am still stuck on it, the only thing i can think of is that the database server has a problem.

mrmufin

Could we have a quick peek at the code used to create the $enter_time and $overdue_time variables, please?

it would be better if you actually named the fields you were insterting the data into like...

This is not necessary if you're inserting all of the column data in the same order as the columns are listed in the table structure.

Though I generally do prefer including the columns within my INSERT statements; that way, if I alter the table structure I won't have to go back and muddle through the insert statements in an application.

The_Chancer

Just an idea, test whether the data is being passed to the database correctly. Change the column type to Varchar and try it again, if the correct value gets passed, then it is definately a database server problem.

$query="ALTER TABLE (tablename) MODIFY (columnname) VARCHAR(10)";

Hope it helps...

phence

Originally posted by mrmufin
Could we have a quick peek at the code used to create the $enter_time and $overdue_time variables, please?

// get overdue time and enter time
$overdue_time = time() + (86400 $lst_period);
$enter_time = $overdue_time - (86400 $lst_period);

And, yes i am entering the data into the table in the same order of each row.

Weedpacket

Remember that MySQL timestamps have a different format to Unix (and most other) timestamps.

Have you tried to echo $query to see if it really is happy? And does MySQL return any errors?

bretticus

Just a quick note...as weedpacket mentioned, UNIX timestamps and MySQL timestamps are NOT the same format.

Therefore if you are truely sending a UNIX timestamp to a MYSQL timestamp field, you will get FUBARed ;-)

You use PHP date function to convert to yyyymmdd format which TIMESTAMP data type seems to love ;-)

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).

Weedpacket

Originally posted by bretticus

You use PHP date function to convert to yyyymmdd format which TIMESTAMP data type seems to love ;-)

Nicer of course is to use a Unix timestamp (or just an integer) in the first place and avoid all the subsequent to-and-from-MySQL-timestamp conversion. But that's really something that ought to be done at the outset, as it could be quite a tedious alteration to make subsequently...

The only drawback then, of course is the difficulty in storing dates prior to 1970-01-01 or subsquent to 2038-01-18. And I have to admit, that potentially poses a much bigger problem than Y2K did...