pls help me reg this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pls help me reg this query

Author

Topic

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 06:16:31

hi
doing search engine. user vl select category(4 categories in db.integer data type) and vl type text to search.how to write query for this.there are check boxes to let users to select categories.
so there may be any type of combinations. for ex assume tehre is 1,2,3 and 4 check boxes so the combinations may be 1 & 2,or 2&4 or 3&1 .....so pls tell be how ot write a query for this.

ITs urgent.

Thank u.
Abitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 06:22:42

Two methods:-

1.

SELECT fields
  FROM yourtable
  WHERE ','+ @CategoryIDs + ',' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

SELECT fields
   FROM yourtable
   WHERE category IN (SELECT value from dbo.fn_SplitString(@Category,','))

Where fn_SplitString is a function which accepts comma seperated list of categoryids and returns them as an array of values.You can find number of such functions within this forum.

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 06:36:51

Hi!
Thanks for ur reply. i am not using any functions or procedures. i am using query directly. so if u dont mind can u pls acc to this.
plssssssss.
Thanks again.
Abitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 06:41:57

quote:
Originally posted by Abitha

Hi!
Thanks for ur reply. i am not using any functions or procedures. i am using query directly. so if u dont mind can u pls acc to this.
plssssssss.
Thanks again.
Abitha

Then use first method. @CategoryIDs will be parameter passed from your front end application. (value from selected checkboxes)

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 06:47:09

ok.then shall i write query as
SELECT * FROM mytable
WHERE ',+ 1 + ,+ 2 +, + 3 +,+ 4 +, ' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

categories are numbers. then if 2 checkboxes selected means will this work?pls tell

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 06:54:11

quote:
Originally posted by Abitha

ok.then shall i write query as
SELECT * FROM mytable
WHERE ',+ 1 + ,+ 2 +, + 3 +,+ 4 +, ' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

categories are numbers. then if 2 checkboxes selected means will this work?pls tell

Nope you need to include code in your front end appplication to grab the values of selected checkboxes to a variable and pass it down to query.If you are using stored procedure it will have a parameter to accept your front end variable thats what i meant by @CategoryID. If you're using direct query on your application. You need to sunstitute your variable name for @CategoryIDs

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 07:05:37

ohh then shall i use like this

para1=value of checkbox1
para2=value of checkbox2
para3=value of checkbox3
para4=value of checkbox4

SELECT * FROM mytable
WHERE ',+ para1 + ,+ para2 +, + para3 +,+ para4 +, ' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 07:23:39

quote:
Originally posted by Abitha

ohh then shall i use like this

para1=value of checkbox1
para2=value of checkbox2
para3=value of checkbox3
para4=value of checkbox4

SELECT * FROM mytable
WHERE ',+ para1 + ,+ para2 +, + para3 +,+ para4 +, ' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok?

Nope based on what all checkboxes are selected, you need to get comma seperated value of ids into a variable and pass it to query. What is front end you're using?

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 07:26:29

ASP. check boxes are html check boxes.
para1=value of checkbox1
para2=value of checkbox2
para3=value of checkbox3
para4=value of checkbox4

SELECT * FROM mytable
WHERE ',+ para1,para2,para3,para4 +' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok now?

pls tell me about combinations also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 07:43:13

quote:
Originally posted by Abitha

ASP. check boxes are html check boxes.
para1=value of checkbox1
para2=value of checkbox2
para3=value of checkbox3
para4=value of checkbox4

SELECT * FROM mytable
WHERE ',+ para1,para2,para3,para4 +' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok now?

pls tell me about combinations also.

Refer this link

http://www.plus2net.com/asp-tutorial/form-checkbox.php

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 07:59:26

ok.now i have midified like
<html>
<input id="Checkbox1" type="checkbox" name="chk1">checkbox1
<input id="Checkbox1" type="checkbox" name="chk1">checkbox2
<input id="Checkbox1" type="checkbox" name="chk1">checkbox3
<input id="Checkbox1" type="checkbox" name="chk1">checkbox4

<script>

Dim mode,mode_a,i
mode=Request("chk1")

