Concatenate columns in a query to use 'LIKE' operator

I am creating a search to search by name. In the database I have first name (fname) and surname (lname).

fname

lname

The search works when you search by either first name or last name but not the full name.

e.g Entering 'Joe' or 'Smith' as the value will bring back 'Joe Smith'. Entering 'Joe Smith' as the value will bring back no records.

$value = "%".$search_val."%";
$query = "SELECT fname, lname FROM users WHERE (fname LIKE ? OR lname LIKE ?)";

if ($stmt = $conn->prepare($query))

$stmt->bind_param('ss', $value, $value);
$stmt->execute();
$stmt->bind_result($fname, $lname);
/* fetch values */
while ($stmt->fetch())
printf ("%s %sn <br>", $fname, $lname);

/* close statement */
$stmt->close();


/* close connection */
$conn->close();
?>


Is there a way to concatenate fname and lname within the query? Or is there another solution?

fname

lname

WHERE CONCAT(f_name, ' ', l_name) LIKE ?

3 Answers
3

Replace this with your query:-

select concat(fname, ' ',lname) as name
from users
where name LIKE ? ;


You can use the concatenation function refference :

SELECT fname, lname
FROM users
WHERE CONCAT(fname, ' ', lname) LIKE ?


It's not clear whether you are using $_GET, $_POST or $_REQUEST method to get the search query. You also binded your result which I've never seen before in any standard pdo statement. You made your PDO statement complex. However, you can try the following:

<?php
//With positional placeholder & bindParam method

$search_val = $_REQUEST['search_val'];
$value1 = "%$search_val%";
$value2 = "%$search_val%";


$query = "SELECT fname, lname FROM users WHERE fname LIKE ? OR lname LIKE ?";

if ($stmt = $conn->prepare($query))
$stmt->bindParam(1, $value1);
$stmt->bindParam(2, $value2);
$stmt->execute();

/* fetch values */
while ($stmt->fetch())
printf ("%s %sn <br>", $fname, $lname);

/* close statement */
$stmt->close();


/* close connection */
$conn->close();
?>


<?php
//With named placeholder & bindValue method

$search_val = $_REQUEST['search_val'];
$value = "%$search_val%";


$query = "SELECT fname, lname FROM users WHERE fname LIKE :value OR lname LIKE :value";

if ($stmt = $conn->prepare($query))
$stmt->bindValue(':value', $value);
$stmt->execute();

/* fetch values */
while ($stmt->fetch())
printf ("%s %sn <br>", $fname, $lname);

/* close statement */
$stmt->close();


/* close connection */
$conn->close();
?>


Thanks,

fname

lanme

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.