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]

$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]

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]

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 1200518572 at 1200518572 ---------------

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

[alexgeek]

If you want to use the MySQL date formatting this post (http://www.webforumz.com/php-forum/64029-solved-formatting-date.htm) may help.