MySQL Database & Model Classes

corey · 02-24-2009, 10:34 PM

This package can be used to manipulate MySQL database table records as objects.

There is a base class that abstracts basic MySQL database access operations like inserting, selecting, updating and deleting table records.

A sub-class of the base class can store and retrieve table records and perform operations to access the records information as if they are objects, like finding all records or only those that match a given criteria, create or update a given record with values from arrays, and delete a record.

Download as .zip (example files included)

Preview:
Database.php

PHP Code:


			
<?php



/**

 * PHP 4 & 5 Database Class

 * Performs basic MySQL database interactions

 */

 

class Database {



    var $host = 'localhost';

    var $user = 'username';

    var $pass = 'password';

    var $db   = 'databasename';

    

    var $conn;                    // Database connection

    var $last_query;            // Results of last query

    var $last_sql;                // String that contains last sql query

    

    var $show_errors = true;    // Whether or not to show error messages

    

    /**

     * Constructor function

     * Connects and selects database

     *

     * @param    string   MySQL Host

     * @param    string   MySQL Username

     * @param    string   MySQL Password

     * @param    string   MySQL Database name

     * @return   link     Connection link

     */

    function Database($host = '', $user = '', $pass = '', $db = '') {

    

        $host = !empty($host) ? $host : $this->host;

        $user = !empty($user) ? $user : $this->user;

        $pass = !empty($pass) ? $pass : $this->pass;

        $db   = !empty($db)   ? $db   : $this->db;

    

        $this->conn = mysql_connect($host, $user, $pass) or $this->error('Could not connect to database. Make sure settings are correct.');

        

        if (is_resource($this->conn)) {

            mysql_select_db($db, $this->conn) or $this->error("Database '$db' could not be found.");

            return $this->conn;

        }

        

        return false;

    

    }

    

    /**

     * Execute a query on the database

     *

     * @param    string   SQL query to execute

     * @return   query    The query executed

     */

    function query($sql) {

    

        is_resource($this->conn) || $this->Database();

        $this->last_sql = $sql;

        return $this->last_query = mysql_query($sql, $this->conn) or $this->error();

    

    }

    

    /**

     * Very simple select

     *

     * @param    string   Table name to select from

     * @param    string   What to order by

     * @param    string   Where statement

     * @param    string   Columns to select

     * @return   result   Result of query

     */

