Can you help with this basic bit of code please? (newbish)

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
Fimble
Forum Newbie
Posts: 12
Joined: Tue Jul 26, 2005 10:13 am

Can you help with this basic bit of code please? (newbish)

Post by Fimble »

Hi, ive only been learning PHP & MySQL for a couple of days and have run into a problem.
I have a database(student_register) with 2 tables(students and courses)
the course on student table is listed by its numerical id. The course table contains the courses names indexed by thier id.
i.e:
courses
id| Name
1 | C#
2 | VB
3 | A+

i have writen a PHP script to print the contents of students to an html table but rather than just print the course id i want it to look up the id on the courses table and print its actual name.
I have read that you cant us database relationships under MySQL.. is this ture?
and does it mean you have to code any kind of relational behavoir in PHP?

Here is my code a few pointers on how to access other tables within the same active database would be handy... every time i try to queary courses in the same section of code i queary students i get an result like: "Resource id 4" (or somthing like that)

At the moment it just prints out the course in the table as its id so how should i queary 'courses.course_name' do i need to change my mysql_select_db, open another connection, etc?

Thanks in advance.

Code: Select all

<?php
$conn = mysql_connect("localhost", "xxxxx", "xxxxxx");
mysql_select_db("student_register", $conn);
$sql = "SELECT * FROM students";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo"<table border =\"1\" cellpadding =\"4\" cellspacing =\"4\">\n";
echo "<tr>";
echo "<td>Student ID</td>";
echo "<td>Name</td>";
echo "<td>Surname</td>";
echo "<td>Address</td>";
echo "<td>City</td>";
echo "<td>Email</td>";
echo "<td>Phone</td>";
echo "<td>Course</td>";
echo "</tr>";

while ($newArray = mysql_fetch_array($result))
{
$id = $newArray['id'];
$name = $newArray['student_name'];
$surname = $newArray['student_surname'];
$address = $newArray['student_address'];
$email = $newArray['student_email'];
$city = $newArray['student_city'];
$phone = $newArray['student_phone'];
$course = $newArray['student_course'];
$comments = $newArray['student_comments'];
echo "<tr>";
echo "<td>$id</td>";
echo "<td>$name</td>";
echo "<td>$surname</td>";
echo "<td>$address</td>";
echo "<td>$city</td>";
echo "<td>$email</td>";
echo "<td>$phone</td>";
echo "<td>$course</td>";
echo "</tr>";
}
?>
Last edited by Fimble on Tue Jul 26, 2005 11:07 am, edited 1 time in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

You will need to use mysql_fetch_array() again if you make another query, this is why you get "Result #4"
If your tables are all in the same database you will not need mysql_select_db, in no case should you need to open a new connection unless you're trying to connect to another mysql server all together.
Hope this helps

Code: Select all

$result=mysql_query("select `id`, `whatever` from `students`");
while(list($id,$whatever)=mysql_fetch_array($result)) {
    $name=mysql_query("SELECT `name` from `table` where `id` = $id LIMIT 1 ; ");
    $name=mysql_fetch_array($name);
    echo $name[0].' '.$whatever."\n";
}
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT s.*, c.name
FROM students AS s
INNER JOIN courses AS c ON (students.course_id=courses.course_id)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

or something like:

Code: Select all

SELECT student_id, student_name, student_surname, student_address,
       student_email, student_city, student_phone, course_name,
       student_comments
FROM students AS s
INNER JOIN courses AS c USING (course_id)
It can be "dangerous" to use "*" select lists. If your DB schema changes, lots of queries could break that use "*", while an explicit select list survies most common changes (drop/add column pairs, column re-orderings, etc). And if you're joining on an identically named column you can use USING instead of ON.
Fimble
Forum Newbie
Posts: 12
Joined: Tue Jul 26, 2005 10:13 am

Post by Fimble »

Hi guys i tried this in msql:

Code: Select all

SELECT s.*, 
c.course_id 
FROM students AS s 
INNER JOIN courses AS c 
ON (students.student_course = courses.course_id);
but get

Code: Select all

Unknown table 'students' in on claus
how can it see the students table when it does the FROM but not when it does the ON?

Does it matter its called student_course in students and course_id in courses ?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Fimble wrote:Hi guys i tried this in msql:

Code: Select all

SELECT s.*, 
c.course_id 
FROM students AS s 
INNER JOIN courses AS c 
ON (students.student_course = courses.course_id);
but get

Code: Select all

Unknown table 'students' in on claus
how can it see the students table when it does the FROM but not when it does the ON?

Does it matter its called student_course in students and course_id in courses ?
Try changing the students in the ON to the 's' alias established by the AS.
Fimble
Forum Newbie
Posts: 12
Joined: Tue Jul 26, 2005 10:13 am

Post by Fimble »

nielsene wrote:
Fimble wrote:Hi guys i tried this in msql:

Code: Select all

SELECT s.*, 
c.course_id 
FROM students AS s 
INNER JOIN courses AS c 
ON (students.student_course = courses.course_id);
but get

Code: Select all

Unknown table 'students' in on claus
how can it see the students table when it does the FROM but not when it does the ON?

Does it matter its called student_course in students and course_id in courses ?
Try changing the students in the ON to the 's' alias established by the AS.
ok now using

Code: Select all

SELECT s.*, c.course_id 
FROM students AS s 
INNER JOIN courses AS c ON (s.student_course = c.course_id);
it does give me a result BUT the 'student_course' has just been replaced by 'course_id' and the table is now ordered by the course_id column ....
:?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Fimble wrote: ok now using

Code: Select all

SELECT s.*, c.course_id 
FROM students AS s 
INNER JOIN courses AS c ON (s.student_course = c.course_id);
it does give me a result BUT the 'student_course' has just been replaced by 'course_id' and the table is now ordered by the course_id column ....
:?
Try:

Code: Select all

SELECT s.*, c.course_name 
  FROM students AS s 
       INNER JOIN courses AS c ON (s.student_course = c.course_id)
 ORDER BY s.student_surname, s.student_name
Fimble
Forum Newbie
Posts: 12
Joined: Tue Jul 26, 2005 10:13 am

Post by Fimble »

superb! thank you... sorry to just blunder my way through this but its really helping my MySQL knowledge experimenting with all this feedback.

Thanks again.
Post Reply