Hi,
I have three tables: events, users and ev_types.
columns in events: id, user, ev_type, time
columns in ev_types: id, name
columns in users: id, name
I am trying to crosstabulate users against events, with the users in the rows and the ev_types in the columns. After asking google I found a website (evolt.org) with the answer to my problem. And it works... almost! I get the data, proper column names, but my user names are not displayed properly, at least not in my html page.
The script below makes the following query:
select users.name,
sum(if(ev_type = '1', 1, 0)) as 'user logged in' ,
sum(if(ev_type = '2', 1, 0)) as 'user logged out' ,
sum(if(ev_type = '3', 1, 0)) as 'entry added' ,
sum(if(ev_type = '4', 1, 0)) as 'entry changed' ,
sum(if(ev_type = '5', 1, 0)) as 'user did something' ,
count(user) as 'total'
from users
inner join events on users.id = events.user
group by events.user
If i run the query in PHPMyAdmin it displays fine, listing the names of the users. If I display the results in my html page, it lists a 0 in stead of the user name. Must be something to do with pointers? The data is displayed correctly though.
I must be doing something wrong :rolleyes:
Here is the script:
<?php
include("includes/normal.php");
db_connect();
//get all events in table
$sql="select distinct name, ev_types.id from ev_types inner join events on ev_types.id = events.ev_type";
$mevents = mysql_query($sql) or die('oops that was wrong: '.mysql_error());
br();
//build xtab query -> this is the query displayed above
$sql="select users.name";
while($row = mysql_fetch_object($mevents)){
$sql.= ", sum(if(ev_type = '$row->id', 1, 0)) as '$row->name' ";
}
$sql.=", count(user) as 'total' ";
$sql.=" from users inner join events on users.id = events.user group by events.user";
br();
echo($sql);
br();
//get and display xtab
$xtab=mysql_query($sql) or die('oops... '.mysql_error());
echo('<table border="1">');
while($dbrow = mysql_fetch_row($xtab)){
print("<tr>");
$col_num = 0;
foreach($dbrow as $key=>$value){
if($dbrow[$col_num] > 0){
print("<td>$dbrow[$col_num]</td>"); //this is where
//it goes wrong...
} else {
print("<td>0</td>");
}
$col_num++;
}
print("</tr>\n");
}
echo('</table>');
?>
Can anybody tell me how to display the user names?
Thanks!
