Linking words in text to database in realtime

Anon

Hi everyone!

I would like to do the following and have no idea how to do it:
I have one table (dictionary) which holds words and their explanations and I have another table (article) which has a bunch of long texts in it.
When a visitor views one of the texts from article, all the words contained in the dictionary table and in the article should be highlighted in the article (so if he wants to know the meaning of one of the words, he can click on it and get an explanation page).

I don't want the texts themselves to be changed in the database and the texts may be changed from time to time.

What possibilities do I have to do this? It would be interesting to hear some different approaches and to know their advantages and disadvantages.

Thanks a lot in advance,
Goeran

Anon

What I would recommend is you add another column to your table to store the modified text. Have an original_text column, and a linked_text column. Set up a cron job to run every so often that checks for original_text with no linked_text set up. Load up the original text, break it up into words by using explode(). Search for each word in the dictionary. If it exists, then add the link in around the word. Then save this new text to the linked_text column.

You don't want to do this for each page request because it would take too much time. If a linked_text doesn't exist (is null) then just show the original_text column.

You could even have your cron job go through all of the entries, in case one was edited, or have an edited column that it searches for.

If you don't know much about cron, do a search for it on here or www.devshed.com, it's been discussed many times...

Anybody else got a way to do it?

---John Holmes...

Anon

I think you're right -- performance is going to be a problem. Because there are limits on the number of branches in a regular expression, you're going to need to iterate across your word list rather than building one monster search string and calling eregi_replace one time. It's going to be expensive in terms of CPU time -- searching and replacing requires that lots of data get moved around in memory. I can imagine some perverse ways of dealing with this in C, but it does seem that preprocessing the entire database is the smart way to go in PHP.

Anon

Hi Steve,

oops, I think I missed something. What are you are going to do with eregi_replace after you applied explode() on the original text?

Thanks, Goeran

Anon

Actually, John is the one who mentioned explode().

But the idea would be: explode() the string into an array of discrete words. For each array element, apply one of the obvious string-replacement functions. Actually, str_replace() could do the job.

Then implode it all back into a string.

It might be a great idea, depending on how PHP stores array elements internally -- it might avoid moving around a lot of data, which definitely would be a problem if you did search/replace on the entire file (or big text string, if you're pulling this out of a database).

Or not. I just don't know that much about how PHP represents arrays internally.

Anon

$link_id = mysql_connect("host","user","pass");
$db = mysql_select_db("database");
$final_text = array();
$result = mysql_query("SELECT id,original_text FROM your_table WHERE linked_text = NULL OR edited = TRUE");
while (link($id,$otext) = mysql_fetch_row($result))
{
$words = explode(" ",$otext);
for($count=0;$count<count($words);$count++)
{
//remove extra punctuation
$word = ereg_replace("[,.;:\"?!)]","",$words[$count]);
$result2 = mysql_query("SELECT def_page FROM dictionary WHERE lookup_word = '$word'");
if (list($def_page) = mysql_fetch_row($result2))
{
$final_text[$count] = "<a href=\"dictionary/$def_page\">" . $words[$count] . "</a>";
}
else
{
$final_text[$count] = $words[$count];
}
}
$linked_text = implode(" ",$final_text);
$linked_text = addslashes($linked_text);
$result = mysql_query("UPDATE your_table SET linked_text = '$linked_text', edited = FALSE where id=$id");
}

The formatting on here is terrible, so I'm sure won't look right. I just made this up on the fly, so it could have syntax errors, but it shows you the method I described.

---John Holmes...