10

Anyone know how to combine PHP prepared statements with LIKE? i.e.

"SELECT * FROM table WHERE name LIKE %?%";

pirho
  • 11,565
  • 12
  • 43
  • 70
user29772
  • 1,457
  • 7
  • 21
  • 25

8 Answers8

29

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();
Dharman
  • 30,962
  • 25
  • 85
  • 135
Chad Birch
  • 73,098
  • 23
  • 151
  • 149
9

You can use the concatenation operator of your respective sql database:

# oracle
SELECT * FROM table WHERE name LIKE '%' || :param || '%'
# mysql
SELECT * from table WHERE name LIKE CONCAT('%', :param, '%')

I'm not familar with other databases, but they probably have an equivalent function/operator.

Richard Levasseur
  • 14,562
  • 6
  • 50
  • 63
  • That won't work with strings. It will throw warnings about non-integers. – St. John Johnson Mar 19 '09 at 15:42
  • He's probably coming from an Oracle background, that's the Oracle concatenation operator, but in MySQL it's the OR operator. In MySQL you'd have to do CONCAT('%', ?, '%') – Chad Birch Mar 19 '09 at 17:47
  • ah, noted! I haven't used mysql in years. – Richard Levasseur Mar 20 '09 at 04:01
  • NEVER trust any value you put in your sql, even if you really think it is. This is the most obvious solution, because no preparing of the value outside the SQL, so principle of using parameters to prevent sql injection remains. You MUST trust your SQL, not your values. Thanks for the solution! – Terradon Dec 31 '19 at 09:01
2

You could try something like this:

"SELECT * FROM table WHERE name LIKE CONCAT(CONCAT('%',?),'%')"
St. John Johnson
  • 6,590
  • 7
  • 35
  • 56
1

in PHP using MYSQLI you need to define a new parameter which will be declared as:

$stmt = mysqli_prepare($con,"SELECT * FROM table WHERE name LIKE ?");
$newParameter='%'.$query.'%';
mysqli_stmt_bind_param($stmt, "s", $newParameter);
mysqli_stmt_execute($stmt);

this works for me..

A.Kryeem
  • 56
  • 5
0

For me working great, I've looked for answer hours, thx.

    $dbPassword = "pass";
    $dbUserName = "dbusr";
    $dbServer = "localhost";
    $dbName = "mydb";

    $connection = new mysqli($dbServer, $dbUserName, $dbPassword, $dbName);

    if($connection->connect_errno)
    {
        exit("Database Connection Failed. Reason: ".$connection->connect_error);
    }
        $tempFirstName = "reuel";
    $sql = "SELECT first_name, last_name, pen_name FROM authors WHERE first_name LIKE CONCAT(CONCAT('%',?),'%')";
    //echo $sql;

    $stateObj = $connection->prepare($sql);
    $stateObj->bind_param("s",$tempFirstName);
    $stateObj->execute();
    $stateObj->bind_result($first,$last,$pen);
    $stateObj->store_result();

    if($stateObj->num_rows > 0) {
        while($stateObj->fetch()){
            echo "$first, $last \"$pen\"";
            echo '<br>';
        }
    }

    $stateObj->close();
    $connection->close();
zoladp
  • 121
  • 1
  • 6
0

I will just adapt Chad Birch's answer for people like me who are used to utilize bindValue(...) for PDO:

$st = $db->prepare("SELECT * FROM table WHERE name LIKE :name");
$st->bindValue(':name','%'.$name.'%',PDO::PARAM_STR);
$st->execute();
JacopoStanchi
  • 421
  • 5
  • 16
0

In SQL, you can do it like this using prepared statements.

SELECT * FROM TABLE_NAME WHERE (TABLE_COLUMN LIKE CONCAT('%', :SEARCH_TEXT, '%')) OR (ANOTHER_TABLE_COLUMN LIKE CONCAT('%', :SEARCH_TEXT, '%'))
Simon Ugorji
  • 45
  • 6
0

The sprintf can do it. Remember to put another % in front of the original % to escape it.

$ret = sprintf("SELECT * FROM table WHERE name LIKE %%%s%%", $name);

ENTITY 2 is 1
  • 3
  • 3