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
|
<?php
/**
* Minimal wrappers for core PHP mysql_* functions.
* @package mirror
* @subpackage lib
*/
class DB {
private static $dbh;
private static $result;
/**
* Connect to a MySQL database server.
* @param string $host db server, defaults to localhost
* @param string $user db username
* @param string $password db password
* @return PDO dbh
*/
public static function connect($host='localhost',$user=null,$password=null,$database=null)
{
if (!empty($host) && isset($user) && isset($password)) {
$dsn = "mysql:host={$host}";
if(!empty($database)) $dsn .= ";dbname={$database}";
$options = [PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
static::$dbh = new PDO($dsn, $user, $password, $options);
}
if (static::$dbh instanceof PDO) {
return static::$dbh;
}
die("Unable to create database connection in DB::connect()");
}
/**
* Execute a MySQL query.
* @param string $qry MySQL query
*/
public static function query($qry=null, $parameters=[])
{
if(!(static::$dbh instanceof PDO)) static::$dbh = static::connect();
if(is_null($qry))
{
if(static::$result instanceof PDOStatement) return static::$result;
else return false;
}
if(!empty($parameters)) {
static::$result = static::$dbh->prepare($qry);
static::$result->execute($parameters);
}
else {
static::$result = static::$dbh->query($qry);
}
return static::$result;
}
/**
* Fetch a row as an array from a result.
* @param string $result (default to null)
* @return array
*/
public static function fetch($result=null,$type=PDO::FETCH_BOTH)
{
if (is_null($result) && static::$result instanceof PDOStatement)
$result = static::$result;
if (!$result instanceof PDOStatement)
throw new InvalidArgumentException("Fetch called before query issued");
return $result->fetch($type);
}
/**
* Fetch an array based on a query.
* @param string $query database query
* @param int $type result type
* @param string $col_id if passed it, the values of this column in the result set will be used as the array keys in the returned array
* @return array $list array of database rows
* Example of returned array:
* <code>
* DB::get("SELECT * FROM table",PDO::FETCH_ASSOC);
* returns...
* Array
* (
* [0] => Array
* (
* [id] => 1
* [field1] => data1
* [field2] => data2
* )
*
* )
* </code>
*/
public static function get($query,$type=PDO::FETCH_BOTH,$col_id=NULL,$parameters=[])
{
$res = static::query($query, $parameters);
$list = [];
if ($res instanceof PDOStatement && !is_null($col_id) && ($type === PDO::FETCH_BOTH || $type == PDO::FETCH_ASSOC) && $res->rowCount() !== 0) {
$col_test = static::fetch($res,$type);
if (array_key_exists($col_id,$col_test)) {
$list[$col_test[$col_id]] = $col_test;
while ( $buf = static::fetch($res,$type) ) {
$list[$buf[$col_id]] = $buf;
}
return $list;
}
}
if ($res instanceof PDOStatement) {
$list = $res->fetchAll($type);
}
return $list;
}
/**
* Since PHP's mysql_insert_id() sometimes throws an error, this is the replacement
* @param PDO $dbh optional dbh to get the last inserted id from
* @return int the return value of MySQL's last_insert_id()
*/
public static function insert_id($dbh=null)
{
if(!($dbh instanceof PDO)) $dbh = static::connect();
$buf = $dbh->lastInsertId();
return empty($buf) ? false : $buf;
}
/**
* Determine number of rows in result.
* @param PDOStatement $result mysql result
* @return int number of rows in query result
*/
function numrows($result=null)
{
if (is_null($result) && static::$result instanceof PDOStatement)
$result = static::$result;
if (!$result instanceof PDOStatement)
throw new InvalidArgumentException("numrows called before query issued");
return $result->rowCount();
}
/**
* Close the db connection. If a dbh is not specified, assume the last opened link.
* @param resource $dbh optional dbh to close
*/
public static function close($dbh=null)
{
return ($dbh instanceof PDO)?$dbh=null:static::$dbh=null;
}
/**
* Get one record.
* @param string $query query
* @param int $type result type
*/
public static function get_one($query,$type=PDO::FETCH_ASSOC,$parameters=[]) {
$buf = static::get($query.' LIMIT 1',$type,null,$parameters);
return $buf[0];
}
/**
* Get an ID based on name.
* @param string $table
* @param string $id_col
* @param string $name_col
* @param string $name
*/
public static function name_to_id($table,$id_col,$name_col,$name)
{
$buf = static::get_one("SELECT {$id_col} FROM {$table} WHERE {$name_col} = ?", PDO::FETCH_NUM, [$name]);
return $buf[0];
}
}
|
GitWeb