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
select statement using empty criteria
Moderator: General Moderators
Not really, if one of your conditions is that criteria = '', then criteria must equal nothing. You might, however, be able to do LIKE statements:
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?
Code: Select all
SELECT
*
FROM
my_table
WHERE
criteria1 LIKE '%orange%' AND
criteria2 LIKE '%hair%' AND
criteria3 LIKE ''%%'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.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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:
Mac
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";-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas