Ai

Summary: Ai is another mysql data query using an original and efficient system of envelop and masks

Version: 4

Prerequisites: none

Status: functional, but experimental for security reasons maybe

Maintainer: François Elie (francois AT elie.org)

Categories: System tools, Data, CMS, Forms

Discussion: Ai-Talk

Questions answered by this recipe

Is it possible simply to extract data from a database and display them as I want (table, list, etc...) ? Simply = without knowing an script language nor a template language. Only some SQL.

Description

Ai is a markup to extract mysql data and define the manner they will be shown. A mechanism of embedded envelops (for table, for lines, for cells) and the use of mask for columns permit this. It is also very easy to rotate the table.

Principle

See a demo of Ai If the demo is out of order, tell me -> mailto:francois AT elie [period] org please.

Installation

Put last version (7) of ai in cookbook directory of your PmWiki, (rename to ai.php)

Put the line "include_once('cookbook/ai.php');" in your local/config.php file You can put FALSE instead of TRUE for the value of $ai_demo variable (line 37) if you don't want to see the ai code. For learning the ai tool, it may be useful to let it at TRUE.

Set the variables to access the database:

  $ai_Hostname='<hostname>'; //default is 'localhost'
  $ai_Username='<username>'; //default is 'root'//
  $ai_Password='<password>'; // default is 'empty'//
  $ai_DBName='<database>';

Ai needs a file to read/write (the database structure) You have to tell to Ai his name and give permissions $ai_structure_file='/path/to/the/file'; It a good idea to put this file out of the zone accessible by the web (out of the DocumentRoot. For example, if the DocumentRoot is /var/www/html/, you can put the file in /var/www. Another solution is the put it in the cookbook directory of pmwiki (it has a .htaccess file to prevent direct access by the web).

How to use it

parameters are given line by line

query
orientation
global envelop
masks for colums

query

Query is standard SQL query. For minimal security reasons, only select, update and insert are allowed.

orientation

h (for horizontal) or v for (vertical). Default is v.

global envelop

Separator between elements is comma. There is 4 element (then fourth is optional.

table element <table>%s</table> by default
line element <tr>%s</tr> by default
cell element <td>%s</td> bu default
header element <th>%s</th> by default

masks for columns

Separator between elements is comma.

To display the first column in italic, the second in bold, and the other in normal:

<i>%s</i>,<b>%s</b>,%s

When then number of fields in the table is superior than the fields expected in all masks, the last mask is repeted to complete. (from v2)

without parameter (note the word 'select' is added by Ai)

(:ai [= 
select * from items
=]:)

with data rotate

(:ai [= 
select * from items 
v
=]:)

envelop is given

(:ai [= 
select * from items

%s,<hr/><ul>%s</ul>,<li>%s</li>
=]:)

masks for columns are given

(:ai [=
select * from items 


<b>%s</b>,<i>%s</i>,<h1>%s</h1>,%s
=]:)

masks for several columns, for links or images etc...

(:ai  [=
select * from items 


<b>%s</b>,<i>%s</i>,<a href="%s"><b>%s</b></a>
=]:)

if masks is not complete, completion is made by `%s`

(:ai  [=
select * from items 


<b>%s</b>,<i>%s</i>
=]:)

normalization of masks ( `%s$1 etc...` can be use in each mask)

Note that you can't refer in a mask to a field in another mask. To do that, you have to repeat reference to a field in the SQL query.

(:ai  [=
select * from items 


<b>%1$s</b>,<i>%1$s</i>
=]:)

see demonstration for another manner to enter masks

Demonstration

see a demo of Ai

Notes

TODO

database abstraction (ADOdb... about ten lines to adapt, very easy to do)
export in pdf, xml etc... (specific envelops)
masks library (image link, etc...)
x css: you will find here a little css sheet (add to your PmWiki css)

Release Notes

version 1: very basic
version 2: multified masks appear
version 3: mask completion and normalisation appear
version 4: insert and update are functional
version 5: masks alternativ sysntax improvement
version 6: checkbox and radio are automatic

Contributors

Comments

See discussion at Ai-Talk

User notes : If you use, used or reviewed this recipe, you can add your name. These statistics appear in the Cookbook listings and will help newcomers browsing through the wiki.

Ai