MySQL Database Utility Methods in PHP

PHP

Public Domain

Download (right click, save as, rename as appropriate)

Embed

Tags:

database mysql
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
<?php

class Database
{
    private $mysqli;
    private static $instance;
    
    private static $cache = array();
    
    // Database connectivity.
    const DB_SERVER = "server";
    const DB_DATABASE = "database";
    const DB_USERNAME = "username";
    const DB_PASSWORD = "password";
    
    private function __construct()
    {
        $mysqli = new mysqli_Extended(self::DB_SERVER, self::DB_USERNAME, self::DB_PASSWORD, self::DB_DATABASE);
        
        // HACK: PHP <= 5.2.9 require this. See example #1 on
        // http://us3.php.net/manual/en/mysqli.connect.php
        if (mysqli_connect_error())
        {
            throw new Exception("Could not connect to the database.");
        }

        $this->mysqli = $mysqli;
    }
    public function __destruct()
    {
        $this->mysqli->close();
    }
    
    private static function Instance()
    {
        if (!isset(self::$instance))
        {
            self::$instance = new Database();
        }

        return self::$instance;
    }
    
    private static function GetParametrizedQueryResult($query, $params)
    {
        $statement = self::Instance()->mysqli->prepare($query);
        if ($statement === false)
        {
            throw new Exception("Could not prepare the query \"$query\".");
        }
        
        $types = "";
        foreach ($params as $param)
        {
            if (is_float($param))
            {
                $types .= "d";
            }
            else if (is_int($param) || is_bool($param))
            {
                $types .= "i";
            }
            else if (is_string($param) || $param === null)
            {
                $types .= "s";
            }
            else
            {
                throw new Exception("One of the given parameters was not a float, int, bool, or string.");
            }
        }
        
        call_user_func_array('mysqli_stmt_bind_param', array_merge(array($statement, $types), $params));
        if ($statement->execute() === false)
        {
            throw new Exception("Could not execute the query \"$query\".");
        }
        
        return $statement;
    }
    public static function ExecuteParametrizedQuery($query/*, ... */)
    {
        $params = array_slice(func_get_args(), 1);
        $statement = self::GetParametrizedQueryResult($query, $params);
        $statement->close();
    }
    public static function FetchRows($query/*, ... */)
    {
        $params = array_slice(func_get_args(), 1);
        $statement = self::GetParametrizedQueryResult($query, $params);
        
        $assocRows = array();
         while ($row = $statement->fetch_assoc())
         {
             $assocRows[] = $row;
         }

        $statement->close();
        return $assocRows;
    }
    public static function GetNumRows($query/*, ... */)
    {
        $params = array_slice(func_get_args(), 1);
        $statement = self::GetParametrizedQueryResult($query, $params);
        
        $statement->store_result();         // This is required before num_rows is valid, apparently.
        $numRows = $statement->num_rows;

        $statement->close();
        return $numRows;
    }
    public static function InsertRow($table, $assocRow)
    {
        $query = "INSERT INTO $table (" . implode(", ", array_keys($assocRow)) . ") VALUES (" . implode(", ", array_fill(0, count($assocRow), "?")) . ")";
        $statement = self::GetParametrizedQueryResult($query, array_values($assocRow));
        $statement->close();
    }
}

class mysqli_Extended extends mysqli
{
    public function __construct($dbHost, $dbUsername, $dbPassword, $dbDatabase)
    {
        parent::__construct($dbHost, $dbUsername, $dbPassword, $dbDatabase);
    }

    public function prepare($query)
    {
        return new stmt_Extended($this, $query);
    }
}

class stmt_Extended extends mysqli_stmt
{
    protected $varsBound = false;
    protected $results = array();

    public function __construct($link, $query)
    {
        parent::__construct($link, $query);
    }
    
    public function fetch_assoc()
    {
        // Checks to see if the variables have been bound; this is so that when
        // using a while ($row = $statement->fetch_assoc()) loop the following
        // code is only executed the first time.
        if (!$this->varsBound)
        {
            $meta = $this->result_metadata();
            while ($column = $meta->fetch_field())
            {
                // This is to stop a syntax error if a column name has a space in
                // e.g. "This Column". 'Typer85 at gmail dot com' pointed this out.
                $columnName = str_replace(' ', '_', $column->name);
                $bindVarArray[] = &$this->results[$columnName];
            }
            call_user_func_array(array($this, 'bind_result'), $bindVarArray);
            $this->varsBound = true;
        }

        $fetchResult = $this->fetch();
        if ($fetchResult === false)
        {
            throw new Exception("Could not fetch the result rows.");
        }
        else if ($fetchResult === null)
        {
            return false;
        }

        // This is a hack. The problem is that the array $this->results is full
        // of references not actual data, therefore when doing the following:
        //     while ($results[] = $statement->fetch_assoc());
        // $results[0], etc. were all references and pointed to the last dataset.
        foreach ($this->results as $k => $v)
        {
            $results[$k] = $v;
        }
        return $results;
    }
}