Site Sponsors:
An Easy PHP CRUD Example ... for SQL / Sqlite! 
When our needs are simple, then so should be our code.

For 100% of what we need to do at the moment (manage a massive collection of data across a local network,) this class will do what we need it to do: CRUD far too many rows consisting of TEXT / string values!


So, is DAO-generation necessary? When using a name-associated array in PHP 5, all we need do is to define our fields, and away we go!


class SqlStringRecord {

function get_file_name() {
return "default.sqlt3";
}

function get_table_name() {
return "contacts";
}

function get_columns() {
$data = array();
$data[0] = "Name";
$data[1] = "Address";
$data[2] = "Note";
return $data;
}

function delete_table() {
$db = new SQLite3($this->get_file_name());
$tbl = $this->get_table_name();
$db->exec("DROP TABLE IF EXISTS $tbl");
$db->close();
if ($db->lastErrorCode() != 0) {
return FALSE;
}
return TRUE;
}

function create_table() {
$this->delete_table();
$fields = $this->get_columns();
$tbl = $this->get_table_name();
$cmd = "CREATE TABLE $tbl (ID INTEGER PRIMARY KEY AUTOINCREMENT";
foreach ($fields as $field) {
$cmd .= ', ';
$cmd .= $field;
$cmd .= ' STRING';
}
$cmd .= ")";
$db = new SQLite3($this->get_file_name());
$db->exec($cmd);
$db->close();
if ($db->lastErrorCode() != 0) {
return FALSE;
}
return TRUE;
}

function count() {
$db = new SQLite3($this->get_file_name());
$tbl = $this->get_table_name();
$rs = $db->query("SELECT COUNT(*) FROM $tbl");
if ($rs == FALSE) {
return FALSE;
}
$data = $rs->fetchArray();
$db->close();
return $data[0];
}

function create($special) {
$cols = $this->get_columns();
$tbl = $this->get_table_name();
$cmd = "INSERT INTO $tbl (";
$bfirst = true;
foreach ($cols as $col) {
if ($bfirst == false) {
$cmd .= ", ";
}
$cmd .= $col;
$bfirst = false;
}
$cmd .= ") VALUES (";
$bfirst = true;
foreach ($cols as $col) {
if ($bfirst == false) {
$cmd .= ", ";
}
$cmd .= "'$special[$col]'";
$bfirst = false;
} $cmd .= ")";
echo "\n\n$cmd\n\n";
$db = new SQLite3($this->get_file_name());
$db->exec($cmd);
$db->close();
if ($db->lastErrorCode() != 0) {
return FALSE;
}
return $this->count();
}

function read($id) {
$db = new SQLite3($this->get_file_name());
$tbl = $this->get_table_name();
$cmd = "SELECT * FROM $tbl WHERE ID = $id LIMIT 1";
$rs = $db->query($cmd);
if ($rs == FALSE) {
return FALSE;
}
$results = $rs->fetchArray(SQLITE3_ASSOC);
$db->close();
return $results;
}

function update($id, $special) {
$tbl = $this->get_table_name();
$cmd = "UPDATE $tbl SET";
$cols = $this->get_columns();
$bfirst = TRUE;
foreach ($cols as $col) {
if ($bfirst == FALSE) {
$cmd .= ", ";
}
$cmd .= " $col = '$special[$col]'";
$bfirst = FALSE;
}
$cmd .= " WHERE ID = $id";
echo "\n\n$cmd\n\n";
$db = new SQLite3($this->get_file_name());
$db->exec($cmd);
$db->close();
if ($db->lastErrorCode() != 0) {
return FALSE;
}
return TRUE;
}

function delete($id) {
$tbl = $this->get_table_name();
$cmd = "DELETE FROM $tbl WHERE ID = $id";
$db = new SQLite3($this->get_file_name());
$db->exec($cmd);
$db->close();
if ($db->lastErrorCode() != 0) {
return FALSE;
}
return TRUE;
}

}


So feel free to update:

get_columns()
get_table_name() [optional]
get_file_name() [optional]

Then simply use associative arrays as your "data access object" - as returned from the read($id) operation - then accessed via your own get_columns() names - to see how easily (ahem) CRUD happens!

The complete code + test case is here.

[ add comment ] ( 33 views )   |  permalink  |  related link

<Back | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Next> Last>>