Where LIKE returns 0 with prepared statments

Question

I have this code for simple advanced search form, i spent hours tying to figure this out i couldn't ..

//==============connect============//
$mysqli = new mysqli("localhost", "root", "AWAAWA@19", "SDE");

//==============connect============//


 $nationality=$_POST['nationality'];
 $birthplace=$_POST['birthplace'];
 $dob=$_POST['dob'];
 $study=$_POST['study'];
 $status=$_POST['status'];
 $kids=$_POST['kids'];
 $smoking=$_POST['smoking'];
 $covered=$_POST['covered'];
 $hobbies=$_POST['hobbies'];

//============ For Pagination ============
    $where .= "nationality LIKE ?";
    $params[] = $nationality;
    $type_string .="s";
if(empty($birthplace)){
    $where .= " and birthplace LIKE ?";
    $params[] = $birthplace;
    $type_string .="s";
}
if(isset($dob)){
     $where .= " and dob LIKE ?";
     $params[] = $dob;
    $type_string .="s";
}
if(isset($study)){
     $where .= " and study LIKE ?";
     $params[] = $study;
    $type_string .="s";
}
if(isset($status)){
     $where .= " and status LIKE ?";
     $params[] = $status;
    $type_string .="s";
}
if(isset($kids)){
     $where .= " and kids LIKE ?";
     $params[] = $kids;
    $type_string .="s";
}
if(isset($smoking)){
     $where .= " and smoking LIKE ?";
     $params[] = $smoking;
    $type_string .="s";
}
if(isset($covered)){
     $where .= " and covered LIKE ?";
     $params[] = $covered;
    $type_string .="s";
}
if(isset($hobbies)){
     $where .= " and hobbies LIKE ?";
     $params[] = $hobbies;
    $type_string .="s";
}

$from_section = 'app_female';
$sql_search= "SELECT id,name,nationality,birthplace,status,study
FROM $from_section
where $where 
order by id desc";
$stmt_search = mysqli_stmt_init ($mysqli);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
      printf("Error: %s.\n", $stmt_search->error);

}
else {
  mysqli_stmt_bind_param($stmt_search,$type_string, ...$params);
  mysqli_stmt_execute($stmt_search);
  mysqli_stmt_store_result($stmt_search);
  mysqli_stmt_bind_result($stmt_search, $id,$name,$nationality,$birthplace,$status,$study);
  echo $count_search = mysqli_stmt_num_rows($stmt_search);
}

mysqli_close($mysqli);

it always returns 0 even tho i have records in database matches the given search terms.. what am i doing wrong here? i used the code on a previous project and now it doesn't work or am i missing something?

You don't show us the initial assignment of `$where` and `$type_string` — Nick, Oct 12 '19 at 22:31
[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 12 '19 at 22:51
`LIKE` without wildcard operator, `%` should be replaced with equality, `=` — Parfait, Oct 13 '19 at 02:30
I mean the answer to this question is to replace all issets with emptys. — Your Common Sense, Oct 13 '19 at 05:08
@Parfait it's possible (and actually necessary if there is to be one) that the wildcard is included in the value being placed into the query (e.g. if `$study = '%science'`) — Nick, Oct 13 '19 at 05:08
@YourCommonSense that would create a lot of where conditions with empty comparison values? — Nick, Oct 13 '19 at 05:09
So users of your HTML form types in wildcards with search literals? Probably should have app code do that not rely on users. — Parfait, Oct 13 '19 at 13:40

Parfait · Answer 1 · 2019-11-01T14:42:12.207

Consider avoiding any building of SQL by conditionals but use one SQL statement for all search combinations. Specifically, use the ternary operator to assign variables to either non-empty $_POST values or null. Checking isset on $_POST variables may be redundant. Then in SQL, run COALESCE to retrieve the first non-null value: either the $_POST value or column itself (i.e., no filter):

// ASSIGN VARIABLES TO POST VALUES ELSE ASSIGN AS null
$nationality = empty($_POST['nationality']) ? null : $_POST['nationality'];
$birthplace = empty($_POST['birthplace']) ? null : $_POST['birthplace'];
$dob = empty($_POST['dob']) ? null : $_POST['dob'];
$study = empty($_POST['study']) ? null : $_POST['study'];
$status = empty($_POST['status']) ? null : $_POST['status'];
$kids = empty($_POST['kid']) ? null : $_POST['kids'];
$smoking = empty($_POST['smoking']) ? null : $_POST['smoking'];
$covered = empty($_POST['covered']) ? null : $_POST['covered'];
$hobbies = empty($_POST['hobbies']) ?  null : $_POST['hobbies'];

$params = [$nationality, $birthplace, $dob, $study, $status,
           $kids, $smoking, $covered, $hobbies];
$type_string = "sssssssss";

// USE ONE SQL THAT IF VARIABLE IS NULL, SET COLUMN EQUAL TO ITSELF (I.E., NO FILTER)
$from_section = 'app_female';
$sql_search = "SELECT id, name, nationality, birthplace, status, study
               FROM $from_section
               WHERE COALESCE(nationality, 'empty') = COALESCE(?, nationality, 'empty')
                 AND COALESCE(birthplace, '1900-01-01') = COALESCE(?, birthplace, '1900-01-01')
                 AND COALESCE(dob, '1900-01-01') = COALESCE(?, dob, '1900-01-01')
                 AND COALESCE(study, 'empty') = COALESCE(?, study, 'empty')
                 AND COALESCE(status, 'empty') = COALESCE(?, status, 'empty')
                 AND COALESCE(kids, 'empty') = COALESCE(?, kids, 'empty')
                 AND COALESCE(smoking, 'empty') = COALESCE(?, smoking, 'empty')
                 AND COALESCE(covered, 'empty') = COALESCE(?, covered, 'empty')
                 AND COALESCE(hobbies, 'empty') = COALESCE(?, hobbies, 'empty')
               ORDER BY id desc";

$stmt_search = mysqli_stmt_init($mysqli);
mysqli_stmt_prepare($stmt_search, $sql_search)
mysqli_stmt_bind_param($stmt_search, $type_string, ...$params);
...

A good rant in general but is does answer the question quite indirectly. And won't work if the OP would use LIKE on purpose. — Your Common Sense, Oct 13 '19 at 05:17
And, sorry for nagging, but i feel it rather important: usually an empty input is considered as "no filter" but your query would search for them as well. — Your Common Sense, Oct 13 '19 at 05:46
Understood @YourCommonSense, above adjusted for ternary operator on `empty` check. And it would help if OP answers any of our above comments. I have know many newcomers to SQL who misuse `LIKE`. If not, it seems the user enters wildcards on web form. — Parfait, Oct 13 '19 at 14:10

Where LIKE returns 0 with prepared statments

1 Answers1