• View
• Edit
• History
• Attach
• Print
• Backlinks

Ai

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.

This section is optional; use it to indicate the types of questions (if any) this recipe is intended to answer.

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 please.

Installation

Put version 4 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

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>
=]:) 

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

If the recipe has multiple releases, then release notes can be placed here. Note that it's often easier for people to work with "release dates" instead of "version numbers".

See Also

Contributors

Comments