Announcement

Collapse
No announcement yet.

Help with mysql - php query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with mysql - php query

    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:

    Code:
    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:

    Code:
    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 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");

    $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
    Founder

    http://www.sportsrant.com

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

    What's your desired outcome?
    Lats...

    Comment


    • #3
      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!!!
      Founder

      http://www.sportsrant.com

      Comment


      • #4
        Try using this query...
        Code:
        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
        Lats...

        Comment


        • #5
          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 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
          Founder

          http://www.sportsrant.com

          Comment


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


            PHP Code:
             
            $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
            Founder

            http://www.sportsrant.com

            Comment


            • #7
              Yep, that looks like it should work okay.

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

              Comment


              • #8
                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
                Founder

                http://www.sportsrant.com

                Comment


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

                  mysql_fetch_array TO $db->fetch_array

                  Comment

                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                  Working...
                  X