Dear support,

I have created multiple tables called: L0001__Orders, L0002__Orders, L0003__Orders etc.

i would like to "UNION ALL" these tables according to some variables we have set for the wordpress user.

I have added some ACF "advanced custom fields plugin" fields to the user where we name the table number 0001,0002,0500.

When a user logs in and opens the table i want the data from the tables mentioned in his ACF fields to be "UNION ALL" showed as a table by wpdatatables.

Now i noticed that "UNION ALL" isn't working in the default SQL query field, and according https://tmsplugins.ticksy.com/ticket/1581208/ i have to create a PhpMyadmin view to achieve this.

The view works fine in the wpdatatables table, but now i have no idea on how to create a query with the ACF fields mentioned as variables in there.

(see attached for images of our setup, and the union all results on wpdatatbles)

Hello,

View: uebersicht_homepagebaukasten

Is this the solution you are looking for?

Best regards
Jens

Hi Jens,

Thanks for you reply.

Using a variable should indeed solve the issue, but the problem is that I have to “"UNION ALL" multiple tables called L0001__Orders, L0002__Orders, L0003__Orders etc. as you will see in my attachment.

But the Query "UNION ALL" is not giving the proper data  when used in the wpdatatables query field. (you will see in the feedback I had attached in my previous post)

So I assume I’m not able to use the query field and need to setup a phpMyAdmin view, but I don’t know how to implement the Wordpress user id, in aan phpMyAdmin view.

It did not change anything to the result.

I solved it now like the following:

1. Added a column to each table with the unique identifier of that table "L0001"
2. Create a VIEW in phpMyAdmin of all the tables that belong to the Wordpress user. 
3. Named the Wordpress user ID in the view's name, like "Userview_7__Orders"
4. Added    SELECT * FROM Userview_%CURRENT_USER_ID%__Orders    to the wpdatatables Query.
5. Now all data arrived in the wpdatatables table and i am able to filter between the tables with the standard filters.

So.
i,m not using the ACF fields anymore to attaches the tables to the user, but now i i attach them in the phpMysql view, buy running the "UNION ALL" in the view only for the tables that belong to the user.