I just realized I do not think there is any php book that really explains how to do this. How can I sort the mysql results on a page but categorize them by groups and use html inbetween them? For example, if the mysql DB table contained items, group_name, type, how can I display it such as:

(html and text here echos group name) Group 1
item A
item B
item C

(html and text here echos group name) Group 2
item A
item B
item C

I had a bud do something like this a few years ago and he used a ton of while statements and the page took about 45 seconds to load. When I look at other scripts they use two queries and one displays the group name and the other selects all of the items in alphabetical order that belong in that group. Could anyone provide me with a working example on how to do this? I actually managed to get close to getting this to work but the problem is that the html part is echoed on every line within the while loop and not just when it is the start of a new group. Thanks!

Problem description is slightly vague, but here's how I understand it:


$res = mysql_query( "SELECT * FROM table ORDER BY groupcol", DB );
$last_group = null;
while( $row = mysql_fetch_assoc( $res ) ){
if( $row['groupcol'] != $last_group ){
echo "<h1>New group</h1>";
$last_group = $row['groupcol'];
}

var_dump( $row );
}


(adjust query, groupcol, etc)

HTH
Alex

Thanks that was a fast reply :) Not sure what DB means in your query though I will try to integrate what you wrote into my script though.

second arg to MySQL query == database connection handle.

Good luck!
Alex

A lot easier than I thought thanks a lot :D

Sorry one last question. Is it possible to group two different types into one group? Because this is an online game, one "weapons" have types such as "WEAPON" and "CURSE". Can I place both WEAPONS and CURSE in the same Weapons group? I did use a switch but it displays two separate groups named weapons then since it is ordered by type DESC.

You'll need to do some preprocessing in this case, or adjust your query. Fundamentally though, as constructive criticism, these kinds of patterns are generally attributed to a lack of proper normalization at the database level.

But to patch things up, if I understand still..you want items with groupcol of "CURSE" and "WEAPON" to be in the same group? You can do something like this then, barring any memory constraints:



/**
* Create key associations
*/
function equiv( $k ){
if( $k == "CURSE" )
return "WEAPON";
return $k;
}

$res = mysql_query( "SELECT * FROM table", DB );
$dat = array();
while( $row = mysql_fetch_assoc( $res ) ){
if( !isset( $dat[equiv($row['groupcol'])] ) )
$dat[equiv($row['groupcol'])] = array();

array_push(equiv($row['groupcol']), $row );
}

// you can sub-sort each member of $dat[] if you want here

foreach( array_keys( $dat ) as $group ){
echo "<h1>$group</h1>";

foreach( $dat[$group] as $row ){
var_dump( $row );
}
}



Untested, but should be fine, or close to fine ;)

Ok thanks and sorry for the delay in replying for some reason WHT stops sending me email notifications after a certain amount of posts or something. Hmm you bring up a good point perhaps I can modify the script and create a new "group" field or something and run a query to select all items within that group after I assign all of those items to that group. I will try that thanks again :)

oops double post by accident.

(oops meant to create a new topic)