AutoCRUD - PHP 下的透明持久層

AutoCRUD是一個php下的持久層，雖然它目前只支援MySQL資料庫，但是它無需配置，也很方便實用。

已建立下面幾個表為例

CREATE TABLE `article` (
  `articleid` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` text,
  `datetimestamp` int(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`articleid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `article2category` (
  `article` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  `category` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`article`,`category`)
) TYPE=MyISAM;

CREATE TABLE `category` (
  `categoryid` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `parent` smallint(5) UNSIGNED DEFAULT '0',
  PRIMARY KEY  (`categoryid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `comment` (
  `commentid` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT '',
  `author` varchar(255) DEFAULT NULL,
  `article` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`commentid`),
  KEY `article` (`article`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `user` (
  `userid` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(255) DEFAULT '',
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `username` (`username`)
) TYPE=MyISAM AUTO_INCREMENT=1;

基本操作

1 初始化

<?php
include ('autocrud/autocrud.php');

$crud = new AutoCRUD;
$result = $crud->connect ('sa', '', 'demo');

if (autocrud_is_error($result) == true) {
        // error occured, find out what error
        echo $result->getCode() . ': ' . $result->getMessage();
        die();
}

$crud->generate();  // 該函式會自動生成一些存取資料所用的程式碼到資料庫裡面

?>

2 插入紀錄

<?php
// ...
// setup AutoCRUD
// ...

$new_row = array('title' => 'My article', 'content' => 'Article content goes here', 'datetimestamp' => time());
$result = $crud->article->insert ($new_row);

if (autocrud_is_error($result) == true) {
        // error occured, find out what error
        echo $result->getCode() . ': ' . $result->getMessage();
        die();
}

echo 'Row inserted';

?>

3 更新紀錄

<?php
$row = array('title' => 'New title 2', 'content' => 'New Article content goes here', 'datetimestamp' => time());

$id = '1';
$crud->article->where = "articleid = " . $crud->quote($id);
$result = $crud->article->update ($row);

// we left out the title, so error should be returned

if (autocrud_is_error($result) == true) {
        // error occured, find out what error
        echo $result->getCode() . ': ' . $result->getMessage();
        die();
}

echo 'Record updated';

?>

4 刪除紀錄

<?php
// ...
// setup AutoCRUD
// ...

$crud->article->delete(1);
echo 'Record delete';

?>

5 選擇紀錄

<?php
// ...
// setup AutoCRUD
// ...

// Get a list of articles
$list = $crud->article->select();

echo 'Number of records: ' . count($list) . '';
foreach ($list as $article) {
        echo $article['title'] . '';
}
?>

<?php
// Specify WHERE and ORDER BY
$crud->article->where = "articleid != 6";
$crud->article->orderby = "title DESC";

// Get a list of articles
$list = $crud->article->select();

echo 'Number of records: ' . count($list) . '';
foreach ($list as $article) {
        echo $article['title'] . '';
}
?>

翻頁處理

<?php
// Do paging
$crud->article->paging = true; # Enable paging
$crud->article->perpage = 2; # Set number of records to show per page
$crud->article->currentpage = (empty($_GET['page'])) ? 1 : $_GET['page']; # Set current page

// Get a list of articles
$list = $crud->article->select();

echo 'Number of records: ' . count($list) . '';
foreach ($list as $article) {
        echo $article['title'] . '';
}

if ($crud->article->currentpage > 1) {
        echo '($crud->article->currentpage-1) . '">Previous Page ';
}

if ($crud->article->currentpage < $crud->article->totalpages) {
        echo ' ($crud->article->currentpage+1) . '">Next Page';
}

?>

<?php
// Get article #5
$article = $crud->article->get(5);

if ($article == false) {
        echo 'Article doesn/'t exist';
}

echo '

'

?>

6 處理多表連線

<?php
// Setup relationship
$crud->comment->addRelationship ($crud->article, 'article', 'articleid', 'many-to-one');

// Get articles and their comments
$crud->article->join ('comment');
$articles = $crud->article->select();

echo '

'

?>

<?php
// Setup relationship
$crud->comment->addRelationship ($crud->article, 'article', 'articleid', 'many-to-one');

// Get comments and their article
$crud->comment->join ('article');
$comments = $crud->comment->select();

echo '

'

'

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=779777