Jump to content

Don't know where to start? help


ausmedia

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.