[RESOLVED] query! One works, One Doesn't

msimonds

This is puzzling me. I have this query that works and prints out all the teachers and their associated students:

$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
         ");

then I have this one where I am trying to pass a $_GET variable in the URL string and I get nothing:

$tid = $_GET['teacher_id'];
$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 students.teacher_id = '$tid'
            ORDER BY teachers.teacher_name
         ");

If I pass the teacher_id, or in this case $tid, example being:

roster.php?tid=4

it should work!!!

Help anyone!

msimonds

I forgot to say that this test url from the first query prints out all the teachers and the students in their class:

http://www.sportsrant.com/school/roster.php

what I am trying to do with the second query is pass the teacher id from another page and make it so it will just get the teacher that is queried and his/her students for a class roster!!

Hope that makes sense

esukf

You are not passing GET correctly. Since you are using

 $tid = $_GET['teacher_id'];

Your GET querystring should be :-

roster.php?teacher_id=4

msimonds

Okay that helped a lot Thanks eskuf, i really apprecaite it

but

that should return all the students for that one teacher, it is returning all the students in the database multiple times:

How can i make the students.teacher_id = just the $tid in this case?

Thanks in advance!

BTW here is the whole roster script!!

<?php

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

$title = "Class Roster";


//Setup the datbase connection
include("./include/config.php");
include("./include/opendb.php");
$tid = $_GET['teacher_id'];
$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 students.teacher_id = '$tid'
            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");
?>

esukf

From looking at you query, i think you need a INNER JOIN query. Try

$query = ("
            SELECT teacher.teacher_id, teachers.teacher_name, students.*
            FROM teachers INNER JOIN students ON teachers.teacher_id = students.teacher_id
            WHERE teachers.teacher_id = '$tid'
            ORDER BY teachers.teacher_name
         ");

msimonds

WOW that was it!!!!

Outstanding!!!! Thanks so much!!

msimonds

so will this form work from a drop down?

oops forgot to pass the form here:

<form action="roster_test.php" method="get"><?
         include("./include/config.php");
         include("./include/opendb.php");
         $query = 'SELECT teacher_id, teacher_name FROM teachers ORDER BY teacher_name';
         $r = mysql_query($query) or exit(mysql_error());
         echo '<select name="teacher_id">';
          while ($row = mysql_fetch_assoc($r))
          {
              echo '<option value="' . $row['teacher_id'] . '">' . $row['teacher_name'] . '</option>';
          }
          echo '</select>';
          include("./include/closedb.php");
         ?></p>
         <input type="submit" name="submit" value="View Roster" /></td>
    </form>

would like to make it dynamic, like on the drop down, submit it without having a button

msimonds

if I use this form:

<form action="roster_test.php" method="get"><?
         include("./include/config.php");
         include("./include/opendb.php");
         $query = 'SELECT teacher_id, teacher_name FROM teachers ORDER BY teacher_name';
         $r = mysql_query($query) or exit(mysql_error());
         echo '<select name="teacher_id">';
          while ($row = mysql_fetch_assoc($r))
          {
              echo '<option value="' . $row['teacher_id'] . '">' . $row['teacher_name'] . '</option>';
          }
          echo '</select>';
          include("./include/closedb.php");
         ?></p>
         <? echo "<input type=\"hidden\" value=\"{$row['teacher_id']}\" /><br />"; ?>
         <input type="submit" name="submit" value="View Roster" /></td>
    </form>

the query string looks like this:

roster_test.php?teacher_id=1&submit=View+Roster

Sorry I am doing this on localhost

esukf

It should still work even with the extra get values. However it would be better if you pass the values using POST method instead of GET. You will have to change $GET['teacher_id'] to $POST['teacher_id'] instead.

Also going to http://www.sportsrant.com/school/roster_test.php?teacher_id=1&submit=View+Roster
it appears you are using the wrong sql statement.

msimonds

Sorry I forgot to update the data from localhost to my server

http://www.sportsrant.com/school/index.php

look at that now and tell me if the drop down works

Man I apprecaite you taking all the time to help me, that is outstanding!

ALSO I changed the form to $_POST

umm any idea on how to make the drop down work without a submit button

esukf

You need to use javascript. Change

<select name="teacher_id"> 
to 
<select name="teacher_id" onChange="this.form.submit()">

You won't need the hidden filed and submit button so can remove those.

msimonds

Perfect dude!!

I will make this resolved

This is a great place to get help and ideas!

esukf THANKS!!