[SOLVED] key word field

[anatak]

How would you put a keyword column to a table with pictures ?

 

What I want to do is to have a column in the picture table where I would store keyword related to the picture.

for example a picture about a honda cbr 600 bike would get the keywords bike motorcycle motorbike honda cbr

 

the aim is to be able to select only the pictures with a given keyword in the keyword column.

to make it a little bit more difficult (maybe) this also has to work with multiple languages for example honda cbr 600 would get keywords

bike motorcycle honda in english and Japanese (multibyte)

 

Anyone any ideas or experience with something like this ?

 

thanks

anatak

[Mchl]

Make separate table called 'keywords' to store your... keywords and then another one 'keywords_pictures' that will relate keywords to pictures.

 

This is an example of many-to-many relation (one picture can have many keywords, and one keyword can be assigned to many pictures)

[anatak]

how would you take the input for the keywords for pictures ?

say a user uploads a snowboard picture. He wants to assign the keywords

winter snow snowboard holiday to the picture

Do you have a number of input fields or would you have 1 input field and separate all the words with a string function ? I guess m_byte string functions will have to be used.

 

Isn't a search going to be very slow ? searching for a picture about snowboard first have to find the snowboard reference in the keyboard table after that search the keyboard_pictures table and finally get the pictures from the pictures table with the right reference

 

 

 

[Mchl]

Yes. One input field, and then use explode to get an array of keywords.

 

No. The search will not be slow (compared to design, where all keywords are stored in one table alongside pictures), provided that you create indexes and use proper queries.

[anatak]

I have a question about indexes. If you use primary keys do you still need to create indexes ?

if yes how do you create an index ? is it something that is part of the table structure or do you need to create indexes regularly with a cron job or something similar ?

 

Thanks for all the help till now.

it is really appreciated.

 

of topic

I see in your signature that you use PostgreSQL. Is PostgreSQL similar to MySQL in php support ?

 

anatak

[Mchl]

PostgresSQL is in my imput queue - which means I'm going to spend some time learning about it soon But as far as I know, PHP interaction is very similar. See pgsql

 

As for the indexes, yes you (usually) need them even if you use primary keys. If you have phpmyadmin or similar tool it is really easy to create. And creating them is a one_time thing done either during creating a table, or with ALTER TABLE syntax.

Basically you should create indexes on fields that are appearing in WHERE part of your queries. Check mysql manual for some more information about it. 7.4. Optimizing Database Structure

[anatak]

ok thanks

I thought that a primary key was an index.

I learned SQL a long time ago on Oracle (don't remember wich version) and I think that oracle created indexes when you created a table with a primary key automatically.

 

thanks a lot

I ll close topic now

 

anatak

[Mchl]

Primary key is also an index. Just a special one (in fact if I remember correctly, primary key is part of all other indexes when InnoDB is used... although I can't remember well... I think I've read something like that on some blog)