tero.co.uk

Super Database Function

This is a simple super PHP database function for selecting, inserting, updating and deleting data from a MySQL database. It is very easy to use, and can even save uploaded files and email you if there is an error.

Contents

Quick Examples

Here are a few quick examples of what you can do to select, insert, update and delete a customer row. The most impressive one is in bold - a single line to save a form into a database table.

//Select the customer with id number 14
$customer = SuperDatabase ('customers', 14);
//Select all customers with the given status, ordered by name
$customers = SuperDatabase ('customers', 'status=2', 'name');
//Output the name of each customer
foreach ($customers as $customer) echo $customer['name'] . '<br/>';
//Insert or update a customer using the posted data, returning the id number
$customerid = SuperDatabase ('customers', $_POST);
//Delete customer number 14
SuperDatabase ('customers', 14, null);

Download, connect and include

To use SuperDatasase, first download the file and save as superdatabase.php on your web server. Then you must connect to the MySQL database before using the SuperDatabase function. Most PHP sites which use a database do this in a single file which is included by all other pages. You will need to use the PHP functions mysql_connect and mysql_select_db to do this, and then include or copy the SuperDatabase function. Then you're ready to go!

//Connect to the MySQL database server with the host name, user name and password
mysql_connect ('localhost', 'dbuser', 'dbpassword');
//Choose the database you will be using
mysql_select_db ('dbname');
//Include the super database file
include ('superdatabase.php');

Arguments

SuperDatabase takes 6 arguments, though you'll usually only need to use the first 3:

$table: the table to use

This is the table to use for SELECTs, INSERTs, UPDATEs and DELETEs. $table is the only required argument to the SuperDatabase function. By default, a SELECT statement will look up all columns (eg SELECT * FROM customers). If you need more flexibility, you can pass in columns and joins as part of the $table. Such as:

//Select all customers in no particular order
$customers = SuperDatabase ('customers');
//Select only the nane and city
$customers = SuperDatabase ('name, city FROM customers');
//Join to another table and select all data
$customers = SuperDatabase ('customers LEFT JOIN orders ON orders.customerid=customers.id');

$where: the where condition

This is the WHERE condition of the database statement. If you pass in a number, then SuperDatabase assumes it is an id number, and will return only the specified row. If you need to do a GROUP BY, then add it to the end of the $where condition. (Note that if you pass in an array, then the array is used for the next $data argument instead.)

//Filter customers by some condition
$customers = SuperDatabase ('customers', "city='Brighton'");
//Select a customer by id number and return just that customer
$customer = SuperDatabase ('customers', 12);
//If you pass in 0 as the id number, then it will not find a customer and will return null
$customer = SuperDatabase ('customers', 0);
//So if you want to look up a certain customer based on the URL, be sure to use intval
$customer = SuperDatabase ('customers', intval ($_GET['id']));
//Or else if $_GET['id'] is not set, then it will return all customers
$customers = SuperDatabase ('customers', $_GET['id']);
//Grouping data by adding a GROUP BY after the WHERE condition
$customers = SuperDatabase ('customers', 'status>0 GROUP BY city');
//Or just pass in the GROUP BY directly
$customers = SuperDatabase ('customers', 'GROUP BY city');

$order or $data: for ordering SELECT statements or the data for an INSERT or UPDATE

The third argument to SupderDatabase is used differently depending on the type of variable you pass in. If you pass in a string, it becomes the ORDER BY for a SELECT statement. If you pass in a number, it will be the LIMIT for a SELECT statement, and if you pass in the number 1, then only one row will be returned from the database (and a verification value will be computed, see $datecol below). If you want an ORDER BY and a LIMIT, you must combine them.

//Return customers ordered by name
$customers = SuperDatabase ('customers', '', 'lastname');
//Return a single customer by email address
$customer = SuperDatabase ('customers', "email='me@test.com'", 1);
//Order and limit the results
$customers = SuperDatabase ('customers', '', 'lastname LIMIT 10');

