Newbie PHP and MySQL question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
BlubberBoy
Forum Newbie
Posts: 2
Joined: Tue Oct 06, 2009 2:57 am

Newbie PHP and MySQL question

Post by BlubberBoy »

Apologies for posting such a basic question on here but I am something of a newbie to PHP.

I am writing a very simple application to retrieve and display data from a teachers table. The table includes a subject code but I would like to show the subject name instead, which lives in a different table containing the subjectCode and subjectName.

Simple as that! But I can't think exactly how to achieve it. Any tips would be most welcome. Thanks in advance! A small (simplified) excerpt from my code is shown below:

<?php

// Default page display

// Connect to the database server
$dbcnx = @mysql_connect('xxx', 'xxx', 'xxx');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

// Select the database

if (!@mysql_select_db('xxx_xxx')) {
exit('<p>Unable to locate the ' .
'database at this time.</p>');
}

// Request the ID and text of all teachers

$result = @mysql_query('SELECT id, schoolId, teacherNo, firstName, lastName, subjectCode FROM teachers ORDER BY lastname');
if (!$result) {
exit('<p>Error performing query: mysql_error() </p>');
}

echo "<table cellpadding=2 bgcolor=white align=center>
<tr class=tablehead>
<td><span class='headings'>Teacher No</span></td>
<td><span class='headings'>First name</span></td>
<td><span class='headings'>Last name</span></td>
<td><span class='headings'>Subject code</span></td>
</tr>";

while ($row = mysql_fetch_array($result)) {

$id=$row['id'];
$schoolId=$row['schoolId'];
$teacherNo=$row['teacherNo'];
$firstName=$row['firstName'];
$lastName=$row['lastName'];
$subjectGroup=$row['subjectCode'];

/* echo '<table width=100% border=0 bgcolor="white">'; */

echo
'<tr valign=top class=tablebody>
<td><span class=data>'. $teacherNo .'</span></td>
<td><span class=data>'. $firstName .'</span></td>
<td><span class=data>'. $lastName .'</span></td>
<td><span class=data>'. $subjectCode .'</span></td>

</tr>';
}
User avatar
N1gel
Forum Commoner
Posts: 95
Joined: Sun Apr 30, 2006 12:01 pm

Re: Newbie PHP and MySQL question

Post by N1gel »

You should probably do this using a join query. (but I can never remeber the correct syntax off the top of my head)

or you could just do it by embedding a sub query as below. But this might considered a little cheap and nasty

Code: Select all

 
SELECT id, schoolId, teacherNo, firstName, lastName, subjectCode,
(SELECT subjectName FROM subject WHERE subject.subjectCode = teachers.subjectCode) AS 'subjectName' FROM teachers ORDER BY lastname
 
Just another note I noticed your format seems to restrict a teacher to having only one subject, I thought you might want this to be a many to many relationship so might require an intermidiate table.
BlubberBoy
Forum Newbie
Posts: 2
Joined: Tue Oct 06, 2009 2:57 am

Re: Newbie PHP and MySQL question

Post by BlubberBoy »

Fantastic. Thank you.
Such a small thing but it'll completely transform my application.
PS: I did simplify the code slightly - in the real thing I hold up to four subjects for each teacher, so I can use the same strategy, though I completely take your point about the many-to-many relationships.
Post Reply