I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack
) in a certain column contains certain data (string $needle
), like this:
mysql_query("
SELECT *
FROM `table`
WHERE `column`.contains('{$needle}')
");
In PHP, the function is called substr($haystack, $needle)
, so maybe:
WHERE substr(`column`, '{$needle}')=1
This question is related to
php
mysql
sql
string-comparison
You probably are looking for find_in_set
function:
Where find_in_set($needle,'column') > 0
This function acts like in_array
function in PHP
be aware that this is dangerous:
WHERE `column` LIKE '%{$needle}%'
do first:
$needle = mysql_real_escape_string($needle);
so it will prevent possible attacks.
WHERE `column` LIKE '%$needle%'
Mine is using LOCATE
in mysql:
LOCATE(substr,str), LOCATE(substr,str,pos)
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.
In your case:
mysql_query("
SELECT * FROM `table`
WHERE LOCATE('{$needle}','column') > 0
");
In addition to the answer from @WoLpH.
When using the LIKE
keyword you also have the ability to limit which direction the string matches. For example:
If you were looking for a string that starts with your $needle
:
... WHERE column LIKE '{$needle}%'
If you were looking for a string that ends with the $needle
:
... WHERE column LIKE '%{$needle}'
Source: Stackoverflow.com