If you pass in an array, then it will be used in an INSERT (if there is no WHERE condition or id number in the array) or UPDATE statement. The keys of the array must match the column names of the database table, or else the data will be ignored. If the array contains an id number, it forces an UPDATE rather than INSERT. For INSERTs and single row UPDATEs, SuperDatabase will return the id number. For multiple row UPDATEs it returns null. Note that if there is no WHERE condition, you can pass the data into the second argument instead. SuperDatabase handles all quoting and escaping itself, converts dates into their proper format, and can even handled uploaded files (see below).

//Insert a customer, the array keys must match the columns in the database, it returns the id
$customerid = SuperDatabase ('customers', '', array ('name'=>'Maria'));
//This is the same as passing the array as the second argument
$customerid = SuperDatabase ('customers', array ('name'='Maria'));
//For a single row update, pass in the id number as the second argument
$customerid = SuperDatabase ('customers', 12, array ('city'=>'Brighton');
//All data values are treated as strings, use _asis to run functions or do math
$customerid = SuperDatabase ('customers', 12, array ('numorders_asis'='numorders+1'));
//Or as part of the array itself
$customerid = SuperDatabase ('customers', array ('id'=>12, 'city'=>'Brighton');
//To update many rows, pass in a WHERE condition
SuperDatabase ('customers', 'status=1', array ('status'=>2));
//This function is especially useful for processing the results of a form, as long as the
//names of the form elements match the column names in the database.
$customerid = SuperDatabase ('customers', $_POST);

Finally, if you pass in null, then it will run a DELETE. For DELETEs, you must pass in a WHERE condition, otherwise it will cause a database error. This is to prevent all the rows in a table getting deleted by mistake. This always returns null.

//Delete a customer by id
SuperDatabase ('customers', 20, null);
//Delete by city
SuperDatabase ('customers', "city='Crawley'", null);
//This is not allowed and causes a database error
SuperDatabase ('customers', '', null);
//To delete everything from a table, pass in a fake WHERE condition. Note that 1=1 is not allowed
//because it is common in SQL injection attacks so it is ignored.
SuperDatabase ('customers', '2=2', null);

$debug and errors: true, false, an IP address or an email address

SuperDatabase has a comprehensive debugging and error handling ability. If you pass in $debug as true, then all database statements will be output to the browser as they are run, including the rows affected for INSERTs and UPDATEs, and the number of rows returned and the time taken for SELECTs.

If you pass in an IP address, then $debug will be set to true only when browsing from that IP address - this is very useful when you need to fix and test things in a live environment, and you don't want anybody else to see the database statements.

The way SuperDatabase handles errors depends on the error_reporting value set in PHP. If E_NOTICE is set (PHP shows all error notices), then a database error will cause the offending statement to be output to the browser, and PHP will exit. If there is any other error reporting, PHP will output the errors in <h3> tags and keep running. If there is no error reporting, PHP will ignore the error completely and keep going.

However, if you pass in an email address to $debug, then the database error, the $_SERVER variable, $_GET, $_POST and a backtrace will all be emailed to the address given, and the page will keep executing. It will send at most three errors emails per page.

By default $debug is set to false, so no debugging statements are shown and the error reporting depends on your PHP setting. But you can change the default to an IP address or email address as above, or you can pass in true just for the page you are working on.

//Output the database statement as it is run
$customers = SuperDatabase ('customers', 12, '', true);
//Only output when browsing from this IP address
$customers = SuperDatabase ('customers', '', 'lastname', '10.1.2.3');
//Don't output any debugging messages, but email any errors to me
$customers = SuperDatabase ('customers', '', 'badcolumn', 'me@superdatabase.co.uk');

$idcol: the column used for the id number

This is the database column for the id number, which defaults to id. The column should be set up as INT NOT NULL PRIMARY KEY AUTO_INCREMENT. The $idcol is used when you want to SELECT, UPDATE or DELETE a single row by number from the database, and is returned by INSERT statements. It is also used for the verification value (see below).

With SuperDatabase, it will be a lot easier if all your tables use the same column for the id number, as then you can just change the default in superdatabase.php, or else you'll have to pass it in every time, or add some if conditions or a lookup at the top of the SuperDatabase function.

$datecol: the column used to store the creation date

When a new row is inserted, the current date and time are stored in this column, which defaults to createddate. This should be a DATETIME column. When a single row is SELECTed and returned from the database, the $idcol and $datecol are also used to create a verification field. This is a complex, unique string, depending only on the $idcol and $datecol (both of which are guaranteed to be set for every row in the database). It is useful for things like verifying an email address - you can use the verification to verify they haven't tried to change their id number.

What it returns

SuperDatabase returns different things depening on how it was called. For multiple select statements it returns an array of arrays, with each subarray representing one row from the database, or a blank array if nothing is found. For single SELECT statements, where you have passed an id number as the $where argument, or the $order as 1, it returns a single array with that row is returned, or a blank array if it's not found. For INSERTs and single UPDATEs it returns the id number of the row just inserted or updated. For multilpe UPDATEs and DELETEs it returns null. Errors return null as well, though you can get SuperDatabase to email you the contents of the error using the $debug argument as described above.

Uploading files

SuperDatabase can also handle uploading files from a form if you pass in a filepath parameter. The filepath should be a writable directory where SuperDatabse can save the uploaded file. If filepath starts with a /, it will be taken from the document root, or else relative to the current file. The file you upload will be saved in a new directory within the filepath, and the location will be stored in the database.

The following example assumes you have a pictures table with columns for the id number, picture name and an extra "picture" column which will store its location, along with a /myfiles/ directory to store the actual files. When you upload a file, a directory will be created according to the table name, id number and column name, and the file location will be saved. The file location will be something like /myfiles/pictures-000001-picture/mypicture.jpg. This ensures that every file uploaded will be unique and will retain its oringal name, and you can have several files per database row. Here's the example:

<?
    if (isset ($_POST['picture'])) {
        $_POST['filepath'] = '/myfiles/'; //so that the filepath is passed in
        $pictureid = SuperDatabase ('pictures', $_POST); //create a new row
    }
?>
<form method="post" enctype="multipart/form-data">
Picture name: <input name="name" type="text" size="20"/><br/>
Upload a picture: <input name="picture" type="file"/>
(remove? <input name="picture_remove" type="checkbox"/>)
</form>

Passing in picture_remove allows you to remove an existing file. When you upload a new file, the previous one will be automatically removed. Note that the file will not be removed when the database row is deleted.

SuperDatabaseTree: Heirarchical Tree Function

The file superdatabase.php includes two other very useful functions. The first is for retrieving data from a heirarchical database table, a table where rows can have parents. This is useful for storing categories or any other information that can be represented by a data tree.

The main arguments to the function are the $table, the $order to return the results in, the $parentid to start at (defaults to 0), and an optional $filter (a WHERE condition which all rows must fulfill). The function returns an array of rows in heirarchical order, with each row having a rowdepth, rowbefore and rowafter (for making into an <ul> unordered list). You can also make it return options for a <select> form element.

The remaining arguments are a starting $depth (mainly used internally), a $maxdepth to signify the maximum depth the function should look (prevents it going into an endless loop), the $idcol for the database column storing the id of each row (defaults to id) for, and $parentcol for the database column storing the parent id of each row (defaults to parentid).

This is a full example of how it works, starting with a mini MySQL table to store categories. First create the table with this database statement (in phpMyAdmin or phpminiadmin or similar);

#First set up a database table in MySQL
CREATE TABLE catagories (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parentid INT NOT NULL DEFAULT '0',
    name VARCHAR(50) NOT NULL DEFAULT '',
    createddate DATETIME
);

Then insert some categories into this table using the INSERT mode of SuperDatabase:

//Insert some rows using the SuperDatabase function, remembering the id in a variable
$animalid = SuperDatabase ('categories', array ('name'=>'Animal'));
$mammalid = SuperDatabase ('categories', array ('name'=>'Mammal', 'parentid'=>$animalid));
SuperDatabase ('categories', array ('name'=>'Cat', 'parentid'=>$mammalid));
SuperDatabase ('categories', array ('name'=>'Dog', 'parentid'=>$mammalid));
SuperDatabase ('categories', array ('name'=>'Human', 'parentid'=>$mammalid));
$plantid = SuperDatabase ('categories', array ('name'=>'Plant'));
SuperDatabase ('categories', array ('name'=>'Tree', 'parentid'=>$plantid));
SuperDatabase ('categories', array ('name'=>'Shrub', 'parentid'=>$plantid));
SuperDatabase ('categories', array ('name'=>'Fungus'));

Now you can easily output a heirarchical list. The rowbefore and rowafter output the necessary <ul> and <li> elements for an unordered list. Alternatively you can just use the rowdepth (shown in parentheses) to indent the rows.

foreach (SuperDatabaseTree ('categories', 'name') as $row)
    echo $row['rowbefore'] . $row['name'] . ' (' . $row['rowdepth'] . ')' . $row['rowafter'];

This will display:

  • Animal (0)
    • Mammal (1)
      • Cat (2)
      • Dog (2)
      • Human (2)
  • Fungus (0)
  • Plant (0)
    • Shrub (1)
    • Tree (1)
//Or use a call like this to start from a different id number (Mammal in this case)
$rows = SuperDatabaseTree ('categories', 'name', 1);
//Filter to show only rows containing the letter m
$rows = SuperDatabaseTree ('categories', 'name', 0, "name LIKE '%m%'");

Alternatively, you ask SuperDatabaseTree to output options for a <select> list. To do this, use the $parentid argument to pass in the column name you'd like to display, followed by a colon and the currently selected id number. You can also add another colon and then the number of spaces to use for indentation (defaults to 5). For example:

echo '<form>Choose a category: <select name="categoryid">';
echo SuperDatabaseTree ('categories', 'name', 'name:7');
echo '</select></form>';

Which will display:

Choose a category:

LogInPerson logging in function

The file superdatabase.php also contains this easy function for authenticating people. Use it with a login form to see if someone has entered a valid user name and password. It then remembers them using a session variable and if desired a cookie.

You only need to pass in arguments when a person first tries to login. The main arguments are the database $table, $user for the user name (or email address if you want), $password for the password, $where which is an SQL WHERE condition, and $remember which determines whether the person will be remembered in a cookie or not (otherwise they'll only remembered for as long as their PHP session - usually half an hour or until they close their browser). If $remember is a number, then the cookie will be kept for that number of days, or else $remember defaults to 100 days if passed in.

The remaining arguments are the $usercol for the database column where the user name is stored, $passwordcol for the column with the password and $idcol for the column with the id number (not necessary but used for quicker looking up and refreshing the session and cookie). Use it like this to log a person in from a form, assuming you have a users table with email and password columns.

<?
//Passing in 'yes' for the $remember argument will remember them in a cookie for 100 days
if (!empty ($_POST['email'])) LogInPerson ('users', $_POST['email'], $_POST['password'], '', $_POST['remember']);
?>
<form  method="post" >
Your email address: <input type="text" name="email" value="" size="35"/><br/>
Your password: <input type="password"  name="password"  size="10"/><br/>
Remember me? <input type="checkbox"  name="remember"  value="yes"/><br/>
<input type="submit"  name="loginbutton"  value="Login"/>
</form>

After logging them in you can just call the function without arguments to see if they are logged in or not:

if (!LogInPerson()) echo 'You are not logged in!';

To log them out call LogInPerson with just the first argument, like this:

LogInPerson ('logout'); //log the person out

If the user details change and you want to refresh the session and cookie to reflect the new information, then call with just the $table and anything for the user name:

LogInPerson ('users', 'refresh'); //refresh the session and cookie

Note that for LogInPerson to work, you must start sessions somewhere at the top of each PHP page. Putting it in the same file as your database connection declared, or in superdatabase.php is a good idea. If not then LogInPerson will also only work at the top of a page, and not halfway through it (eg it won't work after you've output any content to the browser).

//Place at the top of each page for the LogInPerson function to work from everywhere
session_start();

Donations please
This script is provided for free, but donations are welcome to cover the time spent creating and maintaining it:

Donate