mysql query remove white space in where clause

Question

$query="SELECT * FROM tablename where fieldname='".$folders['name']."'";

the above line is my query where the Field in Where clause contains spaces in between the data.how to remove the spaces present in that files..like using trim or replace in mysql in where clause

$folders['name'] is a filed in another table.....little bad in english.....i hope u understand — acushla, Mar 23 '13 at 06:19

score 1 · Answer 1 · edited May 23 '17 at 10:25

If you want to remove all spaces present in the data then you can Replace() with eith empty character, eg

$var = $folders['name'];
$query = "SELECT * FROM tablename where REPLACE(fieldname, ' ','') = '$var'";

but remember that it can kill the index, so the query will perform full table scan causing slow on large databases.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

mysql query remove white space in where clause

1 Answers1