Prepared statements: Can't wrap my head around binding arrays

[system]

Hi everyone.

I wrote the following, which is a simple search, and which works.

[php]$searchTerm = ‘%’ . $searchTerm . ‘%’;
if ($stmt = $conn->prepare("
SELECT SQL_CALC_FOUND_ROWS id, title, description, keywords FROM images
WHERE title LIKE ?
OR description LIKE ?
OR keywords LIKE ?
ORDER BY createddate DESC
LIMIT “.$limit.” OFFSET “.$offset.”
")) {

$stmt->bind_param(“sss”,$searchTerm,$searchTerm,$searchTerm); // bind_param: i - integer, d - double, s - string, b - blob[/php]

The problem is that it searches by the full string only. I want to explode $searchTerm into separate words and bind them, so, no matter what the position or in what column the word is, as long as they all present somewhere.

I tried to bind the array I created from $searchTerm string with call_user_func_array, but I couldn’t make it work.

[ErnieAlex]

Well, just use different search terms…

Instead of : ,$searchTerm,$searchTerm,$searchTerm);

use something like : ,$searchTerm1,$searchTerm2,$searchTerm3);

And use three DIFFERENT terms… Should work for you. Your code was correct, but, just looking for the same
term each time does not work…

[Deanbear]

The above code works fine. $searchTerm is used 3 times to search the same words in title, description, and keywords.

My question is how do I bind an array with dynamic parameters?

I need to explode $searchTerm and I need to bind the array.

Let’s say:

$searchTerm = ‘Old black cat on the roof’

$separateWords = explode(" ", $searchTerm);

Now I have an array $separateWords with unknown number of words (because it is a user input).

I need to bind them to my query to search within title, description, and keywords.

[benanamen]

Give this a shot…

[php]$separateWords = explode(" ", $searchTerm);
$sql="SELECT * FROM images WHERE ";// Leave space
$i=0;
foreach($separateWords as $v)
{
$v=trim($v);
if($i==0)
{
$sql.=“title LIKE ‘%$v%’ OR description LIKE ‘%$v%’ OR keywords LIKE ‘%$v%’”;
}
else
{
$sql.=“title LIKE ‘%$v%’ OR description LIKE ‘%$v%’ OR keywords LIKE ‘%$v%’”;
}

$i++;
}[/php]