OpenCrypt - Membership Software
Username:
Password:
Register / My Account / Forgotten Password?
 Home  Features and Benefits  Online Demonstration  Purchase  Our Services  Help and Support  my.OpenCrypt 
 Welcome  New Visitor  Latest NewsRSS Feed Company Information  Press/Testimonials  Frequently Asked Questions  BlogNew Content
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:
Delicious Stumble It!
Stumble It!
 
<- Join the ionix Team!
 
AJAX Tree Menu with PHP ->
Articles
Subscribe to RSS Feed
OpenCrypt Version 1.9
New Product: myRepono Website Backup Service
Website Pre-Launch Checklist
ionix in 2010
PHP Security Tips - Part 1
OpenCrypt 1.8 New Feature Highlights
Video Conversion API
We're on Twitter!
IP Location API
Integrating OpenCrypt's PHP Login Interface
Developing Mobile Web Sites
Service Recovery
Building relationships with your customers
Doing the unexpected
PHP/JavaScript World Map with Continent and Ocean Selection
We've Moved!
Customer Showcase: A-Z-Animals.com
Optimising your secure content for Google
Membership business models
AJAX Calendar with PHP and mySQL
PHP Function for Reciprocal Linkback Checking
AJAX Tree Menu with PHP - Revisited
What is membership software?
Login form designs and inspiration
PHP subscriber counter with Addicted to Feedburner class and Feedburner Awareness API
AJAX Tree Menu with PHP
AJAX data listings engine with PHP and mySQL (BETA)
Join the ionix Team!
Press Release: OpenCrypt 2.0 in Development!
PHP: Basic functions for quickstart PHP framework
Welcome to the OpenCrypt Blog!
Visitor Comments
joe 10 out of 10
Space Holder
looks great... however, to someone who knows nothing about mysql it is a nightmare!
Space Holder
Space Holder
Neal 10 out of 10
Space Holder
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
Space Holder
Space Holder
Dimitri 10 out of 10
Space Holder
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
Space Holder
Space Holder
TSwain 10 out of 10
Space Holder
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! :)
Space Holder
Space Holder
R.S. Carter 7 out of 10
Space Holder
thanks it works great. I have 1 issue with pages. on page 2 the nav links disappear.
Space Holder
Space Holder
Younten Jamtsho 10 out of 10
Space Holder
great work!! keep it up
Space Holder
Space Holder
Matt 10 out of 10
Space Holder
Superb, just what I needed and works great
Space Holder
Space Holder
research3 10 out of 10
Space Holder
Greate job!!! -- Many thanks ---
Space Holder
Space Holder
Vernon 10 out of 10
Space Holder
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!
Space Holder
Space Holder
Vernon 10 out of 10
Space Holder
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!
Space Holder
Space Holder
Roberto D. 10 out of 10
Space Holder
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
Space Holder
Space Holder
OpenCrypt Team 10 out of 10
Space Holder
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.
Space Holder
Space Holder
Jesse 8 out of 10
Space Holder
Almost exactly what I'm looking for! Is there anyway to have those checkboxes tied to a TINYINT and update mysql during the onclick?
Space Holder
Space Holder
Ricardo 10 out of 10
Space Holder
Just what I was looking for!!
You saved me a lot of hours of coding.
Thanks a lot

Space Holder
Space Holder
OpenCrypt Team 10 out of 10
Space Holder
Varnav, please could you explain the issue in more detail? We use the datetime field in the example which works perfectly.
Space Holder
Space Holder
varnav 6 out of 10
Space Holder
It has problem with displaying timestamp type fields.
Space Holder
Space Holder
Helen 10 out of 10
Space Holder
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?
Space Holder
Space Holder
OpenCrypt Team 10 out of 10
Space Holder
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.
Space Holder
Space Holder
Gustavo 10 out of 10
Space Holder
very cool man, thanks!
Space Holder
Space Holder
Khalil 10 out of 10
Space Holder
Great, Thank you.
Space Holder
Space Holder
Eric 10 out of 10
Space Holder
Superb AJAX coding and extremely useful in almost any database query.
Space Holder
Space Holder
Tom 10 out of 10
Space Holder
Can you give some hints on how I would I add the display output to a WP theme template?
Space Holder
Space Holder
Roz 10 out of 10
Space Holder
this is BEAUTIFULLY done. Well coded and very detailed, and especially useful!!! Thanks!
Space Holder
Space Holder
Josh 10 out of 10
Space Holder
Fantastic amazing great php mysql ajax data viewer. Perfect, just what I wanted!
Space Holder
Space Holder
Josh 10 out of 10
Space Holder
Fantastic amazing great php mysql ajax data viewer. Perfect, just what I wanted!
Space Holder
Space Holder
Gustavo Baquero 10 out of 10
Space Holder
Excellent!!!
Space Holder
Space Holder
Average Rating: 9.65 out of 10 (26 Votes)
Space Holder
Submit Comment
Space Holder
Article Rating:
Space Holder
Your Name:
Space Holder
Your Comment/Response:
Space Holder
Space Holder
Space Holder

Follow us on Twitter

Copyright © 1999 - 2011 ionix Limited. Affiliates, Contact Us

Powered by OpenCrypt Membership Software, Backups by myRepono Website Backup