filtering multiple recordset.

Report · Sep 19, 2012

I have posted 2 more discussions on the forum so i do aplogise but i am unsure the best way to describe what i am looking for

i have a product page. with links to individual products

on the product page i need to call in other tables based on the catagory of the product and sizes of the product,

i have created multiple recordsets joining the table but need help with the filtering of the information

i have a recordset showing the product information (basics - name, size, price)

$colname_Recordset1 = "-1";

if (isset($_GET['ID'])) {

$colname_Recordset1 = $_GET['ID'];

}

mysql_select_db($database_beau, $beau);

$query_Recordset1 = sprintf("SELECT * FROM beauProd WHERE ID = %s", GetSQLValueString($colname_Recordset1, "int"));

$Recordset1 = mysql_query($query_Recordset1, $beau) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

i have a recordset to join the size table with the product table

$colname_Recordset2 = "-1";

if (isset($_GET['SizeProdID'])) {

$colname_Recordset2 = $_GET['SizeProdID'];

}

mysql_select_db($database_beau, $beau);

$query_Recordset2 = sprintf("SELECT * FROM beauProd, beauSizes WHERE beauSizes.SizeProdID = beauProd.SizeProdID AND beauProd.SizeProdID = %s", GetSQLValueString($colname_Recordset2, "int"));

$Recordset2 = mysql_query($query_Recordset2, $beau) or die(mysql_error());

$row_Recordset2 = mysql_fetch_assoc($Recordset2);

$totalRows_Recordset2 = mysql_num_rows($Recordset2);

in the body i have a select list that needs to display the records of all the products in that catagory

<tr>

<option value="Select Design">Select Design</option>

<?php

do {

?>

<?php

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

$rows = mysql_num_rows($Recordset1);

if($rows > 0) {

mysql_data_seek($Recordset1, 0);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

}

?>

</select></td>

</div></td>

</tr>

</table>

</form>

and a select list that needs to show the sizes of each product

<option value="select Category">Select Size</option>

<?php

do {

?>

<?php

} while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));

$rows = mysql_num_rows($Recordset1);

if($rows > 0) {

mysql_data_seek($Recordset2, 0);

$row_Recordset2 = mysql_fetch_assoc($Recordset2);

}

?>

</select>

the way i have it at the moment i cant get either select list to work. CAN ANYONE ADIVSE PLEASE

thanks in advance

Report · Sep 19, 2012

Jonathan, I've been trying to help you sort out your problems, but wasn't available to visit the forums for a couple of days. Posting multiple threads about your difficulties is only going to confuse matters even further.

What do you mean by not being able to get either select list to work? The PHP code looks fine, so it's presumably a problem with your SQL query not returning the values you expect.

I suspect the problem lies in the way you have structured your database. Looking at the tables in one of your other threads, I can't work out how your sizes table relates to the products table. It seems as though you need a cross-reference table which contains two IDs as a joint primary key.

Let's say you have two products: a red T-shirt and a blue one.

ProdID Product_name

1 Red T-shirt

2 Blue T-shirt

You also have five sizes:

SizeID Size

1 Small

2 Medium

3 Large

4 Extra large

5 XXL

You can store the sizes like this:

ProdID SizeID

1 3

1 4

2 1

2 2

2 4

The red T-shirt is available only in large and extra large, but the blue T-shirt is in small, medium, and extra large.

Report · Sep 19, 2012

i am very sorry i have posted multiple thread, its i was unsure what i was really asking in the topic.. the reason i have made my sizes the way i have is so that they can manually be changed when they are out of stock. i have given each product its own size, this way there is a list with the product and the size and when said product is sold out i have a check box to say so.

it looks like with the above logic i can do this.

on the product description page i am trying to call in the full list of products from that catagory

using the CatID to join the product and the catagory

and the sizes from the product

using the SizeProdID to join the product and the size table.

then filter what i need out from there. but as you can see that isnt doing what i want it to and the product list is just showing the product that has been selected and the sizes is showing nothing,

Report · Sep 19, 2012

soryy i forgot to mention my logic behind the sizes

i have sizes that vary

0-3 months

3-6 months

6-12 months

12-24 months

small

meduim

large

2 years

3 years

4 years

so that is why i have built the table like i did, or is this incorrect the way i done it?

Report · Sep 20, 2012

>I suspect the problem lies in the way you have structured your database. Looking at the tables in one of your other threads, I can't work out how your sizes table relates to the products table.

the table joins are below

Catagory Table

catID

catname

Product Table

ID

catID

SizeProdID

and a Size Table

SizeID

SizeProdID

catname

so the size table relates to the product table with the SizeProdID feild

Report · Sep 20, 2012

i have just removes the filter out of the recordset2 and now all the sizes are shown in the drop down menu. so the data is getting through?

i am at a blank on what to do

Report · Sep 20, 2012

im sorry to keep this post up but i am really stuck on what to do now, I have all the backend working great but i just dont know how to join the tables in and diplay the correct results.

