Jump to content

Archived

This topic is now archived and is closed to further replies.

Fearpig

Displaying Sub Results

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

Share this post


Link to post
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:

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

×

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.