CODEWITHSUNDEEP
phpmysql
Yousef Altaf
Yousef Altaf

Reputation: 2773

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

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

Upvotes: 0

Views: 306

Answers (2)

jeroen
jeroen

Reputation: 91792

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.

Upvotes: 1

RomanPerekhrest
RomanPerekhrest

Reputation: 92894

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

Upvotes: 1

Related Questions