I can make a basic filter where on the main product page you select a product and the information for that product is displayed (only information from the product table) but i need to display other information from tables that are associated with this product. E.g the rest of the products from the catagory and at least this way i can make a select list to jump to the other products

Report · Sep 21, 2012

Jonathan Fortis wrote:
the table joins are below
Catagory Table
catID
catname

Product Table
ID
catID
SizeProdID

and a Size Table
SizeID
SizeProdID
catname

This is where your problem lies. Each table in a relational database should store information about one subject only. The product table should contain details of the product and be linked to the category table. However, the product table can't store details of the size, because the product comes in different sizes. Equally, the size table can't store details of either the product or the category because sizes apply to different products, which might be in different categories.

As I suggested before, you need a cross-reference table that stores details of the available sizes for each product. The product ID and size ID need to be a joint primary key in the cross-reference table. You can also add a column to indicate whether the product in a particular size is in or out of stock.

Report · Sep 21, 2012

Hello David

Thanks for getting back to me.

so each table should just have one linked feild

so the product table is ok linked by catID?

but then i need to remake to sizes? This is what i cant get my head around. Sorry about this.

I need to create a size table with

a size ID and a size name

what do i do if i have sizes that vary

0-3 months

3-6 months

6-12 months

12-24 months

small

meduim

large

2 years

3 years

4 years

can these all go into one field?

could i just make a select list and store them like that?

thanks so much for you time so far, this is the last part of the site i have to do.

Report · Sep 21, 2012

Jonathan Fortis wrote:
can these all go into one field?

No. One field should hold one piece of information.

Sizes table

size_id	size
1	0-3 months
2	3-6 months
3	6-12 months
4	12-24 months
5	small
6	medium
7	large
8	2 years
9	3 years
10	4 years

Categories table

cat_id	category
1	baby
2	toddler
3	child

Products table

prod_id	cat_id	product
1	1	Baby-gro
2	1	Bib
3	2	T-shirt

Stock table

prod_id	size_id	stock
1	1	50
1	2	0
3	3	35
3	4	20
3	8	23
3	9	0
3	10	15

In the stock table, prod_id and size_id must be declared as a joint primary key. This ensures that only one record can be created for each combination of product and size.

There are 50 Baby-gros for 0-3 months in stock. Baby-gros for 3-6 months are normally available, but currently out of stock.

T-shirts for 6-12 months, 12-24 months, and ages 2-4 are normally available. There is stock for all except age 3.

[Edited to correct the prod_id values in the stock table]

Report · Sep 21, 2012

ok i need to start from fresh.

i have the catagory table complete and the product table complete. i am working on the size table which i have done as you are shown so just need to make the stock table. I take it then i join these tables in to admin section

When i make the table form i can associate a product with a catagory no problem but do i associate the product with the size using the stock table?

Report · Sep 21, 2012

ok i want to go through this bit by bit as i got totally lost before. I have now created a form for the products with the following

Recordset1

product id

name

desc

price

amount in stock

Recordset2

i have a select list that is populated with the catagories

Recordset3

I have a select list populated with the sizes

But only one size can be selected and submitted.

i then need to go back in and add the product again with a different size. is this correct?

Report · Sep 21, 2012

or do i need to have an add product page without the sizes, then go to another page and associate the product with the size using a select menu

this way keeping the id's for the product the same??

Report · Sep 21, 2012

Jonathan Fortis wrote:
I have a select list populated with the sizes
But only one size can be selected and submitted.

Make it a multiple-choice select list, and add a pair of square braces after the name:

Using the square brackets after the name submits the values as an array. So, $_POST['size'] will contain a subarray. You need to loop through the subarray to insert each value with the product ID into the stock table.

You're learning the hard way of the problems that come from relying on Dreamweaver server behaviors. They're extremely limited, and shouldn't really be used for a production site.

I cover this type of scenario in PHP Solutions, 2nd Edition. But it's all based on hand-coding and using the MySQL Improved object-oriented interface.

I'm afraid I'm going offline for some time to deal with an urgent project of my own. Good luck.

Report · Sep 21, 2012

thanks so much for your help up until now

Report · Sep 21, 2012

one more quick one. until i figure out how to do the <select name="size[]" multiple> can i continue and enter them in one by one?

Report · Sep 21, 2012

Ok after finding another forum link from David http://forums.adobe.com/message/882226 i found out how to make a multiple select list. the way it has done it was to put all size id's in one feild in the DB.i am not sure if this correct but i will test it.

the next thing i need to find out is how to bring all of this togeather. i have a product page showing all the products and when i click on a product i need it to go to a product page showing that product information which i can do by the id. but i also need to get the information in from the other tables. so if anyone out there can lend a hand i would be appreciative

Report · Sep 23, 2012

Hello I have managed to get the tables working great, the only thing i did do differnt is have the sizes add seperatly, rather than mulitple select as all the SizeID were being stored in one feild and i needed them in individual ones so they could be displayed in seperate forms. thanks very much for your help.

All i need to do is get the front end working now.

filtering multiple recordset.

1 Correct answer

Sizes table

Categories table

Products table

Stock table

Sizes table

Categories table

Products table

Stock table