Page 1 of 1

SELECT one field, multiple tables, different values... how?

Posted: Fri Apr 16, 2004 12:14 pm
by Sinemacula
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.):

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&#1111;'cata.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">ESI</font></td>
					<td><?php echo $myrow&#1111;'esi.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">Fisher</font></td>
					<td><?php echo $myrow&#1111;'fisher.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">Omega</font></td>
					<td><?php echo $myrow&#1111;'omega.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">PCI</font></td>
					<td><?php echo $myrow&#1111;'pci.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">SDI</font></td>
					<td><?php echo $myrow&#1111;'sdi.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">SQSQ</font></td>
					<td><?php echo $myrow&#1111;'sqsq.compl_date']; ?></td>
				</tr>
				<tr>
					<td bgcolor="#330099"><font color="white">TCI</font></td>
					<td><?php echo $myrow&#1111;'tci.compl_date']; ?></td>
				</tr>
			</table>
...but from this I get:
Query failed: Column: 'compl_date' in field list is ambiguous
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.

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

Posted: Fri Apr 16, 2004 12:17 pm
by magicrobotmonkey
SELECT cata.compl_date AS cata_compl, esi.compl_date AS esi_compl etc...

Posted: Fri Apr 16, 2004 12:44 pm
by Sinemacula
Thanks for that quick reply!

The error message is now gone, but my html results table is blank... checking with phpMyAdmin, I know the codenumber I'm using does have entries in the databases, so I know my table is not supposed to be empty.

(I did change the echo statements to

Code: Select all

<$php echo $myrow&#1111;'cata_compl']; ?>
etc. (after first trying it as it was with the same results).

So, I think I must still be missing something.

Posted: Fri Apr 16, 2004 12:46 pm
by magicrobotmonkey
echo the query right before you use it and post the results

Posted: Fri Apr 16, 2004 12:57 pm
by Weirdan
UNION will suit you better:

Code: Select all

...........
$tables = array ('cata', 'esi', 'fisher', 'omega', 'pci', 'sdi', 'sqsq', 'tci');
$queries = array();
foreach($tables as $table)
   $queries[] = "select '$table' as table_name, max(compl_date) from $table where codenumber='" . mysql_escape_string($codenumber) . "'";
if( count($queries) ) {
   $query = implode(' UNION ', $queries);
   $res = mysql_query($query);
    //... and so on
}
PS: personally I think you have bad database design.

Posted: Fri Apr 16, 2004 12:58 pm
by Sinemacula
Sorry... here's where my noobness shows clearly...

not sure where you want me to place the 'echo'... should I put it right before the $query statement? or the $result statement? or somewhere else?

(Not being sure, I've just tried it before the $query statement... and here's what I got:
SELECT cata.compl_date AS cata_compl, esi.compl_date AS esi_compl, fisher.compl_date AS fisher_compl, omega.compl_date AS omega_compl, pci.compl_date AS pci_compl, sdi.compl_date AS sdi_compl, sqsq.compl_date AS sqsq_compl, tci.compl_date AS tci_compl 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=060-743
followed by the empty html table.)

Thanks.

Posted: Fri Apr 16, 2004 1:00 pm
by magicrobotmonkey
good answed, weirdan! I like that (and I agree with you on the design comment)

Posted: Fri Apr 16, 2004 1:04 pm
by Weirdan
Sinemacula wrote:
SELECT cata.compl_date AS cata_compl, esi.compl_date AS esi_compl, fisher.compl_date AS fisher_compl, omega.compl_date AS omega_compl, pci.compl_date AS pci_compl, sdi.compl_date AS sdi_compl, sqsq.compl_date AS sqsq_compl, tci.compl_date AS tci_compl 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=060-743
you need to enclose your codenumber in quotes since it's not number. Currently your query searches for codenumber -683 (note the 'minus' sign)

Also keep in mind that your query will return nothing unless the user has completed all assessments.

Posted: Fri Apr 16, 2004 1:08 pm
by Sinemacula
Well, in fact, as I've been asked for this additional functionality (after the fact) I have come to realize that the database design could certainly have used some input from someone more experienced than I. However, now I have to work with what I've got...

...but, your comment got me thinking... another way to deal with this might be to add an additional table with fields for codenumber, assessment_name, and completion_date... since I'm collecting those with each assessment submission anyway, I could just INSERT them into the new table and then make tracking by codenumber easier (from here on in, anyway).

What do you think? Would that just add another layer of mess in the longer run?

Posted: Fri Apr 16, 2004 1:11 pm
by Sinemacula
Adding the quotes worked... thanks!!!

However, returning nothing if not all assessments have been completed kinda defeats the purpose :roll:

So, I guess I need to find another solution... maybe I'll play with the additional table idea for a while.

Thanks,
Scott

Posted: Fri Apr 16, 2004 1:19 pm
by Weirdan
Sinemacula wrote: ...but, your comment got me thinking... another way to deal with this might be to add an additional table with fields for codenumber, assessment_name, and completion_date... since I'm collecting those with each assessment submission anyway, I could just INSERT them into the new table and then make tracking by codenumber easier (from here on in, anyway).

What do you think? Would that just add another layer of mess in the longer run?
I think it's a good idea. It's known in RDBMS world as 'many-to-many relation'. You have many users and many assessments ('many' = 'more than one' ;) ). And the fact of completing assessment is a relation between user and assessment.

Posted: Fri Apr 16, 2004 1:31 pm
by Sinemacula
Actually, I think I did do a bit of 'many-to-many' stuff in part of the design... for example, I've got tables for:
Users:
  • real_name
    codenumber
    email
    registration_date
    etc.
AssessmentX:
  • codenumber (which is automatically added from a cookie)
    compl_date (automatically added as a datetime)
    Answer1
    Answer2
    etc.
AssessmentY:
  • codenumber
    compl_date
    etc.
    etc.
and so on... don't know why I didn't think of adding a separate table for tracking assessment completion earlier! :oops:

Posted: Fri Apr 16, 2004 2:58 pm
by Weirdan
If I was you I would use something like:

Users
........

Assessments
id
text
etc

Answers
codenumber
assessm_id
compl_date
answer1
answer2
etc

Posted: Fri Apr 16, 2004 3:10 pm
by Sinemacula
I think that if I ever build something like this again, I will definitely go with something more like what you're suggesting. I'll probably also check in here for feedback before I get so far I can't feasibly change direction! :oops: :lol: :wink:

Posted: Fri Apr 16, 2004 7:23 pm
by Weirdan
You are welcome anytime ;)