3 # Copyright (C) 2006 Jason Woofenden
5 # This file is part of wfpl.
7 # wfpl is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU Lesser General Public License as published by the Free
9 # Software Foundation; either version 2.1 of the License, or (at your option)
12 # wfpl is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
14 # FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for
17 # You should have received a copy of the GNU Lesser General Public License
18 # along with wfpl; if not, write to the Free Software Foundation, Inc., 51
19 # Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
22 require_once('code/wfpl/encode.php');
23 require_once('code/wfpl/format.php');
25 # db_connect() -- connect to a mysql database
29 # database: the name of the database you want to connect to. Defaults to the
30 # second-to-last part of the domain name. eg for foo.example.com it would be
33 # user: username for connecting to the database. Defaults to
34 # $GLOBALS['db_username'] or (if that's not set) "www".
36 # password: password for connecting to the database. Defaults to
37 # $GLOBALS['db_password'] or (if that's not set "".
41 # the database connection handle. You'll only need this if you want to have
42 # multiple databases open at once.
44 function db_connect($database = 'auto', $user = 'auto', $pass = 'auto', $host = 'localhost') {
45 if($database == 'auto') {
46 if(isset($GLOBALS['db_name'])) {
47 $database = $GLOBALS['db_name'];
49 $host = $_SERVER['SERVER_NAME'];
50 $host = explode('.', $host);
52 $database = array_pop($host);
58 if(isset($GLOBALS['db_username'])) {
59 $user = $GLOBALS['db_username'];
66 if(isset($GLOBALS['db_password'])) {
67 $pass = $GLOBALS['db_password'];
73 $GLOBALS['wfpl_db_handle'] = mysql_connect($host, $user, $pass);
74 if(!$GLOBALS['wfpl_db_handle']) {
75 die('Could not connect to the database: ' . mysql_error());
78 if(!mysql_select_db($database, $GLOBALS['wfpl_db_handle'])) {
79 die("Couldn not access database \"$database\": " . mysql_error($GLOBALS['wfpl_db_handle']));
82 return $GLOBALS['wfpl_db_handle'];
85 # Unless you're doing something unusual like an ALTER TABLE don't call this directly
86 function db_send_query($sql) {
87 #echo("Sending query: " . enc_html($sql) . "<br>\n");
88 $result = mysql_query($sql, $GLOBALS['wfpl_db_handle']);
90 die(enc_html('DATABASE ERROR: ' . mysql_error($GLOBALS['wfpl_db_handle']) . ' in the following query: ' . $sql));
96 # All select queries use this to generate the where clause, so they can work
97 # like printf. Currently three % codes are supported:
99 # %% put a % in the output
100 # %i put an integer in the output (strips non-numeric digits, and puts in 0 if blank)
101 # %" output double quotes, surrounding the variable which is encoded to be in there.
102 # %s output encoded to be in double quotes, but don't output the quotes
104 # complex example: db_get_rows('mytable', 'id', 'where name=%" or company like "%%%s%%"', $name, $company_partial);
106 function db_printf($str) {
107 $args = func_get_args();
108 $args = array_slice($args, 1);
109 return _db_printf($str, $args);
112 # This function does the work, but takes the parameters in an array
113 function _db_printf($str, $args) {
114 $args = array_reverse($args); # because array_pop() takes from the end
117 $pos = strpos($str, '%');
118 if($pos === false) { # not found
122 # move everything up to (but not including) % to the output
123 $out .= substr($str, 0, $pos);
125 # grab the character after the %
126 $chr = substr($str, $pos + 1, 1);
128 # remove the stuff we've read from input
129 $str = substr($str, $pos + 2);
132 $out .= '"' . enc_sql(array_pop($args)) . '"';
133 } elseif($chr == 's') {
134 $out .= enc_sql(array_pop($args));
135 } elseif($chr == 'i') {
136 $int = format_int(array_pop($args));
137 if($int == '') $int = '0';
148 function db_send_get($table, $columns, $where, $args) {
149 $sql = "SELECT $columns FROM $table";
151 $sql .= ' ' . _db_printf($where, $args);
154 return db_send_query($sql);
158 function db_get_rows($table, $columns, $where = '') {
159 $args = func_get_args();
160 $args = array_slice($args, 3);
161 $result = db_send_get($table, $columns, $where, $args);
164 while($row = mysql_fetch_row($result)) {
168 mysql_free_result($result);
173 function db_get_column($table, $columns, $where = '') {
174 $args = func_get_args();
175 $args = array_slice($args, 3);
176 $result = db_send_get($table, $columns, $where, $args);
179 while($row = mysql_fetch_row($result)) {
183 mysql_free_result($result);
188 function db_get_row($table, $columns, $where = '') {
189 $args = func_get_args();
190 $args = array_slice($args, 3);
191 $result = db_send_get($table, $columns, $where, $args);
193 $row = mysql_fetch_row($result);
195 mysql_free_result($result);
200 function db_get_value($table, $columns, $where = '') {
201 $args = func_get_args();
202 $args = array_slice($args, 3);
203 $result = db_send_get($table, $columns, $where, $args);
205 $value = mysql_fetch_row($result);
206 if($value !== false) {
210 mysql_free_result($result);
215 function db_count($table, $where = '') {
216 return db_get_value($table, 'count(*)', $where);
219 # call either of these ways:
221 # db_insert('people', 'name,company', 'jason', 'widgets ltd');
223 # db_insert('people', 'name,company', array('jason', 'widgets ltd'));
224 function db_insert($table, $columns, $values) {
225 if(!is_array($values)) {
226 $values = func_get_args();
227 $values = array_slice($values, 2);
230 db_insert_ish('INSERT', $table, $columns, $values);
232 # same as above, except uses the "replace" command instead of "insert"
233 function db_replace($table, $columns, $values) {
234 if(!is_array($values)) {
235 $values = func_get_args();
236 $values = array_slice($values, 2);
239 db_insert_ish('REPLACE', $table, $columns, $values);
242 # return the value mysql made up for the auto_increment field (for the last insert)
243 function db_auto_id() {
244 return mysql_insert_id($GLOBALS['wfpl_db_handle']);
248 # used to implement db_insert() and db_replace()
249 function db_insert_ish($command, $table, $columns, $values) {
252 foreach($values as $value) {
253 if($sql) $sql .= ',';
254 $sql .= '"' . enc_sql($value) . '"';
257 $sql = "$command INTO $table ($columns) values($sql)";
262 # to be consistant with the syntax of the other db functions, $values can be an
263 # array, a single value, or multiple parameters.
265 # as usual the where clause stuff is optional, but it will ofcourse update the
266 # whole table if you leave it off.
270 # # name everybody Bruce
271 # db_update('users', 'name', 'Bruce');
273 # # name user #6 Bruce
274 # db_update('users', 'name', 'Bruce', 'where id=%i', 6);
276 # # update the whole bit for user #6
277 # db_update('users', 'name,email,description', 'Bruce', 'bruce@example.com', 'is a cool guy', 'where id=%i', 6);
279 # # update the whole bit for user #6 (passing data as an array)
280 # $data = array('Bruce', 'bruce@example.com', 'is a cool guy');
281 # db_update('users', 'name,email,description', $data, 'where id=%i', 6);
283 # The prototype is really something like this:
284 # db_update(table, columns, values..., where(optional), where_args...(optional))
285 function db_update($table, $columns, $values) {
286 $args = func_get_args();
287 $args = array_slice($args, 2);
288 $columns = explode(',', $columns);
289 $num_fields = count($columns);
291 if(is_array($values)) {
292 $args = array_slice($args, 1);
294 $values = array_slice($args, 0, $num_fields);
295 $args = array_slice($args, $num_fields);
299 for($i = 0; $i < $num_fields; ++$i) {
303 $sql .= $columns[$i] . ' = "' . enc_sql($values[$i]) . '"';
307 $sql = "UPDATE $table SET $sql";
309 # if there's any more arguments
312 $args = array_slice($args, 1);
315 # any left for where claus arguments?
317 $sql .= _db_printf($where, $args);
327 # pass args for printf-style where clause as usual
328 function db_delete($table, $where = '') {
329 $sql = "DELETE FROM $table";
332 $args = func_get_args();
333 $args = array_slice($args, 2);
335 $sql .= _db_printf($where, $args);