PHP script to create table

Question

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

<?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);

?>

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 — Lennart, 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? — Lennart, Feb 22 '12 at 16:05
one which will hold original date of creation of the record and one to change every time when updated. — Jakub Zak, Feb 22 '12 at 16:07
I've never used that, i allways did that manually, when i added a new record, so I can't really help you any further, becuase that is new to me, my advice just do it manually when you add a new record (so add a DATE field instead), i can't find `updated TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP,` on google so I don't even know if that exists — Lennart, Feb 22 '12 at 16:12

score 4 · Accepted Answer · answered Feb 22 '12 at 15:56

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.

score 0 · Answer 2 · answered Feb 22 '12 at 16:20

0

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 )
);

answered Feb 22 '12 at 16:20

Ed Meacham

543
1
5
19

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? – Ed Meacham 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? – Ed Meacham Feb 22 '12 at 17:14
And perhaps this has something to do with it: http://stackoverflow.com/a/4897134/682232 – Ed Meacham Feb 22 '12 at 18:34

PHP script to create table

2 Answers2