SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  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,338
    Mentioned
    63 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,338
    Mentioned
    63 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,338
    Mentioned
    63 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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 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"

  8. #8
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 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"

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,033
    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

  11. #11
    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


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
  •