Rhyme / Word List Retriever

ideaessence · 05-15-2010, 04:24 PM

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.

Fou-Lu · 05-15-2010, 06:17 PM

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.

ideaessence · 05-15-2010, 08:31 PM

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.

Fou-Lu · 05-16-2010, 01:59 AM

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.

ideaessence · 05-17-2010, 02:27 AM

Thanks for replying.

Quote:

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.

Fou-Lu · 05-17-2010, 03:42 PM

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.

ideaessence · 05-18-2010, 03:42 AM

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!