identify comma separated values

jefterwil

I have a database field that contains 3 values separated by commas;

Example: 1,2,3

These values are used to determine mulitple categories in which to place the user places an classifed ad.

I can't figure out how to seperate the three values in order to add them to the relevant category total count for web page display.

I've figured out how to display the total category count if the category field only has one number:

$sql = "SELECT category, count(*) AS cnt FROM classifieds GROUP BY category";
$res = mysql_query($sql);

$auto = 0;
$comp = 0;
$dvd = 0;

if(mysql_num_rows($res) > 0) {

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

  if($r['category'] == "1") {    
       $auto += $r['cnt']; 
  } 
  if($r['category'] == "2") {    
       $comp += $r['cnt']; 
  }
  if($r['category'] == "3") {    
       $dvd += $r['cnt']; 
  }
}

}

Autos(<?=$auto;?>)
Computers(<?=$auto;?>)
DVDs(<?=$dvd;?>)

which displays like this (using arbitrary totals between the parens)

Autos(2)
Computers(0)
DVDs(4)

I decided to let users place an item into multiple categories, hence the category field using comma separated values: 1,2,3

So, to repeat my question, how do I script for the field have "1,2,3" and distinguish each number in order to add them to the total count for each category?

Thanks!

AstroTeg

I hate to say it, but you have a database design problem. You never ever never want to store arrays in a field unless you really really really have a good reason to do so.

Break out that field so you have 2 additional tables.

One table will contain the category IDs and their names.

The other table will contain 2 fields. A category ID and classifieds ID. You link the the categories to the classifieds this way. It may sound like more work, but there's tons of nifty SQL things you can do (including what you're trying to do).

Neumy

You will want to look at Tokenizing -- strtok()

http://ca2.php.net/strtok

AstroTeg

Originally posted by Neumy
You will want to look at Tokenizing -- strtok()

http://ca2.php.net/strtok

I'm going to nicely disagree with you. It'll help, but this is still wrong database design.

Neumy

I agree with your observation on his database - it's messy. But if he needs to work around commas (now or in the future), he'll want to know about strtok.