Multiple Values + Php Help

[Euphoria]

Hi All,

I have a database with the following tables

Table Categories

Id
Category

Table Members

id
cid (linked to the categories table)
Name
Email
Website

The cid field can store multiple values ie (1,3,2,5) depending on what category is selected. My problem is how to display the values in dreamweaver, rather than (1,3,2,5) display it as the category name ie (kitchen, bathroom, design, test) i hope this makes sense.

Many Thanks
Euphoria

[black-dog]

Originally Posted by Euphoria

Hi All,

I have a database with the following tables

Table Categories

Id
Category

Table Members

id
cid (linked to the categories table)
Name
Email
Website

The cid field can store multiple values ie (1,3,2,5) depending on what category is selected. My problem is how to display the values in dreamweaver, rather than (1,3,2,5) display it as the category name ie (kitchen, bathroom, design, test) i hope this makes sense.

For a properly normalised database you'd need an extra table

Table member_categories
id
member_id
category_id

However, let's say you don't want to do that. I've no idea how you'd get Dreamweaver to do it but the php code might go something like this. I'm assuming your category ids are separated by commas.

//get a look up array of categories
$sql="SELECT * FROM Categories";
$result=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_assoc($result)){
$category_lookup[$row['id']]=$row['category'];
}

//get member categories
$sql="SELECT cid FROM Members WHERE id='<insert member id here>'";
$result=mysql_query($sql) or die(mysql_error());
$row=mysql_fetch_assoc($result);
//create an array from comma separated ids
$category_ids=explode(",",$row['cid');

//display category names by looping through array
foreach($category_ids as $value){
echo $category_lookup[$value]."<br>";
}

Untested.

[Euphoria]

Hi Black-dog,

Thanks for you quick reply, i will give that a try in a bit, hopefully it works.

Many Thanks
Euphoria

[Euphoria]

Hi All,

I manage to get it working by adding in the following

$category_list = NULL; // predefine the variable
$categories_array = explode(",", $row_rsDirectory['cid']); // the cid in the database is stored as "1,2,3" etc, use the comma to "explode" it into an array
foreach($categories_array as $category_id){ // loop for each value that is in the array
$cat_query = mysql_query(sprintf("SELECT `category` FROM `categories` WHERE `deleted`=0 AND `id`=%d LIMIT 1", $category_id), $connxxx) or die(mysql_error($connxxx));
$cat_result = mysql_fetch_array($cat_query);
$category_list.= $cat_result[0]." | ";
mysql_free_result($cat_query);
}
$category_list = substr($category_list, 0, (strlen($category_list)-3)); // to remove the trailing | and spaces


Thanks
Euphoria