|
| |
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
 |
MySQL Inner Join issue - 10/12/2005 12:42:30
Hey all...
I have been struggling with this JOIN statement for 2 days. Maybe you can see something I am missing.
When I run this SQL
SELECT tbltopic.forum_id, tbltopic.topic_id, tbltopic.subject, tbltopic.date, tblthread.thread_id, tblthread.topic_id, tblthread.username, tblthread.message, tblthread.message_date FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where forum_id = " & request.form("forum_id") & "
I get this error:
Database Results Error
Description: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 256' at line 1
Number: -2147217900 (0x80040E14)
When I run the SQL this way
SELECT tbltopic.forum_id, tbltopic.topic_id, tbltopic.subject, tbltopic.date, tblthread.thread_id, tblthread.topic_id, tblthread.username, tblthread.message, tblthread.message_date FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where forum_id = '" & request.form("forum_id") & "'
I get this error.
Database Results Error
Description: Column 'forum_id' in where clause is ambiguous
Number: -2147217871 (0x80040E31)
Source: Microsoft OLE DB Provider for ODBC Drivers
The variable is being passed to the DRW via a hyperlink that sends the Forum_ID to it.
http://hc-wxmp-rrwagn/forum_choice.asp?Forum_ID=1
Any Ideas?
_____________________________
No matter where you go, there you are.
|
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 12:54:49
Does this make a difference?
SELECT tbltopic.forum_id, tbltopic.topic_id, tbltopic.subject, tbltopic.date, tblthread.thread_id, tblthread.topic_id, tblthread.username, tblthread.message, tblthread.message_date FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where tbltopic.forum_id = " & request.form("forum_id") & "
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 12:58:26
Duane,
it brings back this message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 256' at line 1
Number: -2147217900 (0x80040E14)
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 14:00:55
So what data type is forum_id, numeric or text?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 14:12:58
The datatype is DECIMAL(10,0)
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 15:44:24
It appears that forum_id is coded as a text field. What happens if you use this:
SELECT tbltopic.forum_id, tbltopic.topic_id, tblthread.thread_id, tblthread.topic_id
FROM tbltopic
INNER JOIN tblthread
ON tbltopic.topic_id = tblthread.topic_id
where tbltopic.forum_id = '" & request.form("forum_id") & "'
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 16:03:08
I get No Records Returned by adding the '
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 16:06:29
OK, I may have one more stab in the dark. Delete the single quotes I just had you add. Then post the full code for the page in question.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 16:11:00
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>forum</title>
</head>
<body>
<table width="100%" border="1">
<thead>
<tr>
<td><b>forum_id</b></td>
<td><b>topic_id</b></td>
<td><b>subject</b></td>
<td><b>date</b></td>
<td><b>thread_id</b></td>
<td><b>topic_id</b></td>
<td><b>username</b></td>
<td><b>message</b></td>
<td><b>message_date</b></td>
</tr>
</thead>
<tbody>
<!--#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 tbltopic.forum_id, tbltopic.topic_id, tblthread.thread_id, tblthread.topic_id FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where tbltopic.forum_id = " & request.form("forum_id") & ""
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&forum_id=201&topic_id=19&subject=201&date=133&thread_id=19&topic_id=201&username=201&message=201&message_date=201&"
fp_iDisplayCols=9
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"forum_id")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"topic_id")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"subject")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"date")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"thread_id")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"topic_id")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"username")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"message")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"message_date")%> </td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</body>
</html>
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 16:56:36
I'll be shocked if this solves it, but see if this makes a difference:
Find this line:
fp_iMaxRecords=256
and change it to:
fp_iMaxRecords=0
The only reason it occurs to me is the fact that your error message mentions 256.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 17:03:57
gets me this message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Maybe you are on to something here...
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 17:08:34
i changed it to 9
adn i got this message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 9' at line 1
You hit it right on the head here! Now how do we fix it? lol
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 17:12:18
The fp_iMaxRecords line is not essential. Try deleting it altogether, although I suspect the error is still elsewhere.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 17:19:59
disregard last post.
When deleting the imax_records
we get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 17:21:27
I suspect it has something to do with your join, but don't know enought about mySQL to know for sure. I will research what I can. Hopefully another member with mySQL experience can chime in.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 18:22:30
This is another wild guess, but have you verified that the form value is indeed being received? In other words, add <%=request.form("forum_id")%> somewhere to your page to ensure the forum_id is being received.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 19:23:27
Hey there...
I tried adding LIMIT 1,100 to the SQL and it came up with the same error.
i have also verified that the form value is being received by putting some text fields with request strings in there.
im out of options.....
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 21:30:16
quote:
ORIGINAL: Spooky
Can we go back a step?
If you use the same SQL but add an actual value for the variable, does the same error occur?
That's the puzzling part because earlier he said this:
quote:
when I run this query in MySQL Query Browser
SELECT tbltopic.forum_id, tbltopic.topic_id, tblthread.thread_id, tblthread.topic_id FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where tbltopic.forum_id = '8'
Everything is ok
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Spooky
Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 21:48:04
But the data type was decimal?
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/12/2005 22:02:50
That's what he said. Odd, ain't it?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
OscarSierra
Posts: 179
Joined: 5/14/2004
From: Norway
Status: offline
|
RE: MySQL Inner Join issue - 10/13/2005 4:28:19
Spooky gave me a very good advise with a similar (almost) problem, "try use PHP admin" if you can.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/13/2005 7:07:46
when I ran the sql in the mysql browser , the mysql browser returned results. So I put the code into a DRW, but the DRW errored out.
I couldn't understand the decimal issue myself so I dropped the tables last night and recreated them. This time they say integers, but the issue still remains.
????
_____________________________
No matter where you go, there you are.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: MySQL Inner Join issue - 10/13/2005 9:46:51
Back to Spooky's question . . . What happens if you hardcode a forum_id number into the DRW SQL instead of using Request.Form("forum_id")? Does that return results? For example:
SELECT tbltopic.forum_id, tbltopic.topic_id, tblthread.thread_id, tblthread.topic_id FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where tbltopic.forum_id = 8
(Making sure the ID you use actually exists in the table)
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/13/2005 10:04:20
OKay...
When I do that I get no records returned using this SQL
<% 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 tbltopic.forum_id, tbltopic.topic_id, tblthread.thread_id, tblthread.topic_id FROM tbltopic INNER JOIN tblthread ON tbltopic.topic_id = tblthread.topic_id where tbltopic.forum_id = 1 "
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=4 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="forum_id"
fp_sMenuValue="forum_id"
fp_iDisplayCols=4
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
Now when I put that into the MySQL browser it returns data.
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 568
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: MySQL Inner Join issue - 10/13/2005 10:13:54
I killed my results page and started a new one...
When i hard code teh forum_id=1 into it I get results. Cool!
When I try to pass the variable to it I get no records returned.
progress?
_____________________________
No matter where you go, there you are.
| |
|
 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 
k¥ 
