problem referencing tables

[rninja]

I've got the table structure like so:
main info: members
referenced icons:member_icons
linking table: icon_types

What I am trying to do is build a search for my site and I would like to pick out specific icons from the icon_types table. The icons are referenced by numbers. Here is an example which references one icon.

mysql> select members.user,member.webanimal,icon_types.animal_icon from members,icon_types where member.memberID='3' and member.webanimal=icon_types.animalID;

What I want to do is bring up three icons. I am having a hard time getting this to work. Can anyone suggest a method, and maybe provide an example?

Thank you!

Rninja

http://www.rninja.com

 

[TonyGroves]

Sorry, I don't understand. Can you explain the problem a bit better?


-----
ALTER world DROP injustice, ADD peace;

 

[rninja]

Sure.

The data is held in one table as numbers (ids), then the pictures are in another table with picture name and another field for id. I need to get the id's to mesh with the particular search subject, and not randomly.

for instance, maybe table structure would help:

table: data
fields: userid,user,frog,cat,turtle,fish

table2: icon_types
fields: id,animal_type,animal_icon

a basic select string to get the data from "data" is:
Select * from data;
this will bring a result like-
1 samuser 1 2 3 4

a basic select string from "icon_types" is:
1 frog frog.gif
2 cat cat.gif

etc...

What I want to do is have the statement bring up the image names and not the numbers stored in data, but it has to be based on the userid.

Rninja

http://www.rninja.com

 

[TonyGroves]

Your database structure is not clear. What values do the animal fields in the
data table hold? For example, is it the case that the fish field holds either a 4 (the record number for a fish) or a null to indicate whether or not the user has a fish?

If that is the case, then the following should work:
[tt]
SELECT
d.userid,i1.animal_icon,i2.animal_icon,
i3.animal_icon,i4.animal_icon
FROM
data d
LEFT JOIN icon_types i1 ON d.frog=i1.id
LEFT JOIN icon_types i2 ON d.cat=i2.id
LEFT JOIN icon_types i3 ON d.turtle=i3.id
LEFT JOIN icon_types i4 ON d.fish=i4.id
WHERE d.userid=3;
[/tt]

However, it seems you would want to rethink your database structure to eliminate
redundant information. You might consider:[ul]
[li]Either rename the animal fields to animal1, animal2, animal3, animal4, so that the animals are identified by the field values only; or change the field types to boolean (true/false) to indicate whether the user has a particular animal.[/li]
[li]Instead of having a single user record with separate columns for each of his animals, have a separate record for each user/animal occurrence. This would allow more flexibility regarding the number of animals a user could have, and would also be more "relationally correct", making queries simpler.[/li]
[/ul]

-----
ALTER world DROP injustice, ADD peace;