#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    15
    What is the most efficient way to retrieve groups of infomation and print the headings only once. For example, I have a list of physicians (name, phone number) grouped by specialty (anesthesiology, cardiology, etc.). I want to list them by specialty but print the specialty once (first specialty, list of names and phones; second specialty, list of names and phones; etc.). Do I have to reconnect to the database (MySQL), or get a new result each time?

    ------------------
    From the day we're born, we're running out of time.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    63
    Rep Power
    15
    You can do a join between the physician and the specialty tables... that would result in extra data in the result set but you don't have to use it all.

    SELECT physicians.*, specialty.specialty_name FROM physicians, specialty where physician.specialty_id = specialty.specialty_id group by specialty.specialty_id

    That way, in the result set, you have the specialty name... as you are reading through the results, when the specialty changes, you print it.



    ------------------
    ----------------------
    Ulysses J Ludwig
    Consultant
    Ujludwig@yahoo.com
    213.948.8278
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    15
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by ujludwig:
    You can do a join between the physician and the specialty tables... that would result in extra data in the result set but you don't have to use it all.

    SELECT physicians.*, specialty.specialty_name FROM physicians, specialty where physician.specialty_id = specialty.specialty_id group by specialty.specialty_id

    That way, in the result set, you have the specialty name... as you are reading through the results, when the specialty changes, you print it.

    [/quote]

    Well, of course, I didn't explain it well. The existing page is at: http://www.wathosp.org/physicians/ph...directory.php3
    Problem is, the specialty and names and phones are in the same table (don't really need a separate table for specialty, though I could make one if I'm wrong).

    Here's the stripped down code (without the text formatting, etc.):

    <?
    $db = mysql_connect("username", "password");
    mysql_select_db("databasename",$db);
    $result = mysql_query("SELECT * FROM physicians ORDER BY specialty,last",$db);

    if ($myrow = mysql_fetch_array($result)) {
    printf("<TABLE BORDER=0 BGCOLOR='#FF0000' CELLSPACING=1 CELLPADDING=2 WIDTH=490 >n");

    printf("<TR><TD><b>Specialty</b></TD>
    <TD>Physician</TD>
    <TD>Telephone</TD></TR>n");
    do {
    printf("<TR><TD>%s</TD>
    <TD>%s %s, %s</TD>
    <TD>%s</TD></tr>n",
    $myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
    } while ($myrow = mysql_fetch_array($result));
    echo "</table><br><br>n";
    } else {
    echo "Sorry, no physicians were found!";
    }
    mysql_free_result($result);
    mysql_close($db);
    ?>



    ------------------
    From the day we're born, we're running out of time.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    63
    Rep Power
    15
    Ok, you were 1 step away from what I was getting at... within your loop just check to see if the specialty changes, if it does, print it


    do {
    if ($myrow["specialty"] != $old_specialty)
    {
    echo ($myrow["specialty"]);
    }
    $old_specialty = $myrow["specialty"];

    printf("<TR><TD>%s</TD>
    <TD>%s %s, %s</TD>
    <TD>%s</TD></tr>n",
    $myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
    } while ($myrow = mysql_fetch_array($result));


    ------------------
    ----------------------
    Ulysses J Ludwig
    Consultant
    Ujludwig@yahoo.com
    213.948.8278
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    15
    Thanks! That was it except for an "else" clause (to print the physicians with the specialty field blank). The final code that worked is:

    <?
    $db = mysql_connect("username", "password");
    mysql_select_db("databasename",$db);
    $result = mysql_query("SELECT * FROM physicians ORDER BY specialty,last",$db);
    if ($myrow = mysql_fetch_array($result)) {
    printf("<TABLE BORDER=1 WIDTH=500 >n");
    printf("<TR><b>Specialty</b></TD><TD><b>Physician</b></TD>
    <TD><b>Telephone</b></TD></TR>n");
    do {
    if ($myrow["specialty"] != $old_specialty)
    {
    printf ("<TR><TD>%s</TD><TD>%s %s, %s</TD><TD>%s</TD></tr>", $myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
    }
    else {
    printf ("<TR><TD></TD><TD>%s %s, %s</TD><TD>%s</TD></tr>", $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
    }
    $old_specialty = $myrow["specialty"];
    } while ($myrow = mysql_fetch_array($result));
    echo "</table><br><br>n";
    } else {
    echo "Sorry, no physicians were found!";
    }
    mysql_free_result($result);
    mysql_close($db);
    ?>

    The result can be seen at: http://www.wathosp.org/physicians/ph...directory.php3

    ------------------
    From the day we're born, we're running out of time.

Similar Threads

  1. Replies: 0
    Last Post: December 17th, 2003, 04:03 PM
  2. Confused about method calls ><
    By Brittster in forum Java Help
    Replies: 1
    Last Post: November 19th, 2003, 02:45 PM
  3. wats wrong with my method? newbie alert!
    By dave2k in forum Java Help
    Replies: 4
    Last Post: November 10th, 2003, 11:44 AM
  4. Help with "free(): invalid pointer" error on ftoa() method
    By registering in forum C Programming
    Replies: 2
    Last Post: October 29th, 2003, 11:59 AM
  5. how to match a whole method using regx
    By mzkhanis in forum Perl Programming
    Replies: 2
    Last Post: September 30th, 2003, 01:19 AM

IMN logo majestic logo threadwatch logo seochat tools logo