:: samScripts.com :: My Scripts


[Home] [My Scripts] [Contact] [Business Card Designer] [Donate] [$8.95 Domain Names]

Home > Scripts > mysqltemplate

mysqltemplate

... feel free to use this script for whatever you like.

mysqltemplate

mysqltemplate is an easy to use and very flexible class for querying mysql databases and displaying the output from a template, with support for 'paged' results and tabular display.

Description:

Contents

Introduction

Reference & Examples

Introduction:

MySQLTemplate is a very easy to use php class for executing mysql database queries and displaying the output formatted by html templates.

With a few commands you can page through results and display them in tables.

It also has support for caching results using morecache.

Read these instructions, and pop into the support site if you need any help.

Examples:

All the examples use the scripts database on this site. You will need to change them according to your own databases.

They also use the following css styles:

Basic Usage and the constructor function

<?php

$mt = new mysqltemplate($connection, $database, $host, $username, $password);

// $connection is an open connection to mysql - normally left as 0
// $database is the name of the database (as used with mysql_select_db()
// $host is normally "localhost"
// $username is the username required to connect to the database
// $password is the password required to connect to the database

?>

mysqltemplate only connects to the database the first time a query is executed.

The following example shows mysqltemplate being used to display the introductions and links to the 9 latest scripts on this site:

<?php
$template = '<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>';

$mt = new mysqltemplate(0, $mydatabase, $host, $user, $pass);
$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
FROM scripttable ORDER BY title DESC LIMIT 0,9";
$mt->query($sql);
?>

Output:

voxinfo
This script will only be of direct use to people hosting their sites with Voxtreme, as their purpose is to display information about the servers. Useful to others as an example perhaps.

timerclass
This is a simple, easy to use class for timing how long all or parts of your scripts take.

mysqltemplate
mysqltemplate is an easy to use and very flexible class for querying mysql databases and displaying the output from a template, with support for 'paged' results and tabular display.

morecache
morecache is an output caching class. Use it to give your pages a big boost of speed.

friendlyurls
Very simple utility script to help you put nice looking links on your site instead of long query strings.

File downloads
A short snippet for downloading files.

error reporter
The following script is useful once your site goes live. It logs any error messages that occur and can email them to you. You can also set it to output a simple error message to your site visitors, rather than a standard 'error in /home/htdocs/publichtml/

Email address validation function
Simple function to validate email addresses.

EasyAds
Easy ads is a very simple banner ad (or any other html content) rotation system. Features include multiple ad categories, easy administration script, ad weighting, and easy install.

Alternating Row Colours:

This examples shows the same query, but using the <:row>...</:row> tag to create alternating background colours for each row. It also shows usage of the <:header>...</:header> and <:footer>...</:footer> tags to set html to be displayed before and after the rest of the output:

<?php
$template = '
<:header>
<table style="width: 100%; padding: 0px;"><tr><th class="tdheading">These are the latest scripts</th></tr>
</:header>
<:row>
<tr>
<td class="td1">
<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
</td>
</tr>
</:row>
<:row>
<tr>
<td class="td2">
<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
</td>
</tr>
</:row>
<:footer>
</table>
</:footer>
';
$mt = new mysqltemplate(0, $mydatabase, $host, $user, $pass);
$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
FROM scripttable ORDER BY title DESC LIMIT 0,9";
$mt->query($sql);
?>

Output:

These are the latest scripts
voxinfo This script will only be of direct use to people hosting their sites with Voxtreme, as their purpose is to display information about the servers. Useful to others as an example perhaps.
timerclass This is a simple, easy to use class for timing how long all or parts of your scripts take.
mysqltemplate mysqltemplate is an easy to use and very flexible class for querying mysql databases and displaying the output from a template, with support for 'paged' results and tabular display.
morecache morecache is an output caching class. Use it to give your pages a big boost of speed.
friendlyurls Very simple utility script to help you put nice looking links on your site instead of long query strings.
File downloads A short snippet for downloading files.
error reporter The following script is useful once your site goes live. It logs any error messages that occur and can email them to you. You can also set it to output a simple error message to your site visitors, rather than a standard 'error in /home/htdocs/publichtml/
Email address validation function Simple function to validate email addresses.
EasyAds Easy ads is a very simple banner ad (or any other html content) rotation system. Features include multiple ad categories, easy administration script, ad weighting, and easy install.

