query loop question

[markal3x]

I'm new to this forum.  I've looked the posts in this section and haven't found an answer to my question (at least one that I can understand).  Thanks in advance for taking the time to read this.

 

Say i have a database table called 'FOOD' with two columns  'NAME' and 'TYPE' and a list of corresponding entries that looks something like this:

 

Apple    |  Fruit

Orange  |  Fruit

Pear     | Fruit

Carrot   | Vegetable

Spinach |  Vegetable

 

etc.

 

If I wanted to generate a list of these items sorted by type,

 

e.g.

FRUIT

-apple

-orange

-pear

 

VEGETABLE

-carrot

-spinach

 

Do I have to query the database twice to create two records sets for each type or is there a way to query the database once for all the records and then use php to sort the records by type and generate lists under appropriate headings.

 

Thanks (forgive the lengthy explanation.)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Wolphie]

Just one query.

 

<?php
$query = "SELECT `name`, `type` FROM `db_name` . `tbl_name` ORDER BY `type`";
$query = mysql_query($query, $db_con_id) or die('<b>Error:</b> ' . mysql_error());
while($row = mysql_fetch_array($query)) {
  echo $row['name'];
  echo '<br />';
  echo $row['type'];
}
?>

[markal3x]

Thank you very much for your response!

 

I'll give this a shot.  On first glance though, this looks like it might generate a list in this format:

 

apple

fruit

orange

fruit

pear

fruit

etc...

 

instead of

Fruit

-apple

-orange

-pear

 

but, i guess the output of that is more of an array question than a mysql question now isn't it.

[Xurion]

<?php
$query = mysql_query("SELECT name FROM db  WHERE type='fruit' ORDER BY name");
echo $row['type'];
while($row = mysql_fetch_array($query)) {
  echo '-'.$row['name'].'<br />';
}
echo '<br />;'
$query = mysql_query("SELECT name FROM db  WHERE type='vegetable' ORDER BY name");
echo $row['type'];
while($row = mysql_fetch_array($query)) {
  echo '-'.$row['name'].'<br />';
}
?>

 

That should do it.

[markal3x]

Thanks for your response.

 

I Know it's possible to do it with multiple queries, but unlike in my example, my real application deals with lists of 500 or more divided into 20 or more categories.  Making that many queries (20+) on a single page seems excessive. I was hoping  there would be a better way of doing it...

 

I have an inkling the solution lies in how a multidimensional array is created by the query and looped in some magical way...

[moselkady]

 

We can try this

 

<?php
$query = "SELECT `name`, `type` FROM `db_name` . `tbl_name` ORDER BY `type`";
$query = mysql_query($query, $db_con_id) or die('<b>Error:</b> ' . mysql_error());
$type = '';
while($row = mysql_fetch_array($query)) 
{
  if ($row['type'] != $type)
  {
     $type = strtoupper($row['type']);
     echo "<br/>$type<br/>";
  }
  echo "- {$row['name']}<br/>";
}
?>

 

 

[markal3x]

I'll give it a shot,

thanks

[Xurion]

Easier with an extra ORDER BY parameter at the end of the mysql statement:

 

<?php
$query = "SELECT name, type FROM tbl ORDER BY type, name";
$query = mysql_query($query);
while($row = mysql_fetch_array($query))
{
  if ($row['type'] != $type)
  {
    $type = strtoupper($row['type']);
    echo "<br />$type<br />";
  }
  echo "- $row['name']<br />";
}
?>

 

This will arrange them in the right order.