select statement using empty criteria

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
muppet
Forum Newbie
Posts: 22
Joined: Sun Jan 25, 2004 2:01 pm

select statement using empty criteria

Post by muppet »

Is it possible to use a select statement that works empty criteria. eg a form is passed with 10 possible selections, only some of which are chosen by the user, if i use where criteria = '' it returns no results.

I tried a javascript function on the form page to create a string that comprised of only the chosen options before sending it to the results page and using this string in the select statement but it was to inflexible to be of any real use.

Any suggestions greatfully received before i loose a grip on reality..........

Ta muchly
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Not really, if one of your conditions is that criteria = '', then criteria must equal nothing. You might, however, be able to do LIKE statements:

Code: Select all

SELECT
     *
FROM
     my_table
WHERE
     criteria1 LIKE '%orange%' AND
     criteria2 LIKE '%hair%' AND
     criteria3 LIKE ''%%'
This will return all entries that contain the string 'orange' in criteria1, 'hair' in criteria2, and anything in criteria3.

Have you tried building the query in PHP?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you don't want to search on fields with no data entered you can also build your SQL statement in such a way that they are excluded from the search, example below:

Code: Select all

// assuming the fields and the entered data are all in an array called $data

$sql_criteria = array();
foreach ($data as $key => $value) {
    if (!empty($value)) {
        $sql_criteria[]  = $key." = '".mysql_escape_string($value)."'";
    }
}

$sql_criteria = (!empty($sql_criteria)) ? implode(' AND ', $sql_criteria) : 1;

$sql = "SELECT field1, field2, field3 FROM table WHERE $sql_criteria";
Mac
muppet
Forum Newbie
Posts: 22
Joined: Sun Jan 25, 2004 2:01 pm

Post by muppet »

Thanks guys, i've been playing around with building the sql string in php as opposed to JS, I'm getting there slowly, just getting bogged down with my and & or at the moment. Ever feel that your going round in a never ending for loop!!!!

Many thanks for your advice.

SMIFFY :-)
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

that's fine until you run out of memory and do a memory dump or have your brian leak out your ears. :D
MAX-EXECUTION_TIME ='head hurts time for a break'
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Yes, build your sqlstring incrementally, if a value is entered into one of the
10 possible selections and passes your edits, append that to the sqlstring. If it was not entered - do not bother with it. If it is a required part of the query - edit for that and flag it.

fv
muppet
Forum Newbie
Posts: 22
Joined: Sun Jan 25, 2004 2:01 pm

Post by muppet »

SORTED - Woo Hoo

Thanks for the help peeps. I've made a solution, bit cranky but it does the job - onto the next one.

Thanks again :D
Post Reply