*Note: You can have as many different alternating colour as you like: just create a seperate <:row>...</:row> block for each one.

Error Handling with the <:error=...> ... </:error=...> tags:

The query() function returns true on success and false if there is an error. You can include special tags within you template to have an error message automatically displayed if the query fails, or if there are no results found. Note that under these circumstances none of the template is output except for any error message.

To set an error message for a query failure, enclose it within:

<:error=dberror>...your error message here...</:error=dberror>

There are two optional tags you can use inside the dberror template:

<:message> will display the mysql_error() message.
<:sql> will display the sql query that failed.

These tags are very handy for debugging your queries.

Example of these tags:

This example captures a spelling mistake with one of the field names:

<?php

$template = '<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
<:error=dberror>Sorry, an error has occurred with the database.<br><br>The error was: <:message><br><br>The query that caused this error was: <:sql>.</:error=dberror>
';

$mt->settemplate($template);
$sql = "SELECT iintro, REPLACE(title, ' ','_') AS urltitle, title
FROM scriptstable ORDER BY title DESC LIMIT 0,9";
$mt->query($sql);

?>

Output:

mysql_query failed: Unknown column 'iintro' in 'field list'Sorry, an error has occurred with the database.

The error was: Unknown column 'iintro' in 'field list'

The query that caused this error was: SELECT iintro, REPLACE(title, ' ','_') AS urltitle, title FROM ssscripts ORDER BY title DESC LIMIT 0,9.

The other error message tag is <:error=norecords>...</:error=norecords>. It will be displayed if an empty result set is returned by the query.

<?php

$template = '<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
<:error=norecords>Sorry, no results matched your search.</:error=norecords>
';

$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(title, ' ','_') AS urltitle, title
        FROM scriptstable
        WHERE intro LIKE '%somesearchstring%'
        ORDER BY title";
$mt->query($sql);

?>

Output:

Sorry, no results matched your search.

Displaying results in a table:

To get mysqltemplate to display the results in a tabular form, you have to set 1 or 2 properties of the mysqltemplate object, and add 3 extra tags to your template, as well as enclosing the template in <table>...</table> html tags. The table tags can occur outside of your template (in the html) or within the <:header> and <:footer> tags.

The main additional tags required are <:groupheader>...</:groupheader> and <:groupfooter>...</:groupfooter>. These define the html to ouput at the beginning and end of each row of results in the table. Normally, you will just use:

<:groupheader><tr></:groupheader>
<:groupfooter></tr></:groupfooter>

And your template rows will begin and end with <td> and </td> tags. See the examples below:

You should also include an additonal <:error=emptyrow>...</:error=emptyrow> tag.

The contents of this tag will be displayed when there is no result to show. It should be at least something like this:

<:error=emptycell><td> </td></:error=emptycell>

To use tabular output, set the $mysqltemplate->columns value to anything greater than 1.

<?php
$template = '
<:header><table align="center"></:header>
<:groupheader><tr></:groupheader>
<td class="td1">
<a href="/scripts/<:urltitle>"><:title></a>
</td>
<:groupfooter></tr></:groupfooter>
<:footer></table></:footer>';

$mt = new mysqltemplate(0, $mydatabase, $host, $user, $pass);
$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
FROM scripttable ORDER BY title LIMIT 0,9";
$mt->columns = 3;
$mt->query($sql);
?>

Output:

