Help with coding a product list page - URGENT

[simboski19]

Hello, I have developed a site which uses affiliate data to get lots of prices for individual products like kelkoo. I create a master product. Then I match the different affiliate products/prices to this master product. I then display these in a list format and calculate the cheapest price to show here. The problem I have is that I cannot then sort this list by price high-to-low or low-to-high as I cannot simply apply ORDER BY to the main sql which pulls out the master products.

 

So I guess my question is, is there a way of pulling out the cheapest price and then sorting these master products after they have been pulled out or is the only way to sort using the first master query and have another field that is populated somewhere else?

 

Many thanks

Simon

[drisate]

Yup get all the data inside an array then sort it using the php array sorting functions

 

asort()

ksort()

sort()

 

[...]

[Adam]

Could you not wrap the SELECT statement within another SELECT statement that handles the ordering?

 

select * from (
     ...
) as p
order by p.price;

 

Edit

 

Actually I'm not sure what you're doing. I don't understand why you can't order them, without even wrapping the select statement, if you have a price?

[simboski19]

Thanks for the speedy replies.

 

drisate: I have tried adding all products to an array and then sorting this however the page became very very slow so I don't want to use this method.

Adam: To try and explain it a bit better this is the current way it works:

 

- I have list of 5 Master Products (there are no prices associated to the Master Products).

- My list of Master Products are all linked to some products: Child product 1 (£1.99), Child product 2 (£3.99), Child product 3 (£2.95). They all have different prices.

- I only want to show the cheapest Child price (which are in a different table) for each Master Product.

- I query the table to get all of the Master Products, of which there are 5.

- In my while loop for each Master Product I then do a seperate query to get the cheapest Child product price to display.

 

So I get something like as my list:

 

Master Product 1 £1.99

Master Product 2 £3.99

Master Product 3 £2.95

Master Product 4 £1.99

Master Product 5 £2.95

 

I have a filter on the page which is High-to-Low and Low-to-High. However if I try and change the order I cannot because the actual prices are found using the inner query (which is different).

 

Hope that clears it up slightly.

 

Thanks

Simon

 

 

[Adam]

You can select the min() priced child product, grouped by the parent ID and joined onto the master product table to get all the data you need within a single query. I had to guess at your schema but should give you a good idea:

 

select m.id,
       m.title,
       c.id,
       c.title,
       min(c.price)
from master_products m
join child_products c on (m.id = c.parent_id)
group by c.parent_id
order by c.price;

 

You'll probably want to give the selected columns an alias.

[simboski19]

Brilliant Adam. Thanks again for your time and help.

 

I will look at giving this a go and test tomorrow and let you know how I get on. Yeah I'll make sure I give them an alias.

 

Thanks

Simon

[simboski19]

Hi,

 

The query works like a treat, however I realised after I had used and tested it that I have another issue. I am no sure if this will be possible but thought I would ask anyway.

 

Q. The query is returning the results in the correct low-to-high or vice versa order. This is all perfect. However I realised that there are two prices associated with each child item: a normal RRP price and a sale price. The way I had it working was that if a sale price was available (i.e. not empty) it would use this price instead of the normal RPP price.

 

Is there a way of first checking this in the query before we then check the min price?

 

Many thanks

Simon

[Adam]

I realised looking back at this, I made a mistake with the previous SQL I gave you. It would always return the details of the first child product, but the lowest price of them due to the min() use. So fixing that, and incorporating the multiple price columns with a "CASE" statement, you can use this:

 

select m_id,
       m_title,
       c_id,
       c_title,
       min(cost)
from (
    select m.id as m_id,
           m.title as m_title,
           c.id as c_id,
           c.title c_title,
           case when c.sale_price is not null
                then c.sale_price
                else c.price
                end cost
    from master_products m
    join child_products c on (m.id = c.parent_id)
    order by cost
) as x;

 

