CODEWITHSUNDEEP
sqloracle-databasesqlpluswhere-clause
mtfalcon31
mtfalcon31

Reputation: 37

Oracle's SQL - How to get user input and search it in a query

I have a database with menus and items, with a relationship between the two so that items can go on menus.

I want a select statement that can get varchar2 input from the user, and then check if the inputted value is part of the name of any items on the menus.

For example, if the user was to input 'strawberry' I would want the query to output all menus with corresponding items that have the word 'strawberry' in them. Like...

Menu                     Item
--------------------     -----------------------------
Valentine Theme          Chocolate covered strawberry       
Red Drink Theme          Strawberry Margarita
Red Drink Theme          Strawberry sensation

[How] is this possible? Here's what I've tried so far, and both have failed...

Try 1 :

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    (and item_name = '&name_of_item'
    or item_name like '%name_of_item%');

Try 2 (after some googling):

accept item_in prompt 'What item do you want to search for? '

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and item_name like item_in;

undefine item_in

Upvotes: 1

Views: 6144

Answers (3)

mtfalcon31
mtfalcon31

Reputation: 37

Thanks for the advice guys! Here's the working solution I ended up with:

set verify off

select 'The ' || menu_name || ' Menu contains ' || item_name Results
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and item_name like '%'||'&search_for_item'||'%'
order by menu_name,item_name;

set verify on

The only thing I wish I could do now is figure out how to force the inputted value to lowercase, as well as forcing the inputted value to start with a capital letter, so that I could get more results. Still seems to work great in general though!

Upvotes: 0

Timekiller
Timekiller

Reputation: 3136

There are indeed better ways than SQL prompt to get input from user, but your queries are still flawed. You either don't consider the case, or forget to add wildcards. maybe there are other problems, but you didn't state how the queries failed, exactly, so I have to assume.

Try this, your modified second query:

accept item_in prompt 'What item do you want to search for? '

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and upper(item_name) like upper('%'||item_in||'%');

undefine item_in

If it still fails, debug. Try to execute your query without item_in at all, put the string in there directly, until you get the data you want, then replace the string with substitution variable.

Upvotes: 1

Geoff
Geoff

Reputation: 1

Are you set on trying to do all of this through SQL or are you open to other options?

For example, you could use Oracle's Java library JDBC (https://docs.oracle.com/javase/tutorial/jdbc/overview/index.html) to build an interface in Java and connect to your database that way. It would give you better control over the presentation of your data and it might be beneficial to separate users from direct access to your database by creating another tier of programming structure.

Upvotes: 0

Related Questions