Mulitple variables in URL -> database query

[Russinating]

I'm working on a directory and I need a filtering function similar to that of a competitor, whereby you can select and deselect any number of options which pass to the database query. I've always been a long-winded/ugly coder, so while I can probably think of a way of doing this I just wanted to see if there was an easier way.

FYI the options are fixed: the directory is listed by location and category, and there are 24 categories and 53 locations. Any number of options need to be choose-able: from 1 category and 1 location to all 24 categories and all 53 locations (ie. the entire directory).

PS. I'm coding in PHP/MySQL.

Many thanks for your help in advance.

 

[meghatronic]

Surely this is just all neat sql queries?

I'd fully edit out competitor too.

 

[Russinating]

Surely this is just all neat sql queries?

I'd fully edit out competitor too.

Mind explaining what you mean or give an example?

And it's not like I'm advertising them or talking about their products: it's needed to give a specific example of my requirement.

 

[suarve]

I think he means that based on what's been selected you'd just be gnerating a slightly different sql query. E.g. if someone specifies a single category append 'where catid = 2' to the sql.

 

[Eriedor]

As said the key to this is quite simply sql statement generation.

!Important - Remember to sanitise your QueryString.

 

[Russinating]

I think he means that based on what's been selected you'd just be gnerating a slightly different sql query. E.g. if someone specifies a single category append 'where catid = 2' to the sql.

Yeah I understand that, but that didn't answer the question of how to pass and modify multiple variables without having 53x24 different SQL choices

Anyway, I've done it now, probably botched to hell, but it works - it shows what combination of things you're searching for and allows you to add and remove any number as needed.

 

[RobH]

Can you post an example of the code your using to do it at the moment, It will be easier to suggest improvements when we know what your doing. If your hard cosding 52 x 24 queries it sounds like there is a better way.

 

[Russinating]

Ok, here goes. For background info, the URL is in the format 'location=a7ba19b&category=a16ba3ba19b'. I just used a and b as ID separators... couldn't think of anything that was particularly better at the time.

Oh and I'm well aware that the code is probably ugly as hell, and I also haven't security proofed the inputs yet (i.e. stripping slashes etc) as it's not public atm.

$catid = $_POST['category'];
$locid = $_POST['location'];

if($_GET['cat']){
$catid = $_GET['cat'];
}

if($_GET['location']){
$locid = $_GET['location'];
}

$catremove = $_GET['catremove'];
if($catremove){
$catid = str_replace($catremove, '', $catid); // removes chosen category from search
}

$catadd = $_POST['categoryadd'];
if($catadd){
$catid = "$catid$catadd"; // adds chosen category to search
}

$locremove = $_GET['locremove'];
if($locremove){
$locid = str_replace($locremove, '', $locid); // removes chosen location from search
}

$locadd = $_POST['locationadd'];
if($locadd){
$locid = "$locid$locadd"; // adds chosen location to search
}

$catsql = str_replace("a", " OR category=", $catid);
$catsql = str_replace("b", " ", $catsql);
$catsql = substr($catsql, 4); // generates the category SQL

$locationsql = str_replace("a", " OR location=", $locid);
$locationsql = str_replace("b", " ", $locationsql);
$locationsql = substr($locationsql, 4); // generates the location SQL

if((!$locationsql) && ($catsql)){
$sql = mysql_query("SELECT * FROM businesses WHERE $catsql ORDER BY businessname ASC");
$realnumrows = mysql_num_rows($sql);
$sql = mysql_query("SELECT * FROM businesses WHERE $catsql ORDER BY businessname ASC LIMIT $startnum, 10");
}

if((!$catsql) && ($locationsql)){
$sql = mysql_query("SELECT * FROM businesses WHERE $locationsql ORDER BY businessname ASC");
$realnumrows = mysql_num_rows($sql);
$sql = mysql_query("SELECT * FROM businesses WHERE $locationsql ORDER BY businessname ASC LIMIT $startnum, 10");
}

if((!$locationsql) && (!$catsql)){
$sql = mysql_query("SELECT * FROM businesses ORDER BY businessname ASC");
$realnumrows = mysql_num_rows($sql);
$sql = mysql_query("SELECT * FROM businesses ORDER BY businessname ASC LIMIT $startnum, 10");
}

if(($catsql) && ($locationsql)){
$sql = mysql_query("SELECT * FROM businesses WHERE $catsql AND $locationsql ORDER BY businessname ASC");
$realnumrows = mysql_num_rows($sql);
$sql = mysql_query("SELECT * FROM businesses WHERE $catsql AND $locationsql ORDER BY businessname ASC LIMIT $startnum, 10");
}