ausmedia Posted October 4, 2006 Share Posted October 4, 2006 Hi, i have a database with a table that records all the types of stones, eg.ID Size Shape1 1.1 round2 1.2 square3 1.3 round4 2 round5 2.1 square6 2.5 round7 3.7 square8 3.9 square9 4.1 round10 4.9 square11 5 roundWhat i MUST do is sort it into individual shape, also for every size's range i must display a sub-heading like..ROUND SHAPE - 1.0 to 1.9{display all stones within 1.0 to 1.9 which is round}ROUND SHAPE - 2.0 to 2.9{display all stones within 2.0 to 2.9 which is round}ROUND SHAPE - 3.0 to 3.9{display all stones within 3.0 to 3.9 which is round}ROUND SHAPE - 4.0 to 4.9{display all stones within 3.0 to 3.9 which is round}ROUND SHAPE - 5.0 to 5.9{display all stones within 5.0 to 5.9 which is round}SQUARE SHAPE - 1.0 to 1.9{display all stones within 1.0 to 1.9 which is Square}SQUARE SHAPE - 2.0 to 2.9{display all stones within 2.0 to 2.9 which is Square}SQUARE SHAPE - 3.0 to 3.9{display all stones within 3.0 to 3.9 which is Square}SQUARE SHAPE - 4.0 to 4.9{display all stones within 4.0 to 4.9 which is Square}SQUARE SHAPE - 5.0 to 5.9{display all stones within 5.0 to 5.9 which is Square}to create multiple sql statement then displaying them in a loop seems to be a messy task, could someone please give me a hint on how to write a good sql statement to sort this table out in an organized way.Thank you. Quote Link to comment Share on other sites More sharing options...
obsidian Posted October 4, 2006 Share Posted October 4, 2006 well, it sounds like you've got a good handle on what you're after, and since you're pulling ALL your table info anyway, I would just recommend you do something like this:[code]<?php// pull all your info and throw it all into a multi-dimensional array// notice that i'm ordering by shape and size so that i know how to group everything// with that sort of ordering, i'll set everything into their own array for display$res = array( "round" => array(), "square" => array());$sql = mysql_query("SELECT * FROM stoneTable ORDER BY shape ASC, size ASC");while ($row = mysql_fetch_array($sql)) { $shape = strtolower($row['shape']); $size = floor($row['size']); // this grabs all the range you're after $res[$shape][$size][] = $row;}// now, you have everything grouped for display, and you can display them all:foreach ($res as $shape => $group) { echo "<div>\n"; foreach ($group as $size => $records) { echo "<h2>" . ucwords($shape) . " shape - {$size}.0 to {$size}.9</h2>\n"; foreach ($records as $row) { // display your rows here: echo "$row[size] - $row[shape]<br />\n"; } } echo "</div>\n";}?>[/code]does that make sense? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.