Re: Now() : SQL syntax error. But why?
Thanks, Scott. I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! Thanks, again. Have a great day. :) On Thu, Apr 30, 2009 at 12:52 PM, Scott Haneda talkli...@newgeo.com wrote: On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: 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; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. 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()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
Always echo out your SQL string, it will make it a lot more obvious. You want to see the result. I php concatenated string can be confusing at times. Also, you are not escaping your data, so if you had a word of 'stops, here' that would break it as well. So in your case, you very well may break it by changing the data you put in. You could also do something like stuffing drop database foo; into your data, and be in for real fun. Pass every string to http://us2.php.net/mysql_real_escape_string On Apr 30, 2009, at 9:27 PM, Antonio PHP wrote: I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
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 talkli...@newgeo.com 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@ *
Re: Now() : SQL syntax error. But why?
On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: 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; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. 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()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Now() : SQL syntax error. But why?
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? Please help~.
Re: Now() : SQL syntax error. But why?
Can you please give the full table structure and query? On Tue, Apr 28, 2009 at 23:18, Antonio PHP php.anto...@gmail.com 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? Please help~. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
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@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
