Wiki : ManipulatingTables
Documentation Home :: Categories :: Index :: Recent Changes :: Comments :: Search :: Help :: Login/RegisterManipulating Database Tables
The following commands would normally only be used in the context of pninit.php
We can look at some real life examples in pninit.php to illustrate these examples.
For an explanation of the $sql definition see Adodb Data Dictionary
Creating Tables
For this we use the call
DBUtil::createTable($tablename, $sql);
<?php
$sql = "id I NOTNULL AUTO PRIMARY,
title C(255) NOTNULL DEFAULT '',
content C(255) NOTNULL DEFAULT '',
online L NOTNULL DEFAULT 0";
if (!DBUtil::createTable('ht_table1', $sql)) {
return LogUtil::registerError(_CREATETABLEFAILED);
}
?>
$sql = "id I NOTNULL AUTO PRIMARY,
title C(255) NOTNULL DEFAULT '',
content C(255) NOTNULL DEFAULT '',
online L NOTNULL DEFAULT 0";
if (!DBUtil::createTable('ht_table1', $sql)) {
return LogUtil::registerError(_CREATETABLEFAILED);
}
?>
Dropping Tables
To delete or drop a table and lose all the data within that table use
DBUtil::dropTable($tablename);
So we have the following example:
<?php
if (!DBUtil::dropTable('exampletable')) {
return LogUtil::registerError(_DROPTABLEFAILED);
}
?>
if (!DBUtil::dropTable('exampletable')) {
return LogUtil::registerError(_DROPTABLEFAILED);
}
?>
Creating Table Indexes
DBUtil::createIndex('myindexname', 'exampletable', 'field1');
or with multiple column indexes
For example:
<?php
if (!DBUtil::createIndex('myindexname', 'exampletable', array('field1', 'field2')) {
return LogUtil::registerError(_CREATEINDEXFAILED);
}
?>
if (!DBUtil::createIndex('myindexname', 'exampletable', array('field1', 'field2')) {
return LogUtil::registerError(_CREATEINDEXFAILED);
}
?>
Dropping Table Indexes
DBUtil::dropIndex('myindexname', 'exampletable');
For example:
<?php
if (!DBUtil::dropIndex('myindexname', 'exampletable') {
return LogUtil::registerError(_DROPINDEXFAILED);
}
?>
if (!DBUtil::dropIndex('myindexname', 'exampletable') {
return LogUtil::registerError(_DROPINDEXFAILED);
}
?>
Changing Table Structure
This is probably one of the most interesting and cool features in ADODB. If we need to modify either and existing field, add or remove fields, or even sort the order, we can just specify the data dictionary for how we want it to look and ADODB will take care of everything.
DBUtil::changeTable($tablename, $sql);
A real example could be to change the title field from 255 to 50 characters maximum length and add a debug field as a BLOB type.
<?php
$sql = "id I NOTNULL AUTO PRIMARY,
title C(50) NOTNULL DEFAULT '',
content C(255) NOTNULL DEFAULT '',
online I(1) NOTNULL DEFAULT 0,
debug B";
if (!DBUtil::changeTable('mytable', $sql)) {
return LogUtil::registerError(_CHANGETABLEFAILED);
}
?>
$sql = "id I NOTNULL AUTO PRIMARY,
title C(50) NOTNULL DEFAULT '',
content C(255) NOTNULL DEFAULT '',
online I(1) NOTNULL DEFAULT 0,
debug B";
if (!DBUtil::changeTable('mytable', $sql)) {
return LogUtil::registerError(_CHANGETABLEFAILED);
}
?>
Previous | Top | Next
CategoryDeveloperDocs
|
