PHP OOP saving, getting class properties to database

Question

I'm trying to learn oop. I'm working with PHP-MySQL. And I have troubles about database jobs with oop way (save, update, get etc.).

Let me explain it with an example project.

Lets say I want to make a site with multiple user types. I have a single database table with an enum "type" field. And I made classes like these:

abstract class User {
 //common properties, functions etc.. like id, username.
}

class Admin extends User {
 protected $type = "ADMIN";
 //I want here to have admin specific fields, functions etc...
}

...and some other user types like that. Here is the thing. I want a common class that can save and update objects into database. What's the way to do that? I'll make an object like $user = new User(); bla.. bla.. and I'll say "Save this user" but how? Do I have to make functions for each of my classes that have specific SQL statements like "INSERT INTO table(name, pass, etc) VALUES ('name', 'pass', etc)"?

Another point is I want a common factory class that returns me an object. An example I'll say "Get me the user which have this id and instantiate it with admin class if that user is an admin or the other classes like that".

And I need some help about "how to instantiate like mysqli_fetch_assoc() result with objects". That returns an array. Do I need to do like "$object->setId(returned_array["id"])"?

I've looked some books like PHP in Action, PHP Objects, Patterns and Practice but couldn't find this database specific topics. I hope I could explained it well and sorry for my bad English :)

Answer 1

I think you need an ORM framework. It's hard to create a good one on your own but you can find a few existing frameworks. Be carefulf do not use a framework with active record pattern becouse it's an antipattern.

To fetch objects: http://www.php.net/manual/en/mysqli-result.fetch-object.php

But I also recommend you to use mysqli in OO way:

$resource = new mysqli(/* ... */);
$resource->fetch_object(/* ... */)

Answer 2

Here is an example PDO CRUD Class & example usage, hope it points you in the right direction:

<?php
/*** a new crud object ***/
$crud = new crud();

/*** The DSN ***/
$crud->dsn = "mysql:dbname=yourDB;host=localhost";

/*** MySQL username and password ***/
$crud->username = 'username';
$crud->password = 'password';


/*** array of values to insert ***/
$values = array(array('user'=>'bob', 'some_colum'=>'somevalue'));
/*** insert the array of values ***/
$crud->dbInsert('users', $values);

/*** select all records from table ***/
$records = $crud->rawSelect('SELECT * FROM users');

/*** fetch only associative array of values ***/
$rows = $records->fetchAll(PDO::FETCH_ASSOC);

/*** example display the records ***/
foreach($rows as $row){
    foreach($row as $fieldname=>$value){
        echo $fieldname.' = '.$value.'<br />';
    }
}

/*** update the user ***/
$crud->dbUpdate('users', 'user', 'bobs_new', 'id', 3);

/*** get the 3rd record ***/
$res = $crud->dbSelect('users', 'id', 3 );

/*** show the results ***/
foreach($res as $row){
    echo $row['user'].' = '.$row['some_colum'].'<br />';
}



class crud{

    private $db;
    /**
     * Set variables
     */
    public function __set($name, $value)
    {
        switch($name)
        {
            case 'username':
                $this->username = $value;
                break;

            case 'password':
                $this->password = $value;
                break;

            case 'dsn':
                $this->dsn = $value;
                break;

            default:
                throw new Exception("$name is invalid");
        }
    }

    /**
     * @check variables have default value
     */
    public function __isset($name){
        switch($name)
        {
            case 'username':
                $this->username = null;
                break;

            case 'password':
                $this->password = null;
                break;
        }
    }

    /**
     * @Connect to the database and set the error mode to Exception
     * @Throws PDOException on failure
     */
    public function conn(){
        isset($this->username);
        isset($this->password);
        if (!$this->db instanceof PDO)
        {
            $this->db = new PDO($this->dsn, $this->username, $this->password);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
    }


    /**
    * @select values from table
    * @access public
    * @param string $table The name of the table
    * @param string $fieldname
    * @param string $id
    * @return array on success or throw PDOException on failure
    */
    public function dbSelect($table, $fieldname=null, $id=null){
        $this->conn();
        $sql = "SELECT * FROM `$table` WHERE `$fieldname`=:id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }


    /**
     * @execute a raw query
     * @access public
     * @param string $sql
     * @return array
     */
    public function rawSelect($sql){
        $this->conn();
        return $this->db->query($sql);
    }

    /**
     * @run a raw query
     * @param string The query to run
     */
    public function rawQuery($sql){
        $this->conn();
        $this->db->query($sql);
    }


    /**
     * @Insert a value into a table
     * @acces public
     * @param string $table
     * @param array $values
     * @return int The last Insert Id on success or throw PDOexeption on failure
     */
    public function dbInsert($table, $values){
        $this->conn();
        /*** snarg the field names from the first array member ***/
        $fieldnames = array_keys($values[0]);
        /*** now build the query ***/
        $size = sizeof($fieldnames);
        $i = 1;
        $sql = "INSERT INTO $table";
        /*** set the field names ***/
        $fields = '( ' . implode(' ,', $fieldnames) . ' )';
        /*** set the placeholders ***/
        $bound = '(:' . implode(', :', $fieldnames) . ' )';
        /*** put the query together ***/
        $sql .= $fields.' VALUES '.$bound;

        /*** prepare and execute ***/
        $stmt = $this->db->prepare($sql);
        foreach($values as $vals)
        {
            $stmt->execute($vals);
        }
    }

    /**
     * @Update a value in a table
     * @access public
     * @param string $table
     * @param string $fieldname, The field to be updated
     * @param string $value The new value
     * @param string $pk The primary key
     * @param string $id The id
     * @throws PDOException on failure
    */
    public function dbUpdate($table, $fieldname, $value, $pk, $id){
        $this->conn();
        $sql = "UPDATE `$table` SET `$fieldname`='{$value}' WHERE `$pk` = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_STR);
        $stmt->execute();
    }


    /**
     * @Delete a record from a table
     * @access public
     * @param string $table
     * @param string $fieldname
     * @param string $id
     * @throws PDOexception on failure
     * */
    public function dbDelete($table, $fieldname, $id){
        $this->conn();
        $sql = "DELETE FROM `$table` WHERE `$fieldname` = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_STR);
        $stmt->execute();
    }
}

?>