[SOLVED] Field list is ambiguous error message

[loganb]

I'm working on what is essentially an online product catalog for a document management system, where on the list page you have several fields including a course number, faculty member etc.. and also a View Details link and Edit link which is supposed to take the user to a separate page to either view all info related to that product, or to edit/update that product.  The catalog part is working fine, however my problem is when I click on either of the links to view more information i receive the follow message:

 

Can't execute SELECT Semester, CourseNumber, DetailID, Outcome, Component, Faculty, MethodofAssessment, Assessment, Conclusion_recommendation, PaperCopySubmitted, ElectronicCopySubmitted FROM FormRequestDetail, ReceivedForms WHERE DetailID = DetailID: Column 'Semester' in field list is ambiguous

 

I'm attempting to pull from two tables in the same database, and the tables are linked by the DetailID field.  The php code for the online catalog part that is actually related to the individual product rows is

 

<?PHP
if (!isset($sem)){
    $sem='F06';
}

$query = "SELECT Semester, DetailID, CourseNumber, Outcome, Component, Faculty FROM FormRequestDetail where Semester like \"".$sem."%\"";
//$test=@array_search($sem, $sem_array[][2]);
$test=$sem_array[$ind-1][1];
print "<b><div align=center> <span class=\"stockheaderneg\"> <FONT color=#000000 size=+2>Semester: $sem</span></FONT></div><br></b>";
//print "The Query string is: <span class=\"chartlabelneg\">$query </span>";
$query_result=mysql_query($query,$mysql_link)
		or die ('<br />The query failed! <br>\n');
$irow=0;

while ($row = mysql_fetch_object ($query_result))
{
	if (($irow%2)==0){
    print "<TR bgcolor=\"#EBF8E9\" class=\"toolhead11\"> ";
}else{
	print "<TR bgcolor=\"#FFFFFF\" class=\"toolhead11\"> ";
}

print <<<END_HERE
<TD width=10% height=18> <div align="center">$row->CourseNumber  </div></TD>
<TD width=25% height=18> <div align="center">$row->Outcome  </div></TD>
<TD width=25% height=18> <div align="center">$row->Component </div></TD>
<TD width=10% height=18> <div align="center">$row->Faculty </div></TD>
<TD width=10% height=18> <div align="center"><a href="./report_details.php?new=$row->DetailID">View Report</a></div></TD>
<TD width=15% height=18> <div align="center"><a href="./edit_report.php?new=$row->DetailID">Edit Report</a></div></TD>
	 </TR>
END_HERE;
$irow++;
} //while
mysql_close($mysql_link);
?>

 

And the php code so far for the detail page that is throwing the error message is:

 

<?php

$sql ="SELECT CourseNumber, DetailID, Outcome, Component, Faculty, MethodofAssessment,
Assessment, Conclusion_recommendation, PaperCopySubmitted, ElectronicCopySubmitted FROM
FormRequestDetail, ReceivedForms WHERE DetailID = DetailID";

$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());
$row = mysql_fetch_array($result);

echo 'Course Number: <input type="text" name="CourseNumber" value="'. $row['CourseNumber'] .'"><br>';


?>

 

I think the problem is in how I'm attempting to send the DetailID which uniquely id's each product from the catalog page to the view and edit pages, but I'm not sure how to fix this problem.  I haven't done much formatting or outputting of data yet because I'm trying to get the query working, so if anyone has ideas I would appreciate it. 

[genericnumber1]

When you're querying data from two tables you need to specify what table the data you want is from..

 

eg.

 

instead of

SELECT Semester, CourseNumber...

you would do something like

SELECT ReceivedForms.Semester, FormRequestDetail.CourseNumber...

 

instead of

WHERE DetailID = DetailID

you would do

WHERE ReceivedForms.DetailID = FormRequestDetail.DetailID

(though I'd suggest a join, it's faster)

 

but I would suggest using the AS clause to cut down on the size of your query (unless you elect to use * in your select statement instead of what you have there).

[loganb]

Ok, I've changed so that I'm now just pulling all fields from the two tables and am now receiving this message:

Can't execute SELECT FormRequestDetail*, ReceivedForms* Where FormRequestDetail.DetailID = ReceivedForms.DetailID: 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 ' ReceivedForms* Where FormRequestDetail.DetailID = ReceivedForms.DetailID' at line 1

 

I'm still pretty new at PHP if you couldn't tell, and am working right now to try and get it functional, and will then work on making it more efficient and cleaner once it is working. 

 

the new code for the details page is:

 

//connection information omitted

$sql = "SELECT FormRequestDetail*, ReceivedForms* Where FormRequestDetail.DetailID = ReceivedForms.DetailID";

/*$sql ="SELECT FormRequestDetail.Semester, FormRequestDetail.CourseNumber, FormRequestDetail.DetailID, FormRequestDetail.Outcome,
FormRequestDetail.Component, FormRequestDetail.Faculty, ReceivedForms.MethodofAssessment, ReceivedForms.Assessment,
ReceivedForms.Conclusion_recommendations, ReceivedForms.PaperCopySubmitted, ReceivedForms.ElectronicCopySubmitted FROM
FormRequestDetail, ReceivedForms WHERE FormRequestDetail.DetailID = ReceivedForms.DetailID";*/
$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());
$row = mysql_fetch_array($result);

echo 'Course Number: <input type="text" name="CourseNumber" value="'. $row['CourseNumber'] .'"><br>';
?>

[genericnumber1]

SELECT FormRequestDetail.*, ReceivedForms.* FROM

FormRequestDetail, ReceivedForms WHERE FormRequestDetail.DetailID = ReceivedForms.DetailID

[loganb]

That fixed the sql error, thanks for the help.  Now would you have any idea why it won't pass the data in each field thru to the detail page?  All the fields have the same format as the Course Number field showed in my previous post, though it doesn't populate the data into the text box like it should.  I have a couple other pages with similar functions that I'm going to review and try to figure it out, but suggestions are always appreciated.