Hi there

I'm wanting to include an variable integer in a joined query but can't see how to do it in the query window.

The query i'm trying to replicate is as follows:

SELECT product_feature.*, product_feature_link.product_id FROM product_feature_link RIGHT JOIN product_feature ON (product_feature_link.feature_id = product_feature.feature_id AND product_id = %s)

Where %s is the product ID that is drawn from a url variable.

This is the only way i've found to produce a series of checkboxes on the page, with some of them prechecked depending on the product id. I've got it running in PHP using this query so now I just need to reproduce it using Server App.

Please could you show me how to do this?

Thanks

Hello, that's not possible in the join dialog in query builder.

You can use the custom query builder to directly paste the query you need ... www.dmxzone.com/go/33435/dmxzone-database-custom-query

Thanks Teodor.

Can't afford that I'm afraid.

Will stick with PHP for now I guess.

Bruce

You can also take a look at Wappler, which offers much more advanced options, than what you can do in DW and DMXzone extensions, at affordable monthly and yearly subscriptions ... wappler.io/

Hi Teodor

All I'm wanting to get dynamic checkboxes to work with Server Connect and/or App Connect when the details of which ones are already checked is in a separate database table. I really don't want to be spending more money to do something that basic.

I sent an email to dmx support a week ago but didn't get a response so i worked out how to do it with mysql and php and was just hoping if I posted that here then I could replicate that in Server App.

But like I say, if it can't be done then I'll just drop this part of the site from dmx and go back to PHP as it takes 2 minutes to do it.

I am not sure i understand what do you mean by:

Quote

when the details of which ones are already checked is in a separate database table

What is checked in what table and how is that related to your other data on the page... probably your logic is just wrong and relations are not properly setup initially.

Why not create a database view and join your tables as you wish there? Then you can directly use them in server connect.

Hi Teodor

May be best if I paste my earlieremail to support here as it does a better job of explaining what i'm after from the start.

================

I've got a record update page for products and their features.

The products are kept in one database table, the features are in a second table, and the a third table links features with products.

In summary the tables look like this:

PRODUCTS
product_id
product_name

FEATURES
feature_id
feature_name

FEATURE_LINKS
feature_link_id
feature_id
product_id

The update page that I've created selects the product using a url variable. The page then dynamically creates a list of features with a checkbox beside
each, drawn from the features database table.

But what I can't work out is how to get the page to dynamically tick only the checkboxes for which there is an entry in the third table. ie " if this product has this feature then pre-tick the checkbox "

I've tried using left join between the 'features' and 'feature links' tables, but it doesn't work as it doesn't show all of the entries in the 'features' table when i'm specifying just one product_id.

prntscr.com/rmgiaz

As i explained above - create a database view as you need it to return the values and use it in server connect then.

Thanks Teodor. Will give that a try.

I've not created a view before. What do i do about the product_id that's dependent on the url variable?

I can create a view using a constant value (eg '2') for the product_id easily enough, as in:

SELECT product_feature.*, product_feature_link.product_id FROM product_feature_link RIGHT JOIN product_feature ON (product_feature_link.feature_id = product_feature.feature_id AND product_id = 2)

but how do I make that a variable within the mysql statement?

Sorry i am a bit confused by what exactly are you trying to achieve.
Why not filter your query/view in the query filters tab, where you can assign $_GET variables as filter values?

That was the first thing i tried, but it doesn't result in what I need on the page.

What I want to do is a checkbox for every entry in the 'features' table
Then I want to go through the 'feature_links' table and for each checkbox, see if the current product has that feature, and if so put a tick in the checkbox for that feature.

Here are the tables with their current entries:

Features: prntscr.com/rmhokr
Links: prntscr.com/rmhpe2

if i do a join of the two tables then i get all of the feature_links for all product_id's and also, any of the features for which there are no feature_link entries - prntscr.com/rmhsnl

If i then try to filter that by product_id then i can get all the entries with a value for a specific product_id (eg '2') - prntscr.com/rmhvfa