As the master and child product tables have some of the same names (or at least they do in the schema I was playing about with), the aliases are needed to prevent any ambiguity when selecting from the sub-query.

 

Edit

 

You can add a further ORDER BY statement after the sub-query by the way.

[simboski19]

Hi Adam,

 

Sorry but I am having problems getting this right in my head and in the code. Can I show you how the tables are setup:

 

table: master products => tblmpr

table: children => tblfeeds

 

junction table: master product id's - children id's => tbl_mpr_cat_junction

 

So I pull out all of the products from the master product table. We then get the minimum priced child from the child table. And then we do the CASE to check if the sale price should be used. I think it may be in my joins that this is failing (I have obviously simplified the overall query):

 

select

`tblmpr`.`product_id`,

`tblfeeds`.`product_sale_price` AS `feedsaleprice`,

min(`tblfeeds`.`product_price`) AS `feedprice`

from (

select

`tblmpr`.`product_id` AS mprid,

case when `tblfeeds`.`product_sale_price` is not null

then `tblfeeds`.`product_sale_price`

else `tblfeeds`.`product_price`

end feedprice

from

`tbl_mpr_cat_junction`

Inner Join `tblmpr` ON `tbl_mpr_cat_junction`.`mprid` = `tblmpr`.`product_id`

Inner Join `tblmpr_mpc_junction` ON `tblmpr`.`product_id` = `tblmpr_mpc_junction`.`mprid`

Inner Join `tblfeeds` ON `tblmpr_mpc_junction`.`product_xmlid` = `tblfeeds`.`product_xmlid`

order by feedprice

) as x

 

Thanks again.

Simon

 

[Adam]

I only have time for a quick glance over it at the moment, but within the outer select you need to select the aliases you defined within the sub-query. The outer query has no knowledge of the table names and such, it's just 'handed up' if you like, the aliases. Remember you're not selecting from a table, you're selecting from a result set.

[simboski19]

Ah I see that now Adam.

 

I'll have another go now.

 

Simon

[Adam]

Okay, have more time to look at this now. I'm a little unsure what you're trying to do though. Can you explain the relationship the junction table has with the others? You seem to be selecting from it and joining it on again later. Is this intentional? I think it would make more sense to select from the master product table, and join on the additional data. Try this:

 

select
    mprid,
    min(feedprice) feedprice
from (
    select
        m.product_id as mprid,
        case when f.product_sale_price is not null
             then f.product_sale_price
             else f.product_price
             end feedprice
        from tblmpr m
        join tblmpr_mpc_junction j on (m.product_id = j.mprid)
        join tblfeeds f on (j.product_xmlid = f.product_xmlid)
        order by feedprice
) as x;

 

I've removed the back-ticks and given tables an alias, because it was quite hard to read before. If you wish to retrieve more columns you'll need to select them within the inner query, assign them an alias, then select that alias within the outer query. Or alternatively you can use "select *, min(feedprice)" in the outer query.

[simboski19]

Adam,

 

I am getting there, slowly but surely. The query is begining to make sense and pull my products out, however I have added in another table and it is now only pulling 1 product out instead of 2. This is the query I have now with the new table:

 

$productqry =

"

select

*,

min(feedprice) feedprice

from (

select

m.product_id as product_id,

m.product_name as product_name,

m.product_description as product_description,

m.product_image as product_image,

m.product_brand as product_brand,

m.product_active as product_active,

m.product_amazon_code_UK as product_amazon_code_UK,

m.product_amazon_code_US as product_amazon_code_US,

m.product_amazon_code_DE as product_amazon_code_DE,

m.product_amazon_code_FR as product_amazon_code_FR,

cj.masterid as masterid,

cj.catid as catid,

cj.subcatid as subcatid,

case when f.product_sale_price is not null

then f.product_sale_price

else f.product_price

end feedprice

from tblmpr m

join tblmpr_mpc_junction j on (m.product_id = j.mprid)

join tblfeeds f on (j.product_xmlid = f.product_xmlid)

join tbl_mpr_cat_junction cj on (cj.mprid = m.product_id)

WHERE

masterid = $masterid

AND

catid = $categoryid

AND

subcatid = $subcategoryid

order by feedprice

) as x"; 

 

