Data from 2 or 3 database tables listed in one layout

[famousmag]

Hey guys,

It would be helpful to hear a suggestion on how to present some data in a page.

I have 3 database tables , Orders, Shipments and Payments
I would like to list them (ordered by date) in a way like the picture below:

The fields in picture are the fields I need (and they are already part of each table’s schema…

The way I’m thinking of working is:

• Pull the data from each table ordered by date
• Assign these values to an Array
• Present the Array’s data in my page

(Just in case, we are talking about 10-15 records maximum in total)

If not an Array then which could be the way to solve it?
I do not ask for the exact way to build this structure, but only the way to process in order the page not to be slow and resource/traffic effective.

Any suggestion or idea would be much appreciated!

wappler 5.6
PHP
Mysql

Thanks in advance
Tasos

[Teodor]

Take a look at:

Using Data Transformations in Server Connect

Data Transformations Data Transformation options are available in Server Connect. Using them you can easily combine different data sources, add, rename or remove columns from an existing data set. The options available in the Data Transformations are not editing your actual data set, they clone the data and allow you to manipulate it, so your original data source remains unchanged. You can use the Data Transformations with any collection/array such as - Database Query, API Data Source or any…

[famousmag]

Instant solution…
One beer now and a box of beers when I accomplish this structure brother.

You are the one

Thank you brother

[Teodor]

@famousmag

One beer now and a box of beers when I accomplish this structure brother.

Will be in Sithonia after 1st of July

[famousmag]

Sithonia, Chalkidiki. Nice!

I wish we could meet brother but not on vacation at this period.

Wish you to have a good vacation!

[Antony]

I use MySQL views for this.

Lots of advantages, they are well worth learning about if you do this kind of thing a lot!

You need to code them but the learning along the way is immense!

[famousmag]

Thanks for your input @Antony.

I have to check on what MySQL views are… Yes, I’m working on commercial type of applications (products, clients, agents, orders etc and a lot of this kind of reports are needed)

I will search for it.
Thanks!

[famousmag]

@Teodor I don’t know if I got it right, probably I have to read more this tutorial in order to understand.
Just in case, I created a representation of what I want to do…
If you believe that what I am trying to do can be accomplished with data transformations, just tell me a word “yes” and I will keep on trying.

[Teodor]

Maybe explain in more details what data do you have and examples of what exactly are you trying to achieve.

[famousmag]

[Teodor]

In this case you will have to use a custom sql query and use UNION ALL https://www.w3schools.com/sql/sql_ref_union_all.asp to combine them together.

[famousmag]

I will check it.

Thanks a lot brother

[famousmag]

Hey @Teodor,

I followed tour suggestion and seems to work very nice but noticed something weird…
What I am getting back from the custom SQL is a Text instead of an Array.
Although it behaves like an array (the records come normal from the database) when I try to bind values it appears as a Text.
So, when I try to create a Total/Balance field and bind Collection->Sum->Property value to it there is no Collection.

Any idea of what I am doing wrong?

[famousmag]

An update… I can write by hand the code and it is working fine. It is just not pickable in the ui.