Efficient code for SELECT query

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.

# 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";

Efficient code for SELECT query

numnum

graeme_p

GoNC

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week