Select Statement

PHP
1

Hi,

I’m building a shopping cart and have what should be a fairly straightforward requirement to find products that match a category id.

However I only want to show products that are in stock, and ideally would like to sort them with the highest stock numbers showing first which requires processing the product_id against other tables in the database.

If I run my select query and then only show the product information if it is in stock, my problem is this: my pagination system works on a limit of 12 items per page and start count, so if none of the items are in stock, nothing will show on the page.

Options as I see them are to create a temporary table of products and stock count and join products to that table before sorting. Alternatively I could create a function of products that are in stock which returns an array. I could then run my select statement with WHERE checking against that array which would mean that my results are all in stock.

However I’m not sure if either of these are valid or best options and would be grateful for any advice anyone could offer.

2

You’re talking of showcase, not shopping cart?
Simple JOIN statement would probably fulfill your dream.
I can’t say more because have no idea of your database structure.

3

No, shopping cart is correct. It’s for a fashion site so hierarchy I’ve set up is:

a products table where each product_id is for a dress, top etc to drive images and product description, price etc.

Items table has a product_id row with each item_id being a different sized item.

Stock table has an item_id row with each stock_id being an individual piece of stock.

So once I have all the product_ids for a category search can go from product_id to item_ids to stock_ids and get a stock count but struggling for best approach to only show products that are in stock.

Even with table join there isn’t a row that gives me a stock count without first processing.

Hope this makes sense and I’m missing something straightforward and thanks in advance.

4

Still dunno what do you need - no data structure nor even example of output from you.
But I guess you want to implement hierarchal structure.
You will need one table then (or two)
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I still don’t understand why did you separate items and stock table.

5

hypothetically:


SELECT
     p.product_name
     ,i.size
     ,COUNT(*) available_stock
  FROM
     products p
 INNER
  JOIN
     items i
    ON
     p.products_id = i.items_id
 INNER
  JOIN
     stock s
    ON
     i.item_id = s.item_id
 GROUP
    BY
     p.products_id
 ORDER
    BY
     available_stock