A Practical Tutorial Of Zend Framework(五)

2016/06/20 19:13
阅读数 10

Interacting with the Database

Because the database components of the Zend Framework are still relatively unstable, and because I want this demo to be easy to work with, I use a simple class that uses SQLite for storing and retrieving news entries and comments:
<?php

class Database
{
    private 
$_db;

    public function 
__construct($filename)
    {
        
$this->_db = new SQLiteDatabase($filename);
    }

    public function 
addComment($name$comment$newsId)
    {
        
$name sqlite_escape_string($name);
        
$comment sqlite_escape_string($comment);
        
$newsId sqlite_escape_string($newsId);

        
$sql "INSERT
                INTO   comments (name, comment, newsId)
                VALUES ('$name', '$comment', '$newsId')"
;

        return 
$this->_db->query($sql);
    }

    public function 
addNews($title$content)
    {
        
$title sqlite_escape_string($title);
        
$content sqlite_escape_string($content);

        
$sql "INSERT
                INTO   news (title, content)
                VALUES ('$title', '$content')"
;

        return 
$this->_db->query($sql);
    }

    public function 
approveNews($ids)
    {
        foreach (
$ids as $id) {
            
$id sqlite_escape_string($id);

            
$sql "UPDATE news
                    SET    approval = 'T'
                    WHERE  id = '$id'"
;

            if (!
$this->_db->query($sql)) {
                return 
FALSE;
            }
        }

        return 
TRUE;
    }

    public function 
getComments($newsId)
    {
        
$newsId sqlite_escape_string($newsId);

        
$sql "SELECT name, comment
                FROM   comments
                WHERE  newsId = '$newsId'"
;

        if (
$result $this->_db->query($sql)) {
            return 
$result->fetchAll();
        }

        return 
FALSE;
    }

    public function 
getNews($id 'ALL')
    {
        
$id sqlite_escape_string($id);

        switch (
$id) {
            case 
'ALL':
                
$sql "SELECT id,
                               title
                        FROM   news
                        WHERE  approval = 'T'"
;
                break;
            case 
'NEW':
                
$sql "SELECT *
                        FROM   news
                        WHERE  approval != 'T'"
;
                break;
            default:
                
$sql "SELECT *
                        FROM   news
                        WHERE  id = '$id'"
;
                break;
        }

        if (
$result $this->_db->query($sql)) {
            if (
$result->numRows() != 1) {
                return 
$result->fetchAll();
            } else {
                return 
$result->fetch();
            }
        }

        return 
FALSE;
    }
}

?>
(You're free to replace this class with your own solution. This is only included to provide a complete example and is not meant to suggest an implementation.) The constructor of this class expects the full path and filename of the SQLite database, something you must create:
<?php

$db 
= new SQLiteDatabase('/path/to/db.sqlite');

$db->query("CREATE TABLE news (
                id       INTEGER PRIMARY KEY,
                title    VARCHAR(255),
                content  TEXT,
                approval CHAR(1) DEFAULT 'F'
            )"
);

$db->query("CREATE TABLE comments (
                id       INTEGER PRIMARY KEY,
                name     VARCHAR(255),
                comment  TEXT,
                newsId   INTEGER
            )"
);

?>
You only need to do this once, then you can just pass the full path and filename to the constructor of the Database class:
<?php

$db 
= new Database('/path/to/db.sqlite');

?>
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部