PHP won't create a table?

Question

currently I'm making a forum software, and I'm writing the install script, that sets up the database and tables within it. It makes the database and tables fine. It's supposed to make 4 tables: forums, posts, topics, and user_info.

It successfully makes the databse, forums, posts, and topics, but it doesn't create the user_info table, and I can't figure out why.

Here's my code:

$createdb = "CREATE DATABASE forumstest";
$run_createdb = mysql_query("$createdb");

//Create tables
mysql_select_db("forumstest");

$createforums = "CREATE TABLE forums (id int NOT NULL, description text, PRIMARY KEY (id))";
mysql_query("$createforums");

$createposts = "CREATE TABLE posts (id int NOT NULL, created_by varchar(200), date_created date, the_post text, topic_id text, PRIMARY KEY (id))";
mysql_query("$createposts");

$createtopics = "CREATE TABLE topics (id int NOT NULL, title varchar(200), created_by varchar(200), date_created date, forum_id text, PRIMARY KEY (id))";
mysql_query("$createtopics");

$createtopics = "CREATE TABLE user_info (id int NOT NULL, username varchar(25), password varchar(100), name varchar(65), email varchar(65), date date, group text, PRIMARY KEY (id))";
mysql_query("$createtopics");

$dbcreated = 1;
echo "Database created sucessfully!";

Maybe if you added some error checking in your code you might have a better idea why it went wrong. — symcbean, Nov 24 '11 at 15:38

score 6 · Accepted Answer · answered Nov 24 '11 at 15:31

6

date is, just like group, a reserved keyword, which you would have seen if you did some error checking.

answered Nov 24 '11 at 15:31

CodeCaster

147,647
23
218
272

So how do I fix it? Because I still want the date in there. Should I just change the date part of the table to like date_joined? – Darren Nov 24 '11 at 15:32
2

[List of reserved words](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) – Flukey Nov 24 '11 at 15:33
@darren - change the column name to something else – Flukey Nov 24 '11 at 15:33
2

you could try putting date in backticks `date` , but even if that works you'd have to constantly reference this column like that. it's just not a good idea to use reserved words. – Julien Nov 24 '11 at 15:34
First Google hit on "mysql reserved keyword": [Reserved Words](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html): _"Reserved words are permitted as identifiers if you quote them as described in [Section 8.2, “Schema Object Names”](http://dev.mysql.com/doc/refman/5.5/en/identifiers.html)"_. – CodeCaster Nov 24 '11 at 15:34
remove your field alias "date" and rename your field "date' to something like "post_date" – Jhourlad Estrella Nov 24 '11 at 15:34
Thanks guys, I fixed it. I just changed the date and group columns. – Darren Nov 24 '11 at 15:35
also why are your foreign keys marked as a 'text' column type? – Flukey Nov 24 '11 at 15:36
`date` is ***not*** a reserved keyword. – Amal Murali May 05 '14 at 05:12

score 2 · Answer 2 · answered Nov 24 '11 at 15:33

2

date and group are the mysql reserved word. you must keep them with in backticks or not to use the reserved words as column name at all.

`date` date, `group` text

you should use mysql_error() with the mysql_query() to see what error message mysql through.

mysql_query("$createtopics") or trigger_error(mysql_error());

answered Nov 24 '11 at 15:33

Shakti Singh

84,385
21
134
153

still wouldn't recommend naming a column which is a reserved keyword... – Flukey Nov 24 '11 at 15:34

score 0 · Answer 3 · answered Nov 24 '11 at 17:17

0

You are using the same variable to create topics and user_info tables

answered Nov 24 '11 at 17:17

Mik3r

1

score 0 · Answer 4 · answered Nov 25 '11 at 05:03

You should use this two lines for creating user_info table:

$create_user_info = "CREATE TABLE user_info (id int NOT NULL, username varchar(25), password varchar(100), name varchar(65), email varchar(65), 'date_created' date, 'group_user' text, PRIMARY KEY (id))";
    mysql_query("$create_user_info");

//because date and group are reserved keywords

PHP won't create a table?

4 Answers4