HolidayBuyer's Guide

Web Hosting, Design, & Coding forum

General discussion

search all paramaters except blank ones

by adamgram / September 6, 2008 12:19 PM 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
You are posting a reply to: search all paramaters except blank ones
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: search all paramaters except blank ones
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: search for all parameters except blank ones
by Kees Bakker / September 6, 2008 8:50 PM 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
by adamgram / September 7, 2008 12:39 AM 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 ...
by Kees Bakker / September 7, 2008 4:05 AM 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
by adamgram / September 7, 2008 5:26 AM 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
by Kees Bakker / September 7, 2008 6:35 AM PDT
In reply to: ok

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

Kees

Collapse -
?
by adamgram / September 7, 2008 8:17 AM PDT
In reply to: Re: SQl statement

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 ...
by Kees Bakker / September 7, 2008 5:15 PM PDT
In reply to: ?

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
by adamgram / September 16, 2008 9:06 PM 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!
by adamgram / September 16, 2008 9:19 PM PDT
In reply to: dynamically

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....
by adamgram / September 16, 2008 9:34 PM PDT
In reply to: dynamically

nevermind, that dosen't work either...

Collapse -
Keep trying.
by Kees Bakker / September 16, 2008 9:47 PM PDT
In reply to: hmm....

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...
by adamgram / September 16, 2008 9:53 PM PDT
In reply to: Keep trying.

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

Collapse -
Sorry.
by Kees Bakker / September 16, 2008 9:54 PM PDT
In reply to: hmm...

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...
by adamgram / September 16, 2008 10:49 PM PDT
In reply to: hmm...

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!!
by adamgram / September 17, 2008 9:36 AM PDT
In reply to: hmm....

$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.
by Kees Bakker / September 17, 2008 5:52 PM PDT
In reply to: FIXED!!

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

Kees

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

HOLIDAY GIFT GUIDE 2017

Cameras that make great holiday gifts

Let them start the new year with a step up in photo and video quality from a phone.