relations

[fazzfarrell]

I have a databse of pictures that are going into a database, they all relate to different section such as 'blue' 'red' green' 'yellow' etc.

 

But some belong to more than one section such as pic03 belongs to 'red' 'green' and yellow etc.

 

So I need to call them in to differnet sections can anyone shine any light on how to do this?

[craygo]

you can store them in a field separated by commas. Then you can run a loop through the different colors.

 

Do you need help with storing the data? Displaying the data?

 

Maybe some code may help.

 

Ray

[fazzfarrell]

thanks, how do you mean run a loop?

[craygo]

Well once you have the values comma separated in the table. When you query the table and retrieve the values you can change the comma values into an array and loop through them to show them.

 

example.

<?php
$pic = "pic03";
$sec = "blue,red,green";
$section = explode(",", $sec);
echo "Pic $pic is in the folowing sections<br />";
foreach($section as $color){
echo $color."<br />";
}
?>

 

now you can call on each color individually also

echo $section[0]; //will display blue

 

Ray

[rhodesa]

or, you can have one table for your pictures (each picture should have a unique id which i'm gonna call pid)

 

then, a table of picture of 'tags' or 'categories'...whatever you want to call them. each entry in this table will have a pid and then the tag. so in your example, there would be 3 entries (one for each color)

 

then you can join those tables in your query and bingo!

[fazzfarrell]

erm, I have it at the moment calling the pictures by eitheir ProdID=red or ProdID=green etc with pulls the pictures onto the page by there main catagory. This works fine, can't I associate each picture with say pic03 ProdID 1,2,4 in the database (relating to the different colours) and then do a sql statement so that when = '1' it wil pick up any picture that has '1' within the prodID of the table?

[craygo]

yes you can but now you are talking about re-structuring your tables. as rhodesa said you can make a table to hold the picture id and each catagory it is associated with.

 

example structure

pic_table

pid = autoincrement unique id

name = pic name

blah blah blah

 

color_table

cid = autoincrement unique id

pid = relates to the picure id

color = color name

 

so now you have pic03 with pid of 3 in the pic_table and pic04 with a pid of 4

 

in the color_table you would have some entries

cid    pid    color

1      3      red

2      3      blue

3      3      green

4      4      blue

5      4      green

 

Now you query the color_table to get your matches

 

$sql = "SELECT `pic_table`.`name` AS `picname` FROM `pic_table` JOIN `color_table` ON `pic_table`.`pid` = `color_table`.`pid` WHERE `color_table`.`color` = 'red'";

 

the above query would return pic03 only bacause pic04 doesn't have an entry for red

 

Ray

 

 

[rhodesa]

what he said....except in your query, you should do a left join or add a GROUP BY pid at the end. if you had searched for records with the color blue or green, you would get duplicate records back

[fazzfarrell]

quality, thats what I need, just created a quick test and seems to work fine! thanks for your help, both of you have a good weekend!