PHP MySQL search for name like %John D% in prepared statement

Question

I'm trying to find a list of people's names that match a search. My php:

require "accessControl.php";
require "sqlLink.php";

$string = "%dan%";

// create a prepared statement
if ($stmt = $link->prepare("SELECT full_name FROM (SELECT *, CONCAT(firstname," ",lastname) AS full_name FROM users) tmp WHERE full_name LIKE ?")) {

    $stmt->bind_param("s", $string);

    $stmt->execute();

    $stmt->bind_result($result);

    $stmt->fetch();

    $stmt->close();
}

echo $result;

This just gives a 500 error, but when I replace the query with "SELECT lastname FROM users WHERE firstname LIKE ?", it works fine. I spent over an hour searching for a solution, but I'm pretty confused.

I can't believe I did that! Anyway thank you, that should have occurred to me right off the bat — Danny Diehl, Feb 23 '18 at 03:55

Amit Merchant · Accepted Answer · 2018-02-21T08:45:33.730

3

Replace condition in your if with this:

$stmt = $link->prepare("SELECT full_name FROM (SELECT *, CONCAT(firstname,' ',lastname) AS full_name FROM users) tmp WHERE full_name LIKE ?")

Here, you just need to replace " " with ' ' in your CONCAT() function because that would break your entire string which also starts and ends with ".

edited Feb 21 '18 at 08:45

answered Feb 21 '18 at 08:30

Amit Merchant

1,045
6
21

Why should that solve the problem? What is wrong with this `if`? – Twinfriends Feb 21 '18 at 08:32
1

Well, if you check carefully that would certainly result in 500 error. – Amit Merchant Feb 21 '18 at 08:34
It's worth noting that **you should have mentioned** that the problem is that the double quotes within the concat terminate the PHP string and therefore produce a syntax error. Just dumping code as a solution may help OP but will not help anyone else. – apokryfos Feb 21 '18 at 08:38
@apokryfos If you can see I've clearly mentioned that at the end of the answer. – Amit Merchant Feb 21 '18 at 08:40
I don't know why I'm getting downvotes even though I've pointed out the exact issue. I better delete it. -_- – Amit Merchant Feb 21 '18 at 08:44
1

I downvoted because of the issue I mentioned but then you edited to correct it so I retracted the downvote. – apokryfos Feb 21 '18 at 08:47
@apokryfos Thanks for your courtesy! – Amit Merchant Feb 21 '18 at 08:49

score 2 · Answer 2 · answered Feb 21 '18 at 08:32

First don't use " " inside duoble quoted string .. becuase break the string continuity. second you could use concat for buil string with wilchar

$string = "dan";
$link->prepare("SELECT full_name FROM (
                SELECT *, CONCAT(firstname,' ',lastname) AS full_name FROM users
              ) tmp WHERE full_name LIKE concat( '%', ?, '%'))");

PHP MySQL search for name like %John D% in prepared statement

2 Answers2