Limiting records displayed based on an another recordset?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gb225450
Forum Newbie
Posts: 3
Joined: Tue Jul 11, 2006 6:07 am

Limiting records displayed based on an another recordset?

Post 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
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Store the prices in the "Product Recordset " table :wink:
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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?
gb225450
Forum Newbie
Posts: 3
Joined: Tue Jul 11, 2006 6:07 am

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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'];
     }
}
(#10850)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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
gb225450
Forum Newbie
Posts: 3
Joined: Tue Jul 11, 2006 6:07 am

Post 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
Post Reply