[TYPO3-project-formidable] How to perform user defined SQL queries?
Franz Werner
f.werner3 at gmx.net
Tue Dec 19 11:31:55 CET 2006
Hi Jerome,
first of all, thank you for your reply. I tried some of the methods you
mentioned in your previous posting, but I'm afraid I can't get it working.
Is there any actual and complete example extension, like
ameos_demo_formidable that makes use of the nightly build version 0.6?
It seems that the methods below are incompatible with version 0.5.0. Or
is there any documentation available with more infos on how to use
datahandlers?
TIA,
Franz.
Jerome Schneider schrieb:
> Hello list and thx a lot François :)
>
>
> -> you can modify several things (doing a sql query and modify things)
> while saving the form by using :
>
>
> -------------------
>
>
> Here is the complete way of doing this:
>
> <datahandler:DB>
> <tablename>table_name</tablename>
> <keyname>key_name</keyname>
> <labelname>label_name</labelname>
> <process>
> <beforeinsertion>
> <userobj>
> <php><![CDATA[
>
> $aData = array_pop(func_get_args());
>
> $aData["my_existing_field"] = "this is modified before insertion";
> $aData["some_new_field"] = "this is added before insertion";
>
> return $aData;
>
> ]]></php>
> </userobj>
> </beforeinsertion>
> </process>
> </datahandler:DB>
>
> -> in a lister, you can reduce the result or by using a hidden field or
> by giving directly the sql code
>
> What Francois said about the additional WHEREs syntax is exact.
> there are other ways for doing the same:
>
>
>
> ----------------
>
>
> 1 Injecting fields in the search array
>
> <datahandler:LISTER>
> <!-- ... -->
> <process>
> <beforesearch>
> <userobj>
> <php><![CDATA[
>
> $aData = array_pop(func_get_args());
>
> $aData["disable"] = "0";
> $aData["disable"] = "0";
> $aData["my_record_type"] = "THEGOODONES";
>
> return $aData;
>
> ]]></php>
> </userobj>
> </beforesearch>
> </process>
>
> <!-- ... -->
> </datahandler:LISTER>
>
>
> --------------------------
>
> 2 OR :
>
> You can achieve the same using PASSTHRU renderlets in your search
> form for injecting values at runtime:
>
> --------------------------
>
> 3 OR: You can provide a "view" for working on it:
>
> <datahandler:LISTER>
> <!-- ... -->
> <compiledatasource>
> <view>
> <select><![CDATA[
> select
> prod.*,
> fic.uid as ficheuid,
> fic.pdftext as fichepdftext,
> dist.uid as distuid,
> dist.nom as distnom,
> cas.uid as caseuid,
> cas.nom as casenom,
> fam.nom as famillenom,
> fam.uid as familleuid,
> cat.nom as categorienom,
> cat.uid as categorieuid,
> sscat.nom as sscategorienom,
> sscat.uid as sscategorieuid,
>
> CONCAT_WS(
> ',',
> CONCAT('2', LPAD(cas.uid,11,'0'),'0'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'1'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'2'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'3'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'4'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'5'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'6'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'7'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'8'),
> CONCAT('2', LPAD(cas.uid,11,'0'),'9')
> ) as caseean,
> CONCAT_WS(
> ',',
> CONCAT('1', LPAD(fic.uid,11,'0'),'0'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'1'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'2'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'3'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'4'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'5'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'6'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'7'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'8'),
> CONCAT('1', LPAD(fic.uid,11,'0'),'9')
> ) as ficheean
>
> from
> (
> select * from
> tx_ameosceaged_produit
> LEFT OUTER JOIN
> (
> SELECT
> produit_uid,
> group_concat(commentaire, ' ') as commentaire
> FROM
> tx_ameosceaged_commentaire
> GROUP BY
> produit_uid
> ) concatcomment
>
> ON tx_ameosceaged_produit.uid = concatcomment.produit_uid
> ) prod,
> tx_ameosceaged_fiche fic,
> tx_ameosceaged_case cas,
> tx_ameosceaged_distributeur dist,
> tx_ameosceaged_famille fam,
> tx_ameosceaged_categorie cat,
> tx_ameosceaged_sscategorie sscat
>
> where 1
>
>
> and cat.famille = fam.uid
> and sscat.categorie = cat.uid
> and prod.sscategorie = sscat.uid
>
> and fic.casenum = cas.uid
>
> and prod.fiche = fic.uid
> and fic.societe = dist.uid
> ]]></select>
> </view>
> </compiledatasource>
> <!-- ... -->
> </datahandler:LISTER>
>
>
> -------------------------
>
> 4 OR:
> you can alter the way your searchfields
> are processed during search
>
>
>
> <renderlet:CHECKSINGLE
> name="xmasspecific"
> label="Display only Xmas specific items">
>
> <onclick submit="true" />
> <search>
> <overridesql>
> <userobj>
> <php><![CDATA[
>
> $aParams = array_pop(func_get_args());
>
> $sUids = $this->_oParent->getUidsXmasHelpedItems();
>
> if($sUids != "") {
> return "uid IN(" . $sUids . ")";
> // if you're working on a compiled table,
> // prefix the field name with plain ( ie: plain.uid )
> } else {
> return "1=0";
> }
>
> ]]></php>
> </userobj>
> </overridesql>
> </search>
> </renderlet:CHECKSINGLE>
>
>
>
> Happy Xmas season everyone :)
>
> Jerome
>
> François SCHOSSIG a écrit :
>> About the userdefined SQL-queries, you have several possibilities
>> depending on what you want to do.
>> -> use a userobj in the renderlet depending on your query :
>> <userobj>
>> <php><![CDATA[
>> // the SQL query that produces the right result
>> ]]></php>
>> </userobj>
>>
>> -> you can modify several things (doing a sql query and modify things)
>> while saving the form by using :
>> <control>
>> <datahandler:DB>
>> <tablename>table_name</tablename>
>> <keyname>key_name</keyname>
>> <labelname>label_name</labelname>
>> <process>
>> <beforeinsertion>
>> <userobj>
>> <php><![CDATA[
>> // Your code
>> ]]></php>
>> </userobj>
>> </beforeinsertion>
>> </process>
>> </datahandler:DB>
>>
>> -> in a lister, you can reduce the result or by using a hidden field
>> or by giving directly the sql code
>> <search>
>> <atstartup>true</atstartup>
>> <sql>
>> <wheres>
>> <where>
>> <term>fieldName1</term>
>> <comparison><![CDATA[IN]]></comparison>
>> <value>
>> <userobj>
>> <php><![CDATA[return theValue1;]]></php>
>> </userobj>
>> </value>
>> </where>
>> <logic>AND</logic>
>> <beginbrace />
>> <where>
>> <term>fieldName2</term>
>> <comparison><![CDATA[IN]]></comparison>
>> <value>
>> <userobj>
>> <php><![CDATA[return theValue2;]]></php>
>> </userobj>
>> </value>
>> </where>
>> <logic>OR</logic>
>> <where>
>> <term>fieldName3</term>
>> <comparison>=</comparison>
>> <value>0</value>
>> </where>
>> <endbrace />
>> </wheres>
>> </sql>
>> </search>
>>
>> About the other things, Jérôme should answer directly.
>> --
>> F. SCHOSSIG, ICT Manager
>> Assemblée des Régions d'Europe
>> http://www.a-e-r.org
>>
>>
>> Le 18 déc. 06 à 17:59, Franz Werner a écrit :
>>
>>> Hi,
>>>
>>> is it possible to perform userdefined SQL-queries when selecting data
>>> from and inserting data into a database?
>>>
>>> More specific: I would need to join several tables before displaying the
>>> data. In its standard behavior formidable uses the form name to select
>>> the corresponding table, and the ids of the input elements to select the
>>> corrensponding row.
>>>
>>> This seems to be no problem when using user defined renderlets for list
>>> boxes etc. but I coudn't find an example in how to do the same with the
>>> "HTML display list".
>>>
>>> And is there a possibility to insert data into serveral different tables
>>> when creating a new record in the database? I.e. I would like to
>>> validate all elements of a form and insert the data in two or more
>>> different tables.
>>>
>>> TIA,
>>> Franz.
>>> _______________________________________________
>>> TYPO3-project-formidable mailing list
>>> TYPO3-project-formidable at lists.netfielders.de
>>> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-project-formidable
>>>
>>
More information about the TYPO3-project-formidable
mailing list