MySQL Inner Join issue

All Forums >> [Web Development] >> ASP and Database

Message

sentinel -> 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?

BeTheBall -> 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") & "

sentinel -> 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)

BeTheBall -> RE: MySQL Inner Join issue (10/12/2005 14:00:55)
So what data type is forum_id, numeric or text?

sentinel -> RE: MySQL Inner Join issue (10/12/2005 14:12:58)
The datatype is DECIMAL(10,0)

sentinel -> RE: MySQL Inner Join issue (10/12/2005 15:32:22)
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 When I run this query 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") & " I receive this error in a DRW 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) Source: Microsoft OLE DB Provider for ODBC Drivers Can someone tell me what the difference is betweenthese to SQLS? They look identical to me, but I am usually wrong! [&o] Thanks

BeTheBall -> 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") & "'

sentinel -> RE: MySQL Inner Join issue (10/12/2005 16:03:08)
I get No Records Returned by adding the '

BeTheBall -> 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.

sentinel -> 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>

BeTheBall -> 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.

sentinel -> 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...

sentinel -> 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

BeTheBall -> 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.

sentinel -> 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

BeTheBall -> 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.

sentinel -> RE: MySQL Inner Join issue (10/12/2005 17:30:06)
Thanks for taking som much time out of your day to help. This whole MySQL thing has been a nightmare from the beginning for me!... Doing this with MS SQL or Access would have been a breeze. I do hope someone can figure this out. I think more people are going to start using the new version of MySQL for the matter of price * FREE *. In time more people will be flooding this board with the same issues I've been crying about...[:(] Maybe one day there will be a section on here dedicated to MySQL / Frontpage usage / ASP usage. Later

Spooky -> RE: MySQL Inner Join issue (10/12/2005 18:10:19)
I cant locate it in the code at all? Limit is specific to MySQL in this instance, but you havent added it to your code so Im not sure where and why FP is doing it. Perhaps I would suggest adding it youself as a part of the custom SQL string?

BeTheBall -> 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.

sentinel -> 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.....

Spooky -> RE: MySQL Inner Join issue (10/12/2005 19:41:12)
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?

BeTheBall -> 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

Spooky -> RE: MySQL Inner Join issue (10/12/2005 21:48:04)
But the data type was decimal?

BeTheBall -> RE: MySQL Inner Join issue (10/12/2005 22:02:50)
That's what he said. Odd, ain't it?

Spooky -> RE: MySQL Inner Join issue (10/13/2005 1:37:06)
quote: forum_id=201 Hmmmm [8\|]

OscarSierra -> 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 -> 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. ????

BeTheBall -> 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)

sentinel -> 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.

sentinel -> 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?

Page: [1] 2 next > >>

0.109375