Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    33
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Rhyme / Word List Retriever

    Hi. I'm looking to write a simple rhyme retriever. Consider a database with 2 columns of data that look like this.

    --------------------

    Column 1:

    entropy
    tend to freeze
    memory
    melody
    enthalpy
    tenably

    Column 2:

    not a clue
    molecule
    obstacle


    --------------------

    Now, on the web page itself, there is an input field next to a submit button. My goal is to make it so that when a word or phrase is typed, and it exists in one of the columns, the entire column of entries is displayed.

    I realize rhymezone.com has something somewhat similar, but it does not do phrases and would never include something like "entropy" if "memory" was given as a string of input.

    Would this be a big project or just a few simple functions? What functions am I looking at to do this?

    Thanks.
    Last edited by ideaessence; 05-15-2010 at 05:26 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This is a structural issue, this table needs 3NF to prevent anomalies.
    2 tables are needed to flatten a many to many on a self related table. One table contains the word while one table uses a composite key of two words, one to search and one to match. Then a simple left join can get these. This is also more efficient than a full text search, and is far more easy to expand. I can't write any code in my ps3 browser, but I know either Old pedant or guelphpdad can hook you up with the structure and query.
    Php actually has a sound compare (metaphone and its siblings), but they are horribly expensive to use.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    ideaessence (05-17-2010)

  • #3
    New Coder
    Join Date
    Jan 2010
    Posts
    33
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I was hoping for something that is not smart at all (PHP metaphone has to do with an algorithm that recognizes similar sounding words). Rather, I am hoping for something that merely prints a list of strings to the screen if I enter a string that is part of that list. Is that realistic? The application would just be for use by me and possibly a couple friends. I'm not aiming to go big and compete with other sites or anything like that.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yep, perfect sense. I think the SQL route is the best choice. Using the normalized design will allow quick index searches, the query will only differ in that a join is performed returning multiple records instead of a contains returning one record. This will allow much more flexibility to add, remove and link terms instead of having to structurally modify the table to add more (thus eliminating anomalies). I'd show you right now but the virtual keyboard is brutal for coding.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    ideaessence (05-17-2010)

  • #5
    New Coder
    Join Date
    Jan 2010
    Posts
    33
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for replying.

    I'd show you right now but the virtual keyboard is brutal for coding.
    If you could show me when you can get to a regular keyboard, that would be great. I'm not sure how I should go about this. So far, I've gathered that the PHP join() function can be used to achieve what I'm looking to do. If you have time for a short example, I think I might be able to catch on and begin creating it.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You betcha. Man I miss my keyboard :P
    Not a PHP join (join in PHP is an alias of implode which lets you combine a string into an array). We want an SQL join.
    This is essentially your tables:
    Code:
    +------------+         +----------------+
    | Phrase     |         | PhrasePair     |
    +------------++o-----o<+----------------+
    | phrase [PK]|         | pair1 [PK][FK] |
    +------------+         | pair2 [PK][FK] |
                           +----------------+
    Pair1 and Pair2 are both belonging to phrase. Now, how you handle the two is kinda where you make a choice, and effect the query used (will use an OR in the query, or not). Personally, I'm a fan directional control, so I will build it that way. That is, each phrase is associated with a Rhyme, and you can only search for phrase not the ryhme. So, if the table had memory linked with melody, but not melody linked with memory, it would return melody when searching for memory, but would not return memory when searching for melody. This creates more data, but once again adds a level of flexibility.

    To create these:
    Code:
    CREATE TABLE Phrase
    (
        phrase varchar(255) NOT NULL PRIMARY KEY
    ) ENGINE = INNODB;
    
    CREATE TABLE PhrasePair
    (
        phrase varchar(255) NOT NULL,
        rhyme varchar(255) NOT NULL,
        PRIMARY KEY(phrase, rhyme),
        FOREIGN KEY (phrase) REFERENCES Phrase(phrase) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (rhyme) REFERENCES Phrase(phrase) ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE = INNODB;
    I believe that the composite PK on the phrase, rhyme will get around the complaints of non-indexed fields. If it does not (I'm not 100% sure how the indexing of composite keys come into play when its foreign key referenced on individual fields), add this on the PhrasePair table between the PRIMARY KEY(phrase, rhyme) and FOREIGN KEY (phrase)....:
    Code:
    INDEX(phrase),
    INDEX(rhyme),
    But I'm pretty sure you don't need it.

    Querying:
    Code:
    SELECT p.phrase, pp.rhyme
    FROM Phrase p
    LEFT JOIN PhrasePair USING (phrase)
    WHERE p.phrase = 'memory'
    Say our tables have the following records (these are each records, not a text block):
    Code:
    // Phrase
    memory
    melody
    tenably
    
    // PhrasePair
    memory | melody
    memory | tenably
    melody  | tenably
    tenably | melody
    tenably | memory
    The above query (using memory) would return:
    Code:
    memory | melody
    memory | tenably
    And notice the recordset for PhrasePair doesn't include a link between melody and memory, so if you queried melody you would only have one record with melody and tenably returned.

    Cascades were added, so if you choose to delete or update a record from Phrase, all associated entries within the PhrasePair will also be updated / deleted. This should update / destroy the entire record even if only one is updated / removed.
    Last edited by Fou-Lu; 05-17-2010 at 04:45 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    ideaessence (05-18-2010)

  • #7
    New Coder
    Join Date
    Jan 2010
    Posts
    33
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I'm not very experienced with this but it looks like I should be able to get this working if I play around with it. Thanks so much!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •