p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   help with this searching sql condition problems (http://p2p.wrox.com/showthread.php?t=65199)

kumiko January 11th, 2008 09:12 AM

help with this searching sql condition problems
 

halo there....i cant solve the problem and it is quite urgent. i hope u can give me a hand.
i need to do a search. but this search is quite complicated for me since i m newbie here. i m using ms sql with asp script. from my interface, there is one text box for user insert keywords search, and two checkbox for users to check wan search by photographer name or lightbox. by default, it will search the keywords with the photoname and also the photokeywords. when user check the photographer checkbox, it will also search the photographer keywords at the same time; if user check the ligthbox, it will search the photoname, photokeywords and also the lightbox keywords, see if got the lightbox match, then check inside the lightbox got any photo, if got, get the result. if user check both then will search both of the condition. my output will be the photoname, photohrapher name, if the photo is inside the lightbox, then will display the lightbox name also. i duno how to do a good searching that can reduce the searching speed in Db. plz help...
below is my table design. i hope u can help me..thank you.

tbl_photo (photoid, photoname,photokeywords)
tbl_lightbox (lightboxid,lightboxname, lightboxkeywords)
tbl_lightboxphoto(lightboxphotoid,lightboxid,photo id)
p/s : the photokeywors, lightboxkeywords are store in this way --> apple orange pear grapes (with a space between the words)

gbianchi January 11th, 2008 09:18 AM

hi there.. first at all, should be a checkbox, not a radio button ;) (same functionality, but more clear to the user).

second, can you point out an example?? did you already try something??

can you just use 2 querys for this???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

kumiko January 11th, 2008 09:23 AM

hi gbianchi,
soli..yaya..it is a chckbox. i draw it wrongly.
examnple? erm..i have can query out the keywords match with the tbl A only..since it is jz do the matching in one table. but now many tables..quite confuse..thanx


gbianchi January 11th, 2008 09:41 AM

try to separate your problem.. what do you need first?? what do you have?? I think you are not giving us enough info to help you.. also you should start trying to do something and we can help you go in the rigth way...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

kumiko January 11th, 2008 10:08 AM

Code:

<%

if request.form("btnSubmit")<>"" then
Dim  selCrit,arrSearch

    if request.form("strsearch") <>"" then
          Call Keywrd(strSQL)
    end if

    set rs = Server.CreateObject("ADODB.RecordSet")   

    Sub Keywrd(strSQL)
             selCrit = "Or"
             chk_photographer= request.Form("photographer")
             strInput= request.form("strsearch")
             strInput=Replace(strInput,","," ")
             //remove extraneous spaces in a string
             Do While InStr(1, strInput, "  ")
                strInput = Replace(strInput, "  ", " ")
             Loop
             response.Write strInput &"<BR>"

             // use split to break it down into an array
             arrSearch = split(strInput," ")
             strName = join(arrSearch, "%') " & selCrit & " (photoname LIKE '%")
                     strCombine =  join(arrSearch, "%') " & selCrit & " (photokeywords LIKE '%")
                     strphotographer= join(arrSearch, "%') " & selCrit & " (photographer LIKE '%")
                     strlightbox= join(arrSearch, "%') " & selCrit & " (keywords LIKE '%")

               strSQL = "SELECT * FROM tblp_photo"


                    strSQL = strSQL & "  WHERE(photokeywords LIKE '%" & strCombine & "%')"
                    strSQL = strSQL & " OR(photoname LIKE  '%" & strName  & "%')"
                    if request.Form("photographer")= "chkPhoto" then
                       strSQL = strSQL & " OR(photographer LIKE  '%" &  strphotographer & "%')"
                   end if
    End Sub

        rs.Open strSQL,objConn
        If not Rs.EOF Then
            Do While Not Rs.EOF
                response.Write rs("photoname")  &"<BR>"
                response.Write rs("photoid")
            rs.MoveNext
            loop
        end if
                response.write strSQL &"<BR><BR>"
end if

%>
<form name="frm" action="test4.asp" method="post">
<input type="text" name="strsearch" <%if request.form("strsearch")<>"" then  response.write request.form("strsearch") end if %> />
<br />
<input type="checkbox" value="chkPhoto" name="photographer" />
Photographers Name <br />
<input type="checkbox" value="chkLightBox" name="lightbox" />
Lightbox <br />
<input type="submit" name="btnsubmit" value="Submit" />
</form>
</body>
</html>

hi gbianchi,wat i have did is liek the code above. this code help me to query the result by default the photoname and when the yser check the chckbox to search by the photographers name also. but if user check the second checkbox, i duno how to cont to do it. and...if let say user check two checkbox, then how? i really need a help..plz.


gbianchi January 11th, 2008 10:14 AM

let's try to make this simple...

you have 3 diferents querys, aren't you??

well.. let's try to build them first, forget about the code, just the plain SQL.

what do you need for every query?? what data you have, and what data you want to extract from the tables..

maybe it's easier than you think...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

kumiko January 11th, 2008 10:23 AM

erm..i m not sure how many query i should need. but i think as u said.
1st : i will pass in KEYWORDS. the keywords by default will search the photo keywords.
2ndm : if the first checkbox is Checked, then from the keywords also will match with the photographers name.
3rd : if the second checkboz is checked, then the keywords also will match with the table lightbox, tabl lightbox photo as the link i posted it.

p/s: if the user check BOTh of the checkbox, then how?


gbianchi January 11th, 2008 11:13 AM

well.. if you have 2 checkboxes marked, them you will do 2 and 3 at the same time...

what you will show to the user, what data the user will see??? can you just start point by point to construct the query??



HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

kumiko January 11th, 2008 11:43 AM

the user will see the photoname that are matched


kumiko January 11th, 2008 11:49 AM

first, user insert such as --> apple, orange, pear, grape without checked any checkbox, the user will buy default search the input to match with the photo key words.
second, when user check on the Search by photographers name, it will match the string with the photo keywords and also the photographer name ( these two fields are in the same table let say table A).
third, if user check on the checkbox search by lightbox, it will match the input with the lighbox keywords in the table B. table C is store the FK lightbox id & the photo id to show that which photoes are in which lightbox folder.
for the second condition, i dont have idea to continue do the coding. if user checked two cehckbox, the solutionis how? thanx

** http://i263.photobucket.com/albums/i...miko2008/q.jpg


All times are GMT -4. The time now is 11:53 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.