Forms & Database
How To Render Form Data On A Web-Page
Is your u5CMS installation up-to-date and ready for this functionality?For the functionality described on the page at hand, make sure you are using a current u5CMS version. You'll find the version number by surfing to your website and switching your browser to show the HTML source code. At the very bottom of the source code you'll find a line like this:
<!-- This site runs with u5CMS version 12.4.3 from https://yuba.ch/u5cms -->If there is no such line or no version number or one lower than V8.3.3, see the following instructions:
- On your server, go to the files of your old (current) u5CMS installation and copy config.php to config.old.php
- Download the ZIP-File , unzip it and delete the file favicon.ico
- Further, delete all contents in folder r EXCEPT runonce.php
- Transfer the remaining files and folders from the ZIP file over the existing installation (FTP upload)
- Now, on your server, copy the database connection parameters $host $username $password $db from config.old.php to config.php If there are other dedicated settings in config.old.php, set the same in config.php (compare parameter by parameter)
- You must now log in to the backend of your (now updated) u5CMS installation via https://urltoyouru5cmsfrontend/u5admin and open a css-object (e. g. csslayout) in PIDVESA's S code section, for your site to work correctly again.ื
- PIDVESA: The very right column of your u5CMS backend is your repository. PIDVESA stands for Pages, Images, Documents, Videos, External Links, Special Functions, and Account. Find these things by clicking the respective radios P I D V E S or A in the top right corner of your backend screen; cf short reference (pdf)
- You must now log in to the backend of your (now updated) u5CMS installation via https://urltoyouru5cmsfrontend/u5admin for that the database will be updated too
A) If the layout does not look right after having done step 7, go to https://urltoyouru5cmsfrontend/u5admin and open a css-object (e. g. csslayout) in PIDVESA
- PIDVESA: The very right column of your u5CMS backend is your repository. PIDVESA stands for Pages, Images, Documents, Videos, External Links, Special Functions, and Account. Find these things by clicking the respective radios P I D V E S or A in the top right corner of your backend screen; cf short reference (pdf)
B) If your navigation does not look well, copy this code to cssnavleft in PIDVESA
- PIDVESA: The very right column of your u5CMS backend is your repository. PIDVESA stands for Pages, Images, Documents, Videos, External Links, Special Functions, and Account. Find these things by clicking the respective radios P I D V E S or A in the top right corner of your backend screen; cf short reference (pdf)
- PIDVESA: The very right column of your u5CMS backend is your repository. PIDVESA stands for Pages, Images, Documents, Videos, External Links, Special Functions, and Account. Find these things by clicking the respective radios P I D V E S or A in the top right corner of your backend screen; cf short reference (pdf)
- PIDVESA: The very right column of your u5CMS backend is your repository. PIDVESA stands for Pages, Images, Documents, Videos, External Links, Special Functions, and Account. Find these things by clicking the respective radios P I D V E S or A in the top right corner of your backend screen; cf short reference (pdf)
C) If after A) and B) the layout still does not look right, change the doctype <!DOCTYPE html> to the old one
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
Data entered by users in HTML-forms or imported from Excel can easily be rendered on u5CMS-pages.
-
- Total 3 images (click the image)Example: How to render data coming from a survey on a u5CMS-page
Tutorials
1. Step by step from the most easy query for rendering form data to a very nice output-table (pdf)2. Enable users to add, edit, and delete data records in a table → see "scenario 2"
3. Create pseudo-relational HTML-forms (pdf)
Full Syntax Explanation of the :dat-command how to use PHP&SQL instead
[a|b|c|d|e|f|g|h|i|j|k|l|m|n:dat]a=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat] The fieldnames are the name attributes in your HTML code, e. g. <input type="text" name="firstname_mandatory" /> <input type="text" name="place" /> <textarea name="message" /></textarea> Don't know how your users can upload files? See User Uploads How To Question mark [...|?|...:dat]: Take the WHERE clause from GET parameter notes in the URL e. g. ¬es=datacsv LIKE %Peter% (appearing as ¬es=datacsv%20LIKE%20%25Peter%25 in the browsers's address field). Query with LIKE [...|datacsv LIKE %Peter%|...:dat]: Means "show all records containing Peter". Any string, e. g. foo [...|foo|...:dat]: Show all records containing foo in notes field. The exact string (authuser) [...|(authuser)|...:dat]: Show records assigned to certain users. The exact string SELF [...|SELF|...:dat]: This selects all records belonging to the user who is currently logged-in. A full WHERE clause [...|WHERE notes='foo' AND lastmut>1501785073|...:dat]: This is possible since u5CMS version 5.5.5. In config.php, $allowfullwhereasdatparameterd='yes'; must be set. The fieldnames are the name attributes in your HTML code, e. g. <input type="text" name="firstname_mandatory" /> <input type="text" name="place" /> <textarea name="message" /></textarea> Don't know how your users can upload files? See User Uploads How To
Name of field(s). If more than one, separate with commas, no whitspace. Case sensitive! PHP? If you are referencing to a mandatory fieldname, either reference fieldname_mandatory or fieldname* does work. If you are referencing to a system generated field (i. e. id formname headcsv datacsv time ip status notes lastmut authuser humantime, this filedname must be preceded by a question mark e. g. ?notes. If the Notes (or other data) contain line breaks, see paramenter j. Special feature for Notes: if you want to render only a part of your notes, mark the till-position with three pipes (|||) (not here in the parameter but directly in your notes).
Where do I find the fieldnames?
If you offer uploads, you cannot see the fieldnames in your HTML code, you have to know that the filedname of
<script src="upload"></script> or <script src="Pupload"></script>
is userupload1 (or userupload2 if it's the second one in your form).
If you declare your uploads as mandatory, e. g.
<script src="upload_mandatory"></script> or <script src="Pupload_mandatory"></script>
the fieldname becomes userupload1* (i. e. add an asterisk).
b=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
Name of the form carrying page PHP? (that's the name of the page on which the form is, i. e. the value of the GET parameter c of its URL)
c=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
id of the record or ALL for all or ? to take id form a GET parameter &id=
d=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
This parameter (d) can define a WHERE clause. Details
Only one SQL command LIKE (uppercased!) is allowed, nothing else. Do NOT write the command WHERE in the GET parameter notes (it precedes automatically). Do NOT use double nor single quotes in GET parameter notes. The wildcard % MUST be encoded as %25 in the URL's GET parameter notes. You can only (exceptions) search for strings in the field datacsv because your data is stored as a semicolon delimited sequence in one field (column) of your u5CMS database (table formdata, field datacsv) therefore the string searched for must be between % and %. You cannot search in specific fields e. g. firstname. Data in the field datacsv looks like this: ·value;·value;·value;·value;.... You see that every value is preceded with · (middot). You may use these delimiting characters (semicolon and middot) in your WHERE clause if substrings shall not match, e. g. ¬es=datacsv LIKE %·Peter;% (appearing as ¬es=datacsv%20LIKE%20%25%C2%B7Peter%3B%25 in the browsers's address field; The wildcard % MUST be encoded as %25 and the semicolon ; MUST be encoded as %3B in the URL's GET parameter notes.).
Notice: ¬es=datacsv LIKE %Peter%Meyer% does find Peter Meyer and Peter Paul Meyer a.s.o. in datacsv if the words do occur in this order. That's why you can add a boolean AND (&&) since u5CMS 5.3.8 what looks like ¬es=datacsv%20LIKE%20%25Peter%25%26%26%25Meyer%25 in the URL; with this, the word order does not matter. Boolean OR is double-pipe (||).
Advice: For productive environments, when taking sql query pieces (e. g. LIKE, ORDER BY) from GET parameters, set $showdatcommandsqlerrors='no'; in config.php (available since u5CMS 5.5.2).
Only one SQL command LIKE (uppercased!) is allowed, nothing else. Do NOT write the command WHERE (it precedes automatically). Do NOT use double nor single quotes. You can only (exceptions) search for strings in the field datacsv because your data is stored as a semicolon delimited sequence in one field (column) of your u5CMS database (table formdata, field datacsv) therefore the string searched for must be between % and %. You cannot search in specific fields e. g. firstname. Data in the field datacsv looks like this: ·value;·value;·value;·value;.... You see that every value is preceded with · (middot). You may use these delimiting characters (semicolon and since u5CMS 5.5.1 also the middot) in your WHERE clause if substrings shall not match, e. g. [...|datacsv LIKE %·Peter;%|...:dat] PHP?.
The notes field is not an input from public users. It is a field which is present in the u5CMS backend for you to write down notes concerning a certain data record, cf. page 38 in . It's not necessary to write the term searched for between wildcards (%), they are automatically added. PHP?.
To be clear: you do not type an specific authuser, you effectively type (authuser) in parentheses. This command selects all records which have the authuser currently logged-in mentioned (his/her username i. e. e-mail address) in parentheses(!) somewhere in the notes field. The notes field is not an input from public users. It is a field which is present in the u5CMS backend for you to write down notes concerning a certain data record, cf. page 38 in . With that mechanism, you can assign records to certain editors, e. g. for review processes.
The d-parameter must start with the capitalized word WHERE. Strings must be between single quotes. Be aware that non-latin1 characters in the d-parameter are encoded to HTML entities if [...:dat] is not within an HTML-section [h:]...[:h]; i. e. the WHERE clause for strings searched for becomes true if HTML-entities or not are present in the database accordingly. If you want to insert GET parameters in your WHERE clause here, echo them with PHP.
e=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
LIKE status. PHP? Only records with this status will be rendered. more
- Leave empty (not recommended) for all status, e. g. [name*,youremail*,userupload1|survey|ALL|?||<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
- Standard status of new records is 1
- Change status of records cf. page 38 in the manual
f=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
HTML or PHP to render before each(!)* record. *That's why you have to put the table-tag outside of [...:dat] according to pdf p.8.
g=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
HTML or PHP to render after each(!)* record. *That's why you have to put the table-tag outside of [...:dat] according to pdf p.8.
h=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
HTML or PHP to render between fields (if more than one). If you want different HTML-Code after every field, separate the code snippets with a double-at @@ e.g <br>@@,@@<hr>@@:
i=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
Evoke an ORDER BY and optionally set a LIMIT. Details
NOTICE: Since u5CMS 5.3.8 you can set ? as parameter i or a question mark combined with a limit, e. g. [...|? LIMIT 0,100|...:dat] (capitalization mandatory!). The question mark takes the ORDER BY from the GET-parameter sort, e. g. &sort=ORDER BY datacsv DESC (capitalization mandatory! In the URL-field of the browser it may look like this: &sort=ORDER%20BY%20datacsv%20DESC). If you do not mention LIMIT in parameter i, you could add LIMIT to the GET-parameter sort too e. g. &sort=ORDER BY datacsv DESC LIMIT 0,100 (with that you can programmatically create a pageing).
Attention: The value of GET-parameter sort MUST start with the capitalized ORDER BY statement. But: If you define the order directly in parameter i of the :dat-command, there you MUST NOT write the string ORDER BY, just start with the fieldname e. g. datacsv to be ordered by.
Advice: For productive environments, when taking sql query pieces (e. g. LIKE, ORDER BY) from GET parameters, set $showdatcommandsqlerrors='no'; in config.php (available since u5CMS 5.5.2).
j=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
What to do with line breaks in data, e. g. write <br> here. If not defined it will render a pipe. PHP?
Before u5CMS 5.4.5 this parameter was not rendered if [...:dat] was within an HTML-section [h:]...[:h]
k=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
E. g. 300x0 means "if userupload is an image (jpg, jpeg, gif, png), then do not render a link but the image with width 300 and any height". Add an exclamation mark as last character if jpg-images shall be physically re-sized: 300x0!. PHP? Don't know how your users can upload files? See User Uploads How To.
Since u5CMS 5.3.9, denoting both width and height (e. g. 500x300 or 500x300!) does not distort the image, its ratio is preserved, and no edge is longer than your declaration (i. e. max-width and max-height). However, if you want do distort it, write + instead of x (e. g. 500+300 or 500x300!).
At the bottom of your page, add the following code:
[h:]
<script>
jQuery(document).on('click', '.formdataimg' , function() {
window.open(this.src);
});
</script>
[:h]
l=String that must be the the content of the field a. If true, the string defined in parameter m will be rendered; if false, nothing will be rendered. PHP?
m=This string will be rendered instead of the field's value if l is true. PHP?
n=e. g. [name*,youremail*,userupload1|survey|ALL|?|1|<tr><td>|</td></tr>|</td><td>|datacsv ASC|<br>|0x100!|||0!:dat]
Switch HTML interpretation on (works properly since u5CMS 4.5.7). Value: 0 or empty=none. 1=for Notes only. 2=for other Fields only. 3=for all Data. 4=do not interpret HTML but u5CMS-syntax. 5=interpret both (does not render parameter j nor its fallback. HTML-parts have to be between [h:] and [:h]). Since u5CMS 3.9.5 you may add an exclamation mark (e. g. 0! or 1! etc.) to switch automatic linkification on (optimized since u5CMS 5.7.4). PHP?
Examples
Render all data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire:
[name*,firstname*|questionnaire|ALL:dat]
questionnaire is the name of the page on which the form is, i. e. the value of the GET parameter c of its URL;
name* and firstname* reference to the respective input fields' name-Attributes name_mandatory and firstname_mandatory in the HTML-Code (the asterisk is necessary because of the suffix _mandatory). Cf. Where do I find the fieldnames?
If you offer uploads, you cannot see the fieldnames in your HTML code, you have to know that the filedname of
<script src="upload"></script> or <script src="Pupload"></script>
is userupload1 (or userupload2 if it's the second one in your form).
If you declare your uploads as mandatory, e. g.
<script src="upload_mandatory"></script> or <script src="Pupload_mandatory"></script>
the fieldname becomes userupload1* (i. e. add an asterisk).
Render all data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire if the respective records are set to status 1:
[name*,firstname*|questionnaire|ALL||1:dat]
Render all data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire if the respective records are set to status 1. Further, write the string PERSON: before rendering the data, then make a line break before rendering the next record. Render a non-breaking space between every field within a record.I do not want the same output between every field
[name*,firstname*,street*,zip*,place*|questionnaire|ALL||1|<h1>PERSON:</h1>|<br>|,@@<hr>@@<br>@@ :dat]
[name*,firstname*|questionnaire|ALL||1|PERSON:|<br>| :dat]
Render the data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire if the id equals the GET-parameter &id= and the respective record is set to status 1. Further, output the id of the record followed by a link to pagename with the respective id in the GET-parameter. Then close the link and draw a line between every record. Render a non-breaking space between every field within a record.
[name*,firstname*|questionnaire|?||1|$id: <a href="?c=pagename&id=$id">|</a><hr>| :dat]
Render the data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire if the id equals the GET-parameter &id= and the respective record is set to status 1. Further, output the id of the record followed by a link to pagename with the respective id in the GET-parameter. Then close the link and draw a line between every record. Render a non-breaking space between every field within a record. And all that only if the logged in authuser is mentioned in parentheses in the Notes-field of the respective record (typically used to add reviewers to abstracts in a review process).
[name*,firstname*|questionnaire|?|(authuser)|1|$id: <a href="?c=pagename&id=$id">|</a><hr>| :dat]
Render all data of the fields name* and firstname* coming from your form containing u5CMS page named questionnaire if I myself did originally enter this data.
[name*,firstname*|questionnaire|ALL|SELF:dat]
Extended Example: Step by step from the most easy query for rendering form data to a very nice output-table (pdf)
Recursive variables (e. g. $id)
Usage example[name*,joke*|!jokesform|||1|<tr>
<td><a href="e?n=!jokesform&id=$id">edit</a></td>
<td>|<td><a href="d?n=!jokesform&id=$id">delete</a></td>
</tr>|</td><td>|id desc:dat]
This example is taken from here.
$id=id
$st=status
$se=sent fixed since u5CMS 3.9.5
$la=lastmut
$ip=ip fixed since u5CMS 3.9.5
$hu=humantime
$au=authuser who stored the record at hand available since u5CMS 3.9.5
$ah=md5 hash of lowercased $au available since u5CMS 3.9.5
Remark: Creating internal Links With Get Parameters
cf. GET parameters and anchorsAbout Whitespace
The original command is [fieldname|formname:dat]. However, the command also works when whitespace is added, e. g.:[fieldname| formname :dat]
or
[fieldname|
formname:dat]
However, be aware that renaming the object name of the source form formname only automatically changes the :dat-command, when NO whitespace is around formname.
