ansaurus

Question

parsing text for contents of database table of limited size

Answer 1

+1 A:

Your best bet is to manually mark up the news posts in some way, to indicate when a name appears. It's the only way to prevent missed names, or improperly parsed names, and to avoid the huge processing requirement of scanning every news story for every possible name from the database.

Maybe something with a twittery-syntax like:

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

Then run it through a custom sort of markdown-style function when you want to display the news story. It could parse those markers out, find the matching DB record, and generate the link.

It'd be more efficient to convert @[] markers to links before inserting the news story in the database, but that couples things more tightly - if a user is deleted or his ID changes, you have a broken link. Storing the @[] also makes editing stories easier.

Update

If you must have the names automagically detected and converted to links, this can be achieved at a pretty severe performance penalty that only grows as you add more names:

function linkify_names($news) {
  $people = query('select people_id, firstname, lastname from people');

  $from = $to = array();
  foreach ($people as $person) {
    $name = "$person->firstname $person->lastname";
    // TODO - escape regex chars in $name?

    // match [boundary]$name[boundary], case insensitive
    $from[] = "/(\b)($name)(\b)/i";

    // include boundaries in replacement; maintain case of found name
    $to[] = '$1<a href="/people/' . $person->people_id .'">$2</a>$3';
  }

  return preg_replace($from, $to, $news);
}

The difference is, instead of looking up only the names marked with @[] tags, you must look up all names, and exhaustively search for each one. You cannot rely on simple regular expressions to find names in the body of the news document.

meagar 2010-02-03 16:27:27

I have in fact implemented this sort of solution already (perhaps I should have mentioned it in the question, but I wanted to keep it simple). However, I consider this to be a less than ideal solution, simply because the "news" items can be added by any one of a number of users of the web site. While I have provided the functionality to add links in a similar way to that in which you suggested - most users of the site do not bother using the functionality. So I would prefer to have an "automatic" way of doing it.

Tomba 2010-02-03 16:50:35

@Tomba no worries

meagar 2010-02-04 13:51:04

Answer 2

+2 A:

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.

jkyle 2010-02-03 17:47:31

Thanks for taking the time to type that out. I have not used any javascript on the site, but this looks like a perfect use for it. It'll take me a while to get to grips with jquery but I've been meaning to do that for some time now. I don't think I would want myPage.map to be visible to users of the site so I'd have to look into doing it via Ajax.

Tomba 2010-02-03 18:04:28

If you don't want your users to be able to see the map, then a client-side approach is not going to be a good option. Anything that goes across the wire is available to the viewer, including Ajax responses. There are some server-side JS implementations, but you're probably better off recreating this in php in that case.

jkyle 2010-02-03 18:10:22

maybe I'll have to rethink whether I need myPage.map to be invisible

Tomba 2010-02-03 18:32:18

It works great - thanks. I'm very new to jquery and not experienced in javascript: how would I go about obtaining myPage.map through an Ajax function?

Tomba 2010-02-04 16:50:53

Check out the documentation here: http://api.jquery.com/jQuery.getJSON/You'll do something like:$.getJSON('someurl/getMap.php', function(data) { myPage.map = data;});Where 'someurl/getMap.php' is a function on the server that builds the JSON version the data you need.

jkyle 2010-02-04 17:15:12

@jkyle thanks for your help

Tomba 2010-02-05 10:28:41

ansaurus

tags:

views:

answers:

parsing text for contents of database table of limited size

Update

related questions