Script not pulling all data from mysql tables

[karenruth]

I’m trying to create an html directory page which will pull information from a mysql database and display first a department header (sorted alphabetically by department name), then a list of people in that department (sorted alphabetically by person name).

 

My database consists of two tables, one for departments and one for people.

 

I’ve set up two queries:

$querypersonnel = 'select * from department, personnel where department.departmentid = personnel.department order by name';
$querydepartment = 'select * from department order by deptname';

 

And then I’m pulling the information into a table with two nested while loops:

while ($headrow = mysql_fetch_array($resultdepartment)) {
    $counter = $headrow['departmentid'];
    print "<tr><td colspan=2>" . $headrow['deptname'] . "</td></tr>\n<tr><td><hr></td></tr>\n";
    while ($row = mysql_fetch_array($resultpersonnel)) {
        if ($row['department'] == $counter) {
            print "<tr><td>" . $row['name'] . "</td></tr>\n";
        }
    }
}

 

I’m getting all the department names just fine, but I’m only getting person names for the first department.

 

Any ideas where I might be going wrong? (I’ve attached the full php script and the resulting html page as a text file)

 

Thanks in advance.

 

[attachment deleted by admin]

[ialsoagree]

It makes sense it only works for the first department.

 

Your second loop loops through ALL the names, regardless of whether they're in the department or not, it simply only prints the names that are in the department.

 

When it gets to that loop the 2nd time, $row = mysql_fetch_array($resultpersonnel) is false because you've already gone through every result, so the loop is skipped. This happens the 3rd, 4th, 5th, etc. times as well for the same reason. You've already gone through all the results so that line is immediately false.

[ialsoagree]

I was typing up a solution for you (actually 2) and noticed something interesting...

 

$querypersonnel = 'select * from department, personnel where department.departmentid = personnel.department order by name';

 

In this line, you get all the department info AND personnel info. Then you get the department info separately:

 

$querydepartment = 'select * from department order by deptname';

 

Why not do both at the same time?

 

$querypersonnel = 'select * from department, personnel where department.departmentid = personnel.department order by deptname, name';

 

This statement will sort your data first by department name (so all personnel in the same department will be together) and then by personnel name (so within each department, the personnel are sorted by their name alphabetically). This lets you use just 1 loop, looking for the department name (in case it changes) each time the loop iterates.

[karenruth]

When I do it that way, I get a lovely list of all the people alphabetized first by department and then by name, but I don't get the department names at the top of each list.

 

That's why I was using the two queries and two loops, because I couldn't figure out else to combine both lists in order.

[ialsoagree]

When I do it that way, I get a lovely list of all the people alphabetized first by department and then by name, but I don't get the department names at the top of each list.

 

That's why I was using the two queries and two loops, because I couldn't figure out else to combine both lists in order.

 

Sorry for the long delay. The name of the department is going to be stored for every user in that department.

 

That is to say, for each personnel in the database you will have a response row that will contain all the information about that personnel, and all the information about their department. You can access the department name the same way you do currently, except it will be in the array you fetch for the personnel, instead of it's own array:

 

$row = mysql_fetch_array($resultpersonnel);
print "<tr><td colspan=2>" . $row['deptname'] . "</td></tr>\n<tr><td><hr></td></tr>\n";