getting values from loop from SET type.

sijis

I have a 2 tables:

tblGenreList

genre_id
genre_name

Items

item_id
item_name
item_genre (SET type)

I'm trying echo all the types the items has as a genre.
example: Men In Black could be Comedy and Action
so I would like to it to show

Men In Black

Genre: Action; Comedy

but currently i'm just getting

Genre: Action;

Im not sure what's wrong with my code, but i'm sure the problem is here.

$item_id = $_REQUEST['itemID'];

$item = mysql_query("SELECT * from Items WHERE ItemID = '$item_id'", $dbServerConn);
while ($row = mysql_fetch_array($item)) {
	$i_genre=$row['genre'];
}

// my problem is below
$item_genre = mysql_query("SELECT * FROM tblGenreList WHERE genre_id = '$i_genre' ", $dbServerConn);
while($row2 = mysql_fetch_array($item_genre)){
	echo "Genre: " . $row2['genre_name'];
}

thanks for the help.

sarahk

I would have expected 3 tables

tblGenreList

genre_id
genre_name

Items

item_id
item_name

ItemGenre

item_id
genre_id

then you could have a single query

$sql = "select item_name, genre_name from tblGenreList, ItemGenre, Items where Items.item_id = itemGenre.item_id and itemGenre.genre_id = tblGenreList.genre_id";

then loop through and format.

sijis

i think adding that table is unecessary. that's why i'm using the type SET to avoid using an extra table.

sijis

here's the solution:

$item_genre = mysql_query("SELECT * FROM tblGenreList WHERE genre_id in($i_genre) ", $dbServerConn);

a very simple in($string)

ahundiak

What do you mean by "SET type". Is it a comma delimited list of genres? I guess that would work but another table (as sarahk described) would be better. What if you needed to find all items for a couple of genres?

Also:
$i_genre = $row['genre'];
should be
$i_genre = $row['item_genre'];
assuming your table description was percise.

sijis

what i mean by SET type is in the MySQL database the field item_genre in table Items is of "SET type", which means it saves the data in a comma delimited list.

the item_genre field holds the values of the id of the Genre.

tblGenreList

Items

This way I avoid having a table having repetative data. This way I know which item is of what genre or genres.
I could also sort the item by genre by the respective genre_id that is in the Items.item_genre field.