1 <!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2 <html xmlns=
"http://www.w3.org/1999/xhtml" xml:
lang=
"en" lang=
"en">
5 <meta http-equiv=
"Content-Type" content=
"text/html; charset=utf-8" />
6 <title>Database Forge Class : CodeIgniter User Guide
</title>
8 <style type='text/css' media='all'
>@import url('../userguide.css');
</style>
9 <link rel='stylesheet' type='text/css' media='all' href='../userguide.css'
/>
11 <script type=
"text/javascript" src=
"../nav/nav.js"></script>
12 <script type=
"text/javascript" src=
"../nav/prototype.lite.js"></script>
13 <script type=
"text/javascript" src=
"../nav/moo.fx.js"></script>
14 <script type=
"text/javascript" src=
"../nav/user_guide_menu.js"></script>
16 <meta http-equiv='expires' content='-
1'
/>
17 <meta http-equiv= 'pragma' content='no-cache'
/>
18 <meta name='robots' content='all'
/>
19 <meta name='author' content='ExpressionEngine Dev Team'
/>
20 <meta name='description' content='CodeIgniter User Guide'
/>
25 <!-- START NAVIGATION -->
26 <div id=
"nav"><div id=
"nav_inner"><script type=
"text/javascript">create_menu('../');
</script></div></div>
27 <div id=
"nav2"><a name=
"top"></a><a href=
"javascript:void(0);" onclick=
"myHeight.toggle();"><img src=
"../images/nav_toggle_darker.jpg" width=
"154" height=
"43" border=
"0" title=
"Toggle Table of Contents" alt=
"Toggle Table of Contents" /></a></div>
29 <table cellpadding=
"0" cellspacing=
"0" border=
"0" style=
"width:100%">
31 <td><h1>CodeIgniter User Guide Version
2.1.3</h1></td>
32 <td id=
"breadcrumb_right"><a href=
"../toc.html">Table of Contents Page
</a></td>
36 <!-- END NAVIGATION -->
39 <!-- START BREADCRUMB -->
40 <table cellpadding=
"0" cellspacing=
"0" border=
"0" style=
"width:100%">
43 <a href=
"http://codeigniter.com/">CodeIgniter Home
</a> ›
44 <a href=
"../index.html">User Guide Home
</a> ›
45 <a href=
"index.html">Database Library
</a> ›
48 <td id=
"searchbox"><form method=
"get" action=
"http://www.google.com/search"><input type=
"hidden" name=
"as_sitesearch" id=
"as_sitesearch" value=
"codeigniter.com/user_guide/" />Search User Guide
<input type=
"text" class=
"input" style=
"width:200px;" name=
"q" id=
"q" size=
"31" maxlength=
"255" value=
"" /> <input type=
"submit" class=
"submit" name=
"sa" value=
"Go" /></form></td>
51 <!-- END BREADCRUMB -->
57 <!-- START CONTENT -->
60 <h1>Database Forge Class
</h1>
62 <p>The Database Forge Class contains functions that help you manage your database.
</p>
64 <h3>Table of Contents
</h3>
67 <li><a href=
"#init">Initializing the Forge Class
</a></li>
68 <li><a href=
"#create">Creating a Database
</a></li>
69 <li><a href=
"#drop">Dropping a Database
</a></li>
70 <li><a href=
"#add_field">Adding Fields
</a></li>
71 <li><a href=
"#add_key">Adding Keys
</a></li>
72 <li><a href=
"#create_table">Creating a Table
</a></li>
73 <li><a href=
"#drop_table">Dropping a Table
</a></li>
74 <li><a href=
"#rename_table">Renaming a Table
</a></li>
75 <li><a href=
"#modifying_tables">Modifying a Table
</a></li>
79 <h2><a name=
"init"></a>Initializing the Forge Class
</h2>
81 <p class=
"important"><strong>Important:
</strong> In order to initialize the Forge class, your database driver must
82 already be running, since the forge class relies on it.
</p>
84 <p>Load the Forge Class as follows:
</p>
86 <code>$this-
>load-
>dbforge()
</code>
88 <p>Once initialized you will access the functions using the
<dfn>$this-
>dbforge
</dfn> object:
</p>
90 <code>$this-
>dbforge-
>some_function()
</code>
91 <h2><a name=
"create"></a>$this-
>dbforge-
>create_database('db_name')
</h2>
93 <p>Permits you to create the database specified in the first parameter. Returns TRUE/FALSE based on success or failure:
</p>
95 <code>if ($this-
>dbforge-
>create_database('my_db'))
<br />
97 echo 'Database created!';
<br />
103 <h2><a name=
"drop"></a>$this-
>dbforge-
>drop_database('db_name')
</h2>
105 <p>Permits you to drop the database specified in the first parameter. Returns TRUE/FALSE based on success or failure:
</p>
107 <code>if ($this-
>dbforge-
>drop_database('my_db'))
<br />
109 echo 'Database deleted!';
<br />
113 <h1>Creating and Dropping Tables
</h1>
114 <p>There are several things you may wish to do when creating tables. Add fields, add keys to the table, alter columns. CodeIgniter provides a mechanism for this.
</p>
115 <h2><a name=
"add_field" id=
"add_field"></a>Adding fields
</h2>
116 <p>Fields are created via an associative array. Within the array you must include a 'type' key that relates to the datatype of the field. For example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR) also require a 'constraint' key.
</p>
117 <p><code>$fields = array(
<br />
118 'users' =
> array(
<br />
119 'type' =
> 'VARCHAR',
<br />
120 'constraint' =
> '
100',
<br />
121 ),
<br />
122 );
<br />
124 // will translate to
"users VARCHAR(
100)
" when the field is added.
</code></p>
125 <p>Additionally, the following key/values can be used:
</p>
127 <li>unsigned/true : to generate
"UNSIGNED
" in the field definition.
</li>
128 <li>default/value : to generate a default value in the field definition.
</li>
129 <li>null/true : to generate
"NULL
" in the field definition. Without this, the field will default to
"NOT NULL
".
</li>
130 <li>auto_increment/true : generates an auto_increment flag on the field. Note that the field type must be a type that supports this, such as integer.
</li>
132 <p><code>$fields = array(
<br />
133 'blog_id' =
> array(
<br />
134 'type' =
> 'INT',
<br />
135 'constraint' =
> 5,
<br />
136 'unsigned' =
> TRUE,
<br />
137 'auto_increment' =
> TRUE
<br />
138 ),
<br />
139 'blog_title' =
> array(
<br />
140 'type' =
> 'VARCHAR',
<br />
141 'constraint' =
> '
100',
<br />
142 ),
<br />
143 'blog_author' =
> array(
<br />
144 'type' =
>'VARCHAR',
<br />
145 'constraint' =
> '
100',
<br />
146 'default' =
> 'King of Town',
<br />
147 ),
<br />
148 'blog_description' =
> array(
<br />
149 'type' =
> 'TEXT',
<br />
150 'null' =
> TRUE,
<br />
151 ),
<br />
152 );
<br />
154 <p>After the fields have been defined, they can be added using
<dfn>$this-
>dbforge-
>add_field($fields);
</dfn> followed by a call to the
<dfn>create_table()
</dfn> function.
</p>
155 <h3>$this-
>dbforge-
>add_field()
</h3>
156 <p>The add fields function will accept the above array.
</p>
157 <h3>Passing strings as fields
</h3>
158 <p>If you know exactly how you want a field to be created, you can pass the string into the field definitions with add_field()
</p>
159 <p><code>$this-
>dbforge-
>add_field(
"label varchar(
100) NOT NULL DEFAULT 'default label'
");
</code></p>
160 <p class=
"important">Note: Multiple calls to
<dfn>add_field()
</dfn> are cumulative.
</p>
161 <h3>Creating an id field
</h3>
162 <p>There is a special exception for creating id fields. A field with type id will automatically be assinged as an INT(
9) auto_incrementing Primary Key.
</p>
163 <p><code>$this-
>dbforge-
>add_field('id');
<br />
164 // gives id INT(
9) NOT NULL AUTO_INCREMENT
</code></p>
165 <h2><a name=
"add_key" id=
"add_key"></a>Adding Keys
</h2>
166 <p>Generally speaking, you'll want your table to have Keys. This is accomplished with
<dfn>$this-
>dbforge-
>add_key('field')
</dfn>. An optional second parameter set to TRUE will make it a primary key. Note that
<dfn>add_key()
</dfn> must be followed by a call to
<dfn>create_table()
</dfn>.
</p>
167 <p>Multiple column non-primary keys must be sent as an array. Sample output below is for MySQL.
</p>
168 <p><code>$this-
>dbforge-
>add_key('blog_id', TRUE);
<br />
169 // gives PRIMARY KEY `blog_id` (`blog_id`)
<br />
171 $this-
>dbforge-
>add_key('blog_id', TRUE);
<br />
172 $this-
>dbforge-
>add_key('site_id', TRUE);
<br />
173 // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)
<br />
175 $this-
>dbforge-
>add_key('blog_name');
<br />
176 // gives KEY `blog_name` (`blog_name`)
<br />
178 $this-
>dbforge-
>add_key(array('blog_name', 'blog_label'));
<br />
179 // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`)
</code></p>
180 <h2><a name=
"create_table" id=
"create_table"></a>Creating a table
</h2>
181 <p>After fields and keys have been declared, you can create a new table with
</p>
182 <p><code>$this-
>dbforge-
>create_table('table_name');
<br />
183 // gives CREATE TABLE table_name
</code></p>
184 <p>An optional second parameter set to TRUE adds an
"IF NOT EXISTS
" clause into the definition
</p>
185 <p><code>$this-
>dbforge-
>create_table('table_name', TRUE);
<br />
186 // gives CREATE TABLE IF NOT EXISTS table_name
</code></p>
187 <h2><a name=
"drop_table" id=
"drop_table"></a>Dropping a table
</h2>
188 <p>Executes a DROP TABLE sql
</p>
189 <p><code>$this-
>dbforge-
>drop_table('table_name');
<br />
190 // gives DROP TABLE IF EXISTS table_name
</code></p>
191 <h2><a name=
"rename_table" id=
"rename_table"></a>Renaming a table
</h2>
192 <p>Executes a TABLE rename
</p>
193 <p><code>$this-
>dbforge-
>rename_table('old_table_name', 'new_table_name');
<br />
194 // gives ALTER TABLE old_table_name RENAME TO new_table_name
</code></p>
195 <h1><a name=
"modifying_tables" id=
"modifying_tables"></a>Modifying Tables
</h1>
196 <h2>$this-
>dbforge-
>add_column()
</h2>
197 <p>The add_column() function is used to modify an existing table. It accepts the same field array as above, and can be used for an unlimited number of additional fields.
</p>
198 <p><code>$fields = array(
<br />
199 'preferences' =
> array('type' =
> 'TEXT')
<br />
201 $this-
>dbforge-
>add_column('table_name', $fields);
<br />
203 // gives ALTER TABLE table_name ADD preferences TEXT
</code></p>
204 <h2>$this-
>dbforge-
>drop_column()
</h2>
205 <p>Used to remove a column from a table.
</p>
206 <p><code>$this-
>dbforge-
>drop_column('table_name', 'column_to_drop');
</code></p>
207 <h2>$this-
>dbforge-
>modify_column()
</h2>
208 <p>The usage of this function is identical to add_column(), except it alters an existing column rather than adding a new one. In order to change the name you can add a
"name
" key into the field defining array.
</p>
209 <p><code>$fields = array(
<br />
210 'old_name' =
> array(
<br />
211 'name' =
> 'new_name',
<br />
212 'type' =
> 'TEXT',
<br />
213 ),
<br />
215 $this-
>dbforge-
>modify_column('table_name', $fields);
<br />
217 // gives ALTER TABLE table_name CHANGE old_name new_name TEXT
</code></p>
225 Previous Topic:
<a href=
"caching.html">DB Caching Class
</a>
226 ·
227 <a href=
"#top">Top of Page
</a> ·
228 <a href=
"../index.html">User Guide Home
</a> ·
229 Next Topic:
<a href=
"utilities.html">Database Utilities Class
</a><a href=
"../libraries/email.html"></a></p>
230 <p><a href=
"http://codeigniter.com">CodeIgniter
</a> · Copyright
© 2006 -
2012 · <a href=
"http://ellislab.com/">EllisLab, Inc.
</a></p>