Neverside

Ordering results [ MySQL ]

I need a little help with the ORDER BY and GROUP BY clauses, I'm somewhat confused.

This is my db structure for a category table for wallpapers:

fn_walls_cats

id | parent_id | nombre
-----------------------------
1 | 0 | Videogames
2 | 0 | Anime
3 | 0 | Other
4 | 1 | Mario
5 | 1 | Megaman
6 | 1 | Zelda
7 | 2 | Dragon Ball
8 | 2 | Inuyasha
9 | 3 | Abstract

And so on..

The structure is made in a way so that the cat. id of the main cats is the parent_id of fields with a parent_id other than 0.

What I want to do is to be able to display that information in a dropdown menu like this:

-- Videogames --
| Mario
| Megaman
| Zelda

-- Anime --
| Dragon Ball
| Inuyasha

-- Other
| Abstract

This is my query and code:

PHP:

<?php

$re = mysql_query("
            SELECT
                id,
                nombre,
                parent_id
            FROM
                fn_walls_cats
            ORDER BY
                parent_id
                ") or die(mysql_error());

echo '<select name="cat_id">';

while($r = mysql_fetch_array($re)) {

    $parent_id = $r['parent_id'];
    $cat_id = $r['id'];
    $cat_name = $r['nombre'];

    $cat_name = $parent_id == 0 ? '-- '.$cat_name.' --' : '| '.$cat_name;

    echo '<option value="'.$cat_id.'_'.$parent_id.'">'.$cat_name.'</option>'."\n";
}

echo '</select> <br />';

?>

The "_" in between $parent_id and $cat_id will allow me to know the difference between one an other by exploding the string.

But that displays results like this:

PHP:

<?php

<select name="cat_id">
<option value="1_0">-- Videojuegos --</option>
<option value="2_0">-- Anime --</option>
<option value="3_0">-- Misc/Otros --</option>
<option value="4_1">| Consolas</option>
<option value="5_1">| Megaman</option>
<option value="7_1">| Nintendo</option>
<option value="8_1">| Mortal Kombat</option>
<option value="9_1">| Soul Calibur</option>
<option value="10_1">| Final Fantasy</option>
<option value="11_1">| Otros</option>
<option value="14_1">| The Legend of Zelda</option>
<option value="6_2">| Saint Seiya</option>
<option value="12_2">| Inuyasha</option>
<option value="13_2">| Otros</option>
</select> <br />

?>

Is there a way to order it the way I typed above?

Thanks in advance

___________________

"¿Por qué buscais la felicidad, oh, mortales, fuera de vosotros mismos?"
~Boecio

Last edited by Alekz, October 2nd, 2005 09:18 AM (Edited 2 times)

Try this:

PHP:

<?php

function category_option($category) {
  $title = $category['parent_id'] == 0 ? "---{$category['name']}---" : "| {$category['name']}";
  return '<option value="' . $category['id'] . '_' . $category['parent_id'] . '">' . $title . "</option>\n";
}

$categories = array();
while ($category = mysql_fetch_assoc($re)) {
  if ($category['parent_id'] == 0) {
    $categories[$category['id']] = $category;
  } else {
    $categories[$category['parent_id']]['children'][] = $category;
  }
}

foreach ($categories as $category) {
  echo category_option($category);
  if (!empty($category['children'])) {
    foreach ($category['children'] as $subcategory) {
      echo category_option($subcategory);
    }
  }
}

?>

Works perfectly! Thanks a lot Rad, you're awesome!

___________________

"¿Por qué buscais la felicidad, oh, mortales, fuera de vosotros mismos?"
~Boecio

Originally posted by Alekz:

I need a little help with the ORDER BY and GROUP BY clauses, I'm somewhat confused.

... GROUP BY can only be used when running a query that uses SQL aggregate functions (mathematical operators) such as MAX, MIN, AVG, SUM, COUNT etc. For example:

Table Stores

Location | Sales
Chicago 1204
New York 8897
Chicago 7894
LA 1245
Cleveland 780

"SELECT Location, SUM(Sales) FROM Stores ORDER BY Location GROUP BY Location";

... Would output:

Chicago 9098
Cleveland 780
LA 1245
New York 8897

Hope that helps ...

___________________

sixminutes.ca

Originally posted by gregor:

... GROUP BY can only be used when running a query that uses SQL aggregate functions (mathematical operators) such as MAX, MIN, AVG, SUM, COUNT etc. For example:

Actually you can use GROUP BY in a normal query. Example:

Code:

SELECT * FROM table GROUP BY column

Sorry to hijack the thread, but you've solved a problem, now I have a similar one!

I have the following table:
id | article_title | date | body
..........................
1 | this is an article |3rd october 2005 | blah blah content
2 | this is our 2nd article! | 5th october 2005 | content2

Basically, I have this since like, April 2004. And I want to display it like this:

<b>April 2004</b>
An article from april 2004
an article from april 2004

<b>May 2004</b>
Article from may 2004

Etc etc. The dates are stored as 3rd October 2004 (that format).
Will Group By do this? Or how else can I do it?

___________________

Its always best to store dates as a timestamp of sometype rather than a formatted string like you have. That way you can do selects on it much easier and still format the date any way you like on display.

___________________

Neverside Development Director
PHP Snippets
BigToach.com - IT WORKS, TOACHY!