Asked  7 Months ago    Answers:  5   Viewed   21 times

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

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

 Answers

25

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.'%'));

EDIT :: For mysqli user the following.

$test_string = '%' . $test_string . '%';
$st->bind_param('s', $test_string);
$st->execute();
Wednesday, March 31, 2021
 
hillz
answered 7 Months ago
92

I solved like that:

    $tr_chars = array('ç','Ç','?','?','?','?','ü','ö','?','?','Ö','Ü'); 
    $safeName = str_replace($tr_chars, '_', $safeName);
Wednesday, March 31, 2021
 
michele
answered 7 Months ago
72

Is this thinking correct? fetch() for one, fetch_all() for many?

That's actually two different methods of two different objects.

  • fetch() belongs to mysqli statement object and uses ugly method of assigning query result to global variables. Can be run in a loop. Loop is a thing where you can do fetch one single row... many times. So, nothing contradicting in using fetch() to get many records
  • fetch_all() belongs to mysqli result object and being just a syntax sugar, running fetch_assoc() for you in a loop.
Saturday, May 29, 2021
 
jedwards
answered 5 Months ago
96

The underscore is also the same as in most other SQL databases and matches any single character (i.e. it is the same as . in a regular expression). From the fine manual:

An underscore ("_") in the LIKE pattern matches any single character in the string.

For example:

-- The '_' matches the single 'c'
sqlite> select 'pancakes' like 'pan_akes';
1
-- This would need '__' to match the 'ca', only one '_' fails.
sqlite> select 'pancakes' like 'pan_kes';
0
-- '___' also fails, one too many '_'.
sqlite> select 'pancakes' like 'pan___kes';
0

And just to make sure the results make sense: SQLite uses zero and one for booleans.

Thursday, July 29, 2021
 
Zulakis
answered 3 Months ago
22

Try this:

public function insert_data($array){
    $placeholders = array_fill(0, count($array), '?');

    $keys = $values = array();
    foreach($array as $k => $v) {
        $keys[] = $k;
        $values[] = !empty($v) ? $v : null;
    }

    $stmt = self::$mysqli->stmt_init();
    $query = 'INSERT INTO `'.DB_TABLE_PAGES.'` '.
             '('.implode(',', $keys).') VALUES '.
             '('.implode(',', $placeholders).')';
    $stmt->prepare($query);

    call_user_func_array(
        array($stmt, 'bind_param'), 
        array_merge(
            array(str_repeat('s', count($values))),
            $values
        )
    );

    $stmt->execute();
}

Or better yet, use PDO instead:

public function insert_data($array){
    $placeholders = array_fill(0, count($array), '?');

    $keys = $values = array();
    foreach($array as $k => $v){
        $keys[] = $k;
        $values[] = !empty($v) ? $v : null;
    }

    // assuming the PDO instance is $pdo
    $query = 'INSERT INTO `'.DB_TABLE_PAGES.'` '.
             '('.implode(',', $keys).') VALUES '.
             '('.implode(',', $placeholders).')';
    $stmt = $pdo->prepare($query);

    $stmt->execute($values);
}

Note: I've used the null constant because the "NULL" string will be escaped as a string (not as a null value).

Thursday, July 29, 2021
 
Sidarta
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :