Help with mysql - php query

[msimonds]

Any help would be apprecaited. I am new at this and trying to get this query correct.

What I have is two tables, one for students, one for teachers

Here is the students table:

CREATE TABLE `students` (
  `student_id` int(10) unsigned NOT NULL auto_increment,
  `student_fname` varchar(100) NOT NULL default '',
  `student_lname` varchar(100) NOT NULL default '',
  `student_bdate` varchar(10) NOT NULL default '',
  `student_age` int(3) NOT NULL default '0',
  `student_address1` varchar(100) NOT NULL default '',
  `student_address2` varchar(100) default NULL,
  `student_city` varchar(100) NOT NULL default '',
  `student_state` varchar(100) NOT NULL default '',
  `student_zip` int(5) NOT NULL default '0',
  `student_father` varchar(100) default NULL,
  `student_mother` varchar(100) default NULL,
  `student_contact` varchar(12) NOT NULL default '',
  `teacher_id` int(2) NOT NULL default '0',
  PRIMARY KEY  (`student_id`)

here is the teacher table:

TABLE `teachers` (
  `teacher_id` int(10) unsigned NOT NULL auto_increment,
  `teacher_name` varchar(255) NOT NULL default '',
  `teacher_contact` varchar(12) NOT NULL default '',
  `teacher_email` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`teacher_id`)

I am trying to create a script that will allow me to print out class rosters. Here is what I have so far:

<?php

// Address error handing.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);


//Setup the datbase connection
include("./include/config.php");
include("./include/opendb.php");

$query = "SELECT teachers.teacher_id, students.teacher_id, teachers.teacher_name, students.student_fname, students.student_lname ".
         "FROM teachers, students GROUP BY teachers.teacher_name ".
            "WHERE teachers.teacher_id = students.teacher_id";


$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
    echo "{$row['teacher_name']} ' - ' {$row['student_fname']}&nbsp;{$row['student_lname']}";
    echo "<br />";
}



//close the database connection
include("./include/closedb.php");
?>

Can someone give me hand or point me in the right direction!

Thanks in advance

Mike

[Lats]

As each student has a teacher, what do you want this class roster do?

What's your desired outcome?

[msimonds]

thanks for the fast response, I want to be able get click on a link which will render each teacher and their students on a single printable page!!

I appreciate the response Lats!!!

[Lats]

Try using this query...

SELECT 
    teachers.teacher_name, 
    students.student_fname, 
    students.student_lname
FROM 
    teachers, 
    students 
WHERE 
    teachers.teacher_id = students.teacher_id
ORDER BY 
    teachers.teacher_name

[msimonds]

That is great and I acutally did that my friend and here are the results

http://www.sportsrant.com/school/roster_test.php is the url where I placed your query (along with some other info)

the roster is on the bottom.


What I am trying to do with the following code:

<?php
// Address error handing.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);
 
//Setup the datbase connection
include("./include/config.php");
include("./include/opendb.php");
//$tid = $_GET['teacher_id'];
$title = "Class Roster";
$query = ("
            SELECT teachers.teacher_id, students.teacher_id, teachers.teacher_name, students.student_fname, students.student_lname,
                   students.student_bdate, students.student_age, students.student_address1, students.student_address2, students.student_city, students.student_state, students.student_zip, students.student_father, students.student_mother, students.student_contact
            FROM teachers, students
            WHERE teachers.teacher_id = students.teacher_id
            ORDER BY teachers.teacher_name
         ");
$result = mysql_query($query) or die(mysql_error());
$prevkey = "";
// Print out the contents of each row into a table
echo "<h2>$title</h2>\n";
echo '<a href="index.php">Back To The Home Page</a>';
echo "<table border=\"1\">\n";
while($row = mysql_fetch_array($result))
    {
        //Prints out teacher and heading
        if ($prevkey != $row['teacher_id'])
            {
                $prevkey =  $row['teacher_id'];
                echo "<tr>\n";
                echo "<td style=\"font: bold; color: #FF0000; background: #eeeeee\" colspan=\"7\"><strong>Teacher:&nbsp;{$row['teacher_name']}</strong></td>\n";
                echo "</tr>\n";
                echo "<tr style=\"border: thin solid #eeeeee\">\n";
                echo "<td><strong>Student</strong></td>\n";
                echo "<td><strong>Birthdate</strong></td>\n";
                echo "<td><strong>Age</strong></td>\n";
                echo "<td><strong>Address</strong></td>\n";
                echo "<td><strong>Father's Name</strong></td>\n";
                echo "<td><strong>Mother's Name</strong></td>\n";
                echo "<td><strong>Phone Number</strong></td>\n";
                echo "</tr>\n";
            }
        //prints out students under that teacher
        echo "<tr>\n";
        echo "<td>{$row['student_fname']}&nbsp;{$row['student_lname']}</td>\n";
        echo "<td>{$row['student_bdate']}</td>\n";
        echo "<td>{$row['student_age']}</td>\n";
        echo "<td>{$row['student_address1']}<br />{$row['student_city']}, {$row['student_state']} {$row['student_zip']}</td>\n";
        echo "<td>{$row['student_father']}</td>\n";
        echo "<td>{$row['student_mother']}</td>\n";
        echo "<td>{$row['student_contact']}</td>\n";
        echo "</tr>\n";
    }
    echo "</table>\n";
    //Just for Testing
    echo $query;
//close the database connection
include("./include/closedb.php");
?>

is I want it to get a $tid (teacher id) from $_POST on the index page, drop down (which I can do) and then print the teacher by page or by the teacher selected, does that make sense?


I apprecaite all the help man, this is great!! It's nice of you to take the time out of your busy schedule and do this


Regards,
Mike

[msimonds]

I mean would this work if I used either $_GET or $_POST coming from the index.php:

 
$tid = $_GET['teacher_id'];
$title = "Class Roster";
$query = ("
            SELECT teachers.teacher_id, students.teacher_id, teachers.teacher_name, students.student_fname, students.student_lname,
                   students.student_bdate, students.student_age, students.student_address1, students.student_address2, students.student_city, students.student_state, students.student_zip, students.student_father, students.student_mother, students.student_contact
            FROM teachers, students
            WHERE $tid = students.teacher_id
            ORDER BY teachers.teacher_name
         "); 

I believe it would, we shall see

[Lats]

Yep, that looks like it should work okay.

You should check to make sure $tid is an numeric value too.

[msimonds]

Yeah man I will

Hey your from Au, Do you know of a coder down there named Natch from VB.org? He has helped me in the past and was the developer who wrote the custom CMS on my site for vbulletin.

Seems all you mates are friendly!! That is just outstanding!!

Again I apprecaite all the help Lats, thanks for taking the time

[King Kovifor]

also, if your using a vbulletin page for this you might want to do this:

mysql_fetch_array TO $db->fetch_array