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?
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.
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.