|
| |
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
 |
Display data from another table - 10/6/2003 12:36:36
Here is what I have so far:
http://www.hoopsking.com/browse_coaches.asp
What I am trying to make happen is this. You click on the last name of a coach and the books or videos he has authored will appear which will then be linked to the actual book or video. So clicking on a last name may return 4 videos. You could then click on those links for more info on the video.
Table A has the following fields: Item ID, Title, URL, Author (Lookup field from table B)
Table B has: Coach ID, Coach First Name, Coach Last Name
Thanks,
Chris
|
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 15:00:13
You can use hyperlink paramaters to do this, but the exact code will depend on your table B lookup and how that's formatted.
Create a page to display your coach's name, title, URL, etc. Let's call it displaycoaches.asp
The query for the page would be:
fp_sQry="SELECT * FROM TABLE_A WHERE (AUTHOR = ::author::)"
Then from browse_coaches.asp, you change the link to <a href="displaycoaches.asp?author=<%=FP_FieldURL(fp_rs,"COACH_LAST_NAME_FIELD")%>">
I don't know the name of the field in which you're storing your coaches last names, so... :)
That's the basic function. You're matching a name from Table A with a name from Table B to get a result.
Because you have duplicate last names, you'll have to alter this query to match both first AND last names against your author field.
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/6/2003 16:12:25
I've got it going to the display page but it won't return the records. I couldn't get the query you provided to work after I filled in the appropriate titles.
Here is what I have :
Table 1: Catalog Quick Find
Fields: ItemNumber, ItemName, URL, Author
Table 2: Author
Fields: CoachID, First, Last
Here are my web pages:
http://hoopsking.com/browse_coaches.asp
http://www.hoopsking.com/browse_coaches_display.asp
Thanks,
Chris
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 16:56:31
Can you please post the coaches_display.asp file as a txt file so we can see the original code?
just re-save the page as browse_coaches_display.txt
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 16:58:40
Does the Author field from Table 1 have the person's first and last name, or what? Can you post an example entry from that field?
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/6/2003 17:16:13
Here is an example of an entry:
ItemNumber ItemName Author URL
BV-052A Ball Handling Pitino http://www.hoopsking.com/store/videos/Pitino/ball_handling1.htm
Only the last name appears in the author field. The values come from Table 2 and it is a lookup field in Table 1.
Chris
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/6/2003 17:19:55
Here is the code:
<body><table width="100%" border="1">
<thead>
<tr>
<td><b>First</b></td>
<td><b>Last</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="CoachID,First,Last" s-columntypes="3,202,202" s-dataconnection="catalog_quick_find" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="First" s-menuvalue="First" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Author" s-displaycolumns="First,Last" s-criteria="[Last] EQ {Last} +" s-order s-sql="SELECT * FROM Author WHERE (Last = '::Last::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Last=" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="5" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" startspan b-WasTableFormat="TRUE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Author WHERE (Last = '::Last::')"
fp_sDefault="Last="
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="catalog_quick_find"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="First"
fp_sMenuValue="First"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-CheckSum="59525" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="CoachID,First,Last" s-column="First" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>First<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"First")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="7949" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="CoachID,First,Last" s-column="Last" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Last<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Last")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="3808" --></td>
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="5" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64><NOBR><INPUT TYPE=Button VALUE=" |< "><INPUT TYPE=Button VALUE=" < "><INPUT TYPE=Button VALUE=" > "><INPUT TYPE=Button VALUE=" >| "> [1/5]</NOBR><BR></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-CheckSum="62730" --></tbody>
</table></body>
</html>
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 18:06:24
1/ It does work. Just change your hyperlink parameter:
http://hoopsking.com/browse_coaches_display.asp?last=Brodzinski
2/ To get the results, change your hyperlink parameter on browse_coaches.asp. instead of author, use last
3/ This is going to get messy when you have two of the same last name, as you do with Williams
4/ Don't forget to put your code on the Spooky Diet
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 18:16:54
Regarding #3, you can do:
fp_sQry="SELECT * FROM Author WHERE (Last = '::Last::') AND (First ='::First::')"
the link would be:
<a href="browse_coaches_display.asp?last=<%=FP_FieldURL(fp_rs,"Last")%>&first=<%=FPfieldURL(fp_rs,"First)%>">
Then you're asking the page to display records where both the first and last name match. Otherwise, with your Williams, you'll get two to a page. Which is fine, too, I suppose!
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/6/2003 21:18:15
It's displaying the coaches name at this point. I want it do display the list of videos and books the coach has authored. Any thoughts on that?
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 21:49:06
Yes. In your DRW on the page that shows coaches, choose to display all the fields you want to display, which I guess is all the fields in that table. fp_sQry="SELECT * FROM Author WHERE (Last = '::Last::')" is the part that will make it pull each coach's particular records.
I was just showing you the link to send to that page to get it to display actual records.
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/6/2003 21:56:21
In other words:
Follow the steps here for your browse_coaches_display.asp page:
http://www.outfront.net/spooky/search_form.htm
At step H, you want to do WHERE LAST EQUALS LAST
You should then get a page with all of the timesfrom that table displayed. Whatever you don't want, like ID number, just remove.
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/7/2003 11:26:35
Hi,
I couldn't get it to work and am now back to not even passing on the to the display page. I appreciate all your help DarlingBri.
Can you tell me what I'm now doing wrong?
My hyperlink is: browse_coaches_display.asp?author=<%=FP_FieldURL(fp_rs,"Last")%> but that doesn't get me to the browse_coaches_display.asp page.
I guess I'm taking a couple of steps backward before I'm going to go forward.
Chris
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/7/2003 12:01:14
It should be: browse_coaches_display.asp?last=<%=FP_FieldURL(fp_rs,"Last")%>
See here: http://hoopsking.com/browse_coaches_display.asp?last=Brodzinski
You're essentially saying "take me to browse_coaches_display.asp and show me all the records where data in thefield called last = Brodzinski
And on browse_coaches_display, your SQL query is going to all of the records it has for last = Brozinski, or whatever name goes into the URL.
< Message edited by DarlingBri -- 10/7/2003 12:07:26 PM >
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/7/2003 12:32:36
The link you provided does work but here is what I have in my Hyperlink box : browse_coaches_display.asp?Last=<%=FP_FieldURL(fp_rs,"Last")%>
This sends me to my 404 error page.
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/7/2003 12:35:34
Would I be better off making the name field just one field?
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/7/2003 12:48:23
quote:
ORIGINAL: hoopsking
The link you provided does work but here is what I have in my Hyperlink box : browse_coaches_display.asp?Last=<%=FP_FieldURL(fp_rs,"Last")%>
This sends me to my 404 error page.
Dude, look at your source code:
<a href="browse_coaches_display.asp?Last=Altman">
<a href="Altman">Altman</a></a>
Go into HTML view and correct the code. You want one link:
<a href="browse_coaches_display.asp?Last="<%=FP_FieldURL(fp_rs,"Last")%>"> <%=FP_FieldURL(fp_rs,"Last")%></a>
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/7/2003 16:02:45
Hi,
I got most of it working now. Even the Williams only display the correct one.
Now, the problem I am having is that the fields it is looking at First and Last come from the Table "Author". These display on the http://www.hoopsking.com/browse_coaches_display.asp
I need to display fields from the Catalog Quick Find table that contain the item # and item title fields that are based on who the author is. I believe that the problem is that in the Author Table I have fields of First and Last where in the Catlog quick Find table it is just Author (which is a lookup field)
What to do?
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/7/2003 19:39:37
Can you give me an example of what format the data in Author is presented in? Doe? John Doe? Doe John? A number?
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/7/2003 23:08:42
The data in author is:
Coach ID, Last, First so an entry might look like 1, Williams, Gary
I'm not quite sure how to pull this off but I think this scenario might be the easiest. I want to display the coaches names on browse_coaches.asp. (Done) Clicking on a name passes on the Coach ID # to browse_coaches_display.asp. ( Not Done).
I created a query in the database to get the items to display based on the Coaches ID# but I put the ID # in . Here is the query:
SELECT [Catalog Quick Find].ItemNumber, [Catalog Quick Find].ItemName
FROM Author INNER JOIN [Catalog Quick Find] ON Author.CoachID = [Catalog Quick Find].Author
WHERE (((Author.CoachID)=30));
I think that solves most of it except I need that Coach ID passed from the list of coaches.
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/8/2003 10:01:49
I think the Coaching ID will work I just haven't figured out WHERE (((Author.CoachID)= Coach ID.
| |
|
|
|
DarlingBri
Posts: 3123
From: Left of Centre, Cork, Ireland
Status: offline
|
RE: Display data from another table - 10/8/2003 10:38:54
If you don't have the same coach id in both tables, it won't work.
http://hoopsking.com/browse_coaches_display.asp?last=Brodzinski works fine; you can retireve records successfully. what you need to do now is get the other data you want onto that page: the titles, the links to the shopping, etc.
Where is that information stored?
Or am I missing something?
| |
|
|
|
hoopsking
Posts: 165
Joined: 1/16/2002
From:
Status: offline
|
RE: Display data from another table - 10/8/2003 11:20:11
Here is what I have now:
I have the list of coaches but all records with a Coach ID are being returned.
http://www.hoopsking.com/browse_coaches3.asp (List Coaches)
http://www.hoopsking.com/browse_coaches_display3.asp (Displays Videos and books by coaches)
This is the hyperlink from the list of coaches page to pass on the Coaches ID:
browse_coaches_display3.asp?CoachID=<%=FP_FieldURL(fp_rs,"CoachID")%>
This is the custom query for browse_coaches_display3.asp and I don't think the (((Author.CoachID)=CoachID)); is quite right.
SELECT [Catalog Quick Find].ItemNumber, [Catalog Quick Find].ItemName
FROM Author INNER JOIN [Catalog Quick Find] ON Author.CoachID = [Catalog Quick Find].Author
WHERE (((Author.CoachID)=CoachID));
I think my custom query is just a bit off and this will work.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
