Turning words into links using MySQL

Is this method ok? Know of another?

[Aleister]

Here is a simple scenario.

I am pulling text from a table to display on a page. Not huge amounts of text - probably between 3 to 50 sentences or so. Lets call this $desc.

I have another table (#2) which contains keywords, and file names. I am not sure how big it will end up being, but probably not over 200 rows at the very most.

Now before I output $desc to a page, I do a search and replace to turn each of those keywords (which are found in db #2) into links. Here is some code:

$query = "SELECT * FROM char_names";
$result = @mysql_query($query) OR die('Could not execute query: ' . mysql_error());
while ($row = mysql_fetch_array( $result )) {
 $s_id = $row['id'];
 $s_url = "<a href=\"" . $row['page'] . "\">" . $s_id . "</a>";
 $desc = str_replace($s_id, $s_url, $desc);
} 
echo $desc;

This example is stripped down of course, but it is the basic method.

This is of course relatively simple to do, and it works fine, but I just was not sure if it was a practical approach. I imagine if I was dealing with huge amounts of data it could be a problem, but I don't know if there is a much better way to do this.

Any thoughts? Thanks!

[Stu_Rogers]

Lets call this $desc

Careful! desc is a reserved word meaning descending.

In answer to your question though, I can't think of a better way to achieve this. It is an ideal application of php's str_replace.

[mcibor]

You could use translating function strtr [php.net]:

$query = "SELECT * FROM char_names";
$result = @mysql_query($query) OR die('Could not execute query: ' . mysql_error());
while ($row = mysql_fetch_array( $result )) {
$s_id = $row['id'];
$s_url = "<a href=\"" . $row['page'] . "\">" . $s_id . "</a>";
$translate[$s_id] = $s_url;//create an array of translation
}
$desc = strtr($desc, $translate);
echo $desc;

As I read in the manual [php.net] this function is better than str_replace, because it won't try to replace something already replaced.
Best regards
Michal Cibor

PS Desc is a reserved word in mysql, but not as a variable name, only as a field/table name.

[Aleister]

Very nice! strtr at least sounds like it would be easier on things from the way it works. I will measure the execution time and compare the two.

Hrm.. well not only is the variable I was using called $desc, but that is also the name of the field I was using in the table :P Maybe I should change it then.

[mcibor]

Here you've got a list of mysql reserved words. Try not to use them as names:

[dev.mysql.com...]

Michal Cibor

[Aleister]

Thanks :)

btw, I will post back with my personal findings on speed differences between the methods (for this scenario at least).

[Aleister]

Well the first method was on average, 3 times faster with the data I was using. I will use that for now.

What I really should be doing is only converting words to links when I (manually) add info to this database instead of converting everytime the page is displayed.

Right now I am not putting the 'turned into links' data back to the database. I guess it would be easy enough to update the thing when my data changed. I could just run something through to strip out all the links and then rebuild them. (This data may only chance once a week on avg).