Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 11th, 2008, 09:12 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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)
Reply With Quote
  #2 (permalink)  
Old January 11th, 2008, 09:18 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
Reply With Quote
  #3 (permalink)  
Old January 11th, 2008, 09:23 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old January 11th, 2008, 09:41 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
Reply With Quote
  #5 (permalink)  
Old January 11th, 2008, 10:08 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old January 11th, 2008, 10:14 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
Reply With Quote
  #7 (permalink)  
Old January 11th, 2008, 10:23 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #8 (permalink)  
Old January 11th, 2008, 11:13 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
Reply With Quote
  #9 (permalink)  
Old January 11th, 2008, 11:43 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the user will see the photoname that are matched

Reply With Quote
  #10 (permalink)  
Old January 11th, 2008, 11:49 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problems with my SQL string dstein4d Access VBA 9 September 19th, 2008 01:50 PM
SQL Statement problems tdaustin Classic ASP Basics 1 May 30th, 2005 07:16 PM
SQL authentication problems boucaner ADO.NET 7 February 11th, 2005 03:41 AM
sql for searching in between given dates reshmisworld Classic ASP Databases 1 July 26th, 2003 10:23 AM



All times are GMT -4. The time now is 07:20 AM.


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