4
\$\begingroup\$

I am developing a mysqli database wrapper. I've made an effort to make this as fast as possible and easy to use.

However, now I want to start adding callbacks (for fetch), and make this mysqli-only dependent (still using get_result).

trait dbExtras // connection-independent tools
{
    public function echoQuery()
    {
        $args = func_get_args();
        if (count($args) == 1){
            echo (string) $args[0];
        } else {
            $query = $args[0];
            for($i=1; $i < count($args); $i++){
                $query = preg_replace('/[?]/', "'" . $args[$i] . "'" , $query, 1);
            }
            echo $query;
        }
    }
    public function toJSON($stmt)
    {
        $json = array();
        if ($stmt->num_rows > 0) {
            while($row = $stmt->fetch_array(MYSQL_ASSOC)) {$json[] = $row;}
        }
        return json_encode($json);
    }

    //fetch an array from executed SQL query. mode can also be numeric (MYSQLI_NUM) or both (MYSQLI_BOTH)
    public function fetch($stmt, $mode = MYSQLI_ASSOC)
    {
        return $stmt->fetch_array($mode);
    }


    public function fetchAll($stmt, $mode = MYSQLI_ASSOC) {
         return $stmt->fetch_all($mode);
    }
}

class database
{
    //database vars
    public $connection = null;

    use dbExtras; // include methods

    //  Methods Declaration
    public function __construct($host, $database, $username, $password)
    {
        $db = mysqli_connect($host,$username,$password,$database);
        //Check connection
        if($db->connect_errno > 0) {return false;}
        $this->connection = $db;
        // changed from utf8 to utf8mb4, reason: full int8n support
        $this->connection->set_charset("utf8mb4");
    }

    public function close()
    {
        if ($this->connection != null) {return $this->connection->close();}
    }

    function __destruct()
    {
        $this->close();
    }

    public function runQuery()
    {
        $this->ping();          // reconnects if connection is closed

        $args = func_get_args();
        $cnt = count($args);
        if ($cnt === 1){
            return $this->query($args[0]);
        } else {
            $types = '';
            $bind = mysqli_prepare($this->connection, $args[0]);
            for($i=1; $i < $cnt; $i++){
                switch (gettype($args[$i])) {
                    case 'NULL':
                    case 'string':
                        $types .= 's';
                        break;
                    case 'boolean':
                    case 'integer':
                        $types .= 'i';
                        break;
                    case 'blob':
                        $types .= 'b';
                        break;
                    case 'double':
                        $types .= 'd';
                        break;
                    default:
                        $types .= 's';
                        break;
                }
                $values[] = &$args[$i];
            }
            $params = array_merge(array($types), $values);
            unset($types);
            unset($values);
            // new method, saves 30 - 40 % of exec time. old was call_user_func_array(array($bind, "bind_param"), $params);
            $ref    = new ReflectionClass('mysqli_stmt'); 
            $method = $ref->getMethod("bind_param");
            $method->invokeArgs($bind,$params); 
            unset($params);
            if ($bind->execute()) {
                return $bind->get_result();
            } else {
                return false;
            }
        }
    }

    public function getLastInserted()
    {
        return $this->connection->insert_id;
    }

    public function setAutoCommit($bool = TRUE) // required before complex queries --use with addQuery() & commit()
    {
        $this->connection->autocommit($bool);
    } 

    public function query($sql)
    {
        return $this->connection->query($sql);
    }

    public function commit()
    {
        return $this->connection->commit();
    }

    public function escape($string) 
    {
        return $this->connection->real_escape_string($string);
    }

    public function getAffectedRows()
    {
        return mysqli_affected_rows($this->connection);
    }

    public function ping()
    {
        return mysqli_ping($this->connection);
    }

    public function getError()
    {
        return mysqli_error($this->connection);
    }
}

I also use a trait for ease of use in other classes. The main function is runQuery(), and it's the one I've spent most time developing.

I want to know how to make it perform better / more secure.

\$\endgroup\$
  • \$\begingroup\$ in toJSON function I think it should be MYSQLI_ASSOC not MYSQL_ASSOC \$\endgroup\$ – bumperbox Sep 11 '14 at 17:04
  • \$\begingroup\$ @bumperbox, haven't noticed that, will fix. \$\endgroup\$ – ruifn Sep 11 '14 at 17:51
  • 2
    \$\begingroup\$ The purpose of a wrapper is to abstract the raw API, and offer the user a clean API instead. Your class really doesn't do this. Where is the prepare method, for example. I want to be able to create a prepared statement, and use it as much as I want to use it. Your class simply does not allow me to do this. \$\endgroup\$ – Elias Van Ootegem Sep 12 '14 at 7:35
  • 1
    \$\begingroup\$ Phrancis calls it clean, and states 'I've seen a lot'. Not enough imo. This code isn't clean at all. There is no advantage in using this class compared to mysqli. As Elias pointed out it can even do less. So take a step back and ask yourself. What 'problem' am I really solving. \$\endgroup\$ – Pinoniq Sep 12 '14 at 15:12
  • \$\begingroup\$ @Pinoniq, I am solving the problem of preparing & binding variables. If you look closer, the prepare method is present. you can retrieve results like this: $database->runQuery("select * where a = ? and b = ?", $a, $b);. Whith this class, I save a lot of code in my php files. What features you think I should implement to make this more usable? Please make more constructive comments. \$\endgroup\$ – ruifn Sep 12 '14 at 15:57
1
\$\begingroup\$

About code-style: all classes/traits/interfaces must be Capitalized.

Some notes:

  • What do you mean writing database? Connection? Query? Don't mix them.
  • Models can't contain any methods with side-effects(like output), remember it!
  • Don't create superClassThatDoesAllTheWorkForTheApplication it's awful, also this is antipattern. Split tasks as it's shown in first paragraph.
  • Don't use getError method! Just throw an exception!
  • Don't use Reflection if you don't really need them! They are very slow.
  • Don't use for loop where you can use foreach. foreach is faster!
  • Never write code like this in construct method:

    if($db->connect_errno > 0) {return false;} You have exceptions for this!

  • Never write just wrappers for ready functional! If you write database abstraction do it in abstract way and firstly write interfaces. Code is always secondary
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.