SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
Thread: Relationship between tables
-
Oct 30, 2010, 10:43 #1
- 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 ...
-
Oct 30, 2010, 11:30 #2
- Join Date
- Jun 2007
- Posts
- 691
- 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.
-
Oct 30, 2010, 13:45 #3
- 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...
-
Oct 30, 2010, 14:40 #4
- Join Date
- Jun 2007
- Posts
- 691
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
did you create the object "RS2" ?
-
Oct 30, 2010, 14:44 #5
- 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
-
Oct 30, 2010, 15:41 #6
- Join Date
- Jun 2007
- Posts
- 691
- 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.
-
Oct 31, 2010, 03:17 #7
- Join Date
- Jun 2010
- Location
- Israel
- Posts
- 523
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
still didnt solved :/
-
Oct 31, 2010, 03:30 #8
- 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
-
Oct 31, 2010, 11:02 #9
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 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
-
Oct 31, 2010, 11:50 #10
- 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...
-
Nov 1, 2010, 07:57 #11
- 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
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
-
Nov 1, 2010, 08:31 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 1, 2010, 08:51 #13
- 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
-
Nov 1, 2010, 08:57 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 1, 2010, 09:30 #15
- 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")
-
Nov 1, 2010, 09:51 #16
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- 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"
Bookmarks