[Q] Simple Table Search in MySql

Hi everybody,

I want to create a simple site that search words in database. (actually a dictionary site ).



as i tried to draw in paint, : ) the user will search a word and results.php will search that word in database and will show. but as you know one word may has meaning.at this point should i create different column for each different mean or if i put them all in same row and separate with "," and result.php when see "," can replace it with number ?
Example ;
In database :
lang 1 : tool
lang 2 : strumento, arnese, attrezzo

result page :
Tool : 1. strumento 2. arnese 3. attrezzo


how can i create a script like that ? or is there any dictionary script in php like this ?
I was searching since 5 days a script but i couldn't find anything like that and what i found was only glossary..

thank you.

Edit : Or maybe i should 2 differents database for each language..

MySQL is not the best kind of db for this type of app & there are multiple ways to do this, here is one way:

Keywords Table
id - PK
keyword
language

Synonyms Table
kw_1_id
kw_2_id

select id, keyword, language from Keywords where id in (select a.id, b.kw_2_id from Keywords a
inner join Synonyms b ON a.id = b.kw_1_id
where a.keyword like '{search term}')

thank you for your reply.

but what do you suggest me then ? should i go with asp ?

because i'm currently creating database. (scraping words with means..) so i didn't start to create script.

thank you again.

I have to apologize, I wrote the original reply from my tablet and was unable to truly test my SQL. It is wrong. Again, my apologies.

The following SQL will get you the original keyword and the keywords marked as synonyms, as well as their language. You could ORDER BY language.
Here is the proper SQL:

SELECT [id], [keyword], [language] FROM [Keywords]
WHERE [id] IN
(SELECT [kw_2_id] FROM [Synonyms]
WHERE [kw_1_id] IN
(SELECT id FROM [Keywords]
WHERE [keyword] LIKE 'tool'))
OR [id] = (SELECT id FROM [Keywords]
WHERE [keyword] LIKE 'tool')

Your use of PHP or ASP does not really matter. Using a non-relational database like MongoDb is where you could improve things. Neo4J is probably the best one for this type of data querying (graphing database), but it is costly, even for cloud hosting. MongoHQ is reasonable for MongoDb hosting and OrientDb (I have not used it, but have heard it is good) can be hosted cost effectively.

I would stick with what you know and are used to using.

Did you want to have weight on these synonyms? Meaning some are MORE closely related than others?