Gossamer Forum
Home : Products : DBMan SQL : Discussion :

adding records - two select fields, one updates the other?

Quote Reply
adding records - two select fields, one updates the other?
Hi,

I have a tricky problem. To explain it I need to describe the database:

company - pk : comp_id - list of companies (2000)
product - pk : product_id - list of products (222 )
category - pk : cat_id - list of 7 categories; each product can belong to 1 or more category; hence
category_product - pk : cat_id, product_id - one row for each valid category / product combination. (295 rows)

Ok so far?

Companies can edit their own details and can make their own entries for the products they produce. These entries go into the following table:

company_product - pk : company_id, cat_id, product_id - one row for each company / category / product. (Maybe I should have called this table company_category_product - but it seemed a bit long) (4500)

All of this is working.

The problem is with the company_product add form. This has two dropdowns on it - one for category and one for product. Companies choose a category and then choose a product. But it's possible for them to choose illegal category / product combinations, and it's likely that they will.

So how to solve this?

The ideal would be that when the company chooses a category, the list of available products is dynamically updated from the database, by running a query against the category_product table. I'd be very interested in hearing from anyone who thinks they can do this, even if they want paying (GT, anybody else ...?). I'm not keen on solutions involving javascript with all the data embedded in the code...

Other workarounds:

idea 1) - build in a checking routine so illegal category / product combinations can't be saved. Companies might end up getting a lot of error messages which would be annoying.

idea 2) - split the company_product add form into two forms. On the first form choose a category and click "next". Depending on category chosen go to 1 of 7 pages where sub-list of products is displayed. This would be quite acceptable I think, and sounds easier to me than the idea solution above.

idea 3) - can't think of one.


All suggestions welcomed.

thanks
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] adding records - two select fields, one updates the other? In reply to
Hello Timbo

I need exactly the same, Iīm doing this by a javascript combobox with 3 levels, but is not the best way

Itīs for vehicles, so I use the factory, model and version

I donīt know if the javascript can pull the values out of the mysql, but maybe is another way

Fábio
Quote Reply
Re: [assombracao] adding records - two select fields, one updates the other? In reply to
Hi

I've decided to go for a version of idea 2).

I'm going to build 7 add forms, one for each category. (Perhaps in the future when I'm better at all of this, I'll do it another way).

So I just need a dropdown on each form which has the products in that category.

I need the dropdown to be built from the following query :

SELECT product_code, product_name
FROM `Product_Category` WHERE
category_code = 'TA'

SELECT product_code, product_name
FROM `Product_Category` WHERE
category_code = 'FL'

etc.

I can't use the generate_full_down routine because it requires the primary table to have a single primary key, and that isn't the case here.

Could someone help me out with a bit of code that will generate the dropdown?

thanks
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] adding records - two select fields, one updates the other? In reply to
Just simple create a global template like:

sub {
my ($select_name, $cat_id) = @_;
my $tags = GT::Template->tags;

my $results = $DB->table('Product_Category')->select({ category_code => $cat_id }, ['product_code', 'product_name'])->fetchall_hashref;

my (@values, @names);
foreach (@$results) {
push @values, $_->{product_code};
push @names, $_->{product_name};
}

return $tags->{home}->{disp}->select ( {
name => $select_name,
values => \@values,
names => \@names,
value => $tags->{$select_name},
blank => 1,
});
}

Now you can use <%global_name('select_name', category_code)%> tag in template.

Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
Thank you for this.

I created the global exactly as you wrote it and called it product_select.

In the template I call the global like this:
<%product_select('product_code', 'TA')%>

The global returns a select field, but it's empty. So it looks as if the select in the global is returning an empty data set.

Here's the equivalent query in phpmyadmin:

SELECT product_code, product_name
FROM `Product_Category` WHERE
category_code = 'TA'

which returns 29 rows

product_code product_name
BE Belt Leather
BL Bag & Luxury Goods Leather
BO Bookbinding Leather
etc etc

I must be doing something daft. Can you see what I'm doing wrong?

thanks
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] adding records - two select fields, one updates the other? In reply to
Just change it a litle bit like:

sub {
my ($select_name, $cat_id) = @_;
my $tags = GT::Template->tags;
my $db = $DB->table('Product_Category');
my $results = $db->select({ category_code => $cat_id }, ['product_code', 'product_name'])->fetchall_hashref;
my $html = "<select name='$select_name'><option value=''>----</option>";
foreach (@$results) {
$html .= ($tags->{$select_name} eq $_->{product_code}) ? "<option value='$_->{product_code}' selected>$_->{product_name}</option>" : "<option value='$_->{product_code}'>$_->{product_name}</option>";
}
return $html;
}

It should work.

TheStone.

B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
Thank you very much. That is now working

Out of interest, can anyone tell me, would I be able to modify the query to this, or is that a big job?

SELECT Product_Category.product_code, Product_Category.product_name
FROM Product_Category, Product
WHERE
Product_Category.product_code = Product.product_code
and category_code = 'TA'
Quote Reply
Re: [timbo] adding records - two select fields, one updates the other? In reply to
Just simply create a relationship between Product_Category and Product by using Relationships feature. After that, you can use the code below:

