https://www.icyphoenix.com/viewtopic.php?f=4&t=6346&p=43294#p43294
-----------------------------------
Chaotic
Sat 22 Aug, 2009 00:15

DB - How To Add Tables To Database
-----------------------------------
[size=14][b][DOC] How To Add Tables To Database[/b][/size]

So you've found a new modification that you'd like to use on your Icy Phoenix website?  Many modifications you'll find here and on other websites will have special instructions letting you know you'll be adding new or altering some tables to your database.  For those type of modifications to work, you'll need to update your database.

Assuming you know how to access your Icy Phoenix database, look to see what your tables are prefixed with.  If you installed Icy Phoenix using the default prefix, your database tables will have "[b]ip_[/b]" in front of them.  Many users are used to prefixing their tables with the "[b]phpbb_[/b]" prefix.  In fact, it doesn't matter what you use as your prefix when installing a fresh copy of Icy Phoenix.  What matters is you are consistent and remember to name all future prefixes the way you started.

When viewing the install instructions inside a modification folder, you may see something similar to this:

[spoiler][code linenumbers=false]
	CREATE TABLE `ip_medal` (
	`medal_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`cat_id` mediumint(8) UNSIGNED NOT NULL default '1',
	`medal_name` varchar(40) NOT NULL,
	`medal_description` varchar(255) NOT NULL,
	`medal_image` varchar(40) NULL,
	PRIMARY KEY (`medal_id`)
	);

	CREATE TABLE `ip_medal_user` (
	`issue_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`medal_id` mediumint(8) UNSIGNED NOT NULL,
	`user_id` mediumint(8) UNSIGNED NOT NULL,
	`issue_reason` varchar(255) NOT NULL,
	`issue_time` int(11) NOT NULL,
	PRIMARY KEY (`issue_id`)
	);

	CREATE TABLE `ip_medal_mod` (
	`mod_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`medal_id` mediumint(8) UNSIGNED NOT NULL,
	`user_id` mediumint(8) UNSIGNED NOT NULL,
	PRIMARY KEY (`mod_id`)
	);

	CREATE TABLE `ip_medal_cat` (
	`cat_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`cat_title` varchar(100) NOT NULL,
	`cat_order` mediumint(8) UNSIGNED NOT NULL default '0',
	PRIMARY KEY (`cat_id`),
	KEY `cat_order` (`cat_order`)
	);

	INSERT INTO `ip_config` VALUES ('allow_medal_display', '0');
	INSERT INTO `ip_config` VALUES ('medal_display_row', '1');
	INSERT INTO `ip_config` VALUES ('medal_display_col', '3');
	INSERT INTO `ip_config` VALUES ('medal_display_width', '');
	INSERT INTO `ip_config` VALUES ('medal_display_height', '');
	INSERT INTO `ip_config` VALUES ('medal_display_order', '');
	INSERT INTO `ip_medal_cat` VALUES ('1', 'Default', '10');
 
[/code][/spoiler]

Notice the lines that detail the prefixes such as:

[code linenumbers=false]CREATE TABLE `ip_medal` ([/code]

[code linenumbers=false]CREATE TABLE `ip_medal_user` ([/code]

[code linenumbers=false]CREATE TABLE `ip_medal_mod` ([/code]

[code linenumbers=false]CREATE TABLE `ip_medal_cat` ([/code]

[code linenumbers=false]INSERT INTO `ip_config` VALUES ('allow_medal_display', '0');
INSERT INTO `ip_config` VALUES ('medal_display_row', '1');
INSERT INTO `ip_config` VALUES ('medal_display_col', '3');
INSERT INTO `ip_config` VALUES ('medal_display_width', '');
INSERT INTO `ip_config` VALUES ('medal_display_height', '');
INSERT INTO `ip_config` VALUES ('medal_display_order', '');
INSERT INTO `ip_medal_cat` VALUES ('1', 'Default', '10');[/code]

Clearly the prefix that is used in this example is "[b]ip_[/b]".  What if your database prefixes are not "[b]ip_[/b]"?  You simply change the "[b]ip_[/b]" prefix in the install instructions to whatever your prefix is.

If your table prefix is "[b]ilovedogs_[/b]", you would need to turn these install instructions into this:

[spoiler][code linenumbers=false]	CREATE TABLE `ilovedogs_medal` (
	`medal_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`cat_id` mediumint(8) UNSIGNED NOT NULL default '1',
	`medal_name` varchar(40) NOT NULL,
	`medal_description` varchar(255) NOT NULL,
	`medal_image` varchar(40) NULL,
	PRIMARY KEY (`medal_id`)
	);

	CREATE TABLE `ilovedogs_medal_user` (
	`issue_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`medal_id` mediumint(8) UNSIGNED NOT NULL,
	`user_id` mediumint(8) UNSIGNED NOT NULL,
	`issue_reason` varchar(255) NOT NULL,
	`issue_time` int(11) NOT NULL,
	PRIMARY KEY (`issue_id`)
	);

	CREATE TABLE `ilovedogs_medal_mod` (
	`mod_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`medal_id` mediumint(8) UNSIGNED NOT NULL,
	`user_id` mediumint(8) UNSIGNED NOT NULL,
	PRIMARY KEY (`mod_id`)
	);

	CREATE TABLE `ilovedogs_medal_cat` (
	`cat_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`cat_title` varchar(100) NOT NULL,
	`cat_order` mediumint(8) UNSIGNED NOT NULL default '0',
	PRIMARY KEY (`cat_id`),
	KEY `cat_order` (`cat_order`)
	);

	INSERT INTO `ilovedogs_config` VALUES ('allow_medal_display', '0');
	INSERT INTO `ilovedogs_config` VALUES ('medal_display_row', '1');
	INSERT INTO `ilovedogs_config` VALUES ('medal_display_col', '3');
	INSERT INTO `ilovedogs_config` VALUES ('medal_display_width', '');
	INSERT INTO `ilovedogs_config` VALUES ('medal_display_height', '');
	INSERT INTO `ilovedogs_config` VALUES ('medal_display_order', '');
	INSERT INTO `ilovedogs_medal_cat` VALUES ('1', 'Default', '10');[/code][/spoiler]

[img align=right]http://www.icyphoenix.com/files/images/3295/prefix1.png[/img]
In the picture to the right, my database tables are prefixed with "[b]phpbb_[/b]".  Since my table prefix is "[b]phpbb_[/b]", I must make sure that I change these install instructions to fit my prefix.

Once you have changed the prefixes to suit your needs, we're ready to add them to the database.  Using MySQL, click the SQL tab at the top of the page.  Copy and paste the whole command or just copy each block separately.  I took the following SQL, added it, and you can do the same:





















[code linenumbers=false]CREATE TABLE `phpbb_medal` (
	`medal_id` mediumint(8) UNSIGNED NOT NULL auto_increment,
	`cat_id` mediumint(8) UNSIGNED NOT NULL default '1',
	`medal_name` varchar(40) NOT NULL,
	`medal_description` varchar(255) NOT NULL,
	`medal_image` varchar(40) NULL,
	PRIMARY KEY (`medal_id`)
	);[/code]

I was then told "[i]Your SQL query has been executed successfully (Query took 0.0137 sec)[/i]".








[img align=right]http://www.icyphoenix.com/files/images/3295/sql.png[/img]

Keep following the special SQL instructions in the install file until you are finished.


