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.
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.
See a demo of Ai If the demo is out of order, tell me -> mailto:francois AT elie [period] org please.
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
- global envelop
- masks for colums
Query is standard SQL query. For minimal security reasons, only select, update and insert are allowed.
h (for horizontal) or v for (vertical). Default is v.
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:
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
(: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
see a demo of Ai
- 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)
- 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
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 discussion at Ai-Talk