PHP Mysql search problem! Help

[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

[blocblue]

Hi,

Firstly, when implementing a search, I would be inclined to use the like syntax in your select query. For example:

SELECT *
FROM `songs`
WHERE `song` LIKE '<song name>';

This can then be taken further to include the use of the wildcard character - %. For example:

SELECT *
FROM `songs`
WHERE `song` LIKE '%song name%';

This will match any song containing the string "name of song" somewhere within its content. E.g. "Enter the song name you're looking for"

You can again take this one step further and substitute spaces between words with the wildcard character. For example:

SELECT *
FROM `songs`
WHERE `song` LIKE '%song%name%';

This will match any song containing the words "song" and "name" in that order. E.g. "That song doesn't have a very good name"

You could again take it further to search for all of the words in any order. For example:

SELECT *
FROM `songs`
WHERE `song` LIKE '%song%' AND LIKE '%name%';

Hope this helps.

R.

http://www.htmlite.com/mysql011.php

[sandeepji1]

There is no doubt that, we use Wild-character matching strategy,Because in Structure Query Language for Pattern matching we use 'LIKE' command with two variation

1)% that's we use for Multiple character Matching.
1->(a) eg.'s%' it Match all text which start from 's character'
1->(b) eg.'%s' it Match all text which End from 's character'
1->(c) eg.'%s%' it Match all text having 's character' contain.

2)_ that's we use for Single character Matching.

and I agree with Mr.robothy, because, on basis of my upper discussion I'll give same Answer to all, and if you have any problem during Implementation, Then Just mention here I';; Try to Solve them from Basic.

[asahmed]

Thanks for the help guys!

Cheers