Searching Two Tables

poisedforflight

I am trying to pull some information out of two tables and format it in an html table. This is my code, it tells me the query fails.

First, here are my two tables:

CREATE TABLE topicsTable (
topicId INT NOT NULL AUTO_INCREMENT,
topicName VARCHAR(50),
topicParent INT NULL,
PRIMARY KEY (topicID),
);

CREATE TABLE entryTable(
entryId INT NOT NULL AUTO_INCREMENT,
topicId INT,
topicBody TEXT,
PRIMARY KEY (entryId),
FOREIGN KEY (topicId) REFERENCES topicsTable (topicId)
);

here is the script as I have it now:

<?
include 'db.php';

$topicId=$_REQUEST['topicId'];

dbConnect("jmcclure_MyNotes");
$query = "SELECT topicName,topicBody

FROM topicsTable inner join entryTable
on topicsTable.topicId = entryTable.topicId
WHERE topicParent = $topicId";

$result = mysql_query($query) or die ("Query 'A' Failed " . mysql_error());

<html>
<head>
<title>MyNotes</title>
</head>

<?
while($row = mysql_fetch_assoc($result)){
print "<tr>";
print "<td>";
print $row['topicName'];
print "</td></tr>";
print "<tr>";
print "<td>";
print $row['topicBody'];
print "</td></tr>";
}
print "</table>";

</body>
</html>

topicName is in the entryTable and topicBody is in the entryTable. both tables contain topicId. I need to be able to pull the original topic first where topicId is $topicId and parentTopic = null, then underneath it list the child topics where parentTopic = $topicId.

Any ideas?

tomhath

What error do you get?

Are you sure you connected to the right server and database?

poisedforflight

most definitely connected to the proper server and db. the error it returns is that topicId is ambiguous.

tomhath

That error wouldn't come from the query you posted. Is there another one being run to get the original topic row? Make sure every place you have topicId it's prefixed with one of the table names. The error means you have soem thing like

select topicID, ... from topicsTable inner join entryTable ...

It doesn't know which topicId you mean

poisedforflight

Wow, i'm an idiot, I guess I copied the wrong script above. Here is the one that is giving me the problem:

$query = "SELECT topicName,topicBody
FROM topicsTable inner join entryTable
on topicsTable.topicId = entryTable.topicId
WHERE topicsTable.topicId = $topicId";

If i use the above, I get nothing returned. Do you see what I am trying to do?