Building a query based on querystring array passed by $_GET

[EagleM]

I need to build a query that will query tblProduct and return only products that have all the "features" that are passed through the querystring array ($_GET['features']).

For example, what obviously doesn't work is this:

$qryGetProducts = "SELECT id, category, company, model, title, description, picture, price, sale_price, available 
					FROM tblProduct 
					INNER JOIN tblFeature
						ON tblProduct.id_Product = tblFeature.id_Product
					WHERE tblFeature.id_Feature IN   ({$_GET['features']})
					ORDER BY id DESC";

what I need to say is, WHERE the products in DB have all the features in the $_GET['feature'] array.

The 2 problems I see are:
1. Cannot use $_GET['features'] array in a query.
2. I'm not looking IN the $_GET['features'], but in the DB tables, based on values in $_GET['features'].

Can anyone help?

 

[sleipnir214]

You can't use $_GET['features'] like that. PHP does not have a default way of converting an array to a string, so you must do it in your code.

Take a look at implode() to convert the array into a formatted string, then use that string in your query.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[EagleM]

Thanks. That answers question 1.

But how would I write the WHERE clause?
Because I'm not looking for WHERE tblFeature.id_Feature IN ({$_GET['features']}), but rather WHERE ({$_GET['features']}) IN tblFeature.id_Feature

 

[sleipnir214]

I don't understand.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[EagleM]

I did this:

$strFeatures = implode(",", $_GET['features']);

$qryGetProducts = "SELECT DISTINCT id, category, company, model, title, description, picture, price, sale_price, available 
					FROM tblProduct 
					INNER JOIN tblFeatureToProduct
						ON tblProduct.id = tblFeatureToProduct.id_Product
					INNER JOIN tblFeature
						ON tblFeatureToProduct.id_Feature = tblFeature.id_Feature
					WHERE tblFeature.id_Feature IN  ( $strFeatures )
					ORDER BY id DESC";

It works, but it doesn't give the result that I want.
This query shows all the products that have any of the string features in them.

What I want is, to show the products that have *all* of the features listed in the string. I am making a search that people will use to search for products that they need. So if someone requires products that have feature1 and feauture3, I need to display all products that at least have both feature1 and feature3.

What I meant in the previous post is that this happens because the query says "WHERE tblFeature.id_Feature IN ($strFeatures)". What I wish worked is, if it said "WHERE $strFeatures IN (tblFeature.id_Feature), then it would look for records that have the entire collection of the required features.

Any idea how to fix this? :\

 

[jpadie]

unless the $_GET['features'] is numeric you should change your code as follows:

$strFeatures = "'" . implode("','", $_GET['features']) ."'";

on the query, I can't help feeling that your tables may not be constructed properly to perform such a lookup. can you post the query that you would like to execute? If not, then your primary problem is with mysql. Although probably we all could help you it would be much better (and proper) for you to post in the mysql forum and then come back for the php bolt ins to the solution. mysql forum will need your table defs.

 

[EagleM]

The query works fine -- it does what the syntax says. But it's not what I need to do, as I've explained above.

I'll try the mysql forum.

 

[jpadie]

it does what the syntax says. But it's not what I need to do

that's within my definition of "not constructed properly to perform such a lookup

 

[EagleM]

I don't have a reason to suspect that the tables are not made properly, just the query - the WHERE ... IN part of it.

But I did start a thread in the MySQL forum, and your help is very welcome there.

http://tek-tips.com/viewthread.cfm?qid=1369304&page=1

 

[jpadie]

it looks like you've been given a neat solution to the problem in that forum. personally, i'd have suggested restructuring your tables.

if you follow r937's advice make sure that the combination of id and feature in your join table constitutes a unique index or primary key.

 

[EagleM]

How would you restructure the tables? What's wrong with them now?

The join works fine. There are no errors, and the query does what it is told to do. I just didn't know how to tell it to do what I wanted it to do.

 

[jpadie]

rudy (r937) has kludged a solution (very elegantly) but it depends on what i said above

the combination of id and feature in your join table constitutes a unique index or primary key

given the type of query that you want to run, I would think it would be more efficient (quite a bit) to have each feature as a column of a table either just with the productID or with the other core product data. provided the column type is tinyint and a null is allowed, the structure remains very efficient from a sizing perspective and your queries become much easier. but this is better discussed in the mysql forum.

 

[EagleM]

I think I do have it.

- tblProduct has the list of all products.
- tblFeature has the list of all features.
- tblFeatureToProduct has the relationship of features to products. It lists all id_Feature's with corresponding id_Product's.

Either that's what you mean, or I'm not getting it.

 

[jpadie]

that's not what i mean.

i would have gone for each of the feature columns being within the tblProduct

tblProduct
productID numeric 11 autoincrement
name varchar 255
feature1 tinyint
feature2 tinyint

etc