Passing null value to mysql like query

Question

My SQL is like that:

$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);

it is possible that this variable $si_gl_group_id comes null. in my HTML it is like that:

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

when I execute this query on phpmyadmin it works but in script it didn't work.

i advice you to read [How to get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php) and "it is possible that this variable $si_gl_group_id comes null" [How do you debug PHP scripts?](https://stackoverflow.com/questions/888/how-do-you-debug-php-scripts) and post your findings and also read [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Raymond Nijland, Dec 05 '18 at 13:19
If PHP 7+: `$group_id = $_POST['group_id'] ?: null;` - Unless I've completely misunderstood what you are asking. — Script47, Dec 05 '18 at 13:20
What values can `$si_like_param` have? You might be OK depending on this. — Tim Biegeleisen, Dec 05 '18 at 13:20
hi again my error is related with $si_like_param="%".$si_gl_group_id."5"; it should be $si_like_param="%".$si_gl_group_id."%"; mistyping :) — fischer, Dec 05 '18 at 13:28

score 1 · Answer 1 · answered Dec 05 '18 at 13:21

You should use a concat concat('%', :si_group_id, '5')

so
$si_like_param=$si_gl_group_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);

and you should not use old implict join sintax but explicit join sintax as

 $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);

Passing null value to mysql like query

1 Answers1