sorting query results into groups

[guitarromantic]

Hey,

I have a 'staff' table with a column 'status', corresponding to a
'statuses' table with the appropriate titles (eg 1 | Editor in Chief).
I want to display on the page the headings (the titles from statuses)
along with all of the staff members with that status ID.

I've attempted this using a query for each possible status, which I
figure was pretty inefficient (plus it didn't work properly). The best
I could do manually was to just display a list of staff and their
statuses sorted in order of the title ranks.

How can I do this efficiently?

-Matt

[Marcin Dobrucki]

gu************@ gmail.com wrote:

I have a 'staff' table with a column 'status', corresponding to a
'statuses' table with the appropriate titles (eg 1 | Editor in Chief).
I want to display on the page the headings (the titles from statuses)
along with all of the staff members with that status ID.

I've attempted this using a query for each possible status, which I
figure was pretty inefficient (plus it didn't work properly). The best
I could do manually was to just display a list of staff and their
statuses sorted in order of the title ranks.

How can I do this efficiently?

How about (skipping the fetch from the db, assuming you have a result
already):

while ($row =& $res->fetchRow()) {
$staff[$row['title']][] = $row['name'];
}
This should create something like this:

array ( "Secretary" => array (0 => "Lolli Pop",
1 => "Jonsey Jones"
3 => "Bob Bored"),
"Editor in Chief" => array (0 => "Big Fish",
1 => "Jack Sardine"),
...);

Well, you catch the drift.

/Marcin

[Steve]

I have a 'staff' table with a column 'status', corresponding to a
'statuses' table with the appropriate titles (eg 1 | Editor in Chief).
I want to display on the page the headings (the titles from statuses)
along with all of the staff members with that status ID.

You need a query that joins the two tables together. You haven't shown
the table definitions but it would look something like this:

SELECT staff.name, statuses.title
FROM staff, statuses
WHERE staff.statusid = statuses.status id
ORDER BY statuses.rank

---
Steve

[guitarromantic]

Sorry, I should have specified: I already have a join (inner) that does
that. My problem is actually displaying the data in groups.

Marcin: Could you explain a little of what's going on there? I'm pretty
new to all of this. Thanks!

[Marcin Dobrucki]

gu************@ gmail.com wrote:

Sorry, I should have specified: I already have a join (inner) that does
that. My problem is actually displaying the data in groups.

Marcin: Could you explain a little of what's going on there? I'm pretty
new to all of this. Thanks!

Jeps... you are creating a two dimentional hash. So, whereas in a
one-dimentional hash you would have:

array (hash => value,
hash2 => value2,
...);

you now have:

array (hash => array (hash => value,
hash2 => value2...),
haash => array (hash3 => value3
...

The foo[] construct is a shorthand for pushing onto an array (similar
to array_push()), but without the ill side-effect of renumbering indexes
(if that's important).

So, basically what I suggested is that when you process your joined
query result, you create a hash with key values equal to the 'status',
and the value an array of names in that status. You can then "foreach"
on that, to parse it out into some table, for instance like this (see
HTML_Table on the table genrating stuff):

<?php
require_once ('HTML/Table.php');
$staff = array ('Editors in Chief' =>
array (array('John', 'Doe'),
array('Bob', 'Edams'), array('Iggy', 'Pop')),
'Copywriters' =>
array(array('Jo e', 'Little'), array('Billy', 'Jean'),
array('Xena', 'Warrior')));

$t = new HTML_Table(arra y('border' => 1));
foreach($staff as $status => $folks) {
$t->addRow(array($ status),
array('colspan' => 2),
'TH');
foreach ($folks as $name) {
$t->addRow($name );
}
}
$t->display();
?>

This will create the following HTML:
<table border="1">
<tr>
<th colspan="2">Edi tors in Chief</th>
<!-- span -->
</tr>
<tr>
<td>John</td>
<td>Doe</td>

</tr>
<tr>
<td>Bob</td>
<td>Edams</td>
</tr>
<tr>
<td>Iggy</td>

<td>Pop</td>
</tr>
<tr>
<th colspan="2">Cop ywriters</th>
<!-- span -->
</tr>
<tr>
<td>Joe</td>

<td>Little</td>
</tr>
<tr>
<td>Billy</td>
<td>Jean</td>
</tr>
<tr>

<td>Xena</td>
<td>Warrior</td>
</tr>
</table>

The benefit here was that you only need to perform one query on the DB
(to fetch the names/status), and you don't really need to sort it by the
"status" field because that will be done during the construction of the
hash.

/Marcin

[NC]

guitarroman...@ gmail.com wrote:

I have a 'staff' table with a column 'status', corresponding to a
'statuses' table with the appropriate titles (eg 1 | Editor in Chief).
I want to display on the page the headings (the titles from statuses)
along with all of the staff members with that status ID. .... How can I do this efficiently?

Something like this should work (since I don't know your field
naming scheme, I had to invent my own field names):

$query = <<<EOQ
SELECT
staff.firstname AS firstname,
staff.lastname AS lastname,
staff.status AS status,
statuses.title AS title
FROM staff LEFT JOIN statuses ON staff.status = statuses.id
ORDER BY status
EOQ;
$result = mysql_query($qu ery)
or die('Could not execute query: ' . mysql_error());
$status = null;
while ($record = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
if ($record['status'] <> $status) {
$title = $record['title'];
echo "<p>Status: $title</p>\r\n";
$status = $record['status'];
}
$name = $record['firstname'] . ' ' . $record['lastname'];
echo "<p style='padding-right:10px'>$na me</p>\r\n";
}

Cheers,
NC

[guitarromantic]

NC,

That worked beautifully, thank you a ton!

[katreid69002]

gu************@ gmail.com a écrit :

NC,

That worked beautifully, thank you a ton!

I'm really interested i have something a bit similar to deal with
(exept i'm a total beginner)
Could you send me a 'create table' instruction that i shall use ?

[guitarromantic]

hey,

Mine is pretty specific to my site, but here's what I'm using:

CREATE TABLE `staff` (
`Staff_id` int(11) NOT NULL auto_increment,
`username` text NOT NULL,
`Staff_Email` text NOT NULL,
`password` text NOT NULL,
`status` text NOT NULL,
`profile` text NOT NULL,
PRIMARY KEY (`Staff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 75 ;

Password is stored as md5. Status is a number corresponding to the
'statuses' table (contains two columns, 'id' and 'title'). Profile is
just html code for that staff member's personal info.