CODEWITHSUNDEEP
phpmysqlmysqli
Bjaeg
Bjaeg

Reputation: 338

How to 'append' a mysql column onto another

I have built a search function within my website by utilizing the LIKE mysqli query.

I.e.

$contact = $mysqli->query("SELECT * FROM contact WHERE f_name LIKE '%$searching%' OR l_name LIKE '%$searching%'");

Lets say I have a contact

If I type 'bob' in my input field it will successfully find bob likewise if I type 'chan'.

What I want to be able to do is type 'bob chan' and have it locate the contact.

I assume there is some way to 'append' mysqli columns? Something like..

$contact = $mysqli->query("SELECT * FROM contact WHERE f_name.' '.l_name LIKE '%$searching%'");

What would be the correct syntax in this situation?

Upvotes: 1

Views: 86

Answers (3)

Jacob Lambert
Jacob Lambert

Reputation: 7679

While you can concat the fields in the database, you will run into issues if you do it that way. As an example, say you search bob chan using concat(f_name, ' ', l_name) LIKE %searching%, you would not get bobby chan in your result set since bob chan is not in bobby chan (you have by in the middle).

A better option would be to split the search string: $parts = explode(' ', $searching), then use your original filter of:

WHERE f_name LIKE '%$parts[0]%' OR l_name LIKE '%$parts[1]%'

As a side note, you will want to protect against injection by escaping the strings.

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

You can use CONCAT(str1,str2,...) function to join multiple values.

SELECT * FROM contact WHERE concat( f_name, ' ', l_name ) LIKE '%$searching%'

Documentation Reference:

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

CONCAT() returns NULL if any argument is NULL.

mysql> SELECT CONCAT('My', 'S', 'QL');
       'MySQL' 
mysql> SELECT CONCAT('My', NULL, 'QL');
       NULL 
mysql> SELECT CONCAT(14.3);
       '14.3'

For quoted strings, concatenation can be performed by placing the strings next to each other:

 mysql> SELECT 'My' 'S' 'QL';
        'MySQL'

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

The expression to do string concatenation in MySQL would use the CONCAT function, e.g.

  CONCAT(f_name,' ',l_name)

Upvotes: 0

Related Questions