help writing a code to search filled-in paramaters only

adamgram

So I'm very new to all this and I'm trying to build a website based on a MySQL database. I want to make a page that has a bunch of different paramaters you can search by. The way it's set up now if you don't fill out a certain criteria it only turns up entries where that criteria is blank. I want it to show all of the rows where the other columns match, ignoring the blank one. Here's what I'm doing now:

$result = mysql_query("SELECT * FROM table
WHERE var_1='$var_1'
AND var_2='$var_2'
AND var_3='$var_3'
");

$var_1, $var_2, and $var_3 all come from an html form, and if certain fields aren't filled in they're left blank and respond to if... statement where $var_1=='' When I only had 4 of them I wrote out one for each of the 16 possible combinations, but now I want to do 9 that so it's time to figure out a better way.

I've been searching for a couple hours but it seems like I don't know enough about MySQL to know what to look for. I just copied what I have now from a tutorial. Anyone one to help point me in the right direction? Thanks in advance!

bpat1434

Easiest thing I can say is to use PHP to dynamically create the query 😉

<?php

$query = "SELECT *
FROM `table`
WHERE ";

$where = '';
for($i=1; $i<=9; $i++)
{
    $var = 'var_' . $i;

if(empty($$var))
    continue;

if(!empty($where))
    $where .= " AND ";

$where .= "`var_{$i}` = '{$$var}'";
}

$query .= $where;

Obviously make sure that the query makes sense before you put this into production, but something like that should be viable. You could also use a bunch of if() statements to do the same thing.

adamgram

Hey thanks a lot for the help man... I think i'm just going to do it the long way though with 512 if... statements. I played around with what you had but I the the database isn't set up to work that way or something, I'm not really sure... for one thing the var_1 var_2... pattern was just made up for the sake of example, mine are actually things like $name, $price, $rating, and so on so the FOR loop wouldn't work as easily as it did in the example. Also mine starts with '$result' not '$query' so I think I'm hitting problems there too. Anyway, thanks for your help! It did teach me more even though I'm not using it here!

bpat1434

Well, the difference between $result and $query is that I build the query-string before using it in [man]mysql_query/man so if I were to expand my code chunk, It'd look like this:

<?php

$query = "SELECT *
FROM `table`
WHERE ";

$where = '';
for($i=1; $i<=9; $i++)
{
    $var = 'var_' . $i;

if(empty($$var))
    continue;

if(!empty($where))
    $where .= " AND ";

$where .= "`var_{$i}` = '{$$var}'";
}

$query .= $where; 

$result = mysql_query($query);

And I was just giving the "easiest" example for what your example showed. Since you have 512!?!?! columns to match against, then you have to do it manually. If there are 512 combinations that are possible, then you only need like 23 if statements (sqrt(512) approx. 23). If you give an idea of the table layout and exactly the way you're trying to search, I could give a more relevant answer.

adamgram

huh... I'm going to have to look into all that some more. For now what I ended up doing was writing a program to write the 512 combinations for me (it's 9 optional search criteria, so there's 2⁹ = 512 possibilities). Your way is probably easier but this still isn't too bad, I just run the script once and it outputs the script for the 512 elseif... statements I need, then I copy and paste into the php file. At least this way I can change it later without going thought all of them. Anyways, thanks again!

bpat1434

Ah, if there are 9 options, then there are 9 if() statements 😉 If it's not there, then don't include it in the query, otherwise include it. So instead of 512, you can narrow it to 9 🙂 Obviously 9 is the more practical approach, especially in a few months when you want to go back and add an item, or edit how things work and you lose that little script to generate the 512 options....