Filtering with PHP

Hi there hasozduru

There database structure defined in the OSCommerce project is pretty right-on for what you need. You need to be able to have products with an arbitrary set of attributes which you can associate with them. Roughly speaking, a suitable database structure would be

products
---------
id
name (text)
price (numeric)

productattributes
-------------------
id
product -> products.id
attribute -> attributes.id
value (text)

attributes
-----------
id
name (text)

Attributes would be like 'memory', 'width', 'height', 'screen', 'weight' etc. (You might need to separate numericattributes and textattributes)

Hopefully that's enough to get you going?
JP

Thanks jdpipe but it's not enough. I would like a simple code that filters according to the price. Like following example, if customer wants mobiles that more expensive than £ 100, query will be like following (1st line). After filtering by price, if customer clicks to filter according to if mobile has camera or not, it will be like 2nd line. How can I put following code so customer will see mobiles more expensive than £ 100 and has a camera.

Thanks

$sql = "select * from products where price > 100";
$sql = "select * from products where price > 100 AND camera = 'yes' ";

In my database structure above, you would do that query as follows. Let's call that attribute 'hascamera'. We'll store the 'hascamera' attribute ofr a product in the productattributes table, as a 0 or a 1 in the 'value' field.

SELECT attributes.id FROM attributes WHERE attributes.name='hascamera'.

Assume we've got that value into PHP variable $attributes[0]->id. Also assume we've got our desired value of the 'hascamera' attribute in the PHP variable $attributes[0]->val

SELECT
  products.id
  ,products.name
FROM
  products
  ,productattributes attr0
WHERE
  attr0.value={$attributes[0]->val} AND attr0.attribute={$attributes[0]->id}
  AND products.price > 100
ORDER BY
  products.name

If you had other attributes you wanted to select on, you would add more join tables and where clauses, eg

FROM
  products
  ,productattributes attr0
  ,productattributes attr1
WHERE
  attr0.value={$attributes[0]->val} AND attr0.attribute={$attributes[0]->id}
  attr1.value={$attributes[1]->val} AND attr1.attribute={$attributes[1]->id}
  AND products.price > 100

Now, i'm obviously leading towards automating the process of creating the search form and the sql query with this... can you work out how to go from there?

JP

Woops omitted some stuff in the SQL there. See 'prodcuts.id=attr0.product AND' inserted in following:

SELECT
  products.id
  ,products.name
FROM
  products
  ,productattributes attr0
WHERE
  products.id=attr0.product AND attr0.value={$attributes[0]->val} AND attr0.attribute={$attributes[0]->id}
  AND products.price > 100
ORDER BY
  products.name

Yes but how do you write it at the file. Like let say when the address bar equals to basket.php?price_start=100 query will be $sql = "select * from products where price > 100"; How will address bar be basket.php?price_start=100&camera=yes and because of this, query will be $sql = "select * from products where price > 100 AND camera = 'yes' "; I would like the code of this please.

Sorry that's all I'm going to do for free :-(
JP

How much do you want to do it?

I recommend you look carefully at using OSCommerce or ZenCart before starting on a bespoke solution.
However, if you want, I can write the basic database structure and the web page for you. Send me an email at john at curioussymbols dot com and I'll quote you.
JP

I already paid experts to answer my questions. If every expert ask for money, what's the point of experts exchange site? And this question is very simple.

Thanks

Matt
You might be right there. Thanks for pointing that out
Gone.

matt_mcswain

Thanks for your answer but I couldn't make the code work. If you could please read my comment at the very beginning, you will understand what kind of thing do I need.

Thanks

No more comment guys?