NOW()

[kpdvx]

I have had NOW() working before when insertinng a timestamp, however, I cannot get it to work now. Here is the SQL script:
$query = "INSERT INTO art SET
userid = '$uid',
type = '$newtype',
filename = '$newfilename',
thumb = '$newthumb',
title = '$newtitle',
media = '$newmedia',
time = '$newtime',
concept = '$newconcept',
postdate = NOW()";

$result = mysql_query($query);

It fails on insert. Any idea why?

 

[sleipnir214]

If you output $query to the screen, does the SQL look correct? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

And remember timestamps usually need to be surrounded by #'s when you look at that SQL.

-Rob

 

[sleipnir214]

skiflyer:

Say again?


kpdvx:

What database server are you using? MySQL? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

Several databases that I've had the displeasure of toying with, have made me bang my head because the format for a timestamp is

#mm/dd/yyyy#

instead of

'mm/dd/yyyy'

Access would be the one coming to the front of my mind at the moment, and very well could be the only one... not certain.

-Rob

 

[skiflyer]

During the insert query specifically I mean... ugh, I hate when I'm unclear.

If you ran a
SELECT [timestamp_field] FROM tablename
you'd get the nice normal second count but if you tried

INSERT INTO tablename SET [timestamp]='12/21/2002';

it'd be a big fat error, because it wanted

INSERT INTO tablename SET [timestamp]=#12/21/2002";

Hope that made more sense.

-Rob

 

[sleipnir214]

skiflyer:

The use of "NOW()" in kpdvx's example is likely a string which invokes MySQL's builtin function now().

And in MySQL,

INSERT INTO tablename SET columnname = now()

is a legal query. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[kpdvx]

I echoed the query, and it outputs as timestamp = NOW() or timestamp = 'NOW()', or timestamp = #NOW(), or timestamp = '#NOW()' depending on what I set it to be in the query.

I had this working before when I had the query like this: INSERT INTO art (timestamp) VALUES (now());, or something like that, but using SET is much cleaner, and I would like to keep it that way if possible.

 

[sleipnir214]

Again, what database are you using? MySQL?

If so, what does mysql_error() report when the insert fails? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[kpdvx]

MySQL, but I solved the problem. Turns out NOW() does indded work, however in my table, "userid" was set to be unique, and since user "test" had already insert items prior to NOW() being implemented, it failed because of a duplicate entry. Thanks for you help though!

 

[sleipnir214]

kpdvx:

It seems apparent to me that you are not using enough error trapping in your code. Had you written your script to output the return of mysql_error() when a problem occurred, you would have had a valuable clue to diagnosing the problem.

I have a FAQ in this forum (faq434-2999) which has an example of a way to provide good error-trapping in MySQL calls without adding a lot of code complexity. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!