application object This is an experimental script that attempts to replicate (somewhat) the function of ASP's Application Object.	bbtags This class makes it easy to convert [ ] tags to html like on phpBB and other bulletin boards in your own scripts.	calendar Very simple to use calendar class.
dirsizer Little class that gets the number of files and sub-folders in any path, adds up the sizes of the files, and counts the total number of lines in any file.	EasyAds Easy ads is a very simple banner ad (or any other html content) rotation system. Features include multiple ad categories, easy administration script, ad weighting, and easy install.	Email address validation function Simple function to validate email addresses.
error reporter The following script is useful once your site goes live. It logs any error messages that occur and can email them to you. You can also set it to output a simple error message to your site visitors, rather than a standard 'error in /home/htdocs/publichtml/	File downloads A short snippet for downloading files.	friendlyurls Very simple utility script to help you put nice looking links on your site instead of long query strings.

To have the results displayed vertically down the table, rather than horizontally, set $mysqltemplate->tableorder variable to "vertical".

As with normal queries, you can use the <:row>...</:row> tags to create different output for each result.

This example displays the results in a 3 column table, with alternating background colours for each cell. It also uses the empty cell tag:

<?php
$template = '
<:header><table align="center"></:header>
<:groupheader><tr></:groupheader>
<:row>
<td width="33%" valign="top" class="td1">
<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
</td>
</:row>
<:row>
<td width="33%" valign="top" class="td2">
<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>
</td>
</:row>
<:error=emptycell><td bgcolor="black"> </td></:error=emptycell>
<:groupfooter></tr></:groupfooter>
<:footer></table></:footer>';

$mt = new mysqltemplate(0, $mydatabase, $host, $user, $pass);
$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
FROM scripttable ORDER BY title LIMIT 0,8";
$mt->columns = 3;
$mt->tableorder = "vertical";
$mt->query($sql);
?>

Output:

application object This is an experimental script that attempts to replicate (somewhat) the function of ASP's Application Object.	dirsizer Little class that gets the number of files and sub-folders in any path, adds up the sizes of the files, and counts the total number of lines in any file.	error reporter The following script is useful once your site goes live. It logs any error messages that occur and can email them to you. You can also set it to output a simple error message to your site visitors, rather than a standard 'error in /home/htdocs/publichtml/
bbtags This class makes it easy to convert [ ] tags to html like on phpBB and other bulletin boards in your own scripts.	EasyAds Easy ads is a very simple banner ad (or any other html content) rotation system. Features include multiple ad categories, easy administration script, ad weighting, and easy install.	File downloads A short snippet for downloading files.
calendar Very simple to use calendar class.	Email address validation function Simple function to validate email addresses.

Displaying 'Pages' of Results

One question often asked on php forums is 'how do I display paged results'. With mysqltemplate, it is very simple to display a certain number of results per page, with links to other pages, next, previous, first and last page.

To accomplish this, you set two properties in the mysqltemplate object before calling the query function:

$mysqltemplate->currentpage is the page to display.
$mysqltemplate->pagesize is the number of results to display per page.

You can then add these tags to your templates:

<:pagelist> This tag will display a list of all the pages for your results. Usually used to display links to each page.

You also need to define the format of these tags:

<:pagelistformat>...<:thispage>...</:thispage></:pagelistformat>

Where the contents of the tags is displayed for each page, and the contents of <:thispage>...</:thispage> is what is displayed for the current page. The tag <:page> can be used to display each page number.

Other tags are:

<:currentpage> the current page number.

<:pagesize> the number of results per page

<:pagecount> the total number of pages

<:firstrow> the number of the first row on this page

<:lastrow> the number of the last row on this page

<:nextpage> the number of the next page

<:prevpage> the number of the previous page

<:totalrows> the total number of results

The following tags can be placed around the <:nextpage> and <:prevpage> tags. The template inside them will only be displayed if there is a nextpage and previous page. They are useful for displaying next and previous links only when there are next and previous pages.

<:ifnext>...</:ifnext>
<:ifprev>...</:ifprev>

Both normal results and tabled results can be paged. An example of each follows. The links on the output are for reference only, and will not actually work on this page:

Example of normal paging of a result:

<?php

