Concatenate columns in a query to use 'LIKE' operator

Question

I am creating a search to search by name. In the database I have first name (fname) and surname (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 %s\n <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?

Possible duplicate of [String concatenation in MySQL](https://stackoverflow.com/questions/5975958/string-concatenation-in-mysql) — Ankit Bajpai, Sep 13 '18 at 15:05

Varun.Kumar · Accepted Answer · 2018-09-13T16:51:12.587

1

Replace this with your query:-

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

edited Sep 13 '18 at 16:51

answered Sep 13 '18 at 16:30

Varun.Kumar

172
1
1
11

score 0 · Answer 2 · answered Sep 13 '18 at 16:31

0

You can use the concatenation function refference :

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

answered Sep 13 '18 at 16:31

lagripe

766
6
18

Klanto Aguntuk · Answer 3 · 2018-09-13T18:19:41.630

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 %s\n <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 %s\n <br>", $fname, $lname);
}
/* close statement */
$stmt->close();
}

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

Thanks,

This works exactly the same way as my attempt, still doesn't fix the problem of searching for a full name. — cnrhgn, Sep 14 '18 at 07:48
You won't get search output for `fname` & `lanme` together without concatenating the database columns as those are two separate database columns. However, I just showed you how to use PDO statement as per standard documentation as your statement doesn't comply with standard PDO statement. You can see the manual for more details. Other users already gave you the answers about how to get desired result. Thanks, — Klanto Aguntuk, Sep 20 '18 at 06:57

Concatenate columns in a query to use 'LIKE' operator

3 Answers3