Re: [PHP3] sql advice
| From: | (Richard Lynch) | Date: | Fri, 16 Jun 2000 00:33:19 +0000 |
| Subject: | Re: [PHP3] sql advice | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-2014@lists.php.net to get a copy of this message | ||
In article <005101bfd5d1$84742ab0$0201a8c0@ronny>, uberman@onemain.com
("Wes Weems") wrote:
> user issue status date
> -------------------------------------------------------------------
> wes connection closed 4/20/99
> rob browser closing 4/22/99
> connection browser =
> e-mail website installation
> -------------------------------------------------------------------------=
> ------------------------------------------------------------------------
> wes w 10 3 =
> 4 0 8
> -------------------------------------------------------------------------=
> ------------------------------------------------------------------------
> rob 4 3 =
> 2 1 0
> -------------------------------------------------------------------------=
> -----------------------------------------------------------------------
Ah! Now I see what you mean by 0 messing up.
Two options:
1) If your 5 preset issues are in a table, you could do a left outer join:
select count(issue), issue, email
from logs left outer join issues on logs.issue = issues.issue
group by issue
order by e-mail, issues.issue
(Maybe I got the left/right order of the join messed up...)
2) Brute force looping:
<?php
//NOTE: Issues order alphabetically to match up with SQL below:
//If you need them in another order, you'll need to have that something
//You can match up in SQL and here:
$presets = array('browser', 'connection', 'e-mail',
'installation',
'website');
$query = "select count(issue), issue, email from logs group by issue
order by e-mail, issue";
$issues = mysql_query($query) or die(mysql_error());
$last_email = 'an extremely unlikely email';
//There's probably a move_last() for an array, but I forget...
while(list(,$pre) = each($presets)){
//do nothing.
//Just want to be at the end of $presets to start off right.
}
while ($issue = mysql_fetch_row($issues)){
list($count, $iss, $email) = $issue;
//Time for a new row, when we hit a new email:
if ($email != $last_email){
$last_email = $email;
//Finish off previous row's unused presets with 0's:
while (list(,$pre) = each($presets)){
echo "<TD>0</TD>";
}
reset($presets);
echo "</TR>\n";
echo "<TR><TD>$email</TD>";
}
//Spit out 0 for any unused presets until we hit the
//one we have in our result set:
while (list(,$pre) = each($presets) && $pre != $iss){
echo "<TD>0</TD>";
}
//Now the one we are actually on:
echo "<TD>$count</TD>\n";
}
//Finish off last row:
while (list(,$pre) = each($presets)){
echo "<TD>0</TD>";
}
echo "</TR>\n";
?>
--
Richard Lynch | If this was worth $$$ to you, buy a CD
US Customer Support Director | from one of the artists listed here:
Zend Technologies USA | http://www.L-I-E.com/artists.htm
http://www.zend.com | (this has nothing to do with Zend,
duh!)