从mysql表中检索数据时如何忽略空格

Question

I am working in PHP with PDO. The problem I am having is: I have a table called products. It has a column named product_name. Here are some examples of the name of products

product_name
----------
Bfilet de bœuf entier                    
Bar de ligne                             
Saumon entier
Beurre 

So when I execute the query

select * from products where product_name='beurre'

It works fine. It returns the corresponding line.

But if I do:

select _ from products where product_name='Saumon entier'"

It does not return any result. There seems to be a problem when there are spaces involved!

And another unusual thing is that I don't seem to be having this problem on the SQL Command line, only when I use PHP to retrieve the data.

Could someone explain to me why and offer a solution?

Answer 1

Pattern matching

Lookup MySQL pattern matching. Code would be something like:

$stmt = $pdo->prepare('SELECT * FROM products WHERE product_name LIKE ?');
$stmt->execute(['Saumon entier%']);

var_dump($stmt->fetchAll());

After your edit, it appears you don't want LIKE, but you actually have a space at the end of the string. Quick fix would be:

SELECT * FROM products WHERE TRIM(product_name) = 'Saumon entier'

But really you should trim spaces on input.

Answer to question in comments

if i have a french product name such as 'Côte de bœuf', Is there a way i can replace the accents?

In most cases, yes. If your database is utf8 with e.g. utf8_unicode_ci, while ci meaning case-insensitive this would match:

SELECT 'Côte de boeuf' = 'cote de boeuf';

'Côte de boeuf' = 'cote de boeuf'
----------------------------------
                                 1

Note that I removed œ because that won't match, I am not sure to which character it translates. Other option would be to store "non accented" product name in different column and match against those. Lookup iconv in PHP manual.