Search DB and making links

[bioweb]

Hello WW, I'm a first time poster. Great site!

I have a question before I bang my head on the wall anymore.

I have a database of terms and definitions. I want to parse through each definition and find words that are terms in the database and then make a link to that definition. I tried exploding definitions like:

$def_array = explode( " ", $def );

And then running the array through a search database and make link loop. However, I ran into problems when words were flanked by ".", ":", line break, etc. I tried multiple explodes, but then ran into the problem of putting the definition and links back together. I also had problems if the term was two words and not just one.

Is this the best way to go about doing this or is there a better way? Running time of code is not an issue, b/c I'm just going to input code with links into another MySQL field.

Thanks for any insight or suggestions you may have.

[mcibor]

So you have sth like:

Term, definition
electicity, no idea
power management, sth with shutting the PC down
current, now or something flowing (in air, ocean, river, kable)
etc., et cetera - and so on

Then I would just query the db direct:

$search = $_POST['search'];
$search = mysql_real_escape_string($search);

$sql = "(SELECT term, description FROM table WHERE term='$search' ORDER BY term) UNION (SELECT term, description FROM table WHERE term LIKE '%$search%' ORDER BY term) UNION (SELECT term, description FROM table WHERE description LIKE '%$search%' ORDER BY term) LIMIT 0,30";

$query = mysql_query($sql);

and show the results

Hope this helps
Michal Cibor

And welcome to WebmasterWorld!

[bioweb]

Thanks for the welcome.

Sorry I wasn't very clear. Hopefully the below example will explain what I'm trying to do.

I have the following type of DB format:

term, def
base pair, Two nitrogenous (purine or pyrimidine) bases...
Biology, The science of life.
DNA, A molecule made up of nucleotide base pairs.
etc, etc...

I want write some code that will take the plain text definition and populate it with links to other definitions like below:

A <a href="def.php?ID=2">molecule</a> made up of <a href="def.php?ID=4">nucleotide</a> <a href="def.php?ID=12">base pairs</a>.

I can then input this linked definition into another field in the DB so that it is not having to make all the links each time one views a definition.

So the question is how does one normally attack this? Does one explode the definition and then search DB if the term is present, or does one take all the terms in the DB and see if its in the definition? Or is there another way? Or does someone know of a function that could work on this?

[mcibor]

What would I do is first get the terms:

SELECT id,term FROM table
...
$terms = array();//define array temp
while($row = mysql_fetch_assoc($result)) {
$terms[$row['id']] = $row['term'];
}

then check the database for each term and populate the description with it
foreach ($terms as $id => $term) {
SELECT id, description FROM table WHERE description LIKE '%$term'

while ($row2 = mysql_fetch_assoc($result2)) {
$description = substr($term, "<a href=\"def.php?id=$id\">$term</a>", $row2['description']);
"UPDATE table SET description='$description' WHERE id=".$row2['id'];
...}
}

It's not a working code, but should set you to right track.

Best regards
Michal Cibor

[bioweb]

Thank you Michal, I think I'm starting to see the light. I have one more thing to figure out, regarding multiple word terms.

Say I have the following terms:

term, definition
base, a liquid with a high pH
base pair, Two nitrogenous bases held together

If I have a definition with "base pair" depending on how the $terms array is run through I could get:

<a href="def.php?id=2">base</a> pair

or

<a href="def.php?id=50">base pair</a>

or something really messy like:

<a href="def.php?id=50"><a href="def.php?id=2">base</a> pair</a>

I figure I could sort the $terms array in descending length (I assume there is a way to do this). And then larger terms will be done first. However how can I check to make sure a word that I will be replacing with a linked word is not already inbetween an <a href ...></a> so I don't get something like the 3rd example above?

[mcibor]

You can achieve that with strtr() [php.net] function.

If you change one array into another, then it ommits the words already changed. str_replace doesn't ommit them.

Hope this helps
Michal Cibor

If you require an example, don't hesitate to mention