开发者

searching a mysql database

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 = "S开发者_高级运维ELECT * "
         . "From `songs` "
         . "WHERE `Artist` like '".$_REQUEST['q']."' "
         .   "OR `Album` like '".$_REQUEST['q']."' "
         .   "OR `Genre` like '".$_REQUEST['q']."' "
         .   "OR `Title` like '".$_REQUEST['q']."';";


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

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

That will search for [anything]beatles[anything]


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".


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).


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

'; DROP DATABASE music; --

or something similar?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