[RESOLVED] Help displaying data - userid repeats for each item

[lazykik]

I have been banging my head against the wall for days now trying to figure out how to get this to work. I have 3 tables

Flags
- flag_id
- flag_name

User_Flags
- id
- user_id
- flag_id

Users
- user_id
- username

Since a user can have multiple flags, a new row is inserted into user_flags with the user_id and flag_id.

The SQL statment I use is

$query = "SELECT users.username, const_flags.flag_name
FROM (const_flags INNER JOIN lookup_flags ON const_flags.flag_id = lookup_flags.flag_id)
LEFT JOIN users ON lookup_flags.user_id = users.user_id 
        ORDER BY users.username";

mysql_query($query) or die (error);
$result=mysql_query($query);
?>

This obviously repeats the username next to each flag associated with him. What I want to do is have a row for each user and their respective flags in columns. I realize that I could implode the flags keys into the database giving each user 1 row in the database but I want to avoid that if at all possible.

[Weedpacket]

When you say "a row for each user and their respective flags in columns" I presume you mean for display - because if you mean in the database then that would be silly (you say yourself you want to avoid that).

So the database structure is fine. How you display it is up to you. In this case, since you've already ordered by username, I don't even need to mention that.

Have a variable called $current_username. It starts out containing an empty string.
Roughly speaking, every time you fetch a record from the above query's result set, check the username against $current_username. If they're different, then you've finished one user's row and you're starting the next (If $current_username is blank, then this is the first row and there's no previous row to finish). If they're the same, then you just need to add another flag. Either way, you finish dealing with this record by setting $current_username to the current username.

[lazykik]

That worked well, thank you. I just have one more problem, which I can't even think about how to go about doing. I have table layed out with a column header for each seperate flag name. I would like to put an "X" in the corresponding column of the users flag. I may be going about this the completely wrong way so here is what I have so far. Parden my sloppy coding. 😃

<?php
//connect to db
include ("connectdb.php");

//SQL Query 

<table border="1" cellspacing="0" cellpadding="2">
	<tr>
		<th width="110" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Name</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Nro1</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Nro2</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Nro3</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Sky1</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Sky2</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Nro3</font></th>
		<th width="40" align="center"><font size="3" face="Arial, Helvetica, sans-serif">Samples</font></th>

</tr>
<?
$current_name = '';
$current_flag = '';
while($row=mysql_fetch_array($result)){
	if ($current_name != $row['username']) {

?>
	<tr>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $current_name = $row['username']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
		<td align="center"><font size="2" face="Arial, Helvetica, sans-serif"><? echo $row['flag_name']; ?></font></td>
	</tr>
<?
		}
	}
echo "</table>";

?>

[Weedpacket]

First, learn CSS and you'll have much nicer looking HTML 🙂

So I take it you want an X instead of flag_name? What is in $row['flag_name']? One of "Nro1", "Nro2", ... I'm guessing.

<td><?php if($row['flag_name']=='Nro1') echo "X";?></td>
<td><?php if($row['flag_name']=='Nro2') echo "X";?></td>
<td><?php if($row['flag_name']=='etc.') echo "X";?></td>

[lazykik]

This works great! but only shows one flag per user. I tried adding

if ($current_name != $row['username'] && $current_flag != $row['flag_name']) {

but this only excludes anyone from the table that does not have a flag.

[lazykik]

Anyone able to offer any insight on how to display all the items per user on the same row as the user? Even a hint? 😃

Here is a link to how the data is being displayed atm. Data Results

<?php
//connect to db
include ("connectdb.php");


$query = "SELECT users.username, const_flags.flag_name
FROM (const_flags INNER JOIN lookup_flags ON const_flags.flag_id = lookup_flags.flag_id)
LEFT JOIN users ON lookup_flags.user_id = users.user_id 
        ORDER BY users.username";

mysql_query($query) or die (error);
$result=mysql_query($query);
?>
<table border="1" cellspacing="0" cellpadding="2">
	<tr>
		<th>Name</th>
		<th>Skylance Library (1)</th>
		<th>Skylance Oubliette (2)</th>
		<th>Skylance Laboratory (3)</th>
		<th>Key to the Past (Nro1)</th>
		<th>The Burning Prince (Nro2)</th>
		<th>A Message to the Past (Nro3)</th>
		<th>Samples of Corruption</th>

</tr>
<?
$current_name = '';
while($row=mysql_fetch_array($result)){
	if ($current_name != $row['username']) {

?>
	<tr>
		<td><? echo $current_name = $row['username'];} ?></td>
		<td><? if($row['flag_name']=='Skylance Library (1)') echo "X"; ?></td>
		<td><? if($row['flag_name']=='Skylance Oubliette (2)') echo "X"; ?></td>
		<td><? if($row['flag_name']=='Skylance Laboratory (3)') echo "X";?></td>
		<td><? if($row['flag_name']=='Key to the Past (Nro1)') echo "X";?></td>
		<td><? if($row['flag_name']=='The Burning Prince (Nro2)') echo "X"; ?></td>
		<td><? if($row['flag_name']=='A Message to the Past (Nro3)') echo "X";?></td>
		<td><? if($row['flag_name']=='Samples of Corruption') echo "X"; ?></td>


</tr>
<?

}
echo "</table><br /><br />";

?>

[Weedpacket]

Okay, now my brain is shutting down, here. I think I've got it down without major stuffups (and it took me two hours to notice a couple of minor ones) but it'd be smart to go over it line by line (but then you'd have to do that anyway). I'm assuming three tables with at least the given fields. user_flags contains a record for each flag held by each user. I'm assuming that flags are sorted by their id number. If there is some other sorting that's more appropriate, then of course you'd use that.

user
id | name
---+-----


flags
id | name
---+-----


user_flags
flag_id | user_id
--------+--------

// Get the data
$query = mysql_query('select name from flags order by id');
$flag_names = array();
for($i=mysql_num_rows($query); $i>0; $i--)
{
	$flag_names[] = mysql_result($query,$i);
}

$sql = 'select u.name, f.name
from
	flags f, user_flags uf, user u
where
	u.id = uf.user_id and uf.flag_id=f.id';
$query = mysql_query($sql);

$users = array();
for($i=mysql_num_rows($query); $i>0; $i--)
{
	list($username, $flagname) = mysql_fetch_assoc($query);
	$users[$username][$flagname] = true;
}


// Display it
echo '<table><tr>';
foreach($flag_names as $flag)
	echo '<th>'.$flag.'</th>';
echo '</tr>';

foreach($users as $user=>$flags)
{
	echo '<tr>';
	echo '<td>'.$user.'</td>';
	foreach($flag_names as $flag)
		if(isset($flags[$flag])
			echo '<td>X</td>';
		else
			echo '<td></td>';
	echo '</tr>';
}

echo '</table>';

[lazykik]

Wow thanks so much. I never would have thought of that way to do it. I'm still fairly new to php. Will rewite it based on what you've done. Much appreciated 🙂