Can I do a detail page using two DB table queries?

Wappler General How To
#1

I am sure this is very simple but I can’t work it out. Can I create a detail page using the following as an example. These would be the two database tables

image

And the page would have the results display as follows. So if you hover/click on the brand on the left (in this case tbl_brand, id=2 “Honda”), the models and descriptions associated to that brand would show on the right, and you can then click on the Edit button to bring up a modal with an update form.

image

I can do a single query Data Detail page but I can’t seem to work out how to do this. Is it possible or do I need to approach it in a different way?

#2

Hi Neil,
Which part is not clear for you?

#3

To show the car brand is one query
SELECT brand FROM tbl_brand

The table on the right needs a second query
SELECT model, short_description FROM tbl_model WHERE tbl_model.brand_id_fk = tbl_brand.id

I can’t use Data Detail as far as I can see, or can I? I am just stuck on how and where to start with this. I am sure it is simple :crazy_face:

#4

Ah, ok - i thought something specific is causing the issue.

The logic here would be:

  • Create a server action for the brands
  • Create another server action for the models
    • filter the second server action query by a get variable for example ($_GET.brandid)

On the page - add the two server actions and create the repeat regions for them.
Create a variable (Data > Variable) and bind this variable in the second server connect brandid parameter.
Select the brands repeat region - dynamic events > mouse > click > set variable value > set the brand id there.

As soon as you select a brand the second server action will refresh as the variable will change.

1 Like
#5

Brilliant! Just the answer I was looking for. I will tackle this shortly, after my next meeting.

My problem @Teodor is that I get involved in too many different projects (product design, design for print, vehicle liveries, advertising, social media etc), webdev is just a small part of what I do, and so I keep breaking away and coming back to it. I love the way Wappler works and the way you and the team approach the development of this wonderful tool. I first hand-coded in the early Compuserve days, I used Symantec Visual Page (bet no-one remembers that!), was an early adopter of Dreamweaver and it’s various incarnations, and tried numerous other pieces of software but Wappler simply knocks spots off the lot of them. I just need to, and want to, spend more time using Wappler.

2 Likes
#6

Hi @Teodor, once again I am struggling with this., and I know it should be easy.

I followed your instructions but cannot get it to work. Here is what I did…

  • Created 2 Server Actions, each with a simple SQL Query, the 2nd has a $_GET variable for the ID.
  • I then went to the App Structure, added the two Server Actions.
  • I added a Data Variable to accept the ID. This was set with no Value in the Variable Properties.
  • I generated 2 tables to display the data using the Generators > Bootstrap 4 Table Generator.
  • On the 1st table (Brands) I selected the Table Body (tbody) which is the repeat part of the table and went to dynamic events > mouse > click > set variable value > set the brand_id there. I set it from the Table Body #brand_id. But this didn’t work.

In this screen shot I changed names but not the process

image

So the onclick event shows as dmx-on:click="menu_id.setValue(menu_id) but this doesn’t work.

If I manually enter a number such as dmx-on:click="menu_id.setValue('5') then I get the info relating to ID=5.

I tried selecting all of the options (on the screenshot) under Table Body such as # $index but no luck. I also selected all the options under Server Connect: conn_menu_read but with no luck.

As you can probably tell from my haphazard approach, I still cannot work out clearly what I should be doing. Your guidance would be very much appreciated.

#7

Don’t use the same ids for the variable and for the database column name … please rename your variable to something else and try again.

#8

OK, I have renamed the $_GET variable, checked, but still not working.

#9

Not the get variable, the variable on your page.

#10

Sorry @Teodor I am getting annoyed at my own inadequacies at being unable to make this work. Still the same issues.

#11

Please send me a link to your page, where I can check this.

#12

OK will do. Am having to upload pages and database, will send a link when done. Thanks

1 Like
#13

Hi Neil,

Not to take the wind out of Teodor’s sails, maybe you should have a look at https://www.youtube.com/watch?v=wjs1oyfwaMQ&t=2s

But before you do that, make sure that you do not have the same field name in two or more tables.

My naming convention is to always have the name of the table included in the name of the field as in
image
and
image

Edit: I own an old Passat with the W8 engine while my wife drives a Tiguan and my bachelor son a Touareg with registration plates VW2AREG. VW fanatics!

1 Like
#14

Wow! Thanks @Teodor, that sorted it.
Something so simple, cheers.

1 Like
#15

Thanks for looking @Ben, I did have a look and apart from “ID” everything else was unique. Teodor came back with a comment which sorted out my issue. So simple :smile:

Thanks for pointing out the DMXZone video, I keep forgetting to look there. I have learnt a few bits from that so happy days.

I used to always have VWs but in recent years went through Fords, Citroen and finally settled on a superb new SEAT! Haha still part of VW. In the old days had a VW Variant, Fastback, couple of Beetles, numerous Golfs and Polos. I now have a private plate (first one ever) which is my name.

1 Like