duanruinong0619
2014-04-12 15:36
浏览 533
已采纳

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

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?

图片转代码服务由CSDN问答提供 功能建议

我在PHP中使用PDO。 我遇到的问题是: 我有一个名为products的表。 它有一个名为 product_name 的列。 以下是产品名称的一些示例

  product_name 
 ---------- 
Bfiletdebœufentier
Bar de ligne 
Saumon entier 
Beurre  
   
 
 

因此当我执行查询时

 从product_name ='beurre'的产品中选择* 
 <  / code>  
 
 

它工作正常。 它返回相应的行。

但如果我这样做:

 从product_name ='Saumon entier'“
   
 
 

它没有返回任何结果。当涉及空格时似乎有问题!

另一个不寻常的事情是我似乎没有 只有当我使用PHP来检索数据时才能在SQL命令行上出现此问题。

有人可以向我解释原因并提供解决方案吗? < / DIV>

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongwei9365 2014-04-12 15:41
    已采纳

    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题