searching a mysql database

Question

currently i have a database of music that i have db'd in mysql, now i am writing a php frontend for it, and it will list out everything in a table, it works, but if i search "the beatles" it gives me 453 results(correct) however if i just search "beatles" it results in 0 rows, how would i go about making it able to search for something like that?

heres my current line:

$query2 = "SELECT * From `songs` WHERE `Artist` like '".$_REQUEST['q']."'
   OR `Album` like '".$_REQUEST['q']."' OR `Genre` like '".$_REQUEST['q']."'
       OR `Title` like '".$_REQUEST['q']."';";

EDIT Reformatted query by @Yacoby

 $query2 = "SELECT * "
         . "From `songs` "
         . "WHERE `Artist` like '".$_REQUEST['q']."' "
         .   "OR `Album` like '".$_REQUEST['q']."' "
         .   "OR `Genre` like '".$_REQUEST['q']."' "
         .   "OR `Title` like '".$_REQUEST['q']."';";

please remember to use mysql_real_escape_string to sanitize user input: php.net/manual/en/function.mysql-real-escape-string.php — Klaus Byskov Pedersen
– Klaus Byskov Pedersen, Commented Apr 20, 2010 at 16:22

hookedonwinter · Accepted Answer · 2010-04-20 16:18:44Z

2

Throw a % before and after the search term, in the quotes:

WHERE Artist like '%".$_REQUEST['q']."%'

That will search for [anything]beatles[anything]

answered Apr 20, 2010 at 16:18

hookedonwinter

12.7k19 gold badges64 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Parson Over a year ago

thanks much, that seemd to help with that situation, i might need to do some more parsing, i forgot to add that above that, i am escaping the info by doing $_REQUEST['q'] = mysql_real_escape_string($_REQUEST['q']);

hookedonwinter Over a year ago

Obviously, throw the % around each search string. Or, in your example there, do $_REQUEST['q'] = '%' . mysql_real_escape_string($_REQUEST['q']) . '%';

KSwift87 · Accepted Answer · 2010-04-20 16:19:56Z

0

You may want to consider parsing your user's input and cleaning it up. Get rid of words like "the" and just use important words in the query like "beatles".

answered Apr 20, 2010 at 16:19

KSwift87

2,3026 gold badges29 silver badges43 bronze badges

1 Comment

Marcus Adams Over a year ago

Also store the record as "Beatles, The", and just do a search LIKE 'BEATLES%'.

Marc B · Accepted Answer · 2010-04-20 18:55:42Z

0

A LIKE query allows you to use SQL wildcards (% and _). Otherwise it acts exactly as if you were using a regular 'equals' comparison:

SELECT .... WHERE somefield LIKE 'beatles';
SELECT .... WHERE somefield='beatles';

act exactly the same, whereas

SELECT .... WHERE somefield LIKE 'beatles%';

finds any record where somefield starts with 'beatles', and

SELECT .... WHERE somefield='beatles%';

will find only records that contain the literal string 'beatles%' (and note that the % isn't being treated as a wildcard).

answered Apr 20, 2010 at 18:55

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

gmadd · Accepted Answer · 2012-01-18 18:23:54Z

0

You need to protect against SQL injections. What if someone enters:

'; DROP DATABASE music; --

or something similar?

edited Jan 18, 2012 at 18:23

gmadd

1,1529 silver badges18 bronze badges

answered Apr 20, 2010 at 16:21

Nick

1

Collectives™ on Stack Overflow

searching a mysql database

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related