public function crud_queries()
{
// Instance the component.
$db = new Mysqli_Component();
// Create a sample table.
//We will delete this later, so ensure this table does not already exist!
$db->query('
CREATE TABLE IF NOT EXISTS `example_temporary_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
');
// CRUD model.
$crud = new Crud_Model();
$crud->table = 'example_temporary_table';
// Create string row.
$insert_id = $crud->create(array('name' => 'Barney'));
$this->expect_not_empty($insert_id);
// Create integer row.
$insert_id = $crud->create(array('name' => 1234));
$this->expect_not_empty($insert_id);
// Read a row.
$row = $crud->read(1);
$this->expect_object($row);
// Read a row.
$row = $crud->read(2);
$this->expect_object($row);
// Update a row.
$affected = $crud->update(2, array('name' => 'Mortimer'));
$this->expect_not_empty($affected);
// Read a row.
$row = $crud->read(2);
$this->expect_object($row);
// Delete a row.
$affected = $crud->delete(2);
$this->expect_not_empty($affected);
// Create string row.
$insert_id = $crud->create(array('name' => 'Francis'));
$this->expect_not_empty($insert_id);
// Set new column to Read/Update/Delete by.
$crud->field = 'name';
// Update a row by column.
$affected = $crud->update('Francis', array('name' => 'James'));
$this->expect_not_empty($affected);
// Clean up our sample table.
$db->query('DROP TABLE `example_temporary_table`');
// Show query log, current row, and total row count
$this->expect_array($db->get_query_log());
}
/**
* Garbage handler cleans up expired session.
*
* @param integer $max_lifetime
*/
public function gc($max_lifetime)
{
$expires = time() - $max_lifetime;
try {
$this->db->query("\n\t\t\t\tDELETE FROM\n\t\t\t\t\t`sessions` \n\t\t\t\tWHERE\n\t\t\t\t\t`modified` < FROM_UNIXTIME({$expires})\n\t\t\t\t");
} catch (Exception $e) {
die($e->getMessage());
}
}
/**
* Delete a single row.
*
* @param mixed $value Value of field to delete.
* @return integer affected rows
*/
public function delete($value)
{
try {
$result = $this->db->query("DELETE FROM `{$this->table}` WHERE `{$this->field}` = ? LIMIT 1", $value);
return $result->affected;
} catch (Exception $e) {
throw new Exception('Could not delete row: ' . $e->getMessage());
}
}
/**
* Run some queries.
*/
public function create_insert_drop_rows()
{
// Instance the component.
$db = new Mysqli_Component();
// Create a sample table.
//We will delete this later, so ensure this table does not already exist!
$result = $db->query('
CREATE TABLE IF NOT EXISTS `example_temporary_table` (
`id` integer UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)
');
// Expect a result object.
$this->expect_object($result);
// Add some rows to our table. Demonstrates query binding.
$result = $db->query('
INSERT INTO
`example_temporary_table`
(
`name`
)
VALUES (?),(?),(?),(?),(?)
', 'Tom', 'Bob', 'Ingrid', 'Sam', 'James');
// Expect a result object.
$this->expect_object($result);
// Run a sample query on table
$result = $db->query('SELECT * FROM example_temporary_table a LIMIT ?,?', 0, 5);
// Expect a result object.
$this->expect_object($result);
// Clean up our sample table.
$db->query('DROP TABLE `example_temporary_table`');
// Demonstrate foreach through result
$row_ids = array();
foreach ($result as $row) {
$row_ids[] = $row->name;
}
// Show query log, current row, and total row count
$this->expect_array($db->get_query_log());
}
