SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    decreasing the amount of database calls

    Hello guys, i got the following code:
    Code ASP:
    sql1= "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'חתול') ORDER BY FileID DESC"
    RS.Open sql1, conn, 3, 3
    If Not RS.EOF then
     Do While Not RS.EOF
      sName = RS("PetName")
      sDesc = RS("Desc")
      sDesc = RS(sDesc, vbNewLine, "<br/>")
      Response.Write "<tr>"
      ' Code that displays Pet's infos
      iPetID = RS("FileID")
      sql2 = "SELECT * FROM pictures WHERE FileID=" & iPetID
      RS2.Open sql2, conn, 3, 3
      Response.Write "<td width=260>"
      Do While Not RS2.EOF
       sImage = RS2("FileID")
       ' Code that displays Pets images
        RS2.MoveNext
      Loop
      Response.Write "</td>"
      RS2.close
      RS.MoveNext
     Response.Write "</tr>"
     Loop
    RS.close

    I want to know if its possible to change this code to a shorter code, using just a single recordset instead of 2, if yes can anybody show me a way how to do it?

    Thanks,
    Ulthane

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you should use a single join query

    since you have used the dreaded, evil "select star", it is difficult to write the join query, but i hope you will see where to put the "additional infos" columns if you need them
    Code:
    SELECT a.FileID
         , a.PetName
         , a.Desc
         , a.OtherAnimalInfos
         , p.OtherPetInfos
      FROM Animals AS a
    LEFT OUTER
      JOIN pictures AS p
        ON p.FileID = a.FileID
     WHERE a.adoptionstatus= '????'
       AND a.animaltype= '????'
    ORDER 
        BY a.FileID DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hmm, this is the first time i see that join query stuff, took a quick look at tizag tutorial and understood alittle bit on how it works..
    Now, i put that in SQL1 (ofc with some changes according to what i need) and then i wont need SQL2 anymore?
    can u show me please how the new code should look like after adding this sql query?

    Thanks,
    Ulthane

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulthane View Post
    can u show me please how the new code should look like after adding this sql query?
    not me, sorry, no, i don't do whatever language that code is (vbscript? asp? i have no idea)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    oh, thats ASP well ill be trying to play around abit with it, if ill get into troubles ill post here.

    Thanks for the help

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulthane View Post
    ... if ill get into troubles ill post here.
    i have a better idea -- post in the asp forum, not this one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey again guys,
    ill need help again on this matter, i've changed my tables so now it should be easier reducing the amount of SQL calls, atm i use it that way:

    Code:
    "SELECT FileID, PetName, Desc FROM animals WHERE adoptionstatus= 'Available' AND animaltype= 'cat' ORDER BY FileID DESC"
    ' Getting pet infos using a outer DO WHILE
    "SELECT FileName FROM pictures WHERE UniqueNum=" & RS("FileID")
    ' Getting Pet Images using a inner DO WHILE
    i must mention each pet in the animals table can have more than 1 pic on the Pictures table, while FileName is the path to the image, Now how can i get all the infos without having to do the inner SQL command?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulthane View Post
    Now how can i get all the infos without having to do the inner SQL command?
    see post #2 in this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i've already tried it and it still requires me to do another do while to display the images, maybe u could show me otherwise.....because saying "look at post #2" doesn't help me much, im not yet blind.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, mate, i can only give you help with the sql, i don't do asp

    please test the sql and make sure it's returning the right information, and then you can repost your question in the asp forum so that they can help you with the display
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    im sure its more of a database related question rather than ASP question
    i dont need help with displaying i just gave the code above to give u some info on how it is currently looking, so my goal is instead of using:
    Code:
    ' Select from Animals
    ' Getting pet infos using a outer DO WHILE
    ' Select from pictures
    ' Getting Pet Images using a inner DO WHILE
    it would look something like that :
    Code:
    ' Select from animals AND pictures
    ' Something like this:
    sql = "SELECT a.FileID, a.Desc, p.FileName FROM Animals AS a LEFT OUTER JOIN pictures AS p ON p.UniqueNum = a.FileID WHERE a.AnimalType= 'dogs'" 
    ' And now, do WHILE not EOF....
    ' Getting infos AND images
    the problem i encounter with the code you gave me above is that it works only when there is 1 photo (p.UniqueNum field) for each pet, if there's more than 1 photo it will just display the first one it gets and ignore the rest

    Well if there is no way of doing this without a 2nd SQL call then fine ill stay with 2, but if any1 got any idea of doing that with just 1 then i'd like to hear it

    Thanks for the help

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulthane View Post
    the problem i encounter with the code you gave me above is that it works only when there is 1 photo (p.UniqueNum field) for each pet, if there's more than 1 photo it will just display the first one it gets and ignore the rest
    that would be a problem with your asp logic

    using a LEFT OUTER JOIN, the query returns 0, or 1, or many pictures for each animal, depending on how many pictures there are for each animal

    your asp logic has to decide what to do when there is 0, or 1, or many

    but another query is not needed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,029
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT a.FileID
         , a.PetName
         , a.Desc
         , a.OtherAnimalInfos
         , p.OtherPetInfos
      FROM Animals AS a
    LEFT OUTER
      JOIN pictures AS p
        ON p.FileID = a.FileID
     WHERE a.adoptionstatus= '????'
       AND a.animaltype= '????'
    ORDER 
        BY a.FileID DESC
    Ok, I've been following this over on the ASP side so I'll chime in here...

    @ulthane: It's a bit difficult to figure out what's going on without seeing the structure of your images table. In the above SQL statement, what represents the actual file name for the picture? I only see one reference to the pictures table in the SQL statement (p.OtherPetInfos).
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  14. #14
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT a.FileID
         , a.PetName
         , p.FileName
      FROM Animals AS a
    LEFT OUTER
      JOIN pictures AS p
        ON p.FileID = a.FileID
    ORDER 
        BY a.FileID DESC
    Lets just take the example SQL on the above, on the following tables: (btw yeh from the pictures table i only need the fileName where the pFileID = aFileID

    Animals:
    FileID / PetName
    1 / Fido
    2 / Rex

    Pictures
    FileID / FileName
    1 / 1.jpg
    1 / 2.jpg
    2 / 3.jpg

    What will be the results of the SQL? how will they be shown...

    thanks for the help

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulthane View Post
    What will be the results of the SQL?
    Code:
    FileID PetName FileName
     1     Fido    2.jpg
     1     Fido    1.jpg
     2     Rex     3.jpg
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,029
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    That's what I was looking for (p.FileName). Great, we can take this back to the ASP forum and you should be able to loop through the results now.

    Thanks Rudy.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •