Combine PHP Prepared Statments with Like

Combine PHP prepared statments with LIKE

The % signs need to go in the variable that you assign to the parameter, instead of in the query.

I don't know if you're using mysqli or PDO, but with PDO it would be something like:

$st = $db->prepare("SELECT * FROM table WHERE name LIKE ?");
$st->execute(array('%'.$test_string.'%'));

For mysqli user the following.

$test_string = '%' . $test_string . '%';
$st->bind_param('s', $test_string);
$st->execute();

Combine LIKE and IN in PHP MySQLi prepared statement

The like requires wildcards to have loose matching.

e.g.

select * from table where a like 'b'

is the same as:

select * from table where a = 'b'

so a record of b would be found but abc would not.

From the manual:

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

_ matches exactly one character.

So to find abc you'd use:

select * from table where a like '%b%'

For prepared statements the wildcards get appended to the variable, or in the binding, NOT in the query itself. Example 6 on the PDO manual page shows this. http://php.net/manual/en/pdo.prepared-statements.php#example-991 (after the comment // placeholder must be used in the place of the whole value)

Building Multiple LIKE Operator Prepared Statement at run-time

Consider building your LIKE expression portion of prepared SQL statement using implode. Then build a param arguments to be run with call_user_func_array().

$terms = explode(",", str_replace(",", " ,", $_POST['txtD']));

// PREPARED STATEMENT BUILD
$likes = [];
foreach($terms as $t) {
    $likes[] = "col3 LIKE CONCAT('%', ?, '%')";
}

$expr = implode(" or ", $likes);
$sql = "select col1, col2, col3 from tbl ".
       "where col1=? and col2 between ? and ? and (". $expr .")";

// PARAM ARG BUILD
$type = 'iii' . str_repeat("s", count($terms));
$sql_params = array_merge(array($stmt, $type, $param1, $param2, $param3), $terms);

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con, $sql);
call_user_func_array('mysqli_stmt_bind_param', sql_params);    
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

SQL and Param Build Demo

---

Alternatively, consider MySQL's REGEXP for a regex expression using pipes to denote OR logic:

// REPACE COMMAS BY PIPE
$terms = str_replace(",", "|", str_replace(",", " ,", $_POST['txtD']));

$sql = "select col1, col2, col3 from tbl " .
       "where col1=? and col2 between ? and ? and col3 regexp ?";

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con);    
mysqli_stmt_bind_param($stmt, "iii", $param1, $param2, $param3, $terms);
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

Do note as mentioned here REGEXP is slower in execution than the equivalent LIKE expression.

Correct way to use LIKE '%{$var}%' with prepared statements?

Try this

$likeVar = "%" . $yourParam . "%";
$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->bind_param("s", $likeVar);
$stmt->execute();

you need to prepare the query using simply ? then you bind the param using bind_param.

Concatenate query in MySqli prepared statement PHP

You can't prepare multiple queries. Instead, you could prepare the single query once and execute it multiple times:

$exList= $_REQUEST['exList']; 
$routineID = $_REQUEST['routineID'];
$arrayEx = explode(',', $exList);

$queryEx = 'INSERT INTO exercises_x_routines(exerciseID, routineID) VALUES(?,?)';
$stmt = $con->prepare($queryEx);
$stmt->bind_param('si', $ex, $routineID);
foreach ($arrayEx as $ex) {
    if (!$stmt->execute()) {
        $output[]=array("code" => 3003, "error" => $con->error);
        print(json_encode($output));
        $con->close();
        exit;
    }
}

If you want to effectively make this a single insert with no change to the table if any insert fails, you can use a transaction:

$con->beginTransaction();
foreach ($arrayEx as $ex) {
    if (!$stmt->execute()) {
        $output[]=array("code" => 3003, "error" => $con->error);
        print(json_encode($output));
        $con->rollBack();
        $con->close();
        exit;
    }
}
$con->commit();

How can I with mysqli make a query with LIKE and get all results?

Here's how you properly fetch the result

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id, username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "Id: {$row['id']}, Username: {$row['username']}";
}

or, if your PHP is critically outdated, you can also do:

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);

while ($stmt->fetch()) {
  echo "Id: {$id}, Username: {$username}";
}

I hope you realise I got the answer directly from the manual here and here, which is where you should've gone first.

---

Related Topics