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?
精彩评论