A Practical Tutorial Of Zend Framework(五)

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');

?>