How to separate multiple values?

[Darkwoods]

Hey in my category field I got multiple values in the same mysql row

I'm having problem with how to separate them so I can display the name of the category and make a link for each category..

 

the multiple values are something like that 1,2

how can I make it so it would separate after each comma?

 

 

 <?PHP $result = mysql_query("SELECT a.*, b.c_id, b.c_title FROM properties AS a JOIN cat AS b ON a.pr_category = b.c_id",$connect);
	while($row = mysql_fetch_assoc($result))
		{ ?>
            
            <h2><a href="blog-post.php?id="><?php echo $row['pr_title']; ?></a></h2>
            <p class="date">12 July 2011 in <a href="index.php?id<?php echo $row['c_id']; ?>"><?php echo $row['c_title']; ?></a></p>
            <img class="img-large-bg" src="../uploads/images/<?php echo $row['f_img'];  ?>" alt="" title="" />
      	<?PHP 
		}
			?>

[freelance84]

 

explode the $variable at the comma

[silkfire]

Use SUBSTRING_INDEX.

[Darkwoods]

I cant figure this out I have been searching and searching with no luck

[djlee]

That is aweful database design, ive used concated strings instead of using "linker" tables before, but only with very good reason. This just isn't one of them. However based on the code it would appear like silkfire's sugestion would be the best option. You only want to get the category results for the first category (otherwise you'd have multiple's of the same property if a property is in more than one category).

 

so something like this would force it to join the category details only

mysql_query("SELECT a.*, b.c_id, b.c_title FROM properties AS a JOIN cat AS b ON SUBSTRING_INDEX(a.pr_category, ',', 1) = b.c_id",$connect);

[Darkwoods]

hey thanks I know it looks awful that because I am total noob 

 

I'm still having problem with displaying the name of the category please if anybody can help me out with it would be great I have been stuck with this for a while now..

 

<?PHP $result = mysql_query("SELECT a.*, b.c_id, b.c_title FROM properties AS a JOIN cat AS b ON SUBSTRING_INDEX(a.pr_category, ',', 1) = b.c_id",$connect);
	while($row = mysql_fetch_assoc($result))
		{
		$cat_name = $row["c_title"];
		$cat_id = $row["c_id"];
    			$categories = explode(',', $row['pr_category']);
/*test*/
    foreach ($categories as $category) {
        list($cat_id, $cat_name) = explode('=', $category);
        echo '' . $cat_id . ':' . $cat_name . ''; } }

?>