$template ='
<:pagelistformat>
<a href="/scripts/?page=<:page>"><:page></a>
<:thispage><b><:page></b> </:thispage>
</:pagelistformat>
<:header>
<table width="100%">
<tr>
  <td align="left" class="tdheading" width="33%" >
   <:ifprev><a href="/scripts/?page=<:prevpage>">Previous</a></:ifprev>
  </td>
  <td align="center" class="tdheading" width="33%">
   Displaying Page <:currentpage> of <:pagecount><br>Results <:firstrow> to <:lastrow> of <:totalrows>.
  </td>
  <td align="right" class="tdheading" width="33%">
   <:ifnext><a href="/scripts/?page=<:nextpage>">Next</a></:ifnext>
  </td>
</tr>
</:header>
<:row>
<tr>
  <td colspan="3" align="left" class="td1">
   <a href="/scripts/<:urltitle>"><:title></a>
   <br><:intro><br><br>
  </td>
</tr>
</:row>
<:row>
<tr>
  <td colspan="3" align="left" class="td2">
   <a href="/scripts/<:urltitle>"><:title></a>
   <br><:intro><br><br>
  </td>
</tr>
</:row>
<:footer>
  <tr>
   <td colspan="3" align="center"  class="tdfooter"
    Go to page <:pagelist>.
   </td>
  </tr>
</table>
</:footer>
<:error=dberror>Error: <:message><Br><:sql></:error=dberror><:error=norecords>Fuck</:error=norecords>
';

$mt = new mysqltemplate(0, $database, $host, $user, $password);
$mt->settemplate($template);
$mt->pagesize = 6;
$mt->currentpage = 2;
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
        FROM ssscripts ORDER BY title";
$mt->query($sql);

?>

Output:

Previous	Displaying Page 2 of 3 Results 7 to 12 of 13.	Next
error reporter The following script is useful once your site goes live. It logs any error messages that occur and can email them to you. You can also set it to output a simple error message to your site visitors, rather than a standard 'error in /home/htdocs/publichtml/
File downloads A short snippet for downloading files.
friendlyurls Very simple utility script to help you put nice looking links on your site instead of long query strings.
morecache morecache is an output caching class. Use it to give your pages a big boost of speed.
mysqltemplate mysqltemplate is an easy to use and very flexible class for querying mysql databases and displaying the output from a template, with support for 'paged' results and tabular display.
timerclass This is a simple, easy to use class for timing how long all or parts of your scripts take.
Go to page 1 2 3 .

Example of paging of a tabled result:

<?php

$template ='
<:pagelistformat>
<a href="/scripts/?page=<:page>"><:page></a>
<:thispage><b><:page></b> </:thispage>
</:pagelistformat>
<:header>
<table width="100%">
<tr>
  <td align="left" width="33%" class="tdheading" > 
   <:ifprev><a href="/scripts/?page=<:prevpage>">Previous</a></:ifprev>
  </td>
  <td align="center" width="33%" class="tdheading" >
   Displaying Page <:currentpage> of <:pagecount><br>Results <:firstrow> to <:lastrow> of <:totalrows>.
  </td>
  <td align="right" width="33%" class="tdheading">
   <:ifnext><a href="/scripts/?page=<:nextpage>">Next</a></:ifnext>
  </td>
</tr>
</:header>
<:groupheader>
<tr>
</:groupheader>
<:row>
  <td width="33%" align="left" class="td1">
   <a href="/scripts/<:urltitle>"><:title></a>
   <br><:intro><br><br>
  </td>
</:row>
<:row>
  <td width="33%" align="left" class="td2">
   <a href="/scripts/<:urltitle>"><:title></a>
   <br><:intro><br><br>
  </td>
</:row>
<:groupfooter>
</tr>
</:groupfooter>
<:footer>
  <tr>
   <td colspan="3" align="center" class="tdfooter" >
    Go to page <:pagelist>.
   </td>
  </tr>
</table>
</:footer>
<:error=dberror>Error: <:message><Br><:sql></:error=dberror><:error=norecords>Fuck</:error=norecords>
';