mode_a=split(mode,",")
For i=LBound(mode_a) to UBound(mode_a)
Response.Write mode_a(i) + "<br>"
Next
so the query is
SELECT fields
FROM yourtable
WHERE ','+ mode + ',' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok now?
will this search the text on selected categories?

SELECT * FROM mytable
WHERE ',+ para1,para2,para3,para4 +' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

Abitha
Starting Member

18 Posts

Posted - 2008-05-03 : 08:30:52

hi visakh16!
pls help as i am awaiting for your response...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 10:12:49

quote:
Originally posted by Abitha

ok.now i have midified like
<html>
<input id="Checkbox1" type="checkbox" name="chk1">checkbox1
<input id="Checkbox1" type="checkbox" name="chk1">checkbox2
<input id="Checkbox1" type="checkbox" name="chk1">checkbox3
<input id="Checkbox1" type="checkbox" name="chk1">checkbox4

<script>

Dim mode,mode_a,i
mode=Request("chk1")

mode_a=split(mode,",")
For i=LBound(mode_a) to UBound(mode_a)
Response.Write mode_a(i) + "<br>"
Next
so the query is
SELECT fields
FROM yourtable
WHERE ','+ mode + ',' LIKE '%,' + CAST(category AS varchar(5)) + ',%'

is it ok now?
will this search the text on selected categories?

Yes you need to put the SELECT query to string and execute it using
like
SQL = "SELECT fields
FROM yourtable
WHERE ','" & mode & "',' LIKE '%,' + CAST(category AS varchar(5)) + ',%'"

and then execute this

Abitha
Starting Member

18 Posts

Posted - 2008-05-05 : 01:14:54

hi
Thanks for reply. can u pls tell me how to write this query for MS Access. i am using the same in MS Access also. how to write query in MS Access.
pls help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 01:16:03

quote:
Originally posted by Abitha

hi
Thanks for reply. can u pls tell me how to write this query for MS Access. i am using the same in MS Access also. how to write query in MS Access.
pls help.

Not sure how you do this in MS-Access. Please post it in MS Access forums for quick solution.

Abitha
Starting Member

18 Posts

Posted - 2008-05-05 : 01:24:37

is it possbile to use this query for ms access. will it work?

then in this query i want to search string in story field means the query i it correct?

sectorobj.Open "SELECT * FROM tbl_Arab_Newsletter WHERE ','" & mode & "',' LIKE '%, + 'CAST(story AS varchar(5)) + ',%'"

Thank for quick reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 01:25:41

quote:
Originally posted by Abitha

is it possbile to use this query for ms access. will it work?

then in this query i want to search string in story field means the query i it correct?

sectorobj.Open "SELECT * FROM tbl_Arab_Newsletter WHERE ','" & mode & "',' LIKE '%, + 'CAST(story AS varchar(5)) + ',%'"

Thank for quick reply.

Are you passing Story as a comma seperated list?

Abitha
Starting Member

18 Posts

Posted - 2008-05-05 : 01:27:38

no
assume that i want to search string "Airports" in 3 categories.
Then how should be the query??

Abitha
Starting Member

18 Posts

Posted - 2008-05-05 : 01:29:53

actually there is 4 categories. user first select categories then they will search for the stories. they will give string to search.

how to do for this?story is the field where to search the given string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 01:35:41

quote:
Originally posted by Abitha

actually there is 4 categories. user first select categories then they will search for the stories. they will give string to search.

how to do for this?story is the field where to search the given string.

Ok so you mean you will have four checkboxes like cat1,cat2,cat3,cat4
User selectes any one of them and enters a string on textbox
Based on checkbox selected you have to serach in a particular field for string.

ex: if user checked cat1 then search entered string in story field.
Please confirm this is what you want.
b/w can i assume you pass only single string for searching or is there a chance of you passing comma seperated list.
like 'Airport','Harbour',...

Abitha
Starting Member

18 Posts

Posted - 2008-05-05 : 01:44:06

u r right. based on checkboxes selected by the user, search will be done. user may have selected 1 or 2 or 3 categories.
then search string is not seperated by commas. search string is entered in textboxes.

pls help me sir.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources