Get all values from SQL that contains certain value-CodePudding

I'm trying to get all values from table products, that contains for example shirt.
In this case $thin is getting values from searach box "stxt".
It means that if $thin = shirt, I want to get shirt, t-shirt etc.
Right now, only thing that I get is only shirt, despite if I will use "LIKE" or "=" as operator in $sql statement.

$thin = $_POST['stxt'];
$thing = strtoupper($thin);
$sql = "select * from products where upper(productName) LIKE '$thing'";

CodePudding user response：

In your query, put in your LIKE % before and after your variable. Like this "%$variable%". If you want there to be just a number of n characters before or after your variable, put the _ symbol n times. And for security reasons, try to use prepared statements.

Link sql like: https://sql.sh/cours/where/like

CodePudding user response：

can you use a regexp operator ?

$sql = "select * from products where productName regexp $thing";

CodePudding user response：

$thin = $_POST['stxt'];
$thing = strtoupper($thin);
$sql = "select * from products where upper(productName) LIKE '%$thing%'";

MSSQL needs % wildcards, other SQL dialects may differ. Alternatively you could do a REPLACE(productName,'$thing','') and compare the length of that to the original length. Either way it is going to be a full table scan unless you have full text indexes set up.