Display group values in array

[viion]

I have a piece of coding like this:

$itemtrack = mysql_query("SELECT DISTINCT * FROM ". ITEMTRACK_DATABASE ." GROUP BY ". ITEMTRACK_DATABASE_ITEM ." ORDER BY ". ITEMTRACK_DATABASE_ITEM ." ASC"); 

 

This will work fine but when outputting the information, it'll only display for example the first value of the group.

So if table looks similar to:

 

id1 - John - item1

id2 - John - Item1

id2 - Joe - Item 1

 

When grouping all item 1, outputting the mysql_Fetch_Array on it will just output John. How do I make it list both John/Joe in some sort of array?

[PHP Monkeh]

In this case you probably don't want GROUP BY, but rather a WHERE clause.  Try using this:

 

$itemtrack = mysql_query("SELECT DISTINCT * FROM ". ITEMTRACK_DATABASE ." WHERE item1 = '". ITEMTRACK_DATABASE_ITEM ."' ORDER BY ". ITEMTRACK_DATABASE_ITEM ." ASC"); 

 

[viion]

The thing is item1 could be anything, it's pulled from the database itself. I also didn't want it to list item1 multiple times when multiple people have it, i'd rather it group then output the names individually for that specific item.

 

I actually figured out how to do it, it's probably a bad method, but i don't know it works fine and I couldn't find a better Approach.

 

I did a cross-referencing method, so it'd pull the item from the database, then as it's rendering out the information for that item it would cross reference it to pull all the names from the database that the where pulleditem=item in where clause. This would then group the names individually.

 

The code is below: Also the output, the only issue I have now is trying to find a way to count the number of times a specific name is found within the same while as cross-referencing. But it's hard because that's already truncated the columns by the group.

function itemlist_group() {
// Pull Item Information
$itemtrack = mysql_query("SELECT DISTINCT * FROM ". ITEMTRACK_DATABASE ." GROUP BY ". ITEMTRACK_DATABASE_ITEM ." ORDER BY ". ITEMTRACK_DATABASE_ITEM ." ASC"); 
if (!$itemtrack) { echo("<div id='error'><img src='img/icons/icon_cross.png'>Item Track List could not be loaded: " . mysql_error() . "</div>"); } 
$itemtrack_total = mysql_num_rows($itemtrack);

echo "<div id='itemlist'>";
echo "<strong><span id='item'><strong><span id='itemid'>ID</span> ITEM</strong></span><span id='char'><strong>CHARACTERS</strong></span></strong><span id='date'><strong>DATE</strong></span><span id='type'><strong>TYPE</strong></span><span id='delete'><strong><em>Cannot Delete Groups</em></strong></span>";
echo "<div style='clear:both'></div>";
echo "</div>";
echo "<div id='itemlist'>";
while ($itemlist = mysql_fetch_array($itemtrack)) { 
	$itemID = $itemlist[iTEMTRACK_DATABASE_ID];
	$itemname = $itemlist[iTEMTRACK_DATABASE_ITEM];
	$itemchar = $itemlist[iTEMTRACK_DATABASE_CHARACTER];
	$itemdate = $itemlist[iTEMTRACK_DATABASE_DATE];
	$itemtype = $itemlist[iTEMTRACK_DATABASE_TYPE];
	$itemtrack_group = mysql_query("SELECT DISTINCT COUNT(*) FROM ". ITEMTRACK_DATABASE ." WHERE ". ITEMTRACK_DATABASE_ITEM ." LIKE '$itemname%'");
	$itemtrack_print = mysql_fetch_row($itemtrack_group);
	// List Items
	echo "<span id='item'><span id='itemid'>$itemID</span>$itemname (<span id='en-green2'>$itemtrack_print[0]</span>)</span><span id='char'>";
	// Pring Results
	$ONcharacter = "";
	$itemtrack_check = mysql_query("SELECT ". ITEMTRACK_DATABASE_CHARACTER .", ". ITEMTRACK_DATABASE_ITEM ." FROM ". ITEMTRACK_DATABASE ." WHERE ". ITEMTRACK_DATABASE_ITEM ."='$itemname' GROUP BY ". ITEMTRACK_DATABASE_CHARACTER ."");
	while ($checklist = mysql_fetch_array($itemtrack_check)) { 
		$charcheck = $checklist[iTEMTRACK_DATABASE_CHARACTER];
		$itemcheck = $checklist[iTEMTRACK_DATABASE_ITEM];
		$ONcharacter .= "$charcheck ";		
	}
	echo "$ONcharacter";
	echo "</span><span id='date'>$itemdate</span><span id='type'>$itemtype</span>";
	echo "<div style='clear:both'></div>";
}
echo "</div>";
}

 

Result:

 

Hope it helps someone.

 

PS. If anyone confused why output says like test (6) but shows 4 names, this is because 1 of the names has 3 of the item "test" and it's grouped them names together rather than showing the same name 3 times. This is why i'd like to find out how many items they are per name found so it can say Name (x1) AnotherName (x3) and so fourth.

[fenway]

TLDR... does the query produce the desired output?

 

Also, "DISTINCT *" is meaningless.