AJAX data listings engine with PHP and mySQL (BETA)
Saturday 17th May 2008
We have recently been developing an AJAX data listings engine as part of OpenCrypt version 2.0 and thought it may be of use to our visitors... The system is written in PHP and can be used to list data in any mySQL table, all you do is specify the mySQL database details and the engine will automatically generate a data listings table as per the configuration.
The system supports AJAX column sorting and page listings and offers a search facility. The engine generates both AJAX and non-JavaScript versions of the data listings so older web browsers will still be able to use the column sorting, page listing and search facilities.
Usage
After setting the general variables and stylesheets as per the example file (see 'Configuration' instructions below), simply use the following to generate a data listing:
require "data_engine.php";
$output = data_engine_select("TABLE", "FIELD_1|FIELD_2|FIELD_3");
For a more advanced data table use:
$output = data_engine_select("TABLE", "FIELD_1|FIELD_2|FIELD_3",
"1", "20|1", "FIELD_3|ASC", "2|2", "FIELD_2 = 'VALUE'",
"HEADER TEXT", "FOOTER TEXT", "BUTTONS");
Note, the returned output must be parsed to a string and printed after the CSS/JavaScript headers have been printed. This is necessary so the data_engine_select function can print and exit when being called via AJAX, therefore avoiding repeatedly printing the headers which would normally be printed before the engine is loaded.
The ten input flags can be used as outlined below:
data_engine_select("TABLE NAME", "FIELD NAMES", "COLUMN HEADERS",
"PAGE LISTINGS", "DEFAULT SORTING", "DISPLAY SELECT OPTIONS",
"QUERY", "HEADER", "FOOTER", "BUTTONS");
- TABLE NAME
Specify the mySQL database table name.
- FIELD NAMES
Seperated by pipe delimeters, enter the field names in the order they should be shown. e.g. FIELD_NAME_1|FIELD_NAME_2|FIELD_NAME_3
- COLUMN HEADERS
Default 1 - true. 0 - false. When true the column headers will be shown and column sorting features will be available.
- PAGE LISTINGS
Default blank - display all results on one page. Two values are required seperated by pipe delimeter, number of records to display and whether or not to display pagination options to view next pages. e.g. 20|1 will display 20 records per page and will allow you to navigate through pages. 10|0 will display 10 records but will not enable you to view other pages.
- DEFAULT SORTING
Default blank - sort as returned from database. Two values are required seperated by pipe delimeter, field name to sort by and sort order. e.g. FIELD_NAME_3|ASC will order results by the values of FIELD_NAME_3 in ascending order. Alternatively DESC will sort in descending order.
- DISPLAY SELECT OPTIONS
Default blank - no selection options. Two values are required seperated by pipe delimeter, select method type and position. e.g. 0|1 would display radio buttons to the left of each record, 1|2 would display checkbox fields to the right of each record. Note, the first field in each row (FIELD_1 in example above) is used as the value for checkbox/radio fields, therefore it is important the first field is a unique ID if this is used to identify unique records.
- QUERY
Default blank - no custom query. Enter a mySQL query statement (to be inserted after WHERE in SQL statement) to select specific records. e.g. FIELD_NAME_2 = 'VALUE'
Or more multiple values, e.g. FIELD_NAME_2 = 'VALUE' AND FIELD_NAME_3 LIKE 'ANOTHERVALUE%'
- HEADER
HTML or text to be displayed after column headers but before records.
- FOOTER
HTML or text to be displayed after records but before page listings row.
- BUTTONS
HTML or text to be displayed instead of 'Page 1 or 2' text.
Configuration
The example.php file contains the following values which must be set before the 'require "data_engine.php";' statement.
# DATABASE CONFIGURATION
$global['dbhost'] = "localhost";
$global['dbname'] = "DATABASE_NAME";
$global['dbuser'] = "DATABASE_USER";
$global['dbpass'] = "DATABASE_PASS";
These are the general database variables, pretty self-explanatory.
# COLORS CONFIGURATION
$global['header_color'] = "ffffff"; # header colours
$global['header_selected_color'] = "eeeeee";
$global['line_color'] = "dddddd";
$global['row_color1'] = "ffffff"; # row colours
$global['row_color2'] = "f7f7f7";
$global['row_color_selected'] = "FBFBC4";
$global['row_color_mouseover'] = "F5F5BA";
$global['page_box_color'] = "f7f7f7"; # page selection colours
$global['page_box_border'] = "000080";
Our suggestion with these is to have some fun and mess about, try some different colours and see what you can come up with!
# IMAGES
$global['url_images'] = "images";
The URL to the images directory without the trailing slash, e.g. /images or http://www.domain.com/images
You will also notice the example.php file (in the source ZIP file) contains some basic CSS/stylesheets and JavaScript which are used to control the styling of the data listings table, their is also a JavaScript include file called ajax_queue.js. Don't forget to include these if using the data engine in your software.
License and Download
This code is Open Source and is released under the GNU General Public License.
We hope you find this code of use, if you use it in your projects we'd love to hear about it! And of course, if you have any questions please don't hesitate to post a comment and we will respond as soon as possible.
Syntax highlighting powered by JUSH
Share this article:
|
<- Join the ionix Team!
|
AJAX Tree Menu with PHP ->
|
|
|
|
|
|
joe |
10 out of 10 |
|
looks great... however, to someone who knows nothing about mysql it is a nightmare!
|
|
|
Neal |
10 out of 10 |
|
YOU are a coding HOGGGGG!!. You saved me at least a week of brain boggling coding. Works almost perfect. One minor problem though: The row hover color does not go away if you do not have checkboxes or radiobuttons enabled. THANKS
|
|
|
Dimitri |
10 out of 10 |
|
Hi,
Great script!
Only have one problem. I am trying to give the value of the radiobuttons in a querystring. But I can't get it to work. It's like it doesn't find it.
Any help on this?
Grtz
|
|
|
TSwain |
10 out of 10 |
|
Hey, I read a lot of blogs on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say GREAT blog!.....Ill be checking in on a regularly now....Keep up the good work! :)
|
|
|
R.S. Carter |
7 out of 10 |
|
thanks it works great. I have 1 issue with pages. on page 2 the nav links disappear.
|
|
|
Younten Jamtsho |
10 out of 10 |
|
great work!! keep it up
|
|
|
Matt |
10 out of 10 |
|
Superb, just what I needed and works great
|
|
|
research3 |
10 out of 10 |
|
Greate job!!!
-- Many thanks ---
|
|
|
Vernon |
10 out of 10 |
|
Hi, for getting data from multiple tables I specify the global table name like so:
table1 LEFT JOIN table2 ON table1.id = table2.id. It worked for me.
Thanks again for this really useful tool!
|
|
|
Vernon |
10 out of 10 |
|
Hi, for getting data from multiple tables I specify the global table name like so:
table1 LEFT JOIN table2 ON table1.id = table2.id. It worked for me.
Thanks again for this really useful tool!
|
|
|
Roberto D. |
10 out of 10 |
|
Excelent !! is what i really need. I see things like checkboxes, that i can't use it..i don't know how to... or the top checkbox, suppose to checks all the others right?
excelent work my friend, thank you to share
|
|
|
OpenCrypt Team |
10 out of 10 |
|
Jesse, we're currently working on an updated version which enables you to specify which field is used as the ID for the checkbox. This should be available within a couple of months. The current version uses the first column value as the ID so you can just list your TINYINT field as the first column.
|
|
|
Jesse |
8 out of 10 |
|
Almost exactly what I'm looking for! Is there anyway to have those checkboxes tied to a TINYINT and update mysql during the onclick?
|
|
|
Ricardo |
10 out of 10 |
|
Just what I was looking for!!
You saved me a lot of hours of coding.
Thanks a lot
|
|
|
OpenCrypt Team |
10 out of 10 |
|
Varnav, please could you explain the issue in more detail? We use the datetime field in the example which works perfectly.
|
|
|
varnav |
6 out of 10 |
|
It has problem with displaying timestamp type fields.
|
|
|
Helen |
10 out of 10 |
|
Hi,Thanks a lot. I`ve a question. I want to search in my some field and dont show them in the table. what should I do? How can I change table header? and page header?
|
|
|
OpenCrypt Team |
10 out of 10 |
|
Update: We are currently advancing the data engine which is now considered stable and offers support for running total columns and currency/number formatting. The data engine can also now handle PHP array data in addition to mySQL data. The next task is to enable the data engine to retrieve data from multiple tables, either to include the data in single primary rows of so sub data rows can be listed under primary data rows.
|
|
|
Gustavo |
10 out of 10 |
|
very cool man, thanks!
|
|
|
Khalil |
10 out of 10 |
|
Great, Thank you.
|
|
|
Eric |
10 out of 10 |
|
Superb AJAX coding and extremely useful in almost any database query.
|
|
|
Tom |
10 out of 10 |
|
Can you give some hints on how I would I add the display output to a WP theme template?
|
|
|
Roz |
10 out of 10 |
|
this is BEAUTIFULLY done. Well coded and very detailed, and especially useful!!! Thanks!
|
|
|
Josh |
10 out of 10 |
|
Fantastic amazing great php mysql ajax data viewer. Perfect, just what I wanted!
|
|
|
Josh |
10 out of 10 |
|
Fantastic amazing great php mysql ajax data viewer. Perfect, just what I wanted!
|
|
|
Gustavo Baquero |
10 out of 10 |
|
Excellent!!!
|
|
|
Average Rating: 9.34 out of 10 (127 Votes)
|
|
|
|
