Example of embedded SQL statements

Talk about programming tricks and examples

Moderator: alexandrleonenko

lambro
Master
Master
Posts: 102
Joined: Sep 13th, '10, 14:31
Location: Metz, France

Example of embedded SQL statements

Postby lambro » Mar 29th, '11, 08:24

Hi everyone !
Working with various examples from different users and Admin, I was able to use various sql strategies to display data in datagrids. I am currently using version 6.4.1 and I am very happy with it.
Still, since this might be of help to anybody out there (and I got a bit of spare time :) ), I will give one of my recipes. Some users may find it useful afterall, and may even upgrade it so that my own code will get better.
DB => mysql via phpmyadmin (version 5.0.34)
TARGET : Charge DG row dynamically with multiple DB access through sql statement - i.e. embedding sql statements

So here is the deal : I have to dependent DG, the Master giving a Foreign key to the Slave :
essdg01.png
essdg01.png (19.99 KiB) Viewed 10621 times

To simplify: DG01 (Product source) sends its id row as a foreign key for DG02 (Product management by language). I display DG02 in DG01's detail mode and generate the rows only if one row of DG01 is selected.

The first level of sql statement for DG02 reads as such :

Code: Select all

"SELECT ta_disponible.id_dispo, ta_disponible.ta_document_id_doc, ta_disponible.ta_langue_id_lang as llang,
ta_disponible.file, ta_disponible.vignette, ta_document.nom_doc, ta_document.version_doc,
ta_langue.id_lang, ta_langue.language, ta_langue.langue, ta_langue.abrev,
FROM ta_disponible,  ta_langue, ta_document
WHERE ta_disponible.ta_document_id_doc = ".$rid."
AND ta_disponible.ta_langue_id_lang = ta_langue.id_lang
AND ta_disponible.ta_document_id_doc = ta_document.id_doc";

you can see that in fact there is a double linked table scheme, using 3 different tables.
If you look at it carefully, you'll notice I use an alias for one column only -> we'll get back to it : ta_disponible.ta_langue_id_lang as llang.
The $rid variable corresponds to DG01 rowid.

If you take a look at the DG02 grid, you'll see the column : "create new or view product" -> it actually displays a link button which goes toward a third datagrid (and another table), which enables user to create actual products once the basename is set with the version in DG01, and it is associated with a specific language (+pdf file and thumbnail) in DG02.

To create the button, nothing new really,
here is the code to add :

Code: Select all

'<img src=\"images/CREATE.png\" alt=\"Create\" width=\"20\" height=\"17\">' as btnCreate.

There are numerous examples in this forum on how to implement and redirect data through GET or POST method, if you're interested take a look at http://www.apphp.com/forum/viewtopic.php?f=33&t=1531, or http://www.apphp.net/forum/viewtopic.php?f=26&t=2200.


What the real business of this post is about lies between the brackets after the "C" button

The data shown in there gives the number of products that already exist. In order to do so, I had to retrieve the row id of the products which foreign key 01 is DG02 primary key, for each language available.
level 2 of sql statement fetches product rowid count :
SELECT count(id_prod)as total FROM ta_product WHERE ta_disponible_id_dispo in (
level 3 of sql statement : each of DG02 row
select id_dispo from ta_disponible where ta_document_id_doc = ".$rid." and ta_langue_id_lang = llang
-> I use $rid, variable from DG01, and llang, which corresponds to a data returned in the first level of sql statement!

Here is how the final statement reads in DG02 template:

Code: Select all

"SELECT ta_disponible.id_dispo, ta_disponible.ta_document_id_doc, ta_disponible.ta_langue_id_lang as llang,
ta_disponible.file, ta_disponible.vignette, ta_document.nom_doc, ta_document.version_doc,
ta_langue.id_lang, ta_langue.language, ta_langue.langue, ta_langue.abrev,
CONCAT('<img src=\"images/CREATE.png\" alt=\"Create\" width=\"20\" height=\"17\">','(',
(SELECT count(id_prod)as total FROM ta_product WHERE ta_disponible_id_dispo in (select id_dispo from ta_disponible where ta_document_id_doc = ".$rid." and ta_langue_id_lang = llang )),')') as btnCreate
FROM ta_disponible,  ta_langue, ta_document
WHERE ta_disponible.ta_document_id_doc = ".$rid."
AND ta_disponible.ta_langue_id_lang = ta_langue.id_lang
AND ta_disponible.ta_document_id_doc = ta_document.id_doc";


Now I can look directly at DG02 to know whether I have already created a product, or not.
essdg02.png
essdg02.png (25.69 KiB) Viewed 10619 times


In the image above, product xxxx-0111-E4(English) has one reference, xxxx-0111-FR(French) does not have any, and xxxx-0111-ES(Spanish) has two.
Through embedding sql statement, one can truly obtain marvellous results with apphp datagrid pro !
@+ !

Return to “Programming Tricks”