my $results = $DB->table('Product_Category', 'Product')->select({ category_code => 'TA' }, ['Product_Category.product_code', 'product_name'])->fetchall_hashref;

Hope that helps,

TheStone.

B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
I have a very similar requirement, and need help. I have a table called 'Listing', each Listing belongs to a specific Category and a SubCategory. I want that in adding a listing once the user has selected the Category, the options of SubCategory displays only the valid SubCategories depending on the selection of Category by the user. I have following tables.

Listing Category SubCategory
------ -------- ------------
ListID CatID SubCatID
Description CategoryName SubCatName
CatID CatID
SubCatID

I was able to establish relationships that the Add form for Listing shows the CategoryNames and SubCatNames. But it shows all the SubCatNames that are in the SubCategory table. I want to show only the ones that belong the selected Category.

How can I achieve this -that in the Add form once the Category is selected, then SubCategory control is constructed automatically with relevant rows from SubCategory table.

I tried defining Global Template as described in the above post, but could not make it work.

I would appreciate if someone can help me with this.

Thanks.

TIF
Quote Reply
Re: [TIF] adding records - two select fields, one updates the other? In reply to
This can be done with javascript. Follow the steps below to work it out:

1. You need two globals for loading data from Category and SubCategory table.
sub {
return { cats => $DB->table('Category')->select()->fetchall_hashref };
}
sub {
return {subcats => $DB->table('SubCategory')->select()->fetchall_hashref() };
}

2. Here is the add form

<html>
<head>
<%global_cat%>
<%global_subcat%>
<script type="text/javascript">
var subcat = new Object();
<%loop subcats%>
subcat[<%SubCatID%>] = { name: '<%escape_js SubCatName%>', cat: <%CatID%> };
<%endloop%>

function loadSub(cat) {
var subObj = document.getElementById('subcat');
while (subObj.options.length > 0) { subObj.remove(0); }
for (var k in subcat) {
if (subcat[k].cat != cat) continue;
var o = document.createElement('OPTION');
subObj.options.add(o);
o.innerHTML = subcat[k].name;
o.value = k;
}
}
window.onload = function() {
var catObj = document.getElementById('cat');
if (cat.selectedIndex >= 0) {
loadSub(cat.options[cat.selectedIndex].value);
}
}
</script>
</head>
<body>
<select id="cat" name="CatID" onchange="loadSub(this.options[this.selectedIndex].value)">
<%loop cats%><option value="<%CatID%>"><%CategoryName%></option><%endloop%>
</select>
<select id="subcat" name="SubCatID">
</select>
.........................
</body>
</html>


Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
Thanks a lot. Both controls worked well.

I was not able to cleanly place the add form html part ---

<select id="cat" name="CatID" onchange="loadSub(this.options[this.selectedIndex].value)">
<%loop cats%><option value="<%CatID%>"><%Cat%></option><%endloop%>
</select>
<select id="subcat" name="SubCatID">

I am using the 'default' template set. I have placed the above code in the add_form.html template which duplicates the controls.

One set of Category and SubCat are being displayed because of the sub create_listings being called in Dbsql::Relation::HTML::generate_add_form or Dbsql::HTML::generate_add_form. I could not figure out how to suppress them. How does one suppress them?

Thanks once again.

TIF


Quote Reply
Re: [TIF] adding records - two select fields, one updates the other? In reply to
I could not figure out where and how exactly I need to integrate this Javascript part. I want to use this in such a way that it works in all - Add, Modify, Search forms in a similar fashion. It should replace the select controls that DBMan SQL generates with the ones that this Javascripts generate.

Can someone please help me with this, or point me to where exactly changes need to be made to achieve this. Has anyone done similar stuff?

Thanks.

TIF
Quote Reply
Re: [TIF] adding records - two select fields, one updates the other? In reply to
Hi,

In this case, you have to customize the template instead of using <%Dbsql::HTML::generate_add_form%>.

TheStone.

B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
Hi TheStone,

Can you please provide some more hints to how to do this. I am new to this and could not figure out how this would be done.

If I write a custom add-form for this and collect all the data for all the fields including the Cat and SubCat, how do I take these value back to DBMan SQL to store it in the database. If you can please show some sample code, I will try to build on it.

Probably I will have to do similar customisation for other templates for modify & search.

Thanks.

TIF
Quote Reply
Re: [TIF] adding records - two select fields, one updates the other? In reply to
All you have to do is replace the <%Home::HTML...%> tag with raw html code, i.e: here is the add form for Listing table:

<body>
Description: <input type="text" name="Description" value="<%if Description%><%Description%><%endif%>" size="50"/>
CatID:
<select id="cat" name="CatID" onchange="loadSub(this.options[this.selectedIndex].value)">
<%loop cats%><option value="<%CatID%>"><%CategoryName%></option><%endloop%>
</select>
SubCat:
<select id="subcat" name="SubCatID">
</select>
....



B.
Quote Reply
Re: [TheStone] adding records - two select fields, one updates the other? In reply to
Thanks a ton. I could make it work by customizing with raw HTML.

TIF