Javascript – parsing text for contents of database table of limited size

javascriptjqueryMySQLPHP

I have a MySQL table of "people" as part of a web site, for example:

| people_id  | firstname  | lastname    |
-----------------------------------------
| 1          | John       | Lennon      |
| 2          | Paul       | McCartney   |
| 3          | George     | Harrison    |
| 4          | Ringo      | Starr       |
| .          | .          | .           |

My table has about 2000 rows.

I also have a "news" section on the web site. Frequently, these news items contain references to "people", e.g.

John Lennon and Paul McCartney wrote
some of the most popular songs in the
history of rock music.

Is it possible (or reasonable/advisable) to automatically parse each item of news to look for "people" who are in the database, and then turn them into links. So, for example, the above text would be turned into this (or something functionally equivalent):

<a href="/people/1>John Lennon</a> and <a href="/people/2">Paul McCartney</a> wrote some of the most popular songs in the history of rock music.

What would be the best way to do this? I've made a few abortive attempts to do it using regular expressions in php, but I guess this is not the best approach. I don't known much about javascript (and its frameworks) but I would be happy to use this if it makes sense to do so.

This is not an essential feature of the web site (but I feel it would be a nice addition) so I would prefer to omit such a feature rather than increasing the page load time dramatically.

EDIT

I left out some details in the initial question, to keep the length down.

In fact it is the web site for a football club – all of the "people" are members of the web site, and can log in, and add and edit news stories (for example, match reports), in which they frequently refer to other "people". So it's not just me who adds the news stories – they can be added by the (around) 2000 other users.

Although the membership is restricted in that people have to be approved before joining, the system has to be able to cope with complexities such as people with unusual names, and there are a few instances of more than one people with the same name.

I have implemented a kind of solution, in which I use a type of proprietary code to mark up/down people's names (e.g. [p=1]John Lennon[/p]) but I have found that, of the 2000 users of the site, only a handful make use of this.

For what it's worth, the web site is www.ouafc.com, and an example of a news story is at www.ouafc.com/news/312.

Best Answer

I don't know much about php, but here's a quick JavaScript go at it using jQuery 1.4:

<div id="maindiv">
   John Lennon and Paul McCartney wrote some of the most popular songs in the history of rock music.
</div>


<script>
   $(document).ready(function(){
       myPage.linkify($("#maindiv"));
    })

var myPage = {
    map: {
            "John Lennon": 1,
            "Paul McCartney": 2,
            "Rock Music": 3
         },

    linkify: function(domEl){
        var htmlcopy = domEl.html();

        function buildLink(txt, loc){
            return '<a "href = /blah/'+loc+'>'+txt+'</a>';
        }

        for(i in myPage.map){
           var tmpStr = new RegExp(i,"gi");
           htmlcopy = htmlcopy.replace( tmpStr, buildLink(i, myPage.map[i]) );
       }

       domEl.html(htmlcopy);
    }
 }
</script>

myPage.map would be built server-side from the database. This could also be a callback to an Ajax function (that would grab the map) so that it wouldn't stop the rest of the page from doing its thing.

Related Topic