up vote 0 down vote favorite

ok I need to create MySQL table. So i'm running this script but it's just not working. Any idea why? Thank you.

<?php
$con = mysql_connect("database.dcs.aber.ac.uk","xxx","nnnnnnnnnn");
mysql_select_db("jaz",$con);
$sql = "CREATE TABLE storys
(
id int NOT NULL AUTO_INCREMET,
title TINYTEXT,
type TINYTEXT,
link TEXT,
preview TINYTEXT,
tags TINYTEXT,
text MEDIUMTEXT,
updated TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP,
created DATETIME() DEFAULT NULL,
PRIMARY KEY(id)
)";

mysql_query($sql,$con);

mysql_close($con);

?>
share|improve this question
does the user have write access to the table in question – encodes Feb 22 '12 at 15:56
and are the connectionparameters correct? – devOp Feb 22 '12 at 15:57
add the line echo mysql_error(); below the line mysql_query($sql, $con); and then tel me if it outputs anything, this line usually tells you what error is occuring and most likely the reason for your problem – Lenny Feb 22 '12 at 15:57
this is error: 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 ') ON UPDATE CURRENT_TIMESTAMP, created DATETIME() DEFAULT NULL, – Jakub Zak Feb 22 '12 at 16:00
What are these lines: updated TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP, created DATETIME() DEFAULT NULL, ment to do? are you wanting a date field? – Lenny Feb 22 '12 at 16:05
show 2 more comments

2 Answers

up vote 3 down vote accepted

Your code has absolute NO error handling, which would have shown you the reason the query's failing.

$con = mysql_connect(...) or die(mysql_error());

$res = mysql_query(...) or die(mysql_error());

is the bare minimum error handling you should have on pretty much every mysql call.

Had this been in place, you'd have to been told:

id int NOT NULL AUTO_INCREMET,
                            ^---missing 'n', not a valid SQL keyword.
share|improve this answer
up vote 0 down vote

Among the previously listed issues, you are using functions in the data type definitions, and the "ON UPDATE" syntax is wrong.

Here is what I think you are looking for in the SQL:

CREATE TABLE IF NOT EXISTS  `storys` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `title` TINYTEXT,
 `type` TINYTEXT,
 `link` TEXT,
 `preview` TINYTEXT,
 `tags` TINYTEXT,
 `text` MEDIUMTEXT,
 `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
 `created` DATETIME DEFAULT NULL ,
PRIMARY KEY ( id )
);
share|improve this answer
thx but no, still same error. – Jakub Zak Feb 22 '12 at 16:25
What version of MySQL are you using, and can you give the error message again, so we can be sure it is exactly the same? – emeacham Feb 22 '12 at 16:37
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 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , created DATE – Jakub Zak Feb 22 '12 at 16:56
Can you provide the "new" SQL you're running? – emeacham Feb 22 '12 at 17:14
And perhaps this has something to do with it: stackoverflow.com/a/4897134/682232 – emeacham Feb 22 '12 at 18:34

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.