I am just creating my first database with joined tables... I just got QUB to work and it is really great.... but I am having some trouble figuring out how to...... I have spent all day on the net trying to find out how do do this and still cant do it...

Well first......
I am trying to make a site that has products... I did have it all up and running but I figured out I was going about it all wrong... so back to the drawing board....I originally had a add product form that I could enter all the product info manually.... description, category, color ect....
Then I learned a little more about how databases work and I figured that I needed more than one table to store my product info... so that I don't need to re-enter the same info over and over again..... So to start with I just want to have a product table and a category table....... when I get that to work will move on to manufacturer table etc etc .....

So.... I am not sure what fields I need in the category table.... at the moment I have just cat_id cat_name and dateadd_cat
So I created a form that can insert info into the fields... and it works so I put in five categories.... just to test..... say..
Cat_id name
1 cars
2 boats
3 Trains
4 planes
5 rockets

So so far so good..... but here is where I get lost....and I don't know how it all works....

I also have a product table. with all the usual fields and a category field.... witch I want to link to the category table..

So I am not sure how to define what category the product is in so I figured it may work like this......

I made a form to add new products and I put a drop down menu with all the categories.... cars... boats.... etc.... I populated the menu from the category table with values from the cat_id field..... I really not sure if that is what I have to do....

So...... before all this I opened QUB and joined the two tables..... from the category field in the product table to the primary key field in the category table...... I added recordsets to all the pages I needed and ran the qub query I made in qub.....not sure again if this is what I have to do......

So ... my category table has all the categories and my product table is working my forms all work the dropdown menu in the add product form
has all the categories from the category table ... I added about ten products to test if it all works...
BUT when I go to the list products page.... a simple dynamic table it displays all the information about the products I added but in the category field it only displays the corresponding number.... which is how I set it ... but I wanted that number to somehow grab the category name from the category table...... NOW my head is spinning I have no idea how to get this to work......

I am sooo confused I cant even think of the right questions to ask
Am I on the right track? What I need to know is..... how to get it all working...... basically....
what fields do I need in the category table?
How does the whole thing work?

First this is just a test site to get joined tables to work..... that means I have no other pages that can interrupt me or give strange errors... I am also hosting this locally to test easilly.... I use WAMP server... never had any problems with it

So I did the following
I created the two tables in phpmyadmin

I deinfed a new site in DW and a DB conection all no problem

I created two form pages, one to insert into the product and one to insert into the category table.... the forms work fine

SELECT products.*, categories.*
FROM (products LEFT JOIN categories ON categories.cat_id=products.cate_pro)

I will try to explain what I have done a little better...

First this is just a test site to get joined tables to work..... that means I have no other pages that can interrupt me or give strange errors... I am also hosting this locally to test easilly.... I use WAMP server... never had any problems with it

So I did the following
I created the two tables in phpmyadmin

I deinfed a new site in DW and a DB conection all no problem

I created a form page to insert into the category table.... the form work fine

Then I created an insert product form with a field for product category.... witch has a drop down menu to choose what category in the menu properties dialog box it has two fields label and value
I chose to populate from database using the cat_name field as the label and cat_id as the value This is where I get lost.....

I create a dynamic list as an admin page to check that all the record s have been inserted correctly and to add delete edit etc and all the records are inserted ok But in the product category field there is the number value I assigned to the label when I configured the dropdown menu... not the category name....

So then I create a list product page with a normal dynamic table and have a advanced record set that uses the query I created in QUB and then I test and get the same thing only the cat_id in the category field......

So after hours and hours of gooling every possible combination of words to figure out how I retrieve the category name from the category table..... some call master / detail table ... parent /child tables and I still no closer to understanding how it is done.

my understanding is that only values that are the same in both fields will be displayed so that if the pro_cat value and the cat_id value are the same it will display the data in the cat_desc
So that if I chose say 'car' as the category in the drop down menu in the insert product page that would insert the number '1' as the product category field in the product table (as I described earlier when I configured the menu properties so that the lable "car" has a value of "1") and then that field pro_cat is joined to the primary key of the category table cat_id.... so that the number "1" in the pro_cat field in the product table is the same as the number "1" in the cat_id in the category table ("1 "being the id and "car" being the description) so I think that it should show the cat_des instead of the cat_id when I reference the product table in the recordset in my list product page .....but it does not ..... ..... well I think that is how it works.... but I know I am wrong.......

Am I correct or have I got it all wrong...... what I am trying to do is simple ... but I don't know how...

So does that all sound ok like it will work...... because it does not work and I cant figure it out

