PDO CRUD

[Squidge]

Hi all,

I am putting together a CRUD. I have read through tutorials, and references.

Just wanted to see if i was on the right track:

<?php

// root -> /classLib/CRUD/CRUD

spl_autoload_extensions(".php");
spl_autoload_register();

use classLib\database\database_Connection as DB;

class CRUD {

private $id;   
#__construct to be extended  

public function __construct($id=null){

    $this->id     = $id;   
    #__construct to be extended   
}

public function save()
{
    try{
        $sql = "";
        $q = DB::get()->prepare($sql);
        $q->execute(array());
        }

    catch (Exception $e){
        print "Error: " . $e->getMeasage();
    }
}

public function update()
{

}

public function delete()
{
    try{

        $sql = "DELETE FROM [table to be clarified] WHERE id=:id";
        $q = DB::get()->prepare($sql);
        $q->bindValue(':id', $this->id, PDO::PARAM_INT);
        $q->execute();
    } 
    catch (Exception $e){
        print "Error!: " . $e->getMessage();
    }
}
// End of class
}
?>

[diafol]

Have a look at some existing PDO wrappers, there are many out there. You code is pretty incomplete, so its difficult to say if it'll be functional. You have the $sql var, but I can't see how it's being passed to your methods.

In addition, simple CRUDs are usually just that - simple. Building a flexible crud is quite a challenge as you need to be able to build queries from clauses, so IMO, each CRUD method should accept clause arguments or at least there should be a way to access class variables that hold this data.

In particular, the class (again IMO), should be as general as possible. It should not be coupled to other classes or your DB schema as this will lead to maintenance and re-use problems later on if you decide to change the DB/classes or use it in a future project.

There's an article that I found interesting - the use of chained methods for binding parameters. There's no reason why this approach can't be used with clauses too: http://stackoverflow.com/questions/6740153/simple-pdo-wrapper

[pritaeas]

Depending on what you want to build, my code snippet may get you some ideas.

[veedeoo]

Here are the wrappers I found One and two. You can either use them or use them as reference.

I wrote an extended version of ricocheting, but it will only work on frameworks and template engine queries.

If you will be following ricocheting's wrappers, this wrapper is written in Singleton pattern. If you will be using an instance manager instead of auto-loader, you must remove the singleton objects.

In an occasion where you still want to go ahead and write your own wrapper, here are my suggestions.

1. wrapper should have a method that can work on all of PDO objects. For example, you may want to create an object within your class that will handle a requests from specific class of an application.

Example codes ..WARNING! NOT TESTED ... just giving you an idea.

public function bind_to_class($thisClass){
## this is an array output
return (self::fetchAll(PDO::FETCH_CLASS,$thisClass));
}

Alternatively, you can write the same method with query option something like this. Warning! This is an skeleton method and will not work until the proper external objects are implemented and defined.

public function bind_to_class($thisClass, $thisQuery){
## just like the first example this will return the result as array 
$fetchThis = self::query($thisQuery, PDO::FETCH_CLASS, $thisClass);

    while($row = $fetchThis->fetch()) {
     $out[] = $row;
    }
    ## this will deliver the output to the outside
    return $out;
    }

1. Wrapper should be able to return affected row count.
2. Wrapper should be able to return single item query result.
3. Wrapper should have a secondary sanitization mechanism built as an object.
4. The rest you must recreate from all of the links we have provided you..

[Squidge]

Thank you all for replying back, it is greatly appreciated. I have come across the code below, and would like to know your input:

class DB {

    private $dbh;
    private $stmt;

    public function __construct($user, $pass, $dbname) {
        $this->dbh = new PDO(
            "mysql:host=localhost;dbname=$dbname",
            $user,
            $pass,
            array( PDO::ATTR_PERSISTENT => true )
        );
    }

    public function query($query) {
        $this->stmt = $this->dbh->prepare($query);
        return $this;
    }

    public function bind($pos, $value, $type = null) {

        if( is_null($type) ) {
            switch( true ) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }

        $this->stmt->bindValue($pos, $value, $type);
        return $this;
    }

    public function execute() {
        return $this->stmt->execute();
    }

    public function resultset() {
        $this->execute();
        return $this->stmt->fetchAll();
    }

    public function single() {
        $this->execute();
        return $this->stmt->fetch();
    }
}

The overall DB class i have in a namespace teh teh __construct would be removed/edited

[diafol]

I like the bind method - it saves having to produce an array. Although, an array can sometimes be useful, especially if you have many substitutions.

$bind = array(':bind1'=>'foo1'... ':bind25'=>'foo25');

I'd rather write out an array than call a method 25 times.

public function arrayBind($arrBind) {
    $this->stmt->bind($arrBind);
    return $this;
}

Of course, you'd need to validate your data. But for the client, this may be invisible = cleaner code.