Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

search all paramaters except blank ones

Sep 6, 2008 12:19PM PDT

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 attributes 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 made one for each of the 16 possible combinations, but now I want to double 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!

Discussion is locked

- Collapse -
Re: search for all parameters except blank ones
Sep 6, 2008 8:50PM PDT

You've got to build up the query statement field by field.

- Start with SELECT * FROM table WHERE
- That the condition (like VAR_X = '$VAR_X') for filled fields only.
- The one detail to note: for all but the first condition add AND before.

So if only var_1 and var_7 are filled the result would be
SELECT * FROM tabel WHERE var_1 = '$var_1' AND var_7 = '$var_7'.

Kees

- Collapse -
not sure what you mean here
Sep 7, 2008 12:39AM PDT

I'm not sure I follow... are you saying to use the 'like' command or that there's no way around writing out a seperate section of code for each of the 512 combinations there are going to be for my 9 search criteria? I tried:

$result = mysql_query("SELECT * FROM table
WHERE (like VAR_X = '$VAR_X')
");

It gives me an error every time I search.

- Collapse -
Admittedly I made a typo ...
Sep 7, 2008 4:05AM PDT

Let me retry:

Build the query string dynamically in your code. This way:
- Start with SELECT * FROM table WHERE
- Then add the condition (VAR_X = '$VAR_X') for filled fields only. Do nothing for emtpy fields.
- The one detail to note: for all but the first condition add AND before

So if only var_1 and var_7 are filled the result would be
SELECT * FROM tabel WHERE var_1 = '$var_1' AND var_7 = '$var_7'.

Simple excersize: what would the query string be if only var_2, var_4 and var_7 are filled and all other ones empty?

Kees

- Collapse -
ok
Sep 7, 2008 5:26AM PDT

But I'd still have to do that 512 times for 9 paramaters right? I'm looking for a way to automate that process to avoid impossible to find typos and also to make changes in the future without dealing with each case individully.

- Collapse -
Re: SQl statement
Sep 7, 2008 6:35AM PDT

For 9 parameters that's 9 if-statements to build the final SQL-statement.

Kees

- Collapse -
?
Sep 7, 2008 8:17AM PDT

We're definitely not understanding each other than... There are 2 possibilities for each of the 9 optional paramaters... if the user fills it in, you search or if they don't, you don't search. To manually leave out the ones not filled in I'd need a chunk of code for each possibility... var_1 only, var_1 and var_2, var_1 and var_3, etc... that's the 2 to the 9th power = 512 I'm trying to avoid. How can I do it with only 9 if statements?

- Collapse -
You didn't understand ...
Sep 7, 2008 5:15PM PDT

my using "dynamically".

You don't write 512 different statements. You write some code that produces the right statement depending on the fields that are filled in. Can vary from zero (then it's just a select) to 9 (then it will contain all fields). Once you've made the correct statement, use that in stead of the fixed string you use now.

But there is an alternative. I can't give you the exact code (I don't use MySQL), so it's pseudo-code to adapt yourself:
select * from table where (field_1 = param_1 or param_1 = '') and (field_2 = param_2 or param_2 = '') and so on.
You might need to change = '' to is null, however.
It's not sure to work, but I used the principle in MS Access applications.

Kees

- Collapse -
dynamically
Sep 16, 2008 9:06PM PDT

Right, the 'dynamically' part is what I was having trouble with. Using PHP 'if' statements right in the middle of the MySQL query for example...

$result = mysql_query("SELECT * FROM table
WHERE
param_1='1'
AND if ($param_2 != "0") {param_2='$param_2'}
");

nor...
$result = mysql_query("SELECT * FROM table
WHERE
param_1='1'
if ($param_2 != "0") {AND param_2='$param_2'}
");

nor does...
$result = mysql_query("SELECT * FROM rest
WHERE
param_1='1'
AND (param_2='$param_2' OR $param_2 == '0')
");

note I changed param_2 to be numeric and avoid "0" just for troubleshooting purposes to avoid potential problems with "" vs null or whatever

- Collapse -
DUH!
Sep 16, 2008 9:19PM PDT

assign a new variable conditionally based on what was entered to equal either '*' or what was entered... i don't know why I didn't think of that earlier...

if ($param_2 == '0')
{$nprice = "*";}
if ($param_2 != '0')
{$nparam_2 = "$param_2";}

$result = mysql_query("SELECT * FROM table
WHERE
validate='0'
AND param_2 ='$nparam_2'
");

- Collapse -
hmm....
Sep 16, 2008 9:34PM PDT

nevermind, that dosen't work either...

- Collapse -
Keep trying.
Sep 16, 2008 9:47PM PDT

Or pay someone to do it for you.
Or go and follow a formal programming course on some university or commercial company.

Kees

- Collapse -
hmm...
Sep 16, 2008 9:53PM PDT

you know, if you don't have the answer, you don't have to respond...

- Collapse -
Sorry.
Sep 16, 2008 9:54PM PDT

I gave 2 different ways to do it. Somehow, you don't manage to realize even one of them. So I suggested it would be better if you get some hands-on help.

Kees

- Collapse -
not that I really want to get into some petty argument...
Sep 16, 2008 10:49PM PDT

My question was about HOW to build the query string dynamically... your first solution was just a restatement of the question, and the second one didn't work.

Not that I don't appreciate you trying to help, I do, but suggesting I give up and pay someone either to teach me or to make the site for me isn't really helping.

I don't if you took what I said earlier about not understanding each other as being argumentative, but I didn't mean it that way.

At any rate, the main reason I'm making this site is because I want to learn it on my own, so paying someone else to do it would defeat the purpose, and going back to school doesn't make sense considering the wealth of information available on the web. Posting the question on this forum is only one of many things I'm doing to learn PHP and MySQL.

- Collapse -
FIXED!!
Sep 17, 2008 9:36AM PDT

$sql = "SELECT * FROM table WHERE var_0 = '0'";

$vars = array('var_1', 'var_2', 'var_3', 'cvar_4', 'var_5', 'var_6', 'var_7', 'var_8', 'var_9');

foreach ($vars as $v) {
if (! empty($_POST[$v])) {
$sql .= " AND $v = '".mysql_real_escape_string($_POST[$v])."'";
}
}

$result = mysql_query($sql)

This one actually works and I somehow manage to 'realize' it... because it includes the actual code, not just an explanation of what the code should do.

I came across it with the help of some guys on dreamincode.com, check out their forums, they're good

- Collapse -
Good job. Perseverance helps.
Sep 17, 2008 5:52PM PDT

As I said, it are only 9 IF's. You did them in a loop that executes nine times.

Kees