PHP mysql. Trying to retrieve LIKE query using ->bind_param

Question

I am trying to do a search on my database where it returns results based on exact surnames and first names with the initial of the name entered in an input box. For instance, if the user enters Simon Burns it will return all users with the initial S for the first name and Burns for the Surname. Here is my code.

Sorry if this has been covered before but I cannot find a simple answer that works.

$firstname = filter_input(INPUT_POST, 'firstname', FILTER_SANITIZE_STRING);
$surname = filter_input(INPUT_POST, 'surname', FILTER_SANITIZE_STRING);

$prep_query = "SELECT id, firstname, surname, profilepic, Gender, StartYear, EndYear, CircaStart, CircaEnd, JnrHouse, SnrHouse
                   FROM people WHERE firstname LIKE ? AND surname = ?";
$namecheckresult = $connection->prepare($prep_query);

// Return all matches wether registered on not
if ($namecheckresult) 
    {
    $initial = $firstname[0];   
    $namecheckresult->bind_param( 'ss',  $initial . '%', $surname);
    $namecheckresult->execute();
    $namecheckresult->store_result();
    $namecheckresult->bind_result($id, $first, $last, $ProfilePic, $Gender, $StartYear, $EndYear, $CircaStart, $CircaEnd, $JnrHouse, $SnrHouse);

Erm, is there any error code either from PHP or MySQL being returned? — Max Senft, Mar 17 '18 at 22:11
From [manual](http://php.net/manual/en/mysqli-stmt.bind-param.php): " Binds **variables** to a prepared statement as parameters" (emphasis mine). Let me check if I can find a proper duplicate. — Álvaro González, Mar 18 '18 at 11:41

RiggsFolly · Answer 1 · 2018-03-18T00:32:50.613

-1

You have mixed up your 2 parameters

Change it to

$p1 = '%'.$initial;
$namecheckresult->bind_param( 'ss',  $p1 , $surname);

Or you may want to put the % on the front and back of $initial like this

$p1 = "%$initial%";
$namecheckresult->bind_param( 'ss',  $p1 , $surname);

edited Mar 18 '18 at 00:32

answered Mar 17 '18 at 22:26

RiggsFolly

93,638
21
103
149

Thanks for the prompt reply but that does nor work either. Here is the the error i get with that piece of code. ( ! ) Fatal error: Cannot pass parameter 2 by reference – Steve Beard Mar 17 '18 at 23:38
Ok, try that instead, I made a bit of a woopsy – RiggsFolly Mar 18 '18 at 00:33
Yes, thanks RiggsFolly worked it out myself eventually and is the same as your second answer. – Steve Beard Mar 18 '18 at 09:22

score -2 · Answer 2 · answered Mar 18 '18 at 00:04

-2

I have now worked out the answer.

changing $initial = $firstname[0];

to

$initial = "%$firstname[0]%";

works.

Hope this helps others.

answered Mar 18 '18 at 00:04

Steve Beard

1

Well, actually as written on http://php.net/manual/de/mysqli-stmt.bind-param.php the variable has to be a reference. So you may not build a string together in the argument section. A change of `$initial = $firstname[0]; $namecheckresult->bind_param( 'ss', $initial . '%', $surname);` to `$initial = $firstname[0].'%'; $namecheckresult->bind_param( 'ss', $initial, $surname);` should do the work ... – Max Senft Mar 18 '18 at 09:08
I tried the above you posted and that still throws an error. My solution does not and works. – Steve Beard Mar 18 '18 at 09:19

PHP mysql. Trying to retrieve LIKE query using ->bind_param

2 Answers2