Or i can get all the entries with a specific product_id AND all entries that don't have a link to a product - prntscr.com/rmhxwp

But this still doesn't include all of the features in the features database which I need to create the full list of features with checkboxes on my web page.

The only way i've found to do that is by introducing the product_id to the table join - prntscr.com/rmi18z

And that would allow me to put all the features, with checkboxes, on the page and then check those that currently apply to the product. This is on a product update page, so the current features need to be checked to start with and then the user can make changes if they wish.

Now, I would be happy to do this client-side if I could figure out how. And in my original email to DMX support I just stated what i was wanting to achieve and asking any way to do it. But then after a few days i found out how to do it by creating a single query using joins, and then came on here asking how to create that query using Server Connect.

Frankly, if there's any way to do it client side that would be great too.

So what's the logic for the checkboxes on the product pages - do all the features show on each product page, using a repeat region from a database query?

Yes they do

Then you need to use the checkbox group component and dynamically repeat the checkboxes inside it.


Then doing this, from another database query:

Quote

If i then try to filter that by product_id then i can get all the entries with a value for a specific product_id (eg '2') - prntscr.com/rmhvfa

you can assign the returned values to the checkbox group component value, it will automatically check the checkboxes which values are returned.
The expression used for the checkbox group component should be formatted like:

yourServerConnect.data.yourQuery.values("yourValueBinding")

Thanks Teodor

Will give that a go

Hi Teodor

i'm clearly not understanding how to do this. i've created two Server Actions, one that returns all the features in the features table and one that returns the feature_id of just those features with a specific product_id in the feature_links table. Then in App Connect i've added both as Server Connects.

I've used the first one to create the 12 checkboxes dynamically with a repeat region, and i've assigned values dynamically too.

What do i do now? If I try to assign 'checked' using the second Server Action it ends up ticking all of the boxes

prntscr.com/rmkc6l

You need to use the CHECKBOX GROUP component. Not regular checkbox control and applying checked attribute.
Then you apply the expression i posted above as a value for the CHECKBOX GROUP component and it will automatically check the checkboxes based on the value...

OK, just added a checkbox group, but can't see a checked attribute in the properties window. Have I done this right?

prntscr.com/rmldl1

Bruce, once again - you do not use any checked attribute with checkbox group!

You use the expression i posted above in the VALUE field of the CHECKBOX GROUP.
The checkbox group will automatically check the dynamic checkboxes inside it based on the value!

Ah, I understand now. Thanks Teodor

Hi Teodor

Had to take a couple of weeks off from this but back to it now. below is the code I have set up for this in a test page. Can you tell me if I've got it right as it's not working for me:

<div class="checkbox-group" id="categories" is="dmx-checkbox-group" dmx-bind:value="serverconnectListCategoriesChecked.data.queryListCategoryLinks.values("product_id.values")">
    <div dmx-repeat:repeat1="serverconnectListProductCategories.data.queryListCategories">
     	<label dmx-class:selected="checkbox.checked">
	      <input name="checkbox" type="checkbox" id="checkbox" dmx-bind:value="product_cat_id">
	      {{product_cat_name}}</label>
    </div>
  </div>

I can see there's a problem with repeated quotation marks in this part:

dmx-bind:value="serverconnectListCategoriesChecked.data.queryListCategoryLinks.values("product_id")"

But if i take out the inner quotation marks to fix that issue, like this:

dmx-bind:value="serverconnectListCategoriesChecked.data.queryListCategoryLinks.values(product_id)"

It still doesn't seem to work.

If you can let me now it I've got this bit right then it must be an issue elsewhere. I'm displaying the results of query on the page, so i know that the server action is working properly

I couldn't find 'values' as an option to select for the query when I clicked on the lightning to set the value of the checkbox group, so that may provide a clue: prntscr.com/rvdx80

I know I'm nearly there, I just don't know how i've got this last bit wrong. Really appreciate your patience and help with this.