Example of sql data

ie ieraora 07 Nov, 2021

Hello, Can I have an example of sql data in Actions Area:

I try, for example, this:

SELECT `Prezzi1`.`cf_id` AS `Prezzi1.cf_id`, `Prezzi1`.`g_id` AS `Prezzi1.g_id` FROM `#_cf_product_data-2` AS `Prezzi1`;

but I don't see in debug the 'Model' that i can use, for example {var:Prezzi1.cf_id}.

Thank you.

sh shahabpk 16 Nov, 2021

note the action name. It will be like sql_data_1 or something similar. Then you can access the data by using the short code like

{var:sql_data_1.0.fieldname}

0 indicates the row number in the array

ie ieraora 16 Nov, 2021

Thank You shahabpk for Reply.

Is correct with simple query, but if I've for example:

SELECT `Prezzi1`.`cf_id` AS `Prezzi1.cf_id`, `Prezzi1`.`g_id` AS `Prezzi1.g_id`, `Galleria`.`nome_galleria` AS `Galleria.nome_galleria`
FROM `xijhd_cf_product_data-2` AS `Prezzi1` LEFT JOIN `xijhd_cf_gallerie` AS `Galleria` ON `Prezzi1`.`g_id` = `Galleria`.`id_galleria`;

the debug is

[sql_data_100] => Array
(
[result] => SQL processed successfully!
[sql] => SELECT `Prezzi1`.`cf_id` AS `Prezzi1.cf_id`, `Prezzi1`.`g_id` AS `Prezzi1.g_id`, `Galleria`.`nome_galleria` AS `Galleria.nome_galleria`
FROM `xijhd_cf_product_data-2` AS `Prezzi1` LEFT JOIN `xijhd_cf_gallerie` AS `Galleria` ON `Prezzi1`.`g_id` = `Galleria`.`id_galleria`;
[mode] => select
[var] => Array
(
[0] => Array
(
[Prezzi1.cf_id] => 3228
[Prezzi1.g_id] => 1126
[Galleria.nome_galleria] => Galleria di prova
)

[1] => Array
(
[Prezzi1.cf_id] => 3229
[Prezzi1.g_id] => 1126
[Galleria.nome_galleria] => Galleria di prova

And if I use {var:sql_data_100.0.Galleria.nome_galleria} my result is blank.

Gr GreyHead 17 Nov, 2021

Hi ieraora,

I suspect that the problem may be the . in the item names which is breaking the parsing. Please can you try something like SELECT `Prezzi1`.`cf_id' AS 'cf_id`, 'g_id' AS 'Prezzi1.g_id', 'Galleria'.'nome_galleria' AS 'nome_galleria'

That is replacing e.g. AS 'Prezzi1.cf_id` with AS 'cf_id'

Bob

ie ieraora 17 Nov, 2021

I try to use custom because I need to have this result:

1 - A list of products, that I done with REPEATER AREA.
2 - For every product, a dropdown of a list of prices.

I tried also with read data > relations, but I think that is necessary use a CONCAT?

If yes, where I configure this?

Gr GreyHead 18 Nov, 2021

Hi ieraora,

Have you tried the change I suggested?

Sorry, but I don't understand your last post. What exactly is the problem you have there?

Bob

sh shahabpk 09 Dec, 2021

This is similar to the help I asked six months before
https://www.chronoengine.com/forums/posts/t109554/populate-table-list-with-database-sql-data-array
Hope someone can help help us. I think a solution to this question makes a lot of complex job easier for lots of people.
waiting for Max !

sh shahabpk 10 Dec, 2021

Hi ieraora,
As GreyHead suggested the issue is with using table aliases with coloumn name in SQL data. I think Model concept is not applicable to SQL data action . if you use custom name for coloumns it will work. use it like

SELECT `Prezzi1`.`cf_id` AS `cf_id`, `Prezzi1`.`g_id` AS `g_id`, `Galleria`.`nome_galleria` AS `nome_galleria`FROM `xijhd_cf_product_data-2` AS `Prezzi1` LEFT JOIN `xijhd_cf_gallerie` AS `Galleria` ON `Prezzi1`.`g_id` = `Galleria`.`id_galleria`

and use {var:sql_data_100.0.nome_galleria} will return first row data. I tested it and worked

ie ieraora 13 Dec, 2021

Hi shahabpk, thank you for your reply.
Is correct yours info. I thought to have a print of Model name, because I wanted get this for Dropdown Dynamic selection.
I am now following another way.

Thank you