PHP Mysql search problem

php mysql trim spaces search

[asahmed]

Hello

Does anyone know how to effectively search for a string in a Mysql table?

Here's the problem:

Let's say I have a table(id,song) and I want to search for a specific song (ex: One Love)

Using the following query returns the exact song when the user enters it exactly as it is:

$mysearch = "One Love";

"SELECT id, songs FROM pages WHERE title = '" . mysql_real_escape_string($mysearch) . "'"

But the problem is, once the user enters some extra spaces between words the query doesn't seem to find that song anymore:

$mysearch = "One Love"; //with 3 spaces between 'One' and 'Love'

"SELECT id, songs FROM pages WHERE title = '" . mysql_real_escape_string($mysearch) . "'"

I've tried "preg_replace" to remove the extra spaces before submitting the search term but it doesn't seem to work? when printing the search term it looks ok though: "One Love" just like the one in the DB, but Mysql still can't seem to find it!

Please can I do this in a better way? any suggestion?
Help Please

Peace

[Matthew1980]

Hi there asahmed,

Welcome to the Forum!

Try this, a simple preg_replace:-

$mysearch = "one love";//doesn't matter how many spaces are there
$mysearch = preg_replace("/ {2,}/", " ", $mysearch);
//the output remains as just one space
echo $mysearch;

and into the sql:-

//the string
$mysearch = "One Love";

//strip out extra spaces
$mysearch = preg_replace("/ {2,}/", " ", $mysearch);

//put into the query
$SqlQuery = "SELECT `id`, `songs` FROM `pages` WHERE `title` = '".mysql_real_escape_string($mysearch)."' ";

//send query
$sendQuery = mysql_query($SqlQuery);

You don't need to back tick the column names but I do it just by force of habit, it's just handy to have if you encounter column names with spaces in or reserved words in mysql - backticks effectively escape this: [dev.mysql.com ]

Anyway, that will do the trick ;)

[EDIT]: You may need to check to see if the values/strings stored in the DB have upper case letters in them, if they don't you may need to use strtolower around the mysql_real_escape_string() to avoid missing a match because of case sensitivity, just a thought though :) and conversely you could use ucwords to the same effect if the words in the DB are each uppercase, there are a few options open to you though...

Cheers,
MRb

[asahmed]

Hi Matthew,

Thank you so much! your suggestion works!
Thanks for the tips too :)

[Matthew1980]

Hi there asahmed,

Cool. Glad your sorted, no problem, you could actually steam line that bit of code to just the one line too:-

//search term
$mysearch = "One Love";

//put into the query
$SqlQuery = "SELECT `id`, `songs` FROM `pages` WHERE `title` = '".mysql_real_escape_string(preg_replace("/ {2,}/", " ", $mysearch))."' ";

You get the idea though ;) Which ever works for you... they both do the same thing and there is no difference in execution time, it's just to save a few lines of code here and there

Cheers,
MRb

[rocknbil]

While that works,

- What if they type one love? (case sensitivity)
- What if the spaces are elsewhere? If it's "One Love " the space will still be on the end and not match on an equality operator.

While you **should** pre-filter the input to remove extraneous spaces, you should apply the power of mySQL here to do the matching, this is what it's for. Visualize three radio buttons:

starts with = 1
ends with = 2
anywhere = 3
exact match = 0

then do your cleaning in advance:

$term = preg_replace('/\s{2,}/',' ',mysql_real_escape_string($term));

$operators = Array(
" = '$term'", // 0, exact
" like '$term%'", //1, starts with, etc.
" like '$term%', // 2, ends with
" like '%$term%'" // 3, anywhere
);

$query = "select id,songs from pages where title " . $operators[$_POST['searchtype']];

[Matthew1980]

Hi there Rocknbil,

Granted, I was concerned about the case sensitivity, my own preference is to strtolower ANY user input, that way you have uniformity of data, I was thinking of adding the trim() to the mix, but forgot about that :) It's late & I am tired - that's my excuse and I'm sticking to it :)

Though I do 'like' the array suggestion, nicely done, though I admit I don't quite understand the logic there, I'll try reading that in the morning after my cuppa and try again!

No cancel that, It's just clicked - again, I like the suggestion there...

Cheers,
MRb

[asahmed]

Thanks guys for the suggestions :)

@rocknbil, I've already used the trim() and strtolower() functions, now the search function works fine, but the array suggestion looks great! so thanks I may try that :)

Cheers!