try to have PHP MYSQL query ignore empty variable in WHERE clause

Question

Post vars

$institute = $_POST['institute'];

if (isset($_POST['sections'])) {
    $sections = $_POST['sections'];
}

if (isset($_POST['division'])) {
    $division = $_POST['division'];
}

if (isset($_POST['level'])) {
    $level = $_POST['level'];
}

//check empty var
$where = "WHERE a.institute =?";
$bind = "i";
$prams = "$institute, ";
if (!empty($sections)) {
    $where .= "AND a.section = ?";
    $bind .= "i";
    $prams .= "$sections, ";
}

if (!empty($division)) {
    $where .= "AND a.division =?";
    $bind .= "i";
    $prams .= "$division, ";
}

if (!empty($level)) {
    $where .= "AND a.phase =?";
    $bind .= "i";
    $prams .= "$level";
}

//var_dump($institute, $sections, $division, $level);
var_dump($bind);

//$getSearch = $db->prepare("SELECT * FROM student_basic_info WHERE institute =? AND section = ? AND division =?");
$getSearch = $db->prepare("SELECT
a.*, a.id AS stud_id, b.id, b.ins_name, c.id, c.sec_name, d.id, d.div_name
FROM student_basic_info AS a
JOIN institutes AS b ON (a.institute = b.id)
CROSS JOIN ins_sections AS c ON (a.section = c.id)
CROSS JOIN ins_division AS d ON (a.division = d.id)
$where GROUP BY a.id
");
$studSearch = array();
$getSearch->bind_param("'".$bind."'", $prams);
if ($getSearch->execute()) {
    $results = $getSearch->get_result();
    while ($vStud = mysqli_fetch_array($results)) {
        $studSearch[] = $vStud;
        ?>

got

( ! ) Fatal error: Call to a member function bind_param() on a non-object on line 59

Line 59 is

$getSearch->bind_param("'".$bind."'", $prams);

after solving the problem of the Call to a member function bind_param()

now got Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

It seems that your $getSearch is not an object. can you post the instanciation of this object ? — ADreNaLiNe-DJ
– ADreNaLiNe-DJ, Commented Feb 15, 2016 at 12:46

RomanPerekhrest · Accepted Answer · 2016-02-15 12:49:35Z

1

Looks like $getSearch is empty(false). Check your prepare function. It should return true on success.

if ($getSearch = $db->prepare(...)) {
    $getSearch->bind_param(...);
    ...
}
else {
    printf("Errormessage: %s\n", $db->error);
}

answered Feb 15, 2016 at 12:49

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yousef Altaf Over a year ago

thanks yes it says

Errormessage: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?AND a.section = ?AND a.phase =? GROUP BY a.id' at line 7

will check it again

jeroen · Accepted Answer · 2016-02-15 12:55:16Z

1

You need spaces where you add your AND conditions; now your sql is invalid and the prepare will fail:

$where .= " AND a.section = ?";
           ^ here
// etc.

However, now your bind will fail, you cannot concatenate your values and send one long string as the second parameter. You need to bind each value individually.

answered Feb 15, 2016 at 12:55

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

5 Comments

Yousef Altaf Over a year ago

can you please check this Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables when I print the bind it's iii and when print the prams 931 what is the problem then

jeroen Over a year ago

@YousefAltaf Like I mentioned, you need to bind each value individually so you would need another set of if condtions for the different situations.

Yousef Altaf Over a year ago

but when var_dump $where got string 'WHERE a.institute =? AND a.section =? AND a.phase =?' (length=52) and when I var_dump $bind got string 'iii' (length=3) and when var_dump $prams got string '9, 3, 1' (length=7) where is the mistake

jeroen Over a year ago

@YousefAltaf $prams is 1 string containing 3 values, you need 3 strings containing 1 value each. So 4 separate parameters for your bind function in this case.

Yousef Altaf Over a year ago

pls can you edit your answer and show me how I can do this

Collectives™ on Stack Overflow

try to have PHP MYSQL query ignore empty variable in WHERE clause

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related