i'm developing a database search page (using jsp), in this search page users will have multiple search conditions/options in the form of a drop-down list, those options can be left unselected (null in other words), if they're left unselected the complete column should be retrieved. To achieve this i'm using ifnull() function, and i'm using MySQL.
The problem i'm facing is that SQL can't seem to recognize that the string is null, when a null string is passed in the ifnull function, it retrieves null values instead of retrieving the complete column. Passing NULL directly in the query "ifnull(NULL,Location)" seem to work just fine.
The List:
<select name="EX">
<option value="NULL">Any</option>
<option value="1">Option1</option>
<option value="2">Option2</option>
<option value="3">Option3</option>
</select>
How values are handled in the next page:
String NameVar = new String();
if(request.getParameter("EX") == "NULL"){
NameVar = null;
}
else if(request.getParameter("EX") != "NULL"){
NameVar = request.getParameter("EX");
}
My Query:
selectItem = connection.prepareStatement("SELECT Location, ItemType "
+ "FROM DBTEST "
+ "WHERE Location = IFNULL(?,Location)"
Aucun commentaire:
Enregistrer un commentaire