Page 1 of 1

Limiting records displayed based on an another recordset?

Posted: Tue Jul 11, 2006 6:41 am
by gb225450
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

Posted: Tue Jul 11, 2006 7:22 am
by Oren
Store the prices in the "Product Recordset " table :wink:

Posted: Tue Jul 11, 2006 7:45 am
by jamiel
How can one Product have two Prices?

If the ID in the Price RecordSet always coressponds to the ID in the product table, why can't you just select all the prices with that product id and loop through the result set in PHP?

Posted: Tue Jul 11, 2006 11:44 am
by gb225450
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

Posted: Tue Jul 11, 2006 12:14 pm
by Christopher
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'];
     }
}

Posted: Tue Jul 11, 2006 1:17 pm
by Oren
First, to make things clear - My name is Oren.
Second, I suggest you to re-organize your table. Something like:

| ID | PRODUCT | PRICE-SMALL | PRICE-LARGE
| 1 | Green Product | £1.99 | £2.99
| 2 | Blue Product |£10.99 | £12.99

Posted: Tue Jul 11, 2006 3:27 pm
by gb225450
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