[TYPO3-project-formidable] How to perform user defined SQL queries?
Jerome Schneider
j.schneider at ameos.com
Tue Dec 19 10:37:56 CET 2006
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