display all, group by uniques??

[cwilkey]

I'm not sure if this is a PHP or a mySQL question but here it goes...

I have a table with the following:

location category

300x250 Home Page
300x60 News Page
300x250 Home Page

I want to be able to select(*) but group them by category. When I do that:

SELECT * FROM table GROUP BY category

It doesn't bring back all the records. It only results in:

Home Page
300x250
News Page
300x60

Instead of:

Home Page
300x250
300x250
News Page
300x60

...which is what I'm after.

[NightShift58]

You want to ORDER BY category, location

[cwilkey]

Okay after further investigation I think this needs to be done with PHP. Here is my attempt:

[code]
<?php while ($results = mysql_fetch_assoc($inventory)) { ?>

<optgroup label="<?php echo $results['category']; ?>">
<option value='<?php echo $results['location']; ?>'><?php echo $results['location']; ?></option>

<?php } ?>
[\code]

The result I want is to display group by category name and display that in the <optgroup label> tag and then display the location in the <options> tag.

[NightShift58]

If you GROUP BY category, location, you will only get uniques. However, in your post, you specifically said that you wanted all locations, including duplicates. To do that, you should ORDER BY category, location.

With PHP, you can check for a change in category in your WHILE loop, and if there is one, then echo the <OPTGROUP> label.

[cwilkey]

Here is what I'm trying to no avail:

<select name="location" id="location">
<?php $inventory = getInventory(); while ($results = mysql_fetch_assoc($inventory)) { ?>
<?php if ($results['category'] == $results['category']) { ?>
<optgroup label="<?php echo $results['category']; ?>">
<?php } ?>
<option value='<?php echo $results['location']; ?>'><?php echo $results['location']; ?></option>
<?php } ?>
</select>

[NightShift58]

Do you need to escape PHP/HTML/PHP as extensively as you did? It's a recipe for errors...

Try this logic:<?php
echo '<select name="location" id="location">';
$inventory = getInventory();
$last_cat = "";
while ($results = mysql_fetch_assoc($inventory)) {
if ($results['category'] <> $last_cat) {
$last_cat = $results['category'];
echo "<optgroup label='$last_cat'>";
}
echo '<option value="' . $results['location'] . '">' . $results['location'] . '</option>';
}
echo '</select>';
?>

[cwilkey]

That worked. Thanks.

What's' the deal about escaping PHP so much? Does it really cause issues?

[NightShift58]

It does when reading the code...