So you mentioned having a parent_id field in the category table ... is that necessary to display the cat_name.... how does that parent_id fiend join to the product table?

Sorry to make a short msg long but I hope someone has a better idea of what I am trying to do and what I have done....

I really just need someone to explain how when I create a list product page what recordset do I need to use do I need a simple recordset that only retrieves data from the product table (thinking that the product table is now linked to the category table.... or is that only linked after it is joined in qub and added as a new query in an advanced recordset)??

What fields do I need to join in the product table and category table to display the category name from the cat_name field in the cartegory table in the pro_cat field in the product table category?//?????

Now I am really lost..... going to sleep trying to figure this out is giving me nightmares.....

Any help would be great

Have a nice day

Oh ...... would be a great tutorial for adobe to have..... joining tables with product, category and even sub category explained would be great.... I went through all the tutorials on the adobe site and the interakt site and found some great stuff but not exactly what I need

Hi,
It will work and u r mostly on the right track.
using QUB is as easy as cracking a joke. But u need to keep trying..
this time sure i will post u a schema with ur input and a query for dynamic list form. which joins categories.

U need to join the foreign key fields only and all u need to do is drag and drop the fields.

What OS are you working on ?
windows ?
MAC?
If you are on windows
then i suggest u do something
go download one of the following.
1. MySQL Maestro 8.3....... or latest version..
2. Database workbench pro 3.x
all these trial version will do they will teach u to do some easy database design..

I am using a windows computer with mysql phpmyadmin wamp testing server

It took me ages to figure it all out.... but I did get it working....

I was having trouble understanding what happpens when you join two tables..... now I understand a little better...

I will explain how I got it to work....

I created two pages as before a dynamic list and form
I created the recordset using a query built in QUB joining the pro_cat field to cat_id.... all the same as before

So then instead of trying to fugure out how to display the category name in the dynamic list I tried a normal dynamic table

So I created a recordset using the same query (join pro_cat to cat_id and inserted a dynamic table and tested to see what happened......

And WOW..... in the browser window... low and behold the tables were joined in the dynamic table.... the table contained all the fields from the product and the category table side by side..... JOINED..... WOW and I noticed that the pro_cat number was in line with the correct cat_name and was displaying the correct name.... so
my join had worked and it was only returning equal values.... that answered many questions for me......

So then all I did was...... delete the dynamic field pro_cat from under the "Category' colum in the dynamic table and replace it with the cat_name dynamic field...... and deleted all the other unnecessary filds.... and tested it and it all worked perfectly.....
The table now displayed the Name of the product and the category name insterad ot the number...... So my form all worked fine and the finished list worked great as well

I can now insert new products and choose the category from a drop down menu and then display all the products in a dynamic list with the correct category name.....

Now all I have to do is figure out how to filter a recordset to display only products from one category..... that will be my next chalenge...... I will try to figure out for myself.... but will post if I get toooo stuck...

I looked at the tables you gave my in your reply..... I don't understand some parts... these parts

CONSTRAINT `fkeyproductcategory01`

REFERENCES `category`(`cat_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION

CHECKSUM = 1

COMMENT = 'This is products only';

CREATE INDEX `fkeyproductcategory01`
ON `product`

But I will try the tables out and see what happens and google what I don't understand....

Thanks again for you reply...... I went to your site and all the other sites you have blogs and stuff But I could not find anywhere to send you a msg

Well my friend, I was forced to remove all personal information.. from my website.. bcos of some bad people trying to do some social engineering.

Now about what that foreignkey is... u can learn more about it by trying to create tables with my given script..

then try using a category to create a product. and then delete the category,
it wont allow if the constraint is set to restrict.

For this u can learn by reading more about foreign keys..

That all is MySQL specific keywords..
please use a GUI mysql GUI tools which are free from mysql.com
else u will waste lot of type correcting spelling mistakes/typos in ur command.

now to filter and show only products from a single category..
u can do it in many ways,.

way 1:
left hand side u have list of all categories..
person clicks a category and on centre of page section u see all products in that category...
for this u need to add a filter by URL parameter to the ListRecordset of ur dynamic list.

way 2:
there is something called Filter in dynamic list.. just use it..
and in old interakt site there is a mxkollection3.chm file download it. It has a how to section, to always display the filter use it....
this way when a person selects a category the list is filtered by that category.

u can message me directly at mohnkhan< sign of the at here >mohitech.com or mohnkhan(again at signhere ) yahoo.com
or gmail.com

these are all personal emails i dont use company emails anywhere,, bcos they are monitored.