Using array in combination with single elements in prepared PDO mysql query

Question

I finally managed to figure this out, see my answer.

The code below should return entries in a mysql database based on presence of all search terms across two fields, as well as match on a max distance from the client. This concept worked well before I turned it into PDO, and in fact I got it working with PDO where I had temporarily left out everything after username = pb in the mysql query, and had only $query->execute($searcharray); in the execution. But when adding the rest, I get no results if using one search word, and if more than that I get:

Response from server: Error: SQLSTATE[HY093]: Invalid parameter number

Here´s the code:

$searcharray = explode(',', $search);
$tests = array_map(function($word) 
{
return "CONCAT(searchmessage, subject) 
LIKE ?";
}
 , $searcharray);
 $where = implode(' AND ', $tests);
 function addPercentage(&$value,$key)
 {
 $value = "%".$value."%" ;
 }
array_walk($searcharray,"addPercentage");
$query=$conn->prepare("SELECT 
username,sender,message,subject,timestamp,threadid,msgtype FROM Messages  
WHERE $where AND username ='pb'  AND 
(6371 * acos (cos ( radians(?) )
* cos( radians( Messages.latitude ) )
* cos( radians( Messages.longitude ) - radians(?) )
+ sin ( radians(?) )
* sin( radians( Messages.latitude ) )
))
< ?
ORDER BY timestamp");
$query->execute (array($searcharray,$latitude,$longitude,$latitude,$visibledistance));
$sth = $query->fetchAll(PDO::FETCH_ASSOC);

I have the feeling that the (?) values in the original formula should look differently when in a PDO call, and that the way I execute it with $searcharray containing multiple values combined with the single values following it ($latitude...) is not right.

As requested, a little more detail. This code works:

$searcharray = explode(',', $search);
$tests = array_map(function($word) 
{
return "CONCAT(searchmessage, subject) 
LIKE ?";
}
 , $searcharray);
 $where = implode(' AND ', $tests);
 function addPercentage(&$value,$key)
 {
 $value = "%".$value."%" ;
 }
array_walk($searcharray,"addPercentage");
$query=$conn->prepare("SELECT 
username,sender,message,subject,timestamp,threadid,msgtype FROM Messages  
WHERE $where AND username ='pb' ORDER BY timestamp");
$query->execute($searcharray);
$sth = $query->fetchAll(PDO::FETCH_ASSOC);

So if $search = "tent,kat", $where will become

CONCAT(searchmessage, subject) LIKE ? AND CONCAT(searchmessage, 
subject) LIKE ?

And these two words will be in the $searcharray with % signs around them.

Any idea what I´m doing wrong?

It's hard to make heads or tails of the whole query because of the way you have the code broken up. Can you clean that up? — Jay Blanchard
– Jay Blanchard, Commented Jun 16, 2017 at 17:38
@Mihai First parameter "$where" results in one or multiple question marks with LIKE between them, corresponding to the content of $searcharray which are the search terms with % signs around them. After that there are four parameters, that corresponds to 4 question marks. — Corey Hart
– Corey Hart, Commented Jun 16, 2017 at 17:51

Corey Hart · Accepted Answer · 2017-06-17 00:58:53Z

1

How I solved this:

Instead of:

$query->execute 
(array($searcharray,$latitude,$longitude,$latitude,$visibledistance));

I now have:

array_push($searcharray,$latitude,$longitude,$latitude,$visibledistance);
$query->execute($searcharray);

I figured that if everything worked with one array that contained the search terms, it prolly would work as well if I added the remaining elements to the array.

answered Jun 17, 2017 at 0:58

Corey Hart

1931 gold badge2 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using array in combination with single elements in prepared PDO mysql query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related