0

I'm attempting to use a SQL LIKE clause with mysqli prepared statements.

I have already tried other examples such as $sysName = "{$_POST['ss']}%"; and $sysName = $_POST['ss'] . '%';

if(isset($_POST['ss'])) {
   $sysName = $_POST['ss'] . '%';
      if(strlen($sysName) >0) {
         $qry = mysqli_stmt_prepare($link, "SELECT * FROM tblSchools WHERE systemName LIKE ?");
         mysqli_stmt_bind_param($qry,'s',$sysName);
         mysqli_stmt_execute($qry);
         $result = mysqli_stmt_get_result($qry);
      }
}

If $_POST['ss'] is populated with the word sys and there exists a systemName in tblSchools called 'system' then the result set should include the row information that pertains to the 'system' row. No matter what I put in there though the result always comes back null. My connection to the database is successful. I have tested with mysqli_query and just straight strings successfully, but when I switched to prepared statements on the LIKE clause it doesn't work. I've been beating my head against this problem for almost a full day now.

EDIT: In response to first answer Still doesn't work 

    $stmt = mysqli_stmt_init($link);
    $sysName = "sys%";
    if(strlen($sysName) >0) {
        if(!mysqli_stmt_prepare($stmt, "SELECT * FROM tblSchools WHERE systemName LIKE ?")) {
            echo "1";
            exit;
        } else {
            if(mysqli_stmt_bind_param($stmt,'s',$sysName)) echo "2";
            if(mysqli_stmt_execute($stmt)) echo "3";
            $result = mysqli_stmt_fetch($stmt);
            $row = mysqli_fetch_array($result);
            var_dump($row);
            echo "Hey";
        }
    }

Prints 2 and 3 not 1

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Vincent Jenkins
  • 57
  • 2
  • 9
  • When you say *the result always comes back null*, do you mean that `$result` is null? – Nigel Ren Aug 25 '19 at 18:11
  • $result is NULL, when I use mysqli_stmt_num_rows($qry) or mysqli_num_rows($result) both return blank – Vincent Jenkins Aug 25 '19 at 18:16
  • If the statement is UPDATE, DELETE, or INSERT, the total number of affected rows can be determined by using the mysqli_stmt_affected_rows() function. Likewise, if the query yields a result set the mysqli_stmt_fetch() function is used. see: https://www.php.net/manual/en/mysqli-stmt.execute.php – Manzolo Aug 25 '19 at 18:27
  • @AndreaManzi I have used mysqli_stmt_get_result before and had it work. I just attempted to plug in the suggested mysqli_stmt_fetch though and the results are the same. – Vincent Jenkins Aug 25 '19 at 18:31
  • What is $qry? `$result = mysqli_stmt_fetch($qry);` – Manzolo Aug 25 '19 at 19:30
  • i think you should change `$result = mysqli_stmt_fetch($qry);` with `$result = mysqli_stmt_fetch($stmt);` – Manzolo Aug 25 '19 at 19:33
  • @AndreaManzi Sorry I didn't update that line. It's fixed now and I get same results. – Vincent Jenkins Aug 25 '19 at 19:36
  • Since it is not succeeding on the mysqli_stmt_execute I'm assuming it has something to do with the SQL query being formatted incorrectly. – Vincent Jenkins Aug 25 '19 at 19:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198451/discussion-between-andrea-manzi-and-vincent-jenkins). – Manzolo Aug 25 '19 at 19:40

1 Answers1

1

Try this:

$link = \mysqli_connect("127.0.0.1", "user", "password", "dbname");

if (!$link) {
    $error = \mysqli_connect_error();
    $errno = \mysqli_connect_errno();
    print "$errno: $error\n";
    exit();
}

$query = "SELECT * FROM tblSchools WHERE systemName LIKE ?";

$stmt = \mysqli_stmt_init($link);
if (!\mysqli_stmt_prepare($stmt, $query)) {
    print "Failed to prepare statement\n";
    exit;
} else {
    $sysName = 'sys%';
    \mysqli_stmt_bind_param($stmt, "s", $sysName);

    \mysqli_stmt_execute($stmt);
    $result = \mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_array($result);
    var_dump($row);
}

it works for me

Manzolo
  • 1,909
  • 12
  • 13