1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[Q] Simple Table Search in MySql

Discussion in 'PHP & Perl' started by auraita, Mar 6, 2015.

  1. auraita

    auraita Regular Member

    Joined:
    Dec 30, 2013
    Messages:
    283
    Likes Received:
    280
    Hi everybody,

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

    data_pic_1.png

    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..
     
    Last edited: Mar 6, 2015
  2. vayacondios

    vayacondios Newbie

    Joined:
    Mar 3, 2015
    Messages:
    30
    Likes Received:
    5
    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}')
     
    • Thanks Thanks x 1
    Last edited: Mar 7, 2015
  3. auraita

    auraita Regular Member

    Joined:
    Dec 30, 2013
    Messages:
    283
    Likes Received:
    280
    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.
     
  4. vayacondios

    vayacondios Newbie

    Joined:
    Mar 3, 2015
    Messages:
    30
    Likes Received:
    5
    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?