Hi,
I am using php with an MySQL database to build a page that displays all the products in a particular a category. This essentially consists of a repeating region which displays one product after the other. I want to include a “add to basket” button next to each product however each product has several different prices which is stored a separate database table (therefore a dropdown, allowing the user to select the desired option is required). I have successfully created this add to basket button on the “product detailed” page when there is only one product and I can limit the record set base on the value pass in the URL. However, I am not sure how to do this when there is more than one product such as on the category page I am creating. For example, the recordssets might look like:
Product Recordset
+------------------------------
| ID | PRODUCT
| 1 | Green Product
| 2 | Blue Product
+------------------------------
Price Recordset
+------------------------------
| ID | PRICE
| 1 | £1.99
| 1 | £2.99
| 2 | £10.99
| 2 | £12.99
+------------------------------
I am not sure how to loop through the prices in the <select> lists so that only the relevant prices are displayed ie When looping through the “Product Recordset” the select list options should be:
ID 1 (Green Product) = £1.99 & £2.99
ID 2 (Blue Product) = £10.99 & £12.99
Is there a way to relate the two recordsets so that only the prices relating a particular product are shown in the drop down?
Hope this makes sense
Thanks
Wayne
Limiting records displayed based on an another recordset?
Moderator: General Moderators
Hi Orden,
Thanks for the reply.
If I stored all the data in one recordset it would look like this:
| ID | PRODUCT | PRICE
| 1 | Green Product | £1.99
| 1 | Green Product |£2.99
| 2 | Blue Product |£10.99
| 2 | Blue Product |£12.99
When looping through this recordset how would I get it to display each product only once? When looping through the above recordset each record will be shown as a different product which is not the case? ie. There are only 2 product in the above record set not 4?
-----------------------------------------------------------------------------------------------------
Hi Jamiel,
Thanks for the reply.
Each product will have a sample size, a SMALL and a LARGE, each with its own price. Hope that makes it clear.
The IDs in the Price table do correspond with those in the product table. I have selected all the prices which relate to the products selected. However, my problems come display the results.
I firstly loop through the products recordset to display all the products in a given category. Within this repeating region I then I want to loop through a <SELECT> to only show the prices for a particular product. How do I limit this loop to the only relevant prices?
Thanks for the help so far
Wayne
Thanks for the reply.
If I stored all the data in one recordset it would look like this:
| ID | PRODUCT | PRICE
| 1 | Green Product | £1.99
| 1 | Green Product |£2.99
| 2 | Blue Product |£10.99
| 2 | Blue Product |£12.99
When looping through this recordset how would I get it to display each product only once? When looping through the above recordset each record will be shown as a different product which is not the case? ie. There are only 2 product in the above record set not 4?
-----------------------------------------------------------------------------------------------------
Hi Jamiel,
Thanks for the reply.
Each product will have a sample size, a SMALL and a LARGE, each with its own price. Hope that makes it clear.
The IDs in the Price table do correspond with those in the product table. I have selected all the prices which relate to the products selected. However, my problems come display the results.
I firstly loop through the products recordset to display all the products in a given category. Within this repeating region I then I want to loop through a <SELECT> to only show the prices for a particular product. How do I limit this loop to the only relevant prices?
Thanks for the help so far
Wayne
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
This looks like it will have the same logic as all displays that need to do something for each group of records read. Typically the code looks something like this:
Code: Select all
$value = '';
while ($row = $record->fetch()) {
if ($row['value'] != $value) {
if ($value) { // don't do it the first time
// show the group of values
$saved_values = array();
}
$saved_values[] = $row;
$value = $row['value'];
}
}(#10850)
Hi Christopher,
Thanks, that looks like what I need to do. This is only my second site using PHP so not 100% understand the code. I take it I have to create two separate recordsets, one for products and one for prices. At which point would I use the code you suggested? Do you know anywhere I could find out more about this online?
Thanks for the help.
Thanks
Wayne
------------------------------------------------------------------------------------------------------
Hi Oren (sorry I got the name wrong),
The example I gave was of the record set created not the table. The data is stored in separate tables and has to be due to the nature of the data. The site will sell a wide range of products, each with an undetermined number of variations (prices) and so the products have to be stored separately from the prices.
Wayne
Thanks, that looks like what I need to do. This is only my second site using PHP so not 100% understand the code. I take it I have to create two separate recordsets, one for products and one for prices. At which point would I use the code you suggested? Do you know anywhere I could find out more about this online?
Thanks for the help.
Thanks
Wayne
------------------------------------------------------------------------------------------------------
Hi Oren (sorry I got the name wrong),
The example I gave was of the record set created not the table. The data is stored in separate tables and has to be due to the nature of the data. The site will sell a wide range of products, each with an undetermined number of variations (prices) and so the products have to be stored separately from the prices.
Wayne