Welcome to WebmasterWorld Guest from 18.232.99.123

Forum Moderators: open

Message Too Old, No Replies

Efficient code for SELECT query

     
11:33 pm on Aug 20, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:May 17, 2011
posts:170
votes: 0


EDIT: Post title word "SELCT" should be "SELECT" (obviously).

Perhaps this is the elephant in the room when it comes to SELECT queries. It seems a very, very basic issue to me, yet I've found nothing that addresses it explicitly. Here's the scenario, some variation on which appears at a million or so websites:

-----------------

Select a product category (one only):
product category 1
product category 2
product category 3
product category 4
product category 5
product category 6
product category 7
product category 8
product category 9
product category 10

Select a price range (one only):
< $10
$10-$20
> $20

Sort by price:
ascending
descending

[SUBMIT QUERY AND VIEW RESULTS]

-------------------

In this simple example, the total number of possible, distinct queries (permutations) is 60. Okay, you can write a distinct query for each of those 60. But the total number quickly grows as you add fields and records (never mind providing for the selection of more than one item from a list, which I'm omitting from this example to keep things simple). So what do you do? Write 600 distinct queries? 6,000? Conditionals don't serve to reduce that number or make the code more efficient, nor does breaking things down into separate queries (e.g., first select product type, then in the next query select price range and sort for that type). You can take advantage of default selections, but that hardly makes a dent either. There may very well be a simple, and basic, solution that I'm just not aware of.
9:22 am on Aug 21, 2014 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member graeme_p is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts:2953
votes: 195


You write code that generates the query.

Please do read up on SQL injection, prepared statements, sanitising inputs etc. before doing it.

What platform are you using? Often the best solution is to use a library or an ORM to generate queries for you.
12:30 am on Oct 6, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 9, 2014
posts: 150
votes: 0


This feels like a homework assignment, but since it was so long ago, this is how I'd do it for future readers.


# HTML
<select name="category">
<option value="1">Product category 1</option>
...
</select>

<select name="price">
<option value="1">< $10</option>
<option value="2">$10 - $20</option>
<option value="3">> $20</option>
</select>

<select name="sort">
<option value="ASC">ascending</option>
<option value="DESC">descending</option>
</select>




# PHP / MySQL

// Check for fake entries
if (
is_numeric($_GET['category']) &&
is_numeric($_GET['price']) &&
($_GET['sort'] == "ASC" or $_GET['sort'] == "DESC")
) {

// Code for price, but remember TIMTOWDI
if ($_GET['price'] == "1") $price = "price < 10";
elseif ($_GET['price'] == "2")
$price = "price >= 10 AND price <= 20";
else $price = "price > 20";

// Write query
$query = sprintf("SELECT * FROM table WHERE category='%s' AND $price ORDER BY %s",
mysql_real_escape_string($_GET['category']),
mysql_real_escape_string($_GET['sort']));

$result = mysql_query($query) or die(mysql_error());
}

// Submitted a fake entry, give error
else echo "Error";