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>';
}
Newbie PHP and MySQL question
Moderator: General Moderators
Re: Newbie PHP and MySQL question
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
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.
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
-
BlubberBoy
- Forum Newbie
- Posts: 2
- Joined: Tue Oct 06, 2009 2:57 am
Re: Newbie PHP and MySQL question
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.
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.