14, 'name'=>'Paul')); //changes name of customer 14 to Paul
//SuperDatabase ('customers', '', array ('id'=>14, 'name'=>'Paul')); //same as above
//SuperDatabase ('customers', 14, array ('name'=>'Paul')); //same as above
//SuperDatabase ('customers', 'status=1', array ('status'=>2)); //changes all customers with status 1 to status 2
//$customerid = SuperDatabase ('customers', array ('name'=>'Paul')); //inserting a new customer
function SuperDatabase ($table, $where='', $order='', $debug=false, $idcol='id', $datecol='createddate') {
$error = ''; $return = ''; //store the error and what will be returned
if ($debugemail = strpos ($debug, '@') ? $debug : '') $debug = false; //debug email and debug
if (strpos ($debug, '.')) $debug = $_SERVER['REMOTE_ADDR'] == $debug; //only for a certain IP address
//For inserts and updates, the data could be passed in as the second argument
if (is_array ($where)) {$order = $where; $where = '';}
//First do a couple checks for SQL injection
$check = $where; if (is_string ($order)) $check .= " $order"; //check the where and order by columns
if (preg_match ('/\bUNION\b|\bJOIN\b|1=1/i', $check)) return null; //attempting some SQL injection so deny them
if (substr_count ($check, "'") % 2 == 1) return null; //something with an uneven number of quotes, could be SQL injection
//if (preg_match ("/^[^=]*'/", $check)) return null; //a quote before a =, could be SQL injection, or could be a LIKE, so disabled this check
//Form the where condition if an id number was passed in instead of a string
if (preg_match ('/^\d+$/', trim ($where))) { //a number was passed in for the where condition, which can be 0
if (!is_null ($order) && !is_array ($order)) $order = 1; //limit to returning one result
$where = "$idcol='" . intval ($where) . "'"; //lookup by id
}
//Do a delete if null was passed in for the data
if (is_null ($order)) {
$sql = "DELETE FROM $table WHERE $where";
if ($debug) echo "
About to delete from the table $table: $sql
";
if (!mysql_query ($sql)) $error = "Error running $sql to delete data from $table: " . mysql_error();
$return = null; //return a null
}
//Do an insert or update if an array was passed in
else if (is_array ($order)) {
$data = $order; //rename the variable so it's easier to understand
$sql = "SHOW COLUMNS FROM $table"; //get the columns from the table
if ($debug) echo "
Getting all the columns from the table $table: $sql ";
if (!($results = mysql_query ($sql))) $error = "The table $table doesn't exist or doesn't have any columns."; //tables doesn't exist
else { //there are columns in the table
$allcols = array(); while ($row = mysql_fetch_array ($results)) $allcols[$row['Field']] = $row['Type']; //get the columns
$dataid = isset ($data[$idcol]) && intval ($data[$idcol]) > 0 ? intval ($data[$idcol]) : 0; //get the id number of the data
if ($dataid) $where = "$idcol='$dataid'"; //if the data contained an id number, then override the $where to just update this row
$changes = array(); foreach ($data as $col=>$value) { //figure out the changes which need to be made
if (preg_match ('/_removefile$/', $col)) {$col = substr ($col, 0, -11); $value = '';} //clear reference to uploaded file
if (!isset ($allcols[$col])) continue; //this column is not in this table
else if ($col == $idcol) continue; //this is the id column
else if (preg_match ('/^\S+\(/', $value)) {$changes[$col] = $value; continue;} //pass straight through as it's a function
else if ($allcols[$col] == "date") $value = date ('Y-m-d', strtotime ($value)); //it's a date
else if ($allcols[$col] == "datetime") $value = date ('Y-m-d H:i:s', strtotime ($value)); //it's a date time
if (!preg_match ("/^'|\\\\\\\\'|[^\\\\]'/", $value)) $value = stripSlashes ($value); //strip extra slashes
$value = function_exists ("mysql_real_escape_string") ? mysql_real_escape_string ($value) : addSlashes ($value); //escape
$changes[$col] = "'$value'"; //put this change into an array
} //for each change to the data
if (!$changes) $error = "There is no data to insert or update for the table $table."; //nothing to do
else { //data to insert/update
if (!$where && $datecol && isset ($allcols[$datecol])) $changes[$datecol] = 'NOW()'; //record the date the row was created
$sql = $where ? 'UPDATE' : 'INSERT INTO'; //is this an insert or an update
$sql .= " $table SET "; foreach ($changes as $col=>$value) $sql .= "$col=$value, "; //update each column
$sql = substr ($sql, 0, -2); //remove the last comma and space
if ($where) $sql .= " WHERE $where"; //add the where condition
if ($debug) echo "Insert/updating row $dataid in the table $table: $sql ";
if (!mysql_query ($sql)) $error = "Error running $sql to insert/update row $dataid in $table: " . mysql_error();
else { //no error running the statement
if (!$where) $dataid = mysql_insert_id(); //if this was an insert, then get the id number inserted
//add any uploaded file data to the incoming data too if a "filepath" is passed in with the incoming data
if ($dataid>0 && isset ($data['filepath']) && isset ($_FILES) && $_FILES) {
$path = realpath ($data['filepath']); //see if we can find the path
if (!$path) $path = realpath ($_SERVER['DOCUMENT_ROOT'] . '/' . $data['filepath']); //or if absolute
if ($path && is_dir ($path)) { //the path exists for uploading files
$path = preg_replace ('~/$~', '', $path); //take off a trailing /
foreach ($_FILES as $findex=>$fdata) { //for each of the uploaded files
if (!$fdata['tmp_name']) continue; //nothing to upload
if (!isset ($allcols[$findex])) continue; //this column is not in the table
if (preg_match ('/php|inc$/', $fdata['name'])) continue; //no PHP files allowed
$fdir = $path . '/' . $table . '-' . sprintf ("%06d", $dataid) . '-' . $findex . '/';
if ($debug) echo "
Trying to upload $findex file $fdata[name] to $fdir ";
if (!is_dir ($fdir)) mkdir ($fdir); //make the directory to store the file
if (!is_dir ($fdir)) continue; //can't make the directory for putting the file into
$fname = strtolower (preg_replace ('/[^\w\d\.]+/', '-', $fdata['name'])); //clean up name
if (function_exists ('glob')) foreach (glob ("$fdir/*") as $ffile) unlink ($ffile);
if (!$fname) continue; //no name, so don't move the file or save to the database
move_uploaded_file ($fdata['tmp_name'], $fdir . $fname); //move the uploaded file
if (!file_exists ($fdir . $fname)) continue; //file was not moved successfully into place
$fdir = substr ($fdir, strlen ($_SERVER['DOCUMENT_ROOT'])); //remove doc root before saving
$sql = "UPDATE $table SET $findex='$fdir$fname' WHERE $idcol='$dataid'";
if ($debug) echo "File saved, about to save file location to database: $sql ";
if (!mysql_query ($sql)) $error = "Error running $sql to save file location";
} //for each uploaded files
}
else $error = "Can't find the directory $data[filepath] for uploading files";
}
if ($debug) echo "Rows affected: " . mysql_affected_rows() . "
";
$return = $dataid ? $dataid : null; //return the id number inserted/updated or else null for multiple updates
} //no error running the statement
} //data to insert/udpate
} //table exists and has columns
}
//Now deal with a normal SELECT
else {
$cols = '*'; if (preg_match ('/^(.*) FROM (.*)$/i', $table, $tablem)) {$cols = $tablem[1]; $table = $tablem[2];} //so cols can be passed in
$sql = "SELECT $cols FROM $table"; //basic SQL
if (preg_match ('/^\s*GROUP\s+BY/i', $where)) $sql .= " $where"; //it's only a GROUP BY so don't put the word WHERE
else if ($where) $sql .= " WHERE $where"; //get all the columns from the table
if (intval ($order) > 0) $sql .= ' LIMIT ' . intval ($order); //limit to this many rows
else if (strlen ($order) > 0) $sql .= " ORDER BY $order"; //use as an order by
if ($debug) {echo "
About to select: $sql "; list($tu,$ts)=explode(' ',microtime()); $timer=(float)$tu+(float)$ts;}
if (!($results = mysql_query ($sql))) $error = "Error running $sql to select data from $table: " . mysql_error();
else { //not an error selecting data
if ($debug) {echo "Rows found: " . mysql_num_rows ($results) . ", took: "; list($tu,$ts)=explode(' ',microtime()); $timer-=((float)$tu+(float)$ts); echo round(-$timer*1000)/1000 . " seconds
";}
$alldata = array(); while (($data = mysql_fetch_array ($results, MYSQL_ASSOC)) !== false) { //loop through the data
foreach ($data as $key=>$val) //for each column
if (preg_match ('/date$/', $key)) //loop through the data making dates look nice
//the date format used here, has to be translatable by strtotime in the insert/update part above
$data[$key] = $val ? date ('j M Y' . (preg_match('/\d\d:\d\d/',$val)?' H:i':''), strtotime ($val)) : '';
array_push ($alldata, $data); //get the results
} //looping through the data
if (!$alldata) $return = array(); //there is no data to return, so return a blank array
else if ($order == 1) { //they only need 1 row of data, so add the verification string and return the first row
$alldata[0]['verification'] = strtr (substr (crypt (base_convert (intval (($data[$idcol]+17.35)*378.23 + strtotime ($data[$datecol]) / 12.15), 10, 36), 'pP'), 2, 8), '/.=', 'Ua3'); //a verification string
$return = $alldata[0];
}
else $return = $alldata; //return all the data
} //no error selecting data
} //select the data
//If there was an error
if ($error && $debugemail) { //if there was a debugging email address, then use it to send an error
$subject = 'Database error on ' . $_SERVER['HTTP_HOST'];
$message = "Database error at " . date ('j F Y H:i') . ":\n$error";
$message .= "\n\nBACK TRACE: " . print_r (debug_backtrace(), true);
$message .= "\n\nSERVER: " . print_r ($_SERVER, true);
$message .= "\n\nGET: " . print_r ($_GET, true);
$message .= "\n\nPOST: " . print_r ($_POST, true);
global $ALREADYSENT; if ($ALREADYSENT++ < 3) mail ($debugemail, $subject, $message); //so it doesn't send loads and loads and loads of them
}
else if ($error && (error_reporting() & E_NOTICE)) die ("
$error
"); //if they are displaying all errors
else if ($error && error_reporting()) echo "
$error
"; //if they are displaying any errors
//Return a null if there was an error or else the return value from above
return $error ? null : $return;
}
//Get a tree of data from a database
function SuperDatabaseTree ($table, $order='', $parentid=0, $filter='', $depth=0, $maxdepth=5, $idcol='id', $parentcol='parentid') {
if ($depth >= $maxdepth) return array(); //we've gone too deep
if ($aslist = (strpos ($parentid, ':') ? $parentid : '')) $parentid = 0; //$parentid can be passed in as $namecol:$selectedid:$numspaces
$where = "$parentcol=" . intval ($parentid) . ($filter ? (preg_match ('/^\s*GROUP\s+BY/i', $filter) ? ' ' : ' AND ') . $filter : '');
$rows = array(); //what should be returned
foreach (SuperDatabase ($table, $where, $order) as $row) { //for each of the child rows in the database
$row['rowdepth'] = $depth; //current depth
$row['rowbefore'] = '
'; //start an li tag before each row
$row['rowafter'] = "\n"; //this goes after each row, no close
tags or else it closes too early
array_push ($rows, $row); //add this row to the things to be returend
$rows = array_merge ($rows, SuperDatabaseTree ($table, $order, $row[$idcol], $filter, $depth+1, $maxdepth, $idcol, $parentcol));
}
if ($rows) {$rows[0]['rowbefore'] = '
\n";} //list tags
if ($aslist) { //if we want the results return as a list
$parts = explode (':', $aslist); $namecol = $parts[0]; $selectedid = $parts[1]; $numspaces = isset ($parts[2]) ? $parts[2] : 5;
$r = ''; foreach ($rows as $row) { //for each row we found
//if ($row[$parentcol] == $selectedid) continue; //don't show my kids, disabled as this may not be for my own kids
$r .= "\n" . '