To explain the new table:

 

On the website there are many different categories and because a master product can be shown in many different categories I have another table named 'tbl_mpr_cat_junction'. This holds all of the master products and which section they should go in. You will see that I have added on a new join to this table and when this matches the variables: $masterid, $categoryid and $subcategoryid it will show the master products in this category. As I said earlier I should be able to see 2 prods but can only pull out 1. Also the price is not coming out - any thoughts.

 

Thanks

Simon

 

[Adam]

Ah, my mistake. Currently it's returning the lowest price from all of the child products, instead of the lowest price per product. Just need to add in a GROUP BY:

 

[...]
) as x
group by product_id;

 

 

[simboski19]

Fantastic.

 

Adam that is now working perfectly and I have streamlined it to work smoother. I assume much like other queries, it helps the speed to specify the fields instead of just selecting * (ALL fields)? Also, one last thing - why must you give use ') as x' at the end of the inner query?

 

Many many thanks for the help.

 

Simon

[Adam]

The x is required because you need to specify a name for the derived 'virtual' table  - you could access the columns like "x.feedprice".

 

Using * would only really make it faster if you were selecting columns you weren't using. In this case you're already specifying those columns within the inner-query, so I don't think you need to worry about using it on the outer query.

[simboski19]

Great.

 

Ok. sorry this should (sorry) be the last thing. Each child product is associated with a merchant. Here is the final query:

 

select

product_id,

product_name,

product_description,

product_image,

product_brand,

product_active,

product_amazon_code_UK,

product_amazon_code_US,

product_amazon_code_DE,

product_amazon_code_FR,

masterid,

catid,

subcatid,

mername,

currency,

min(feedprice) feedprice

from (

select

m.product_id AS product_id,

m.product_name AS product_name,

m.product_description AS product_description,

m.product_image AS product_image,

m.product_brand AS product_brand,

m.product_active AS product_active,

m.product_amazon_code_UK AS product_amazon_code_UK,

m.product_amazon_code_US AS product_amazon_code_US,

m.product_amazon_code_DE AS product_amazon_code_DE,

m.product_amazon_code_FR AS product_amazon_code_FR,

cj.masterid AS masterid,

cj.catid AS catid,

cj.subcatid AS subcatid,

mr.merchantname AS mername,

mr.currency AS currency,

case when f.product_sale_price is not null

then f.product_sale_price

else f.product_price

end feedprice

from tblmpr m

JOIN tblmpr_mpc_junction j ON (m.product_id = j.mprid)

JOIN tblfeeds f ON (j.product_xmlid = f.product_xmlid)

JOIN tbl_mpr_cat_junction cj ON (cj.mprid = m.product_id)

JOIN tblmerchants mr ON (f.supplier_id = mr.merchantid)

JOIN tblaffiliates af ON (m.product_affiliate = af.affiliateid)

WHERE

masterid = $masterid

AND

catid = $categoryid

AND

subcatid = $subcategoryid

AND

m.product_active = 1

AND

f.product_active = 1

AND

mr.active = 1

AND

af.active = 1

$addgender

$addbrand

) as x

GROUP BY product_id

$pricesort

 

Problem is, even though there are 2 or 3 different children with different merchants it is only pulling out the first one by the looks of things. Any suggestions?

 

Thanks

Simon

[simboski19]

It seems Adam that the query is calculating the correct cheapest price from the list of child products, however it is using the first merchant it comes to.

 

Simon

[Adam]

Looks like you've missed out the order by feedprice in the sub-query.

[simboski19]

Dammit. Just put it back in all working great.

 

Thanks again