Combine PHP prepared statments with LIKE

Viewed 51 times

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

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

Answers

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();

Saturday, September 3, 2022

clarence.hl.liu-2b2fc4836eaf

$stmt = $mysqli->stmt_init();

if($stmt->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)"))
{
   $stmt->bind_param('iss', $_id, $_name, $_type);
   for($i=0;$i<$limit;$i++)
   {
      $_id = $id[$i];
      $_name = $name[$i];
      $_type = $type[$i];
      $stmt->execute();
   }

}

should do it for you!

Sunday, November 20, 2022

devster101

The free_results statement tells the database engine it can release the result set.

When executing your statement, an iterator is created. The client (your app) iterates each result by either downloading them one by one or in chunk.

This allows you app to iterate millions of record without downloading all the results in one chunk.

EDIT

Free result will free memory on the client side. Useful if a single record is very large and memory needs to be freed.

See: http://php.net/manual/en/function.mysql-free-result.php

Friday, August 5, 2022

mzd

$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

Edit: code for CakePHP:

foreach($words as $word){
    $sql[] = array('Model.name LIKE' => '%'.$word.'%');
}

$this->Model->find('all', array(
    'conditions' => array(
        'OR' => $sql
    )
));

Read up on this stuff: http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions

Monday, November 28, 2022

jeff_peterson

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
    $term = trim($term);
    if (!empty($term)) {
        $searchTermBits[] = "bucketname LIKE '%$term%'";
    }
}

...

$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

Thursday, September 1, 2022

user6741253

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

sql-like

Combine PHP prepared statments with LIKE

Answers

php,sql-like