mysql 查询中的动态值

发布于 2024-12-08 13:14:01 字数 425 浏览 1 评论 0原文

我试图使用下面的查询获取结果，每个加盟商的 idRegion 在数据库中记录为 1,2,3,4，所以我想要的是显示 idRegion 2 的所有加盟商。我通过 $_Get 获取 idRegion。这仅显示昏迷前的第一个数字，我认为它应该准备好整个字符串 1,2,3,4 ？当我使用静态值时有效吗？

$colname_franchisee = "-1";
if (isset($_GET['id'])) {
  $colname_franchisee = $_GET['id'];
}


$query_franchisee = sprintf("SELECT * FROM franchise WHERE stiShowInLinks = 'Y' AND idRegion LIKE '%s%' ORDER BY stiName ASC", $colname_franchisee);

原文

I am trying to get result using below query, idRegion is recorded in database as 1,2,3,4 for each franchisee, so what I want is to display all franchisees with idRegion 2. I am getting idRegion via $_Get. this display only first digit before coma, I think so it should ready whole string 1,2,3,4 ? When I am working with static values that works?

$colname_franchisee = "-1";
if (isset($_GET['id'])) {
  $colname_franchisee = $_GET['id'];
}


$query_franchisee = sprintf("SELECT * FROM franchise WHERE stiShowInLinks = 'Y' AND idRegion LIKE '%s%' ORDER BY stiName ASC", $colname_franchisee);

分享到QQ

分享到微博

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

发布评论

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

⊕婉儿 2024-12-15 13:14:01

尽管我不喜欢您的数据库设计，但这应该可行：

$query_franchisee = sprintf("
    SELECT *
    FROM franchise
    WHERE
    stiShowInLinks = 'Y' AND (
        idRegion = '%d' OR
        idRegion LIKE '%d,%%' OR
        idRegion LIKE '%%,%d' OR
        idRegion LIKE '%%,%d,%%'
    )
    ORDER BY stiName ASC
",
$colname_franchisee,
$colname_franchisee,
$colname_franchisee,
$colname_franchisee
);

sprintf 函数将 % 字符视为格式说明符，并以特殊方式处理接下来的几个字符。为了按字面意思使用 % 字符，您必须使用 %%。因此，在 sprintf 之后，您的查询将变为：

idRegion = '1234' OR
idRegion LIKE '1234,%' OR
idRegion LIKE '%,1234' OR
idRegion LIKE '%,1234,%'

This should work although I do not like your database design:

$query_franchisee = sprintf("
    SELECT *
    FROM franchise
    WHERE
    stiShowInLinks = 'Y' AND (
        idRegion = '%d' OR
        idRegion LIKE '%d,%%' OR
        idRegion LIKE '%%,%d' OR
        idRegion LIKE '%%,%d,%%'
    )
    ORDER BY stiName ASC
",
$colname_franchisee,
$colname_franchisee,
$colname_franchisee,
$colname_franchisee
);

The sprintf function treats the % character as a format specifier and treats at the next few characters in a special way. In order to use the % character literally, you must use %%. So after sprintf, your query becomes:

idRegion = '1234' OR
idRegion LIKE '1234,%' OR
idRegion LIKE '%,1234' OR
idRegion LIKE '%,1234,%'

回复收藏 0 原文

月野兔 2024-12-15 13:14:01

$colname_franchisee = "-1";
if (isset($_GET['id'])) {
  $cf = intval($_GET['id']);  only if id integer.

}
$query_franchisee = "SELECT * FROM franchise 
                     WHERE stiShowInLinks = 'Y' 
                       AND idRegion LIKE '%cf%' 
                     ORDER BY stiName ASC", $cf);

$colname_franchisee = "-1";
if (isset($_GET['id'])) {
  $cf = intval($_GET['id']);  only if id integer.

}
$query_franchisee = "SELECT * FROM franchise 
                     WHERE stiShowInLinks = 'Y' 
                       AND idRegion LIKE '%cf%' 
                     ORDER BY stiName ASC", $cf);

回复收藏 0 原文

~没有更多了~