Page 1 of 1
select statement using empty criteria
Posted: Wed Oct 13, 2004 9:41 am
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
Posted: Wed Oct 13, 2004 9:50 am
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?
Posted: Thu Oct 14, 2004 4:31 am
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
Posted: Thu Oct 14, 2004 5:07 am
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

Posted: Thu Oct 14, 2004 5:45 am
by phpScott
that's fine until you run out of memory and do a memory dump or have your brian leak out your ears.
MAX-EXECUTION_TIME ='head hurts time for a break'
Posted: Thu Oct 14, 2004 8:50 pm
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
Posted: Fri Oct 15, 2004 4:05 am
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
