· 8 years ago · Sep 28, 2017, 10:38 PM
1<?php
2
3/* add your database login details to db::connect() */
4
5/* sample usage */
6
7/*
8$sql = "drop table if exists db_test";
9db::query($sql);
10echo "dropping table db_test\n";
11
12
13$sql = "create table db_test(id int auto_increment primary key, name varchar(100), number int)";
14db::query($sql);
15echo "<hr>creating table db_test\n";
16
17
18$sql = "insert into db_test(name, number) values (:name, :number)";
19$arr = array(":name" => "Hello", ":number" => 44);
20$id = db::query($sql, $arr);
21echo "<hr>sample insert, id: $id\n";
22
23
24$sql = "insert into db_test(name, number) values (:name, :number)";
25$arr = array(":name" => "World", ":number" => 55);
26$id = db::query($sql, $arr);
27echo "<hr>second insert, id: $id\n";
28
29
30$sql = "update db_test set number = :number";
31$arr = array(":number" => 77);
32$rows_affected = db::query($sql, $arr);
33echo "<hr>sample update, $rows_affected rows affected\n";
34
35
36$sql = "select * from db_test where name like :name";
37$arr = array(":name" => "%");
38$result = db::query($sql, $arr);
39echo "<hr>sample select returns an array of rows,<br>\n";
40if(count($result) == 0)
41 echo "no records found<br>.\n";
42foreach($result as $row){
43 echo htmlentities($row['id'] . ' ' . $row['name'] . ' ' . $row['number']) . "<br>\n";
44}
45
46
47$sql = "select * from db_test where id = :id";
48$arr = array(":id" => 1);
49$row = db::query_row($sql, $arr);
50echo "<hr>sample select using query_row() to return a single row array,<br>\n";
51if(!$row)
52 echo "row not found\n";
53else
54 echo "row found: " . htmlentities($row['id'] . ' ' . $row['name'] . ' ' . $row['number']) . "\n";
55
56
57$sql = "select name from db_test where id = :id";
58$arr = array(":id" => 1);
59$val = db::query_val($sql, $arr);
60echo "<hr>sample select using query_val() to return a single value,<br>\n";
61if(!$val)
62 echo "value not found\n";
63else
64 echo "value found: " . htmlentities($val) . "\n";
65
66
67db::connect();
68db::$dbh->beginTransaction(); // speed up multi inserts with a transaction
69for($i = 0; $i < 1000; $i++){
70 $sql = "insert into db_test(name, number) values (:name, :number)";
71 $arr = array(":name" => "Name $i", ":number" => $i);
72 db::query($sql, $arr);
73}
74db::$dbh->commit(); // commit transaction
75echo "<hr>inserted $i records in a transaction\n";
76
77
78$sql = "delete from db_test where number = :number or 1=1";
79$arr = array(":number" => 66);
80$rows_affected = db::query($sql, $arr);
81echo "<hr>sample delete, $rows_affected rows affected\n";
82
83*/
84
85class db{
86
87 // purpose: simple pdo wrapper, only tested on mysql
88
89 // configure settings in connect()
90 private static $db_connection_string = null;
91 private static $db_user = null;
92 private static $db_pass = null;
93 private static $display_error_detail = null;
94
95 public static $dbh = null;
96
97 public static function connect(){
98
99 if(self::$dbh)
100 return;
101
102 // *** edit this block according to how you detect your enviroment ***
103 if(getenv('ENV') == 'development'){
104 self::$db_connection_string = 'mysql:host=localhost;dbname=test;';
105 self::$db_user = 'root';
106 self::$db_pass = '';
107 self::$display_error_detail = true; // show full error in development
108 }
109 else{
110 self::$db_connection_string = 'mysql:host=localhost;dbname=test;';
111 self::$db_user = 'root';
112 self::$db_pass = '';
113 self::$display_error_detail = false; // hide errors in production, only "query error" is displayed
114 }
115
116 try{
117 self::$dbh = new PDO(self::$db_connection_string, self::$db_user, self::$db_pass);
118 }
119 catch(PDOException $e){
120 die('pdo connection error: ' . $e->getMessage());
121 }
122 }
123
124
125 public static function query($sql, $sql_param = array()){
126
127 // purpose: run a query
128 // returns:
129 // array of rows for selects
130 // false on errors
131 // id for insert statements
132 // # rows affected for updates and deletes
133
134 self::connect();
135 $sth = self::$dbh->prepare($sql);
136
137 // handle errors
138 if(!$sth->execute($sql_param)){
139 $arr = $sth->errorInfo();
140 $error = $arr[2];
141
142 if(self::$display_error_detail)
143 echo "<pre style='color: red;'>" . htmlentities("Error: $error\n\n$sql\n\n") . "Parameters:\n" . print_r($sql_param, true) . "</pre>";
144 else
145 echo "<pre style='color: red;'>query error</pre>";
146
147 return false;
148 }
149
150 // return insert id for insert and rows affected for update, delete, and replace
151 if(preg_match('/^\s*(insert|replace)/i', $sql))
152 return self::$dbh->lastInsertId();
153 elseif(preg_match('/^\s*(update|delete)/i', $sql))
154 return $sth->rowCount();
155
156 if($sth->columnCount() == 0)
157 return array();
158
159 return $sth->fetchAll(PDO::FETCH_ASSOC);
160
161 }
162
163
164 public static function query_row($sql, $sql_param = array()){
165
166 // purpose: wrapper for query()
167 // returns:
168 // a single row array
169 // returns null when no results are found
170 // returns false on errors
171
172 $result = self::query($sql, $sql_param);
173
174 if($result === false)
175 return false;
176 elseif(is_array($result) && count($result) > 0)
177 return current($result);
178 else
179 return null;
180 }
181
182
183 public static function query_val($sql, $sql_param = array()){
184
185 // purpose: wrapper for query()
186 // returns:
187 // a single value
188 // returns null when no results are found
189 // returns false on errors
190
191 $result = self::query($sql, $sql_param);
192
193 if($result === false)
194 return false;
195 elseif(is_array($result) && count($result) > 0)
196 return current(current($result));
197 else
198 return null;
199 }
200
201}