How to SELECT something (CONCAT) and search the field
Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
select first_name, lastname from user where first_name like '%$user%' or last_name like '%$user%' ; --- Matt Babineau [EMAIL PROTECTED] wrote: Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,' ',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
sorry for the first select (bad copy of a string 'lastname'):
mysql select concat(firstname,' ',lastname) from names where concat(firstname,'
',lastname) like 'Jean Dupond%';
++
| concat(firstname,' ',lastname) |
++
| Jean Dupond|
++
1 row in set (0.00 sec)
mathias
Selon [EMAIL PROTECTED]:
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
yes in therory. But practicaly, you always have business rules and data
knowledge without what you can do nothing.
so the substring must be constructed according to data.
Mathias
Selon Ben Kutsch [EMAIL PROTECTED]:
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
I do this all the time and it works flawlessly. Just like your example and even
more extreme. I use this technique to provide search mechamisms for my
applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like
'%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible
that the table name is being confused with the 'user' table in the MySQL
database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
While the query WHERE expr LIKE '%$user%' works without fail, it can
not use an index, and thus on large tables will be exceedingly slow.
mysql explain select last, first from users where concat(last,first)
like '%user%'\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.01 sec)
Therefor, where it's possible, I would suggest adding a fulltext index
on (first, last), or what ever columns you need. This will handle the
concatenation of these two columns inside the index :)
mysql alter table users add fulltext index (first, last);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
Then use the query WHERE MATCH expr AGAINST ('%$user%').. like this...
mysql explain select first, last from users where match (first,last)
against ('%user%')\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: fulltext
possible_keys: first_2
key: first_2
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.01 sec)
Hope that helps,
~Deva
Ed Reed wrote:
I do this all the time and it works flawlessly. Just like your example and even more extreme. I use this technique to provide search mechamisms for my applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like '%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible that the table name is being confused with the 'user' table in the MySQL database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
