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 ] ( 81 views )   |  permalink  |  related link
PHP EzLog? 
I am teaching a class on Advanced PHP today. Was surprised at how much we all enjoyed a simple logging demonstration:


/**
* Just ezlog a message to a file.
*
* @author profnagy
*/
class logfile {

var $defaultFile = 'default.log';

public function open($file) {
$rev = strrev($file);
if (strpos($rev, 'gol.') != 0)
$this->defaultFile = $file . '.log';
else
$this->defaultFile = $file;
$br = file_exists($this->defaultFile);
return $br;
}

public function write($message) {
$now = date('Y-m-d H:i:s', time());
$line = "$now\t$message\n";
$result = file_put_contents($this->defaultFile, $line, FILE_APPEND | LOCK_EX);
if ($result === false)
return false;
return true;
}

public function remove() {
return unlink($this->defaultFile);
}

}


The example succinctly demonstrates using PHP dates, time, file I/O, locking, strings... and a little bit more. (i.e If you ever went looking for a Java-Style "endsWith()" in PHP, then consider the fast & easy use of strrev() in the above. Can be logically the same as a "startsWith" in the C/C++/PHP/Standard Lib/PHP Core.)

So if you are just getting into the more advanced features of PHP (or, if you enjoy seeing the spirit of the Standard C Library in use!), then you might enjoy playing with the above PHP Class.

By way of a bonus, it looks and smells a lot like the RFC Log Date & Time format I used for my Open Source / Skype's C/C++ (.ezlog) Legacy.

Time-tested & ready for production.


Cheers,

-Rn




[ add comment ] ( 8922 views )   |  permalink  |  related link
Quick PHP Persistence  

Easy Enough?


One of the reasons why SQL databases are such a performance bottleneck in modern enterprise systems is that people store absolutely everything in them. Indeed, over the decades I encounter more and more blank stares when I respond to queries by telling friends that - even given the millions of search-able recipes, quotations, events, and graphics shared throughout my popular sites, that we have never felt the need to use SQL. -Binary searches, text indexing, and data storage routines are easy enough to write?

Requirement Triage


Indeed, it is due to the many fallacies one often discovers in the "one technology fits all" mantra that savvy technical folks refuse to do things like log HTML hits to an enterprise resource. -Without clear business justification, when it comes time to store billions of simple n-keyed records a day - into a bottleneck-sensitive database - perhaps folks are doing so because they do not realize that there is an easy alternative? (After all, to make any database work, someone had to write things like indexing and binary searches (and a whole lot more - wink - wink), at some point?)

When it comes to demonstrating customized, responsive solutions to domain-specific challenges, such was the case when I was recently showing off a performance-boosting notion designed to use PHP CRUD. When discussing how we can quickly & easily save data to an indexed file, the "I'm from Missouri" (i.e. 'show me') -urge came up with a succinct demonstration:


class user_info {

var $data = array();

function __construct() {
$this->data[0] = -1;
$this->data[1] = '';
$this->data[2] = '';
$this->data[3] = '';
$this->data[4] = false;

}

public function getPayload() {
return $this->data[4];
}

public function setPayload($str) {
$this->data[4] = $str;
}

public function getAlias() {
return $this->data[3];
}

public function setAlias($str) {
$this->data[3] = $str;
}

public function getPassword() {
return $this->data[2];
}

public function setPassword($str) {
$this->data[2] = $str;
}

public function getEmailt() {
return $this->data[1];
}

public function setEmail($email) {
$this->data[1] = $email;
}

public function getAccount() {
return $this->data[0];
}

public function setAccount($ss) {
$this->data[0] = $ss;
}

public function isNull() {
return ($this->data[0] === -1);
}

public function toString() {
$result = implode('|', $this->data);
return $result;
}

public function fromString($str) {
$this->data = explode('|', $str);
return true; // gigo
}

}


Simple String


The crux of the above example is a demonstration of how we can quickly read and write class information to a string. No XML. No JSON. Just a nice, efficient, cross platform representation of an easily extensible data format.

Exploding Arrays


By using arrays, encapsulation, and PHP's implode() and explode() feature, a classy lifestyle can get instances stored relatively quickly. -Best of all, when using an encapsulated array(), we are free to rapidly extend the number of fields / columns we keep without change requests, hours of meetings, or ever contacting your friendly neighborhood DBA ;-)

After storing data, comes the searching. While I consider my indexing technologies to be intellectual property, at the end of the day such things are easy enough to write for oneself.

Class Egocentricity


For what it is worth, working within the confines of typeless languages (such as PHP) can also bring a few conveniences to mind. Indeed, while an accomplished object-oriented designer might be interested in designing an instance out of a controller, for maintenance reasons some odd 'typeless-convenience' patterns may surface:


class user_info_crud extends user_info {
var $info;
var $db;

public function __construct() {
user_info::__construct();
$this->info = new IndexedDataInfo();
$this->info->data_min = 1024;
$df = home('front_end_data/user_info.crud');
$this->info->setFileNames($df);
$this->db = new IndexedData();
}

public function create() {
$res = $this->db->append($this->info, '');
if ($res === false)
return false;
$this->setAccount($res);
return $this->update();
}

public function read($ss) {
$str = $this->db->read($this->info, $ss);
if ($str === false)
return false;
return $this->fromString($str);
}

public function update() {
if ($this->isNull() == false) {
$str = $this->toString();
return $this->db->update($this->info, $this->getAccount(), $str);
}
return false;
}

public function delete() {
if ($this->isNull() == false)
return $this->db->update($this->info, $this->getAccount(), '');
return true;
}

public function tally() {
return $this->db->tally($this->info);
}

public function destroyDatabase() {
return $this->info->delete();
}
}


The above demonstrates how we might use PHP CRUD to maintain a list of user accounts. By using the logical, zero-based record-offset as the record identifier / account number, users have that identifier, as well as the email address (etc.) to manage their front-end web-access account. --In addition to leveraging file-level POSIX securities (usr/group, etc), the security conscious can even use PHP CRUD's base64 encoding as another line of defense.

Proper Packaging


But the real point is this: In as much as PHP is - for the moment - extremely typeless - rather than having your CRUD operations return - and manage - objects - it is often easier in PHP to use the above design pattern. While it always seems laughably obscene to derive a controller from an entity (such as we do above), doing so can often make OIOO (One-In One-Out - pronounced "ohio") operations far, far easier to deal with.

Indeed, in the final analysis, the ability to wrap a controller around an object is often what templates (also referred to as parametrized types) are all about.

Better still, if push comes to shove (as it often may), we can always retrieve or otherwise manage an array of user_info.

Enjoy,


-Rn



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

| 1 | 2 | Next> Last>>