how to ignore spaces when retrieving data from mysql table

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?

This is just your speculation on the spaces role. You have to verify it before asking a question — Your Common Sense
– Your Common Sense, Commented Apr 12, 2014 at 15:40
As you suspect, you probably have a space after 'entier'. Cleanse your data before submitting to your database. — Strawberry
– Strawberry, Commented Apr 12, 2014 at 15:45

Dejan Marjanović · Accepted Answer · 2014-04-12 21:04:31Z

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.

edited Apr 12, 2014 at 21:04

answered Apr 12, 2014 at 15:41

Dejan Marjanović

19.4k7 gold badges54 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Your Common Sense Over a year ago

It may cure only symptom, but not dicease.

Dejan Marjanović Over a year ago

@YourCommonSense This answer is now wrong since the OP edited the question and it received other connotation :)

Codious-JR Over a year ago

@TooTSKI Perfect! that worked, thanks alot. Ok but now i have another question, For example, if i have a french product name such as 'Côte de bœuf', Is there a way i can replace the accents? So that to be looking for 'cote de boeuf'?

Dejan Marjanović Over a year ago

@Arj added to answer, depends on your case, but should be truthy.

Collectives™ on Stack Overflow

how to ignore spaces when retrieving data from mysql table

1 Answer 1

Pattern matching

Answer to question in comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pattern matching

Answer to question in comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related