PHP 中的 LIKE 条件无法正常工作

发布于 2024-12-23 00:56:10 字数 327 浏览 4 评论 0原文

我的数据库中有一行名为“active_sizes”，我想按大小过滤我的网站项目，为此，我在 php 中使用 LIKE 条件：

AND active_sizes LIKE '%" . $_GET['size'] . "%'

但是通过使用此代码，我遇到了问题，

例如当 $_GET[' 项目

size']=7.0 此代码显示 active_sizes=17.0我的 active_sizes 值看起来像的 17.0,5.0,6.5,7.5,,

谢谢

原文

i have a row in my database with name "active_sizes" and i want filter my website items by size, for this, i use LIKE Condition in php :

AND active_sizes LIKE '%" . $_GET['size'] . "%'

but by using this code i have problem

for example when $_GET['size']=7.0 this code shows items that active_sizes=17.0

my active_sizes value looks like 17.0,5.0,6.5,7.5,,

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愛放△進行李 2024-12-30 00:56:10

在数据库的单个字段中使用逗号分隔值表明设计不好。您应该规范化事物，并有一个单独的“item_sizes”表。就目前而言，您需要一个非常丑陋的 where 子句来处理此类子字符串不匹配：

$s = (intval)$_GET['size'];

... WHERE (active_sizes = $s)   // the only value in the field
      OR (active_sizes LIKE '$s%,') // at the beginning of the field
      OR (active_sizes LIKE '%,$s,%')  // in the middle of the field
      OR (active_sizes LIKE '%,$s') // at the end of the field

或者，如果您正确规范化事物并将这些单独的值放在它们自己的子表中：

WHERE (active_sizes_child.size = $s)

我知道我会选择哪个。 ..

你没有说明你正在使用哪个数据库，但是如果你使用MySQL，你可以暂时完成同样的事情，但

WHERE find_in_set($s, active_sizes)

代价是失去可移植性。相关文档在这里： http://dev .mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Using comma-separated values in a single field in a database is indicative of bad design. You should normalize things, and have a seperate "item_sizes" table. As it stands now, you need a VERY ugly where clause to handle such sub-string mismatches:

$s = (intval)$_GET['size'];

... WHERE (active_sizes = $s)   // the only value in the field
      OR (active_sizes LIKE '$s%,') // at the beginning of the field
      OR (active_sizes LIKE '%,$s,%')  // in the middle of the field
      OR (active_sizes LIKE '%,$s') // at the end of the field

Or, if you normalized things properly and had these individual values in their own child table:

WHERE (active_sizes_child.size = $s)

I know which one I'd choose to go with...

You don't state which DB you're using, but if you're in MySQL, you can temporarily accomplish the same thing with

WHERE find_in_set($s, active_sizes)

at the cost of losing portability. Relevant docs here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

回复收藏 0 原文

反目相谮 2024-12-30 00:56:10

您的 $_GET 值周围有 % 符号。与 LIKE 结合使用，这意味着任何仅包含您的获取值的字符串都将被重新调整。如果您想要精确匹配，请使用 = 运算符，而不使用百分号。

回复收藏 0 原文

胡大本事 2024-12-30 00:56:10

这将解决您眼前的问题：

AND active_sizes LIKE '" . mysql_real_escape_string($_GET['size']) . "%'

如果您使用的是 MySQL 以外的数据库，请使用相应的转义函数。永远不要相信输入数据。

此外，我建议使用数字字段（DECIMAL 或 NUMERIC ）用于 active_sizes 字段。这将加速您的查询，让您消耗更少的内存，创建诸如 active_sizes BETWEEN 16.5 AND 17.5 之类的查询，并且通常这是对于鞋码来说更正确的数据类型。

This will solve your immediate issue:

AND active_sizes LIKE '" . mysql_real_escape_string($_GET['size']) . "%'

If you are using the database other than MySQL, use corresponding escape function. Never trust input data.

Besides, I'd suggest using numeric field (DECIMAL or NUMERIC) for active_sizes field. This will accelerate your queries, will let you consume less memory, create queries like active_sizes BETWEEN 16.5 AND 17.5, and generally this is more correct data type for a shoe size.

回复收藏 0 原文

~没有更多了~