SELECT one field, multiple tables, different values... how?
Posted: Fri Apr 16, 2004 12:14 pm
I'm somewhat of a noob with both mysql and php, so please bear with me...
I've got a database with several tables for keeping track of online assessments, including the completion date. I would like to be able to check whether a given user (tracked by codenumber) has completed the various assessments. So, what I thought I'd try to do is a SELECT of the completion date from each table based on codenumber (there's some additional code before this, for the db info include, etc.):
...but from this I get:
Any ideas on how to fix the code I've got... or even another/better way to acheive the goal of easily tracking a given user's completion of the assessments (given the existing tables... it's too late to change the database structure for this project... this tracking is an 'add-on' for a functioning site).
Thanks,
Scott
I've got a database with several tables for keeping track of online assessments, including the completion date. I would like to be able to check whether a given user (tracked by codenumber) has completed the various assessments. So, what I thought I'd try to do is a SELECT of the completion date from each table based on codenumber (there's some additional code before this, for the db info include, etc.):
Code: Select all
$codenumber = $_POSTї'codenumber'];
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$query = ("SELECT compl_date FROM cata, esi, fisher, omega, pci, sdi, sqsq, tci WHERE cata.codenumber=esi.codenumber and esi.codenumber=fisher.codenumber and fisher.codenumber=omega.codenumber and omega.codenumber=pci.codenumber and pci.codenumber=sdi.codenumber and sdi.codenumber=sqsq.codenumber and sqsq.codenumber=tci.codenumber and tci.codenumber = $codenumber");
$result = mysql_query($query,$db) or die("<P>Query failed: ".mysql_error());;
$myrow = mysql_fetch_array($result);
?>
<p></p>
<blockquote>
<p></p>
<table border="1" cellspacing="2" cellpadding="5">
<tr>
<td bgcolor="#330099"><font color="white">Assessment</font></td>
<td>Completion Date</td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">CATA</font></td>
<td><?php echo $myrowї'cata.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">ESI</font></td>
<td><?php echo $myrowї'esi.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">Fisher</font></td>
<td><?php echo $myrowї'fisher.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">Omega</font></td>
<td><?php echo $myrowї'omega.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">PCI</font></td>
<td><?php echo $myrowї'pci.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">SDI</font></td>
<td><?php echo $myrowї'sdi.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">SQSQ</font></td>
<td><?php echo $myrowї'sqsq.compl_date']; ?></td>
</tr>
<tr>
<td bgcolor="#330099"><font color="white">TCI</font></td>
<td><?php echo $myrowї'tci.compl_date']; ?></td>
</tr>
</table>Another thought I had was to just check each table to see if the codenumber exists, and then have a YES or NO in the results table... but I don't even know where to start for that.Query failed: Column: 'compl_date' in field list is ambiguous
Any ideas on how to fix the code I've got... or even another/better way to acheive the goal of easily tracking a given user's completion of the assessments (given the existing tables... it's too late to change the database structure for this project... this tracking is an 'add-on' for a functioning site).
Thanks,
Scott