$mt = new mysqltemplate(0, $database, $host, $user, $password);
$mt->settemplate($template);
$mt->pagesize = 4;
$mt->columns = 3;
$mt->currentpage = 1;
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
        FROM ssscripts ORDER BY title";
$mt->query($sql);

?>

Output:

	Displaying Page 1 of 3 Results 1 to 6 of 13.	Next
application object This is an experimental script that attempts to replicate (somewhat) the function of ASP's Application Object.	bbtags This class makes it easy to convert [ ] tags to html like on phpBB and other bulletin boards in your own scripts.	calendar Very simple to use calendar class.
dirsizer Little class that gets the number of files and sub-folders in any path, adds up the sizes of the files, and counts the total number of lines in any file.	EasyAds Easy ads is a very simple banner ad (or any other html content) rotation system. Features include multiple ad categories, easy administration script, ad weighting, and easy install.	Email address validation function Simple function to validate email addresses.
Go to page 1 2 3 .

Note the only changes required to turn the normal paged result into the tabled paged result:

<tr> and </tr> tags were removed from between the <:row>...</:row> tags and placed beween <:groupheader> and <:groupfooter> tags.

A width="33%" attribute was added to the <td> tags for the rows in place of the colspan="3" attribute.

$mt->columns was set to 3 for tabled output.

The $mt->currentpage was set to 1 instead of 2. The purpose of this was just to illustrate the use of the <:ifprev>...</:ifprev> tags. As you can see, we are on page 1, so no link is displayed to the previous page.

Using mysqltemplate for not-templated queries

As it already handles connecting to the database, it makes sense to be able to use mysqltemplate for queries where you don't want the results to be displayed with a template (and for UPDATE, INSERT, DELETE, etc).

To use mysqltemplate as a replacement for the mysql_query() function, just use:

<?php $result = $mysqltemplate->mysql_query($sql);?>

This function simply checks to see if a connection to the database exists, opens one if it doesn't, then calls the mysql_query() function for you and returns the result.

Caching mysqltemplate's output with morecache

morecache is another of my scripts, which provides a very easy to use method of caching script's output to a file, and then reusing it on later requests. This can provide a huge performance boost when used to cache database queries, and mysqltemplate has a built in set of functions to use it.

To use morecache with mysqltemplate, you have to include() the morecache class, create a morecache object, and then set mysqltemplates $cache variable to point to it:

<?php

include("morecache.php");

$mt = new mysqltemplate(0, $database, $host, $username, $password);
$mt->cache =& new morecache("./cached/");

?>

See the morecache documentation for more information on the morecache constructor and other functions.

To cache the results of a query, you just set the $mysqltemplate->cachename variable before calling the query() function. You can also set the time after which the cached content should be refreshed from the database again, by setting the $mysqltemplate->cacheexpiry value to a number of seconds. You can also set it to -1 in which the query will only execute the first time it is called, and after this, the cached version will be used unless you delete it.

To force a cache to expire, you can call morecache's expirecache() function through mysqltemplate like this:

<?php $mysqltemplate->cache->expirecache($cachename);?>

An example of using the caching abilities with the first example in this document is below:

<?php

include("morecache.php");

$mt = new mysqltemplate(0, $database, $host, $username, $password);
$mt->cache =& new morecache("./cached/");

$template = '<a href="/scripts/<:urltitle>"><:title></a>
<br><:intro><br><br>';

$mt = new mysqltemplate(0, $mydatabase, $host, $user, $pass);
$mt->settemplate($template);
$sql = "SELECT intro, REPLACE(' ', '_', title) AS urltitle, title
FROM scripttable ORDER BY title DESC LIMIT 0,9";

$mt->cachename = "listscripts";
$mt->cacheexpiry = 60 * 60 * 24; // expire after 1 day

$mt->query($sql);

?>

The results will be the same as here.

Other things and to-dos

There are a couple of things I want to do: add a few different types of query layout, get the profiling functions working properly, and some others I have temporarily forgotten.

If you have any questions about this class, just ask them in the support forum.

Cheers, Sam