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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

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

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

SELECT cata.compl_date AS cata_compl, esi.compl_date AS esi_compl etc...
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

echo the query right before you use it and post the results
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Last edited by Weirdan on Fri Apr 16, 2004 1:11 pm, edited 2 times in total.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

good answed, weirdan! I like that (and I agree with you on the design comment)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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?
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

You are welcome anytime ;)
Post Reply