Jump to content

Archived

This topic is now archived and is closed to further replies.

ausmedia

Don't know where to start? help

Recommended Posts

Hi, i have a database with a table that records all the types of stones, eg.

ID    Size    Shape

1      1.1      round
2      1.2      square
3      1.3      round
4      2        round
5      2.1      square
6      2.5      round
7      3.7      square
8      3.9      square
9      4.1      round
10      4.9      square
11      5        round

What 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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.