    function select($table, $orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {

    

        $orderby = !empty($orderby) ? "ORDER BY $orderby" : '';

        $where = !empty($where) ? "WHERE $where" : '';

        $limit = !empty($limit) ? "LIMIT $limit" : '';

    

        return $this->query("SELECT $cols FROM $table $where $orderby $limit");

    

    }

    

    /**

     * Performs an insert query

     *

     * @param    string   Table name to query

     * @param    array    Associative array of Column => Value to insert

     * @return   result   Result of query

     */

    function insert($table, $data) {

    

        if (!is_array($data))

            return false;

            

        foreach ($data as $col => $value)

            $data[$col] = $this->escape($value);

        

        $cols = array_keys($data);

        $vals = array_values($data);

        

        $this->query("INSERT INTO $table (".implode(',', $cols).") VALUES (".implode(',', $vals).")");

        return mysql_insert_id();

    

    }

    

    /**

     * Updates a row

     *

     * @param    string   Table name to query

     * @param    array    Associtive array of columns to update

     * @param    string   Where clause

     * @return   result   Result of query

     */

    function update($table, $data, $where) {

    

        if (!is_array($data))

            return false;

            

        foreach ($data as $col => $value) {

            $vals[] = $col.' = '.$this->escape($value);

        }

        

        return $this->query("UPDATE $table SET ".implode(',', $vals)." WHERE $where");

    

    }

    

    /**

     * Delete a single row

     *

     * @param    string   Table name to query

     * @param    string   The column to match against

     * @param    string   Value to match against column

     * @return   result   Result of query

     */

    function delete($table, $where) {

    

        return $this->query("DELETE FROM $table WHERE $where");

    

    }

    

    /**

     * Get results of query

     *

     * @param    string   Return as object or array

     * @return   result   Result of query

     */

    function get($type = 'object') {

    

        $type = $type == 'object' ? 'mysql_fetch_object' : 'mysql_fetch_array';

    

        if (is_resource($this->last_query)) {

            while($rows = $type($this->last_query))

                $results[] = $rows;

        }

        

        else $this->error();

        

        return (!empty($results)) ? $results : null;

    

    }

    

    /**

     * Get first result of query

     *

     * @param    string   Return as object or array

     * @return   result   Result of query

     */

    function get_first($type = 'object') {

    

        $type = $type == 'object' ? 'mysql_fetch_object' : 'mysql_fetch_array';

        

        if (is_resource($this->last_query))

            return $type($this->last_query);

            

        else $this->error();

    

    }

    

    /**

     * Escape strings

     *

     * @param    mixed    String to escape

     * @return   string   Escaped string, ready for SQL insertion

     */

    function escape($data) {

    

        switch(gettype($data)) {

            case 'string':

                $data = "'".mysql_real_escape_string($data)."'";

                break;

            case 'boolean':

                $data = (int) $data;

                break;

            case 'double':

                $data = sprintf('%F', $data);

                break;

            default:

                $data = ($data === null) ? 'null' : $data;

        }

        

        return (string) $data;

    

    }

    

    /**

     * Show simple error messages to help aid development process

     *

     * @param    string   Custom error message to show

     * @return   death    Error page

     */

    function error($msg = '') {

    

        if ($this->show_errors === true) {

            $error = '<h1>Error!</h1>';

            

            if (!empty($msg))

                $error .= "$msg<br />";

                

            if (mysql_error())

                $error .= '<b>MySQL Error:</b> '.mysql_error().'<br />';

                

            if (isset($this->last_sql))

                $error .= '<b>SQL Statement:</b> '.$this->last_sql;

            

            die($error);

        }

    

    }



}

Model.php

PHP Code:


			
<?php



require_once('Database.php');



/**

 * PHP 4 & 5 Database Model Class

 * Sets up the Model interface

 */

 

class Model extends Database {



    var $table;

    var $fields;

    var $id;

    

    /**

     * Constructor function, gets table name, sets the ID of a current row

     * and gets all field names from the table

     *

     * @param   integer   ID of row to update or delete

     */

    function Model($id = '') {

            

        if (empty($this->table))

            $this->table = strtolower(substr(get_class($this), 0, -6));

        

        if (empty($this->fields)) {

            $this->query("SHOW COLUMNS FROM $this->table");

            foreach ($this->get() as $col)

                $this->fields[] = $col->Field;

        }

            

        if (!empty($id)) {

            $this->id = $id;

        }

    

    }

    

    /**

     * Very simple select

     *

     * @param    string   What to order by

     * @param    string   Where statement

     * @param    string   Columns to select

     * @return   result   Result of query

     */

