Store
Community Documentation

v3 Knowledgebase

Creating / Modifying a database table when installing a module.

You can create a new table when you install the module like below:

If you look in any normal module in phpfox that uses the import product method they have a install file that goes on:

"folder where phpfox is installed" \include\xml\YourModuleName.xml

This is the file that installs the module,without it,the module won't show in the import products. You can create,update,delete tables using this file.

Below is an example:

Quote:

<product>
<data>
<product_id>YourModuleName</product_id>
<is_core>0</is_core>
<title>My Module</title>
<description>Learning how to modify the database</description>
<version>2.1</version>
<latest_version></latest_version>
<last_check>1323010924</last_check>
<is_active>1</is_active>
<url></url>
<url_version_check></url_version_check>
</data>
<installs>
<install>
<version>0.1</version>
<install_code><![CDATA[$this->database()->query("CREATE TABLE IF NOT EXISTS `".Phpfox::getT('yourtablename')."` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`destination` varchar(255) NOT NULL,
`time_stamp` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 ");]]></install_code>
<uninstall_code><![CDATA[$this->database()->query("DROP TABLE `" . Phpfox::getT('yourtablename') . "`");]]></uninstall_code>
</install>
</installs>
<modules>
<module_id>yourmodulename</module_id>
</modules>
</product>


The code above will create a table called 'yourtablename' with columns id,user_id,destination,time_stamp.

You can also see the uninstall code, which will delete this table when you uninstall the module.

Queries:


Create a table:

create a table called 'mytable' with columns id,user_id,destination,time_stamp.
$this->database()->query("CREATE TABLE IF NOT EXISTS `".Phpfox::getT('mytable')."` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `destination` varchar(255) NOT NULL,
  `time_stamp` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`)
) AUTO_INCREMENT=1 ");


Delete a table:
$this->database()->query("DROP TABLE `" . Phpfox::getT('yourtablename') . "`");


Modify an existing table.

In this example we modify the table "user" and add a new column called "is_verified"
$this->database()->query("ALTER TABLE `".Phpfox::getT('user')."` ADD  `is_verified` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT  '0'");


With this code we delete the column that we just created in the step above.
$this->database()->query("ALTER TABLE `" . Phpfox::getT('user') . "` DROP COLUMN `is_verified`");


Delete rows in a table that matches specific values in a field.
$this->database()->query("DELETE FROM `" . Phpfox::getT('tablename') . "` WHERE type_id='user_verify'");


Update rows in a table that matches specific values in a field.
$this->database()->query("UPDATE `" . Phpfox::getT('tablename') . "` SET `value_actual` = '0' WHERE `setting_id` = '344'");


You can just paste any of the queries above in the install/uninstall text field when you create a module via AdminCP.

You can also see the code here: http://pastebin.com/CW0FeeSX

Please use the code above in a test enviroment until you feel your module is ready to be used on a live site,so you won't mess up with your database while testing.

Regards,
Robert