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)

    Relationship between tables

    Hello all,
    I've just started working with relationships between table, i had a big table that had the following records:
    ID, Filename1, 2, 3, 4, Filesize1, 2 ,3 ,4, ContentType1, 2, 3, 4, BinaryData1, 2, 3, 4, PetDesc, PetName, PetType(Male/Female), Animalstatus, AnimalType(Dog/Cat)

    Now as u can already understand, i had alot of empty records on that table, because not all of the records were required (not every pet had 4 photos, most pets had just 1 or 2..)
    So i decided to saperate this table to 2 tables like the following:

    Animals:
    ID (AutoNum), PetName, PetDesc, PetType, AnimalType, AdoptionStatus

    Pics:
    ID (Num), FileSize, ContentType, BinaryData, FileName

    Im going to need help with creating an upload form to upload all of the items at once to those 2 tables, how can i do that?

    Atm the page i used to upload photos to the 1 big table looked like that...
    Code:
    ...
    sSQL = "animals WHERE FileID = 0"
    
    RecordSet.AddNew
    
    RecordSet.Fields("FileName").Value = Upload.Fields("File1").FileName
    RecordSet.Fields("FileName2").Value = Upload.Fields("File2").FileName
    RecordSet.Fields("FileName3").Value = Upload.Fields("File3").FileName
    RecordSet.Fields("FileName4").Value = Upload.Fields("File4").FileName
    RecordSet.Fields("FileSize").Value = Upload.Fields("File1").Length
    RecordSet.Fields("FileSize2").Value = Upload.Fields("File2").Length
    RecordSet.Fields("FileSize3").Value = Upload.Fields("File3").Length
    RecordSet.Fields("FileSize4").Value = Upload.Fields("File4").Length
    RecordSet.Fields("ContentType").Value = Upload.Fields("File1").ContentType
    RecordSet.Fields("ContentType2").Value = Upload.Fields("File2").ContentType
    RecordSet.Fields("ContentType3").Value = Upload.Fields("File3").ContentType
    RecordSet.Fields("ContentType4").Value = Upload.Fields("File4").ContentType
    RecordSet.Fields("BinaryData").AppendChunk Upload("File1").BLOB & ChrB(0)
    RecordSet.Fields("BinaryData2").AppendChunk Upload("File2").BLOB & ChrB(0)
    RecordSet.Fields("BinaryData3").AppendChunk Upload("File3").BLOB & ChrB(0)
    RecordSet.Fields("BinaryData4").AppendChunk Upload("File4").BLOB & ChrB(0)
    Recordset.Fields("Desc").Value = upload.fields("Desc1").Value
    Recordset.Fields("PetName").Value = upload.fields("name1").Value
    Recordset.fields("AnimalType").value = upload.fields("animalType1").value
    Recordset.fields("adoptionStatus").value = upload.fields("adoptionStatus1").value
    Recordset.fields("Type").value = upload.fields("type1").value
    
    RecordSet.Update
    ...
    Yeah, what a mess

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    675
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    uploading of multiple files is actually more easily done with a component or ASP.net

    for a component to use, consult your webhost to learn what is available to your website. Research that component to learn its usage.

    If none is available from the webhost, go to the ASP.net group and they can provide guidance on the uploading of multiple files.

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    well nvm it, i succeed building up a form that seems to be doing this nicely in ASP, but now i got stucked at the displaying part

    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 '  Error on this line, Object Required...
      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
      Set RS2 = Nothing
      RS.MoveNext
     Response.Write "</tr>"
     Loop
    RS.close

    Error on the line i marked...

  4. #4
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    675
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you create the object "RS2" ?

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah its all set at the top of the page..
    Code:
    Dim conn, RS, RS2
    ADB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/animals.mdb")
    Set conn = Server.CreateObject("ADODB.Connection")
    Set RS = Server.CreateObject("ADODB.recordset")
    Set RS2 = Server.CreateObject("ADODB.recordset")
    RS.Open ADB
    Very strange uh ? :P

  6. #6
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    675
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's only two objects in that line so it has to be one or the other.

    Create a separate connection (conn2) for use with rs2 and see if it gets resolved.

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    still didnt solved :/

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    solved it >_<
    The problem was that i setted RS2 = Nothing inside the Do While function...
    So when it ran its like RS2 was not even created

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,017
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ulthane, depending on how many records you've got, that could be a large amount of database action as you loop to display the records.

    I suppose you can't use a single joined Query to grab your pet data and all of the pictures into a single recordset but you could run simplify things by running query 1, loading pet data into an array, run query 2 load picture data into another array. Then loop through the array to pull the data. Then you only have to transact with the database twice.

    Database calls inside of a loop can get resource heavy so I like to reduce them as much as possible.

    Also check with the guys in the databases section of the forum. They may have some good ideas about how to set up your databases and relationships between them. They may be able to help you consolidate your query into a single call.

    EDIT: On further review of your code, it appears that it will have to be done with at least two calls to the database. One to grab all the pet data into an array and another to get all of the pictures but I'm not sure if you can put blob data into an array so you may have to settle for database calls within the loop.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello awasson, Thank u for making this point, it was really important for me to hear it, because i've just restarted building my site a few days ago, with making my pages load as fast as possible as my main target.
    Its the first time i hear about arrays, ill have to read around abit some tutorials and see some examples before i can start working with them..

    i noticed my code is pretty long...but thats all i could do with the knowledge i got atm, what u see above is just a small part of the code i got...

    here is the real thing, sorry its too long, but u guys got to see the real thing if i wanna get helped making my pages load faster in someway... if u guys can help me shorthen it alittle bit please it could be very usefull, doesnt must be on code itself, but on advices and examples could do aswell.
    Code ASP:
    <%
    Dim ConnToPets, CallToPets, CallToPets2
    AnimalDB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/animals.mdb")
    Set ConnToPets = Server.CreateObject("ADODB.Connection")
    Set CallToPets = Server.CreateObject("ADODB.recordset")
    Set CallToPets2 = Server.CreateObject("ADODB.recordset")
    ConnToPets.Open AnimalDB
    GetMsg = "SELECT * FROM Animals WHERE (animaltype= 'הודעה מיוחדת')"
    CallToPets.Open GetMsg, ConnToPets, 3, 3
    If Not CallToPets.EOF then
      sDesc = CallToPets("Desc")
      sDesc = Replace (sDesc, vbNewLine, "<br/>")
      Response.Write "<tr>"
      %>
      <td width="260" align="right" style="color:#0066FF;"><br /><br /><b><%=sDesc %></b></td>
      <%
      iPetID = CallToPets("FileID")
      GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
      CallToPets2.Open GetPics, ConnToPets, 3, 3
      Response.Write "<td width=260>"
      sImage = CallToPets2("FileID")
      %>
      <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
      <br />
      <%
      Response.Write "</td>"
      Response.Write "</tr>"
    CallToPets2.close
    End if
    CallToPets.close
    GetAnimals = "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'כלב חודש דף מידע')  ORDER BY fileID DESC"
    CallToPets.Open GetAnimals, ConnToPets, 3, 3
    If Not CallToPets.EOF Then
      Do While Not CallToPets.EOF
       sName = CallToPets("PetName")
       sType = CallToPets("Type")
       sDesc = CallToPets("Desc")
       sDesc = Replace (sDesc, vbNewLine, "<br/>") 
       Response.Write "<tr>"
       %>
       <td align="right" style="color:#0066FF;"><%if sName <> "" then %><b><%if sType = "זכר" then%>כלב החודש: <%=sName %><%elseif sType = "נקבה" then%>כלבת החודש: <%=sName %><%elseif stype = "רבים" then %>כלב/ת החודש: <%=sName %><%end if %></b><br /><br /><%end if %><%=sDesc %></td>
       <%
       iPetID = CallToPets("FileID")
       GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
       CallToPets2.Open GetPics, ConnToPets, 3, 3
       Response.Write "<td width=260>"
       Do While Not CallToPets2.EOF
        sImage = CallToPets2("FileID")
        %>
        <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
        <br />
        <%
        CallToPets2.MoveNext
       Loop
       Response.Write "</td>"
       CallToPets2.Close
       CallToPets.MoveNext
       Response.Write "</tr>"
      Loop
    End if
    CallToPets.close
    GetDogs = "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'כלב')  ORDER BY fileID DESC"
    CallToPets.Open GetDogs, ConnToPets, 3, 3
    If Not CallToPets.EOF Then
      Do While Not CallToPets.EOF
        sDesc = CallToPets("Desc")
        sName = CallToPets("PetName")
        sDesc = Replace (sDesc, vbNewLine, "<br/>") 
        Response.Write "<tr>"
        %>
        <td align="right"><%if sName <> "" then %><b><%=sName %></b><br /><br /><%end if %><%=sDesc %></td>
        <%
        iPetID = CallToPets("FileID")
        GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
        CallToPets2.Open GetPics, ConnToPets, 3, 3
        Response.Write "<td width=260>"
        sCounter= 0
        Do While Not CallToPets2.EOF
         sImage = CallToPets2("FileID")
         %>
         <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
         <br />
         <%
         CallToPets2.MoveNext
        Loop
        Response.Write "</td>"
        CallToPets2.Close
       CallToPets.MoveNext
       Response.Write "</tr>"
       Loop
    End if
    CallToPets.close
    %>
    </table>
    <b><font size="6" color="#0000FF" face="times new roman">כלבים שאומצו</font></b>
    <br /><br />
    <%
    GetAdoptedOnes = "SELECT * FROM Animals WHERE (adoptionstatus= 'לא זמין')  AND (animaltype= 'כלב')  ORDER BY fileID DESC"
    CallToPets.Open GetAdoptedOnes, ConnToPets, 3, 3
    If Not CallToPets.EOF Then
     Response.Write "כל הכלבים שאומצו רשומים כאן"
     Response.Write "<br/>"
     Response.Write "<table align=center width=520 style='margin-bottom:10px; border-style:outset; border-color:#267411;' border=5 cellspacing=3 cellpadding=3>"
     Do While not CallToPets.EOF
      sDesc = CallToPets("Desc")
      sDesc = Replace (sDesc, vbNewLine, "<br/>") 
      sName = CallToPets("PetName")
      sType = CallToPets("Type")
      response.write "<tr>"
      %>
      <td width="260" align="right"><%if sName <> "" then %><b><%=sName%></b><br /><br /><%end if %><%=sDesc %><br /><br />
      <%if stype = "נקבה" then %><span class="adopted"><b>אומצה!</b></span><%elseif stype = "זכר" then%><span class="adopted"><b>אומץ!</b></span><%elseif stype = "רבים" then %><span class="adopted"><b>כולם אומצו!</b></span><%end if %></td>
      <%
      iPetID = CallToPets("FileID")
      GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
      CallToPets2.Open GetPics, ConnToPets, 3, 3
      Response.Write "<td width=260>"
      Do While Not CallToPets2.EOF
       sImage = CallToPets2("FileID")
       %>
       <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
       <br />
       <%
       CallToPets2.MoveNext
      Loop
      Response.Write "</td>"
      CallToPets2.Close
      CallToPets.MoveNext
     Response.Write "</tr>"
     Loop
     Response.Write "</table>"
    Else
     Response.Write "אין כלבים שאומצו כרגע"
     Response.Write "<br/>"
    End if
    CallToPets.close
    ConnToPets.Close
    Set CallToPets = Nothing
    Set CallToPets2 = Nothing
    Set ConnToPets = Nothing
    %>

    Ill make a topic in the database forum aswell now, asking how can i make my database calls shorter if possible...

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey guys, i got this code from the database forum:
    Code:
    SELECT a.FileID
         , a.PetName
         , a.Desc
      FROM Animals AS a
    LEFT OUTER
      JOIN pictures AS p
        ON p.FileID = a.FileID
     WHERE a.adoptionstatus= 'adopted'
       AND a.animaltype= 'dog'
    ORDER 
        BY a.FileID DESC
    i tried to put this SQL code on that page instead of the first SQL line:

    Code:
    sql1="SELECT a.FileID
    , a.PetName
    , a.Desc
      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"
    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
    Well, i dont really know how to progress from here, how can i loop through all the images that belong to the same a.FileID using the new SQL code without having to make a 2nd db call...?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ulthane View Post
    ...how can i loop through all the images that belong to the same a.FileID using the new SQL code without having to make a 2nd db call...?
    start by putting the pet picture into the SELECT clause (where you removed the "p.OtherPetInfos" from the query i gave you)

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

  13. #13
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    K, so it would look like that
    Code:
    SELECT a.FileID
         , a.PetName
         , a.Desc
         , p.FileID
      FROM Animals AS a
    LEFT OUTER
      JOIN pictures AS p
        ON p.FileID = a.FileID
     WHERE a.adoptionstatus= 'adopted'
       AND a.animaltype= 'dog'
    ORDER 
        BY a.FileID DESC
    the P.FileID is the only coluum needed from the pictures table, and then for each p.fileID it goes to "PhotoDownload.asp", which is used to download the image itself into the page

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ulthane View Post
    the P.FileID is the only coluum needed from the pictures table, and then for each p.fileID it goes to "PhotoDownload.asp", which is used to download the image itself into the page
    why do you have a separate page to display the animal's photo?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, i found it on Lewis moten's guide once, found out it is matching what i was looking for and so started using it on my pages....
    That page looks like the following:
    Code:
    FileID = Request.QueryString("p.FileID")
    sql = "SELECT FileName, ContentType, BinaryData FROM Pictures WHERE FileID = " & FileID
    RS.Open sql, ConnToPets, 3, 3
    Response.AddHeader "content-disposition", "inline; filename=" & RS("FileName")
    Response.AddHeader "content-length", RS("BinaryData").ActualSize
    Response.ContentType = RS("ContentType")
    Response.BinaryWrite RS("BinaryData")

  16. #16
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,608
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Note: you can't have multi-line strings in ASP - you need to break them down line by line using the &_ delimiter. Example:

    Code:
    sql = "SELECT a.FileID" &_
    "     , a.PetName" &_
    "     , a.Desc" &_
    "     , p.FileID" &_
    "  FROM Animals AS a" &_
    "LEFT OUTER" &_
    "  JOIN pictures AS p" &_
    "    ON p.FileID = a.FileID" &_
    " WHERE a.adoptionstatus= 'adopted'" &_
    "   AND a.animaltype= 'dog'" &_
    "ORDER " &_
    "    BY a.FileID DESC"
    Ian Anderson
    www.siteguru.co.uk


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
  •