/**
* Demonstrates the features of the Database library.
*
* Table Structure:
* CREATE TABLE `pages` (
* `id` mediumint( 9 ) NOT NULL AUTO_INCREMENT ,
* `page_name` varchar( 100 ) NOT NULL ,
* `title` varchar( 255 ) NOT NULL ,
* `content` longtext NOT NULL ,
* `menu` tinyint( 1 ) NOT NULL default '0',
* `filename` varchar( 255 ) NOT NULL ,
* `order` mediumint( 9 ) NOT NULL ,
* `date` int( 11 ) NOT NULL ,
* `child_of` mediumint( 9 ) NOT NULL default '0',
* PRIMARY KEY ( `id` ) ,
* UNIQUE KEY `filename` ( `filename` )
* ) ENGINE = MYISAM DEFAULT CHARSET = utf8 PACK_KEYS =0;
*
*/
function database()
{
$db = new Database();
$table = 'pages';
echo 'Does the ' . $table . ' table exist? ';
if ($db->table_exists($table)) {
echo '<p>YES! Lets do some work =)</p>';
$query = $db->select('DISTINCT pages.*')->from($table)->get();
echo $db->last_query();
echo '<h3>Iterate through the result:</h3>';
foreach ($query as $item) {
echo '<p>' . $item->title . '</p>';
}
echo '<h3>Numrows using count(): ' . count($query) . '</h3>';
echo 'Table Listing:<pre>' . print_r($db->list_tables(), TRUE) . '</pre>';
echo '<h3>Try Query Binding with objects:</h3>';
$sql = 'SELECT * FROM ' . $table . ' WHERE id = ?';
$query = $db->query($sql, array(1));
echo '<p>' . $db->last_query() . '</p>';
$query->result(TRUE);
foreach ($query as $item) {
echo '<pre>' . print_r($item, true) . '</pre>';
}
echo '<h3>Try Query Binding with arrays (returns both associative and numeric because I pass MYSQL_BOTH to result():</h3>';
$sql = 'SELECT * FROM ' . $table . ' WHERE id = ?';
$query = $db->query($sql, array(1));
echo '<p>' . $db->last_query() . '</p>';
$query->result(FALSE, MYSQL_BOTH);
foreach ($query as $item) {
echo '<pre>' . print_r($item, true) . '</pre>';
}
echo '<h3>Look, we can also manually advance the result pointer!</h3>';
$query = $db->select('title')->from($table)->get();
echo 'First:<pre>' . print_r($query->current(), true) . '</pre><br />';
$query->next();
echo 'Second:<pre>' . print_r($query->current(), true) . '</pre><br />';
$query->next();
echo 'Third:<pre>' . print_r($query->current(), true) . '</pre>';
echo '<h3>And we can reset it to the beginning:</h3>';
$query->rewind();
echo 'Rewound:<pre>' . print_r($query->current(), true) . '</pre>';
echo '<p>Number of rows using count_records(): ' . $db->count_records('pages') . '</p>';
} else {
echo 'NO! The ' . $table . ' table doesn\'t exist, so we can\'t continue =( ';
}
echo "<br/><br/>\n";
echo 'done in {execution_time} seconds';
}
/**
* remove database table of current DatabaseTable object
* @return bool indicates if database table has been removed
*/
function drop()
{
$this->_log->trace("drop DatabaseTable (table_name=" . $this->table_name . ")");
# check if database connection is working
if ($this->_check_database_connection() == FALSE) {
return FALSE;
}
# check if database table exists
if (!$this->_database->table_exists($this->table_name)) {
$this->_handle_error("DatabaseTable does not exist in database", "ERROR_DATABASE_EXISTENCE");
return FALSE;
}
$query = "DROP TABLE " . $this->table_name;
$result = $this->_database->query($query);
if ($result == FALSE) {
$this->_handle_error("could not drop DatabaseTable", "ERROR_DATABASE_PROBLEM");
return FALSE;
}
$this->_log->trace("dropped DatabaseTable (table_name=" . $this->table_name . ")");
return TRUE;
}
continue;
} else {
if ($mode == "including" && !in_array($name, $included)) {
continue;
}
}
if (isset($table['map']) && array_key_exists($name, $table['map'])) {
$dst_name = $table['map'][$name];
$field['src_name'] = $name;
} else {
$dst_name = $name;
}
$dst_fields[$dst_name] = $field;
}
$table_info = array("name" => $table['destination'], "fields" => $dst_fields);
$update_mode = $db_dst->table_exists($table_info['name']);
$res = $db_dst->process_table($table_info);
if ($res != false) {
$first_field = array_shift(array_keys($src_fields));
$query = "SELECT * FROM {$db_src->name}.{$table['source']} ORDER BY {$first_field} ASC";
$res = mysql_query($query, $db_src->dbh);
while ($row = mysql_fetch_assoc($res)) {
if (!$update_mode) {
$q_insert = "INSERT INTO {$db_dst->name}.{$table['destination']} ";
$pairs = get_field_pairs($dst_fields, $row);
$columns = implode(",", array_keys($pairs));
$values = implode(",", array_values($pairs));
$q_insert .= "({$columns}) VALUES ({$values});";
$res_insert = mysql_query($q_insert);
if (!$res_insert) {
ER($res_insert . ": " . mysql_error());
}
echo "reading all users<br>";
$query = "SELECT _name FROM " . USER_TABLE_NAME;
$query_result = $database->query($query);
if ($query_result != FALSE) {
$all_users = array();
while ($row = $database->fetch($query_result)) {
echo " found user <strong>" . $row[0] . "</strong><br>";
array_push($all_users, $row[0]);
}
} else {
fatal("could not find any users");
}
echo "create user list permissions table<br>";
if ($user_list_permissions->create() == FALSE) {
if ($database->table_exists(USERLISTTABLEPERMISSIONS_TABLE_NAME) == TRUE) {
echo "removing table " . USERLISTTABLEPERMISSIONS_TABLE_NAME . "<br>";
$query = "DROP TABLE " . USERLISTTABLEPERMISSIONS_TABLE_NAME;
$result = $database->query($query);
if ($result != FALSE) {
echo "removed table<br>";
}
if ($user_list_permissions->create() == FALSE) {
fatal("could not create user list permissions table");
}
} else {
fatal("could not create user list permissions table");
}
}
echo "create user list permissions for all users and all lists<br>";
echo "user admin is list admin of all lists<br>";
