LIKE in SQL is not working

Question

I'm using a PDO to run this query, is there a way to see if the query return false? and why my query return nothing when I expect a result?

                $selectsql = "SELECT `firstname`,`surname`,`phone`,`address`,`username`,`StudentID` FROM `student_info` WHERE ? LIKE ?";
                $selectstmt = $conn->prepare($selectsql);
                $selectstmt->execute(array($searchby,"%{$searchval}%"));
                while($data = $selectstmt->fetch(PDO::FETCH_ASSOC)){
                  echo "
                  <tr>
                    <td>{$data['firstname']}</td>
                    <td>{$data['surname']}</td>
                    <td>{$data['phone']}</td>
                    <td>{$data['address']}</td>
                    <td>{$data['username']}</td>
                    <td>Delete Account</td>                    
                  </tr>
                  ";
                  var_dump($data);
                }

I var dumpped the $searchby and the $searchval and they seems fine to me, no mistype or whatsoever.

you need to something like this LIKE 'name' you forget to put single quotas — krishn Patel, Apr 10 '17 at 08:15
always remember like is look like this '%searchval%' in a real query — Vijunav Vastivch, Apr 10 '17 at 08:17
Is it `"%'{$searchval}'%"` or `"'%{$searchval}%'"`? I tried both but nothing seems to work — Axis, Apr 10 '17 at 08:34
What is `$searchby` ? It *looks* like that should be a field name; and you can't bind fields as parameters. — CD001, Apr 10 '17 at 08:38

score 0 · Answer 1 · edited May 23 '17 at 12:10

There are several issues with your code.

The first and biggest issue is that you assume your query is correct, so you do not check for any sql errors. PDO provides extensive exception handling (see PDO Exception Questions - How to Catch Them question here on SO).

This would tell you that you cannot use a parameter to substitute for a field name in prepared statement. Why? Because the way prepared statements work is that MySQL compiles them and prepares their execution plan. If a field or table is not known at a compile time, then MySQL can't prepare the execution plan either. This means that you cannot have

WHERE ? LIKE ?

in a prepared statement, you must provide the field name in the query. So, you can have

WHERE address LIKE ?

But fix the exception handling first because that would enable you to get the actual error message.

score 0 · Accepted Answer · answered Apr 10 '17 at 10:05

The problem with your query is here :

$selectsql = "SELECT firstname`,`surname`,`phone`,`address`,`username`,`StudentID` FROM `student_info` WHERE ? LIKE ?";

That placeholder after where is messing up your query, after the where clause there should be a column name, by the way I see $searchby changes based on the item selected by the user. One thing you should note is that :

Table and Column names cannot be replaced by parameters in PDO.

Your query should look like.

<?php

$search = "%".$searchval."%";
$selectsql = "SELECT `firstname`,`surname`,`phone`,`address`,`username`,`StudentID` FROM `student_info` WHERE $searchby LIKE ?";
    $selectstmt = $conn->prepare($selectsql);
    $selectstmt->execute([$search]);
    $results = $selectstmt->fetch((PDO::FETCH_ASSOC));

    if(count($results) > 0){
      var_dump($results);
        foreach($results as $data):?>
          <tr>
              <td><?php echo $data['firstname'];?></td>
              <td><?php echo $data['surname'];?></td>
              <td><?php echo $data['phone'];?></td>
              <td><?php echo $data['address'];?></td>
              <td><?php echo $data['username'];?></td>
              <td>Delete Account</td>                    
        </tr>

      <?php
          endforeach;
    }else{

      echo "no results found";
    }
?>

LIKE in SQL is not working

2 Answers2