向类似mysql的查询传递null值

yduiuuwa 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(267)

我的sql是这样的：

$si_like_param="%".$si_gl_group_id."5";
$stmt = $db->prepare("select  m1.id, m1.name, m1.surname, m1.fathername, 
                              m1.region_num,m1.school_num,m1.variant,
                              m1.sinif, m1.qrup,m1.f_lan 
                      from  students_answers m1, 
                            results m2 
                      where 
                            m1.id_exam=:si_exam_id 
                        and m1.id=m2.st_answer_id
                        and m2.result_level >= :si_min_score
                        and m1.school_num in ('003','018','019','020')
                        and m1.region_num = 20
                        and m1.qrup like :si_group_id
                      order by m2.result_level desc;");

$stmt->bindparam(":si_exam_id", $si_exam_id);
$stmt->bindparam(":si_min_score", $si_min_score);
$stmt->bindparam(":si_group_id", $si_like_param,PDO::PARAM_STR);

变量$si\u gl\u group\u id可能为空。在我的html中是这样的：

<option value="">All</option>
<option value="0">Zero</option>
<option value="1">One</option>

当我在phpmyadmin上执行这个查询时，它工作了，但在脚本中它不工作。

mysql php

来源：https://stackoverflow.com/questions/53633255/passing-null-value-to-mysql-like-query

1条答案

按热度按时间

xytpbqjk1#

你应该用concat concat('%', :si_group_id, '5') 所以
$si\u like\u param=$si\u gl\u group\u id；

$stmt = $db->prepare("select  m1.id, m1.name, m1.surname, m1.fathername
    , m1.region_num, m1.school_num,m1.variant,m1.sinif, m1.qrup,m1.f_lan 
    from students_answers m1, results m2 
    where m1.id_exam=:si_exam_id 
        and m1.id=m2.st_answer_id
        and m2.result_level >= :si_min_score
  and m1.school_num in ('003','018','019','020')
  and m1.region_num = 20
  and m1.qrup like  concat('%', :si_group_id, '5')
        order by m2.result_level desc;");
                        $stmt->bindparam(":si_exam_id", $si_exam_id);
                        $stmt->bindparam(":si_min_score", $si_min_score);
                        $stmt->bindparam(":si_group_id", $si_like_param,PDO::PARAM_STR);

您不应该使用旧的implict join sintax，而应该使用显式join sintax

$stmt = $db->prepare("select  m1.id, m1.name, m1.surname, m1.fathername
    , m1.region_num, m1.school_num,m1.variant,m1.sinif, m1.qrup,m1.f_lan 
    from students_answers m1
    INNER JOIN results m2  ON m1.id=m2.st_answer_id
    where m1.id_exam=:si_exam_id 

        and m2.result_level >= :si_min_score
      and m1.school_num in ('003','018','019','020')
      and m1.region_num = 20
      and m1.qrup like  concat('%', :si_group_id, '5')
        order by m2.result_level desc;");
                        $stmt->bindparam(":si_exam_id", $si_exam_id);
                        $stmt->bindparam(":si_min_score", $si_min_score);
                        $stmt->bindparam(":si_group_id", $si_like_param,PDO::PARAM_STR);

赞(0）回复(0）举报 2021-06-16

我来回答

向类似mysql的查询传递null值

1条答案

相关问题

热门标签

最新问答