Can't use GROUP BY

[luiddog]

I have a DB column that stores info with a comma separator. (ie burger, bun, cheese). I have numerous rows many with similar entries in this column. So it looks like:

1- burger, bun, cheese
2- burger, bun
3- burger, bun, cheese, mustard
4- burger, bun, mustard
6- burger, bun, cheese, ketchup
7- burger, bun, ketchup
9- burger, bun, cheese, onions
10- burger, bun, onions, mustard

So I can't just use GROUP BY because the info is different. What I'm trying to get is:

burger
bun
cheese
ketchup
mustard
onions

I know there's a way to do it but I don't know how. Also, I'm really bad with arrays so if anybody recommends that could you be clear about how to do it.

[laserlight]

I have a DB column that stores info with a comma separator. (ie burger, bun, cheese).

That is the source of the problem. Your database is not even in first normal form because you have multi-valued fields. You might as well use a flat file database, e.g., a CSV file since your table is effectively a CSV file, not a table in a relational database.

Read the following PHP Builder articles before you continue:
SQL Theory & How-To
Database Normalization And Design Techniques

Then redesign your database schema such that you can now use GROUP BY properly. In fact, chances are you would not even need to use a GROUP BY at all, since you probably would have a table for all the ingredients.

[luiddog]

That would be ideal but I'm working with a pre-configured DB that I can't change. There must be an array script to do this.

[laserlight]

That would be ideal but I'm working with a pre-configured DB that I can't change.

That's very sad 🙁

There must be an array script to do this.

Yes, but it is generally more inefficient:

$result = $dbh->query('SELECT "column" FROM "table"');
$items = array();
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $row_items = explode(',', $row);
    foreach ($row_items as $row_item) {
        $items[] = trim($row_item);
    }
}
$items = array_unique($items);

Another way to do it that may be more efficient would be:

$result = $dbh->query('SELECT "column" FROM "table"');
$items = array();
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $row_items = explode(',', $row);
    foreach ($row_items as $row_item) {
        $items[trim($row_item)] = true;
    }
}
$items = array_keys($items);

In my examples above I am assuming that the PDO extension is used.

[luiddog]

I looked into that function and it looks useful. However, not to be a pain, but the server doesn't have it installed. I'm trying to get them to install it but isn't there a way to create an array of all the results then only show the unique results?

[luiddog]

here's what I came up with:

mysql_select_db($database_db, $db);
$query_category = "SELECT ingredient FROM users";
$category = mysql_query($query_category, $db) or die(mysql_error());
$row_category = mysql_fetch_assoc($category);
$totalRows_category = mysql_num_rows($category);

do { 
$ecat = explode(",",$row_category['ingredient']);
foreach($ecat as $vcat) {
$cat[] .= $vcat;
}
} while ($row_category = mysql_fetch_assoc($category)); 

$catc = array_unique($cat);
print_r($catc);

But is there a better way. BTW I obviously use dreamweaver

[Piranha]

Can you add tables to the database? If so I would recommend you to add two tables to get the ingredients in another table as well. Then you could update that table automatically once every 5 minutes/15 minutes/1 hour/1 day.

[Edit]
By the way, have you talked with someone about changing the database and script completely? And explained why it should be changed and what problems there are now? That would solve this problem, and many problems in the future as well.
[/edit]

[laserlight]

here's what I came up with:

Actually, if you want to map the code from PDO to the legacy mysql interface, it would be:

mysql_select_db($database_db, $db);
$query = "SELECT ingredient FROM users";
$result = mysql_query($query, $db) or die(mysql_error());
$categories = array();
while ($row = mysql_fetch_assoc($result)) {
    $row_categories = explode(',', $row);
    foreach ($row_categories as $row_category) {
        $categories[] = trim($row_category);
    }
}
$categories = array_unique($categories);
print_r($categories);