Page 1 of 1

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

Posted: Tue Jul 26, 2005 10:34 am
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>";
}
?>

Posted: Tue Jul 26, 2005 10:59 am
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";
}

Posted: Tue Jul 26, 2005 11:34 am
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)

Posted: Tue Jul 26, 2005 11:40 am
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.

Posted: Tue Jul 26, 2005 1:40 pm
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 ?

Posted: Tue Jul 26, 2005 1:55 pm
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.

Posted: Tue Jul 26, 2005 2:20 pm
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 ....
:?

Posted: Tue Jul 26, 2005 2:26 pm
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

Posted: Tue Jul 26, 2005 2:28 pm
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.