Connect two tables

Hi @wozek86,

Thank you for upgrading to premium!

You can use the Query Builder to combine two tables. If you’re new to SQL queries, you can use the Visual Query Builder:
https://wpdataaccess.com/docs/query-builder/visual-query-builder/

Hope this helps! Let us know if you have more questions.

@kim L Thank you for your response. The jquery solution works great in publishing. I would like to be able to combine them into a Data Project. Is it possible? So far I have used the parent / child solution

Hi @wozek86,

Thanks for the reply!

Could you give us more details about the project you’re making?

We’ll be able to help you more effectively if we know more about it.

If it’s alright, could you also detail how you’ve utilized the parent/child method?

Have you had the chance to check out Data Forms?

We look forward to your response.

Thank you for the quick reply. I am using MAPsvg plugin where meta data is used in two tables. In one of them there is a description for a given region, and in the other there is a status (available / unavailable). I wanted to be able to combine these two tables and sort in a line by ID in one line. It works great when posting on the frontend. However, for the backend for the administrator, to be able to display the status of a given area, I have to click on a given line. I would like to be able to present the table in the admin field as for the frontend.

Hi @wozek86,

Can you share some screenshots of the front-end and font-end? Please use the contact form on the plugin website if you prefer to share this information in private.

Thanks,
Peter

Ok, I’ll provide the screenshots here. My frontend was made with Query Builder. I combined two tables here (objects and object status)
When I try to do this in DATA PROJECT for the backend I cannot combine these two tables to display objects and statuses in a single row.

Frontend:
https://freeimage.host/i/ZFHI71

Backend:
https://freeimage.host/i/ZFHvY7

Hi @wozek86,

Thank you for the screenshots! Now I understand what you are looking for.

You cannot use a query in a Data Project like you do in the Data Publsiher. But you can use a view. Just create a view for your query and use that view in your Data Project.

Here is the MySQL documentation for creating views:
https://dev.mysql.com/doc/refman/8.0/en/create-view.html
You can do this from the Query Builder.

Adding a link to edit your view data is a bit more complicated. Views are not updateable, so you need to add link to one of the tables in your join or both using a dynamic hyperlink. Dynamic hyperlinks are explained here:
https://wpdataaccess.com/docs/data-explorer/dynamic-hyperlinks/
The dynamic hyperlink needs to contain the primary key for table you want to edit.

Does this make sense?

Thanks,
Peter

Thank you for your response. I have one more question. I would like the status column to display text instead of the status. There are currently three states (0,1,2). How can I assign a given text for each state in the view table

Hi @wozek86,

You have two options to achieve the desired result:
(1) Use an enum column
(2) Use a lookup

The first option is the simplest and saves you a lot of work any time you use it. With the premium version itis very easy to enable inline editing in your list table. This feature is explained here:
https://wpdataaccess.com/docs/data-explorer/column-settings/#inline-editing

When you enable inline editing for your status column, the plugin automatically converts your enum values to a listbox and allows the user to change the status without the need to open the data entry form for update.

A lookup works as well, but you to enable it for each list table and data entry form. For enum columns this happens automatically.

Does this help?
Peter

Excellent thank you very much. This solves my problem ??

Great! ??