Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Filtering with PHP

Hi

I have an online shopping site. I am selling electronic products like PDAs and Mobiles etc... I would like to write a code so customers at the first place can filter PDA's results according to operating system. Let say there are 30 PDAs that has 400Mhz CPU. After that, there should be other filter option that customer can select memory. Let say customer selected 256MB memory and there are 14 PDAs. At the last step, customer will be able to select PDAs between price £ 150 and £ 250. In the end, customer will have, for instance, 6 PDAs and he will see those 6 PDAs has 400Mhz CPU, 256MB memory and it's between £ 150 and £ 250 so those PDAs will be suitable for that customer. What is the best way of creating structure of that logic with PHP and MySQL. I can give you an example site that does it so you can understand better. http://dealtime.co.uk/xPP-Personal_Organisers . Please filter some brands or prices to see how the logic works.

If you could please write me a simple code regarding this, it will be great.

Kind regards
0
hasozduru
Asked:
hasozduru
  • 6
  • 6
  • 2
2 Solutions
 
jdpipeCommented:
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

0
 
hasozduruAuthor Commented:
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' ";
0
 
jdpipeCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jdpipeCommented:
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
0
 
hasozduruAuthor Commented:
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.
0
 
jdpipeCommented:
Sorry that's all I'm going to do for free :-(
JP
0
 
hasozduruAuthor Commented:
How much do you want to do it?
0
 
jdpipeCommented:
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
0
 
hasozduruAuthor Commented:
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
0
 
matt_mcswainCommented:
jdpipe, I believe your last post might've violated a couple Member Guidelines.

hasozduru, have you tried using a loop to build your WHERE clause? If your GET variable names match up with your table field names, it's pretty trivial.

//untested
$sql = 'select * from products where ';
foreach($_GET as $key=>$value)
  $sql .= $key ." = '".mysql_real_escape_string($value). "' AND ";
$sql = substr($sql,0,strlen($sql)-5);//remove last AND

For specialized comparisons like price_start, you'll need to be a bit more careful in your loop:

foreach($_GET as $key=>$value){
  if ($key == 'price_start')
     $sql .=  " price > '". mysql_real_escape_string($value). "' AND ";
  else
     $sql .= $key ." = '".mysql_real_escape_string($value). "' AND ";
}
>> If every expert ask for money,
Is this common???
0
 
jdpipeCommented:
Matt
You might be right there. Thanks for pointing that out
Gone.
0
 
matt_mcswainCommented:
I haven't been posting here long, but it seems to be acceptable post whatever you want in your profile regarding freelancing and whatnot, but to try to keep it out of the threads. I believe this is OK:

"Check out my profile ;)"

But, I'm not a Moderator and opinion seems to differ.
I think it's mainly about keeping the PAQ clean and minimizing the off-topic comments, which I'm violating with this very post; Ahh! :-0
>>Gone.
I hope you didn't think I was trying to throw you out of this Q, because that certainly wasn't the case.
 
0
 
hasozduruAuthor Commented:
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
0
 
hasozduruAuthor Commented:
No more comment guys?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now