MySQL - Search the database by text string with special characters

Question

I have a news system, and we need you to access the details of the news from a link that will take the title of the news, not the news ID database.

Note: Some published news have words and letters with special characters, for example:

News title: $title = Nuevo demo de la canción de ñandú;

This title explode with PHP to remove hyphens and spaces would be as follows.

Explode title: $title_explode = explode(' ','-', $title);

Looks like this: $title_filter = nuevo-demo-de-la-canción-de-ñandú;

I created in the database a field title in this field the $title will be saved, and I have another field within the database, title_filter, where would place the title $title_filter, which it is the title that exploit, and would be saved also in the database, but we have a ñ and accented letters as we can to query the database to bring me to this record smoothly comparison.

Query: $sql = SELECT * FROM news WHERE title = $title_filter;

Domain with the news: www.dominio.com/nuevo-demo-de-la-canción-de-ñandú

My questions:

1. This search is effective?
2. You can optimize and remove accents $ title to make it so: $title_filter = nuevo-demo-de-la-cancion-de-nandu;
3. Worth create the additional field of the database title_filter to saved clean and do the title search on that title.

Note: The idea of not using the word ID to search the database is to have cleaner seo links and improve the website.

I hope you have understood me and can help me solve this dilemma and make database query data more effective.

Answer 1

I answer you according to the data you give me:

Question 1

That search wouldn't give you any result because the field title is not filtered. That means, you are comparing:

$title_filtered = 'Nuevo-demo-de-la-canción-de-ñandú';
SELECT * FROM news  WHERE title = $title_filter

But title contains => 'Nuevo demo de la canción de ñandú'

So you should change your SQL query to this one:

SELECT * FROM news WHERE title_filter = $title_filter

Question 2

Yes, this is posible with two different ways, both are explained in Stackoverflow (I give you the links):

• Using a REGEX and string replace.
• Using a list of a characters to replace
• Using the translator PHP functions iconv

You can read these three solutions in this stackoverflow post. It is perfectly explained.

Question 3

Definitly yes, if you don't create that fields the performance of your applications will we affected:

1. Everytime a user (or a crawler) visit any URL of your site, you have to filter the title.
2. As you only have the URL filtered, and the to apply the filter you must do it on database site, that means over use unnecesarly the database server. And as you know, that is a well know Neck bottle.
3. It is a great security gap because use data user directly without filters expose you to SQL injections.

May be your doubts are about database space consumtion of having a new field. But the problems to not do that are more relevant, as you see above, also the number of new titles won't cause any space memory problem.

Extra - About the filtering

One posible problem you have is the way you replace spaces to -. If you have something like this:

$title = "This is a test "

Your method will filter into this:

$title_filtered = "This-----is-a-test----"

So, to avoid those kinds of problems is better to use a REGEX like this:

$string = '    hello     world     ';
$filters = array('@^[\t\s]+@', '@[\t\s]+$@', '@[\t\s]+@');
$filtered = array('', '', '-');

$title_filtered = preg_replace($filters, $filtered, $string);

Extra - About the Database performance

Other suggestion that will help you, is the use a MySQL index.

Because if you don't have one, every time an user visit an url you will do a full scan on your news table searching for the correct row. And that is not scalable and will give extra work to your server when your traffic and the number of news increase.

To solve that a MySQL Index is the best way, take a look to the oficial page. Simplifying, instead of doing a full scan of the news table, you only will do a search into a tree data structure.

Using this one should be enought:

ALTER TABLE `news` ADD INDEX `index_title_filter` (`title_filter`)

If you want to know more, read this stackoverflow post.