Jump to content

Displaying Sub Results


Fearpig

Recommended Posts

Hi Guys,
Could anyone help me with this one.... I'm trying to display some data in the format of:

Department1
Name1
Name2
Name3

Deparment2
Name4
Name5

Department3
Name6
Name7
Name8...

I've got as far as this code:

[code]<?php

$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);

while ($row = mysql_fetch_array($result)) {
    printf("<b>%s </b><br>\n", $row["Department"]);
    while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
        }
    }
    echo '<br><br>';
}
?>[/code]

But unfortunately I only get:

Department1
Name1
Name2
Name3

Department2

Department3...

It look like the subresults are only being processed on the first pass  :(. Can anyone point me in the right direction?
Cheers
Tom
Link to comment
Share on other sites

Your second fetch statement:

[code]  while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
        }
[/code]
reads the entire selection within the first fetch for "Department". It prints out the matching $row['ID'] s and ingnores al the other ones. I would advise to use a JOIN so you can handle all via one select statement.
[code]SELECT t1.ID, t2.Department, t2.First_name, t2.Lastname
FROM tbl_department as t1, tbl_telephonenumbers at t2
WHERE t1.ID=t2.Department ORDER BY t1.Department[/code]

Ronald  :cool:
Link to comment
Share on other sites

Thanks Ronald but I didn't want to display the data that way.... and I managed to solve it myself  ;D  (I am very chuffed!!)

All I needed to do was move the $subresult inside of the loop so that it was processed on each pass!

[code]<?php

$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
//move $subresult from here

while ($row = mysql_fetch_array($result)) {
    printf("<b>%s </b><br>\n", $row["Department"]);
    $subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
}
    }
    echo '<br><br>';
}
?>[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.