Mod Create new Table..

[John3971]

Hey im working on a plugin and i want this plugin to create a new table in the database and i don´t know how i should get it work i only get error so can somebody give the code that creates a new table that i can put in?

[Opserty]

Just Google "MySQL Create Table" you will get loads of tutorials/guides and the official documentation.

[Andrew Green]

$db->query_write("CREATE TABLE test (testid integer NOT NULL PRIMARY KEY auto_increment, testname text)");

[John3971]

Originally Posted by Andrew Green

$db->query_write("CREATE TABLE test (testid integer NOT NULL PRIMARY KEY auto_increment, testname text)");

that was the one i had but i keep getting this error when im importing the plugin:

Database error in vBulletin 3.7.0 Beta 3:

Invalid SQL:
CREATE TABLE vb_news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, date date, time time, poster varchar, text text);

MySQL 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 ' date date, time time, poster varchar, text text)' at line 1
Error Number  : 1064
Date          : Wednesday, January 16th 2008 @ 09:17:28 PM
Script        : http://test.fanrage.org/testvb/admin/plugin.php?do=productimport
Referrer      : http://test.fanrage.org/testvb/admin/plugin.php?do=productadd
IP Address    : 85.224.63.34
Username      : John
Classname     : vB_Database
MySQL Version : 5.0.18-nt-log

[Opserty]

date and time are MySQL keywords I think and also text. Make sure the column names aren't the same as the column types. Also enclose the column names in single quotes.

[John3971]

i tried this now

$db->query_write("CREATE TABLE " . TABLE_PREFIX . "news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, date varchar, time varchar, poster varchar, newst text)");

but it still doesn?t work i get this error:

Database error in vBulletin 3.7.0 Beta 3:

Invalid SQL:
CREATE TABLE vb_news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, date varchar, time varchar, poster varchar, newst text);

MySQL 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 ' date varchar, time varchar, poster varchar, newst text)' at line 1
Error Number  : 1064
Date          : Wednesday, January 16th 2008 @ 09:49:56 PM
Script        : http://test.fanrage.org/testvb/admin/plugin.php?do=productimport
Referrer      : http://test.fanrage.org/testvb/admin/plugin.php?do=productadd
IP Address    : 85.224.63.34
Username      : John
Classname     : vB_Database
MySQL Version : 5.0.18-nt-log

[Andrew Green]

You can't name a field "date" or "time", you need to rename those, ex:

$db->query_write("CREATE TABLE " . TABLE_PREFIX . "news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, newsdate varchar, newstime varchar, poster varchar, newst text)");

[John3971]

Originally Posted by Andrew Green

You can't name a field "date" or "time", you need to rename those, ex:

$db->query_write("CREATE TABLE " . TABLE_PREFIX . "news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, newsdate varchar, newstime varchar, poster varchar, newst text)");

aa then i understand :P

but i still get error when i use the one you wrote here.

Database error in vBulletin 3.7.0 Beta 3:

Invalid SQL:
CREATE TABLE vb_news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar, newsdate varchar, newstime varchar, poster varchar, newst text);

MySQL 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 ' newsdate varchar, newstime varchar, poster varchar, newst text)' at line 1
Error Number  : 1064
Date          : Wednesday, January 16th 2008 @ 10:04:39 PM
Script        : http://test.fanrage.org/testvb/admin/plugin.php?do=productimport
Referrer      : http://test.fanrage.org/testvb/admin/plugin.php?do=productadd
IP Address    : 85.224.63.34
Username      : John
Classname     : vB_Database
MySQL Version : 5.0.18-nt-log

[petteyg359]

Varchar type requires a length. Not optional.

CREATE TABLE vb_news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar(128), newsdate varchar(10), newstime varchar(10), poster varchar(32), newst text);

Assuming your date is no more than MM/DD/YYYY (fuzzy/verbose dates would need a longer string), and time can be at most HH:MM:SSPM unless you make that number bigger too. 10 on time should be enough for very fuzzy times, too (yesterday, and 1 hour ago - 9 hours ago both work, but anything minutes wouldn't).

[John3971]

Originally Posted by petteyg359

Varchar type requires a length. Not optional.

CREATE TABLE vb_news (id integer NOT NULL PRIMARY KEY auto_increment, title varchar(128), newsdate varchar(10), newstime varchar(10), poster varchar(32), newst text);

Assuming your date is no more than MM/DD/YYYY (fuzzy/verbose dates would need a longer string), and time can be at most HH:MM:SSPM unless you make that number bigger too. 10 on time should be enough for very fuzzy times, too (yesterday, and 1 hour ago - 9 hours ago both work, but anything minutes wouldn't).

now it worked thanks alot. i really suck on mysql

--------------- Added [DATE]1200518572[/DATE] at [TIME]1200518572[/TIME] ---------------

okey got another question related to this.
i want the date to be saved like MM/DD/YYYY but i want it to be shown as the option says in acp. like this: 1st January 2008