Help comparing case before eregi_replace

CrystalWood

I am working on a Keyword Search. I search a string for keywords and want to display the string with the keywords in another color.

I found this bit of code that loops and replaces each instance of the keyword with font tags around it.

$keyword_list = explode(",", $keywords);

for ($i=0; $i < count($keywords); $i++)
{
$comments = eregi_replace("$keyword_list[$i]", "$keyword_list[$i]", $comments);

}

This works fine. But the problem is, I've converted the $keyword_list to all uppercase in order to perform the SQL Query.

How do I compare the case of the word found in the $comments string to the $keyword_list[$i]? (If the word is all capital, I want to display it all capital. If it is upper and lower, I want to display it upper and lower...)

Is there a way to break out the substring that matches $keyword_list[$i] found in $comments and store it in it's own variable?

Any help would be appreciated... 🙂

~Crystal Wood

sarah

One way would be to store a version of the $keyword_list variable before you change it to upper-case for the query. Call it something like "$keyword_listRAW" and then use it instead of $keyword_list in the code bit that changes the color.

CrystalWood

Maybe this will clarify...

------------------ SQL QUERY --------------

SELECT * FROM $table WHERE (upper($field) LIKE '%$keyword%') ORDER BY $recordNumber DESC;

I have to allow users to enter their comments into the database in three different ways:

THE CAT SAT ON THE MAT
The Cat Sat On The Mat
The cat sat on the mat

When they search, they may not enter their keyword in the same case as what their comments were stored. So if they search for "cat" "Cat" or "CAT", I want it to return all instances. And when I display it, I want it to be in the same case as it was stored...

THE CAT SAT ON THE MAT
The Cat Sat On The Mat
The cat sat on the mat

Does this help?

diego25

Are you sure it returns the field in all uppercase? 'cause the upper() function is used to compare. What you are selecting is everything, i.e. *, which does not have the upper function included. e.g. if your query were
SELECT upper($field) FROM $tabel WHERE upper($field) LIKE "$keyword";

then it would return all uppercase. But if you don't include the upper() function in the fields selected, then it'd return the original case.

I've tested this. I used
SELECT * FROM myTable WHERE upper(myField) = "SOMETHING";
and it returned the field in the original case.

Diego

sarah

I think it depends on the type of database...

Example:

$keyword="CAT";

SELECT * FROM $table WHERE $field LIKE '%$keyword%'

MySQL is not case sensitive. This would return all records with 'CAT','Cat', or 'cat'.

PostgreSQL (I think) will return only records with 'CAT'.

If you are using MySQL, forget the upper() function and all should be good.

xblue

I think they problem may be elsewhere.

Assume we got a keyword, e.g. from user input. We don't need to convert it to uppercase, but it must be spelled somehow.

Now we perform the DB query, no problems here.

Then we do the replacement, but if the text from the DB contained the keyword once uppercase, once lowercase, once mixed, it would be always replaced with however the user spelled it (or how we converted it, if we did that).

To solve this, I can only think of backreferences in the replacement argument. Maybe the ereg-functions support this too, but I'd recommend the preg-functions anyway.

Try:

preg_replace("/$keyword_list[$i]/i", "<font color=\"#0000FF\">$0</font>", $comments);

This performs a case-insensitive search for the keyword, $0 is a backreference to the current match.

Hope I got you right,
xblue

xblue

One additional thought: in case the keywords actually are taken from user input, it may break your script if they contained any sign that has special meaning within a regular expression.

preg_quote is a function to escape all these special characters, you may find this helpful.

CrystalWood

Okay, let's try this again...

Let's say there are two records in the database:

ALL DOGS GO TO HEAVEN. IT'S RAINING CATS AND DOGS. THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG.

The little girl wanted a dog for Christmas, so her mother took her to the dog pound. Dogs make good pets.

And the user searches for the word dog. Because I used strtoupper($keyword_list[$i]) to match the upper($field) in the SELECT statement, this is how the records would appear...

ALL DOGS GO TO HEAVEN. IT'S RAINING CATS AND DOGS. THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG.

The little girl wanted a DOG for Christmas, so her mother took her to the DOG pound. DOGs make good pets.

As you can see, the FIRST record turned out fine, but the SECOND record SHOULD look like the one below:

ALL DOGS GO TO HEAVEN. IT'S RAINING CATS AND DOGS. THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG.

The little girl wanted a dog for Christmas, so her mother took her to the dog pound. Dogs make good pets.

My SELECT statement works fine. It returns ALL instances of $keyword_list[$i] (whether UPPER, lower, or MiXeD case...).

The problem I'm having now, is REPLACING the found item with it's original case, NOT with the case stored in $keyword_list[$i]...

Let me know if you have any more suggestions... I will try the preg_replace and let you know if I got it to work...

Weedpacket

$comments = "The little girl wanted a dog for Christmas, so her mother took her to the dog pound. Dogs make good pets. ";
echo preg_replace("/DOG/i", "<font color=\"#0000FF\">$0</font>", $comments);

Works fine for me.

CrystalWood

WEEDPACKET: (Cool Alexie Sayle pic)

I got the prereplace to work. Now I'm having difficulty with the select statement. I am able to get the wildcard select to work, but I want to give the user the option to do an EXACT match.

Let's say they input "dog" as their keyword

IF they fill in the checkbox, I want to match "dog" (not "dogs" or "dogcatcher")

Any idea how to write an SQL statement do do that?

(And I don't know if it matters, but I'm using PostgreSQL...)