php MySQL Search on two columns

Question

I'm trying to do a search function using php and MySQL. I need to search from two different columns, one for song title and one for artist and user can search using:

1. artist name.
2. song title.
3. both of them (without order)
4. there will be special characters for ex: ' (apostrophe)

This is what I have done but I need a more efficient way to do this. I have also thought using similar_text(); in php. Can anyone suggest me a better way to do this. Thank You

table: songs

|artist   |    title   |
|----------------------|
|eminem   | not afraid |
|kida     | o'najr     |

My code:

$search_value = $_POST["search_value"];
$query = "select * from `songs` where concat(`title`, `artist`) like '%$search_value%'";

Is there something that doesn't work? Are you getting any errors that you're not expecting? — Jay Blanchard
– Jay Blanchard, Commented Sep 23, 2014 at 18:31
This looks terrifyingly insecure. Are you sure your user parameters are properly escaped? $_POST data NEVER goes directly into a query. Without escaping in some form this will not work. — tadman
– tadman, Commented Sep 23, 2014 at 18:33
It works but you know... it's not such a good solution. Also for ex. it should not work with part of a string. For ex. if you search: raid it shows Eminem - Not Afraid as a result. and this should not happen. — M1X
– M1X, Commented Sep 23, 2014 at 18:35
its shows eminem becuase you may be printing artist . try printing title !! — zod
– zod, Commented Sep 23, 2014 at 18:36
@rexhin - Why shouldn't it show that? if you don't want to search the song title then remove that part of the query. It;s code, not magic. — I wrestled a bear once.
– I wrestled a bear once., Commented Sep 23, 2014 at 18:48

Bobot · Accepted Answer · 2014-09-23 19:42:55Z

You should use the SQL OR statement, better than CONCAT one in this case.
Combined with a space before and after what you search, this should give you the expected result ! (I mean if you search for 'raid' you will not find 'Eminem - Not Afraid', if you want to find it you have to search for 'afraid' for exemple ; If you want to sort the results by revelance, you will have to create indexes and use them, or use Levenshtein method or something else ...)
Don't forget to escape your data before using it in sql statements.

Btw if you want to make it case insesitive you will have to use blabla COLLATE UTF8_GENERAL_CI LIKE %what you search% blabla

// if you are using mysql_connect()
$search_value = ' '.mysql_real_escape_string($_POST["search_value"]).' ';
$query = "select * from `songs` where `title` like '%$search_value%' or  `artist` like '%$search_value%'";

// if you are using PDO
$args[':search_value'] = '% '.$_POST["search_value"].' %';

$query = "SELECT * FROM `songs` WHERE `title` LIKE :search_value OR `artist` LIKE :search_value";

$qry = $PDO->prepare($query);
$res = $qry->execute($args);

For a multi-words search you can also use

// for mysql_connect()
$search_value = $_POST["search_value"];
$search_value = explode(' ', $search_value);
foreach($search_value as $k => $v){
    $search_value[$k] = mysql_real_escape_string($v);
}
$search_value = ' '.implode(' % ', $search_value).' ';

// for PDO
$search_value = explode(' ', $_POST["search_value"]);
$search_value = implode(' % ', $search_value);
$args[':search_value'] = '% '.$search_value.' %';

StaticVariable · Accepted Answer · 2014-09-23 18:38:41Z

1

You can simply use sql or statement if you don't want confusion .

$search_value = $_POST["search_value"];//check user input first than create a query.
$query = "select * from `songs` where `title` like '%$search_value%' or  `artist` like '%$search_value%'";

answered Sep 23, 2014 at 18:38

StaticVariable

5,2934 gold badges26 silver badges46 bronze badges

Collectives™ on Stack Overflow

php MySQL Search on two columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related