    function select($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {

    

        if (!empty($this->id) && empty($where)) $where .= "id = $this->id";

        

        return parent::select($this->table, $orderby, $where, $cols, $limit);

    

    }

    

    /**

     * Insert a new record, or update existing

     *

     * @param   array   Data to insert (usually $_POST)

     */

    function save($data) {

    

        if (!is_array($data))

            return false;

    

        for ($i=0; $i<count($this->fields); $i++) {

            $set[$this->fields[$i]] = !empty($data[$this->fields[$i]]) ? $data[$this->fields[$i]] : '';

        }

        

        if (empty($this->id))

            return $this->insert($this->table, $set);

        else {

            foreach ($set as $key => $val) {

                if (empty($set[$key]) || $set[$key] == '')

                    unset($set[$key]);

            }

            return $this->update($this->table, $set, "id = '$this->id'");

        }

    

    }

    

    /**

     * Select from table

     *

     * @param   string   Order by

     * @param   string   Where clause

     * @param   string   Columns to select

     */

    function find_all($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {

    

        $orderby = (!empty($orderby)) ? $orderby : 'id DESC';

        $where = (!empty($where)) ? $where : '';

        $cols = (!empty($cols)) ? $cols : '*';

        $limit = (!empty($limit)) ? $limit : '';

    

        $this->select($orderby, $where, $cols, $limit);

        return $this->get();

    

    }

    

    /**

     * Select single row

     *

     * @param   string   Order by

     * @param   string   Where clause

     * @param   string   Columns to select

     */

    function find($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {

    

        $orderby = (!empty($orderby)) ? $orderby : 'id DESC';

        $where = (!empty($where)) ? $where : '';

        if (!empty($this->id) && empty($where)) $where .= "id = $this->id";

        $cols = (!empty($cols)) ? $cols : '*';

        $limit = (!empty($limit)) ? $limit : '';

    

        $this->select($orderby, $where, $cols, $limit);

        return $this->get_first();

    

    }

    

    /**

     * Delete row or rows

     *

     * @param   string   Where clause

     */

    function delete($where) {

    

        if (!empty($this->id) && empty($where)) $where .= "id = $this->id";

    

        return parent::delete($this->table, $where);

    

    }

    



}

adham is me · 05-15-2009, 06:22 PM

thanks for useful post

but what is the use of

if (is_resource($this->last_query))

??

allworknoplay · 05-15-2009, 06:39 PM

Quote:

Originally Posted by adham is me

thanks for useful post

but what is the use of

if (is_resource($this->last_query))

??

If I had to guess, it just checks if the last query was successful so there was a resource to use for obtaining the data from the query...?

sketchMedia · 05-15-2009, 07:01 PM

Quote:

If I had to guess, it just checks if the last query was successful so there was a resource to use for obtaining the data from the query...?

You guess correct

, successful or the correct type of query (INSERT, UPDATE etc don't return a result set therefore mysql_query will return a boolean only)

allworknoplay · 05-15-2009, 07:05 PM

Quote:

Originally Posted by sketchMedia

You guess correct

, successful or the correct type of query (INSERT, UPDATE etc don't return a result set therefore mysql_query will return a boolean only)

Wow I'm actually right about something.

Since INSERTS/UPDATES don't return result sets, the boolean value can still be used the same way to determine the status of the query correct?

Or would it be better to test for "mysql_affected_rows" instead?

sketchMedia · 05-15-2009, 09:17 PM

Depends on what you want to use the result for, just a check to see if the row was inserted for example bool is fine. However if you needed to check how many rows were updated, then use mysql_affected_rows.

For example, if you want to update all rows with id's between 8 and 9, but the table only holds 4 records, (thus only has id's 1-4) mysql_query will return true (i.e. mysql didnt explode), but mysql_num_rows will return 0.

In short, it depends on what you want to check for.

allworknoplay · 05-15-2009, 09:27 PM

Quote:

Originally Posted by sketchMedia

Depends on what you want to use the result for, just a check to see if the row was inserted for example bool is fine. However if you needed to check how many rows were updated, then use mysql_affected_rows.

Also if you want to update all rows with id's between 8 and 9, but the table only holds 4 records, thus only has id's 1-4, mysql_query will return true (i.e. mysql didnt explode), but mysql_num_rows will return 0.

In short, it depends on what you want to check for.

Good FYI, thanks...

CoryMathews · 05-15-2009, 11:55 PM

What is the point of the model class? I am having trouble seeing why you would want to use that extra layer.

corey · 05-16-2009, 04:01 AM

the model class just makes it easier to work with one table. You can add specific functions for that table if you'd like.
Look up PHP models for more information about it.

adham is me · 05-16-2009, 04:18 AM

thanks for help