List:General Discussion« Previous MessageNext Message »
From:Antonio PHP Date:April 29 2009 6:29pm
Subject:Re: Now() : SQL syntax error. But why?
View as plain text  
Thanks. NOW() and php date(); work for my newly created test tables, but it
doesn't work for my working table. I can't insert date, time or now()
into my old table (which is as below).

For now, I'm using MySQL auto timestamp ('Updated' field), but I need to
insert date when the data was created!

Why is this? Please help me. (I'm using the newest versions of PHP and
MySQL)

This is MySQL data structure. - I underlined where it causes the error
message. (datetime)
`id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
`Revenue` mediumint(6) NOT NULL,
`Company_Size` mediumint(6) NOT NULL,
`Ownership` tinyint(1) NOT NULL,
`Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT
NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
*`Created` datetime NOT NULL,
*PRIMARY KEY (`id_Company`),
KEY `Ownership` (`Ownership`)
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8
FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE
CASCADE ON UPDATE CASCADE;

Here is php script -----------------------------
$sql = "INSERT INTO company SET
Name='$Name',
Revenue='$Revenue',
Company_Size='$Company_Size',
Ownership='$Ownership',
Homepage='$Homepage',
Job_Source='$Job_Source'
*Created=NOW() // if I remove this line it works fine.
*";
mysql_query ($sql) or die (mysql_error());


Also, this doesn't work for this table.
$Datetime = date( 'Y-m-d H:i:s');

INSERT INTO ....
Created='$Datetime'...



On Wed, Apr 29, 2009 at 9:28 AM, Scott Haneda <talklists@stripped> wrote:

> We need to see your entire query and the table structure.  timestamp fields
> can have options set to auto update them, where order matters, and only one
> field can support that feature.
>
> Please supply more data.
>
>
> On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote:
>
> You have an error in your SQL syntax; check the manual that corresponds to
>> your MySQL server version for the right syntax to use near 'Created =
>> NOW(),
>> Updated = NOW()' at line 8
>>
>> 'Created' and 'Updated' are set to datetime (InnoDB).
>>
>> The same syntax works for some newly created tables... and gives no error.
>>
>> It's very strange. 'Now()' works for some tables, and it doesn't for some.
>> (All set in phpmyadmin...)
>>
>> What could have caused this? Any similar experience?
>>
>
> --
> Scott * If you contact me off list replace talklists@ with scott@ *
>
>

Thread
Now() : SQL syntax error. But why?Antonio PHP28 Apr
  • Re: Now() : SQL syntax error. But why?Martijn Engler28 Apr
  • Re: Now() : SQL syntax error. But why?Scott Haneda29 Apr
    • Re: Now() : SQL syntax error. But why?Antonio PHP29 Apr
      • Re: Now() : SQL syntax error. But why?Scott Haneda30 Apr
        • Re: Now() : SQL syntax error. But why?Antonio PHP1 May
          • Re: Now() : SQL syntax error. But why?Scott Haneda1 May