Solved

sql query

Posted on 2011-09-05
20
291 Views
Last Modified: 2012-08-14
Hey guys, i have a database and a coloum called foodtypes which stores all my food types for a restaurant.

hey is a sample layout

id        Restaurant_Name     Food_Types        
1         ritz                             Buffet,Chinese,Indian


now in my website i have a checklistbox which the user select the food types :
here is a list of my checkboxlist

Buffet
Chinese
Indian
Italian
Portuguese
Seafood
Other

now if the user selects one of the following i want to do a search?

the hard part is that my coloum where my foodtypes is stored for a reastarant has "," to seperate the food types :( .

please help?

how would i go about to search in a query?
0
Comment
Question by:JCWEBHOST
  • 9
  • 5
  • 5
  • +1
20 Comments
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482804
how about

select something
from your table
where foodtype like '%<<your checkboxvalue>>%'
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482816
tbh  why not have 3 tables, foodtypes, restaurants and foodtypesatrestaurant f.i. ,
0
 

Author Comment

by:JCWEBHOST
ID: 36482817
will it check if a have Italian,Portuguese input form my checklistbox

and this is stored in my coloum Buffet,Chinese,Indian,Italian
0
 

Author Comment

by:JCWEBHOST
ID: 36482820
can not have 3 tables, i have a table to pull the list of foodtypes
0
 

Author Comment

by:JCWEBHOST
ID: 36482827
here my tables

TABLE: Food Types
COLOUMS : id,foodtypes

TABLE Restaurants
COLOUMS : id,restaurants,foodtypes
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482828
it will be almost impossible to do what you want :) but what you can do is have a loop in asp.net to use the query i mentioned earlier
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482832

TABLE: Food Types
COLOUMS : id,foodtypes

TABLE Restaurants
COLOUMS : id,restaurants

TABLE FoodTypesAtRestaurant
COLUMNS: id, restaurant_id,foodtype_id

cant change it to this?
0
 

Author Comment

by:JCWEBHOST
ID: 36483139
i do not want to put a 3 table
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36483143
then use a loop in your asp.net code and do 1 value at a time
0
 

Author Comment

by:JCWEBHOST
ID: 36483154
can you show me a loop in sql query to loop through a coloum foodtypes?

if i input Buffet,Chinese,Indian

must loop through the coloum and sperate the string and if it match return the row.

I know it hard but i need to do it that way.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483161
Something like this should do the trick.

The below searches for only chinese, but obviously you can change the variables in the select statement. I would suggest putting this in an sp and have these variables as inputs.

declare @FRENCH bit,@ITALIAN bit,@CHINESE bit
select @FRENCH=0,@ITALIAN=0,@CHINESE=1

select * from restaurants
where 
foodtype like case @FRENCH  WHEN 1 then '%FRENCH%' END OR
foodtype like case @ITALIAN  WHEN 1 then '%ITALIAN%' END OR
foodtype like case @CHINESE  WHEN 1 then '%CHINESE%' END

Open in new window

0
 

Author Comment

by:JCWEBHOST
ID: 36483180
i check that out and it works, but my problem is here

select @Buffet=1,@Italian=0,@Chinese=0

my foodtypes are in the table, how would i know how to assign that?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483205
This SHOULD work. The only issue you may have would be when the is a food type IT & ITALIAN. As IT would be a substring of ITALIAN, when selected it would bring up both. If you could have an extra seperator the would help ie [ITALIAN] [IT].

select distinct rest from #restaurants r
join #foodtypes f on r.foodtype like '%'+ f.foodtype+'%'
where f.foodtype IN ("SEARCH VALUES")

Open in new window

0
 

Author Comment

by:JCWEBHOST
ID: 36483244
i am lost:

here is my two table that i have:

TABLE ONE: foodtypes
Coloums: id, type

TABLE TWO: pretoria-("Restaurant Name")
Coloums: id, foodtypes,name



0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 36483268
select distinct rest from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ("SEARCH VALUES") 

Open in new window


The "SEARCH VALUES" sections should have something like 'BUFFET,CHINESE' etc.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483273
Sorry the beginning of the first line should be

select distinct name

instead of

select distinct rest
0
 

Author Comment

by:JCWEBHOST
ID: 36483293
select distinct r_name from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet,Indian')


do not give an output


select distinct r_name from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet')

give an output
0
 

Author Comment

by:JCWEBHOST
ID: 36483297
should i put an empty space by the coma?

like  'Buffet, Indian'
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483300
Sorry, my fault. Should be 'BUFFET','INDIAN'

Otherwise its searching for "BUFFET,INDIAN" as an entire string.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36483344
Hi Guys,

what about trying to turn the selection list into an array and then stepping through the array to build up the SQL statement required to pluck the entries required from the table (see my example code attached)

Apologies if the SAP is bad as my ASP skills are on the poor side of average at the moment (still trying to learn) but you should be able to get the idea and someone with some coding ability may be able to make it work.

Hope this helps,

Mark.
<%
'set up variable foodList and use SPLIT function to turn it into an array of the selected food types
dim foodList
foodList = Split(<<your checkboxvalue>>, ",")

'Set up SQL String
sqlstring = "SELECT * FROM restaurants WHERE "

'Loop through the array and add a where clause for each of the selected food types submitted
for i = 0 to Ubound(foodList)
	sqlString = sqlString & "restaraunts.Food_Types LIKE '%"& response.write(foodList(i)) &"%' OR"
next

'Strip the last three characters from the SQL string to remove the additional " OR" that would break the SQL statement
sqlString = LEFT(SqlString,(LEN(sqlString)-3))

'Run the SQL
rs = yourConnection.execute(sqlString)
%>

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now