A join to handle this situation?

[squigs]

Hello,

I'm trying to figure out what would be the most appropriate way to handle this situation. I have created a query with the following code which displays my price categories but I want to create an additional query that utilizes the original value of low_price to retrieve data within the specified ranges. Any ideas?

 

$refinePriceSQL = mysql_query("SELECT *, price_range, COUNT(*) AS numbers
								 FROM (SELECT CASE 
									              WHEN low_price < 100 THEN '$100 or less'
									              WHEN low_price >= 100 AND low_price <= 150 THEN '$100-$150'  
									              WHEN low_price >= 150 AND low_price <= 200 THEN '$150-$200'
                                                      WHEN low_price >= 200 AND low_price <= 250 THEN '$200-$250'
                                                      WHEN low_price >= 250 AND low_price <= 300 THEN '$250-$300'
                                                      WHEN low_price >= 300 AND low_price <= 350 THEN '$300-$350'
                                                      WHEN low_price >= 350 AND low_price <= 400 THEN '$350-$400'
                                                      WHEN low_price >= 400 AND low_price <= 450 THEN '$400-$450'
												  ELSE 'Over $500'
                                                      END as price_range
                                                      FROM inventory
                                    WHERE 1 AND $searchClause) b
                                    GROUP BY price_range
								ORDER BY price_range");	

 

 

[fenway]

Huh?

[squigs]

Sorry, confusingly put because it's confusing me I suppose. I'll try to elaborate a little:

 

The code above would output my price range for example diaplaying something like:

 

$100-$150(7)

$150-$200(12)

etc...

 

What I'm doing with those query results is turn them into hyperlinks which pass the data through the URL so I can call it back to another query which will filter all else out. So where I am running into trouble is when I pass the exact string e.g.$100-$150 to the URL I can use that variable in my new query but in my DB the actual prices are stored in the column I have been working with called low_price which is decimal.

 

I would like to use something like this as a query:

FWI $cost = the data passed along the URL

                                                 ("SELECT * 
	                    FROM inventory  
		    WHERE $searchClause $brandClause AND low_price = '$cost'");	

 

And obviously this does not work for a number of reasons:

a) low_price would equal something like 100.00 not $100-$150.

b)the cloumn low_price is renamed in the original query to price_range.

c)unless using a join I would somehow have to build a query within the existing loop which I haven't been successful with.

Thus my wondering would a join be helpful for this.

 

I hope that this post sort of clarifies what I intend to do and someone can come up with some help for me.

 

Cheers

 

[fenway]

You don't need to pass the pretty string and use it in your query -- just have your URL include low & high values, and search those.

[squigs]

You don't need to pass the pretty string and use it in your query -- just have your URL include low & high values, and search those.

 

Which brings me to my original question, I have different price ranges being listed in a "pretty string" which is what I want, but from within that loop how do I just pass the original value (a plain decimal number or low_price as example) which would allow me to use itto further my search.

[fenway]

If you asking how to pass parameters via a URL, this is the wrong forum.

[squigs]

No that wasn't the question but you did give some helpful insight in stating  "just have your URL include low & high values, and search those."

I was trying to do this but it took a while to figure out how. I adjusted the query from my original post to contain two more CASES and now it reads like this.

 

$refinePriceSQL = mysql_query("SELECT *, price_range, COUNT(*) AS numbers
								 FROM (SELECT     CASE
									              WHEN low_price < 100 THEN '100'
									              WHEN low_price >= 100 AND low_price <= 150 THEN '150'  
									              WHEN low_price >= 150 AND low_price <= 200 THEN '200'
                                                      WHEN low_price >= 200 AND low_price <= 250 THEN '250'
                                                      WHEN low_price >= 250 AND low_price <= 300 THEN '300'
                                                      WHEN low_price >= 300 AND low_price <= 350 THEN '350'
                                                      WHEN low_price >= 350 AND low_price <= 400 THEN '400'
                                                      WHEN low_price >= 400 AND low_price <= 450 THEN '450'
												  ELSE '500'
												  END as high_range,
												  CASE
									              WHEN low_price < 100 THEN '0'
									              WHEN low_price >= 100 AND low_price <= 150 THEN '100'  
									              WHEN low_price >= 150 AND low_price <= 200 THEN '150'
                                                      WHEN low_price >= 200 AND low_price <= 250 THEN '200'
                                                      WHEN low_price >= 250 AND low_price <= 300 THEN '250'
                                                      WHEN low_price >= 300 AND low_price <= 350 THEN '300'
                                                      WHEN low_price >= 350 AND low_price <= 400 THEN '350'
                                                      WHEN low_price >= 400 AND low_price <= 450 THEN '400'
												  ELSE '450'
												  END as low_range,
									              CASE 
									              WHEN low_price < 100 THEN '$100 or less'
									              WHEN low_price >= 100 AND low_price <= 150 THEN '$100-$150'  
									              WHEN low_price >= 150 AND low_price <= 200 THEN '$150-$200'
                                                      WHEN low_price >= 200 AND low_price <= 250 THEN '$200-$250'
                                                      WHEN low_price >= 250 AND low_price <= 300 THEN '$250-$300'
                                                      WHEN low_price >= 300 AND low_price <= 350 THEN '$300-$350'
                                                      WHEN low_price >= 350 AND low_price <= 400 THEN '$350-$400'
                                                      WHEN low_price >= 400 AND low_price <= 450 THEN '$400-$450'
												  ELSE 'Over $500'
                                                      END as price_range
                                                      FROM inventory
                                    WHERE 1 AND $searchClause $brandClause $catClause) b
                                    GROUP BY price_range
								ORDER BY price_range");	

 

This allowed me two more fields to play with "high_range and low_range" to create the data to pass via the URL.

Thanks