Invision Power Services > 1 input field searches two columns?

Full Version: 1 input field searches two columns?

Invision Power Services > Community Forums > Community Web Design and Coding

Kenny Pollock

Mar 26 2006, 03:37 AM

I have columns in my table, first_name and last_name.
I want to use one input form field to search names.

Example:
In database, I have Kenny for first name, Pollock for last name.
In the input field, I type Kenny and Kenny Pollock is returned, I type Pollock and Kenny Pollock is returned, I type Kenny Pollock and Kenny Pollock is returned.

How would I go about doing that? What query would I use for this?

Thank you!

sbauer

Mar 26 2006, 05:45 AM

basic example:

select * from users where first_name like '%something%' or last_name like '%something%'

That will get you the first part. You could do a number of things to do the full name thing. (split, create a combined field, etc..)

Stewart

Mar 26 2006, 01:19 PM

SQL

select CONCAT(first_name, ' ', last_name) from users where first_name like '%something%' or last_name like '%something%'

is what I would do if it only needed to run on mySQL

Could also use CONCAT_WS(' ', first_name , last_name), result is the same..

Starnox

Mar 26 2006, 01:52 PM

You may wanna read up on fulltext searching.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Kenny Pollock

Mar 26 2006, 04:07 PM

QUOTE(Stewart Campbell @ Mar 26 2006, 08:19 AM)

SQL

select CONCAT(first_name, ' ', last_name) from users where first_name like '%something%' or last_name like '%something%'

is what I would do if it only needed to run on mySQL

Could also use CONCAT_WS(' ', first_name , last_name), result is the same..

Works great! Thank you!

Now, using that query I've run into a problem... I'm sure the answer is simple, I'm not just skilled enough when it comes to arrays to do what I want to.

I was using while( $r = mysql_fetch_array on that query and $r['first_name'] was giving me nothing nor was $r['last_name']... So I tested to make sure all the data was correct and that the query was being executed... then I did a print_r( $r ) and got:

CODE

Array ( [0] => Kenny Pollock [CONCAT(first_name, ' ', last_name)] => Kenny Pollock )

How can I grab data from that row? For example, I use the query you gave me Stewart and find myself, Kenny Pollock. Now I want my id in the table and the first name and the last name. Possible?

Starnox

Mar 26 2006, 04:09 PM

SQL

select CONCAT(first_name, ' ', last_name) as full_name from users where first_name like '%something%' or last_name like '%something%'

Now you can use $r["full_name"];

for an id called id just use

SQL

select CONCAT(first_name, ' ', last_name) as full_name, id from users where first_name like '%something%' or last_name like '%something%'

Now you can use $r["id"];

Stephen

Mar 26 2006, 04:09 PM

use the SQL

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where first_name like '%something%' or last_name like '%something%'

instead

<edit>Beat me too it

Kenny Pollock

Mar 26 2006, 05:17 PM

Ah you guys are great :-)

Kenny Pollock

Mar 26 2006, 06:16 PM

Hmm, Kenny works, Pollock works, but Kenny Pollock does not. Any ideas?

Phil Mossop

Mar 26 2006, 06:36 PM

See if this works:

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where (first_name, ' ', last_name) like '%something%'

Make sure you try it with Kenny, Pollock and Kenny Pollock. It might work, I'm not sure!

If it does work, you might like to try this for a neater looking query, Again, I'm not sure if it'll work:

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where name like '%something%'

Kenny Pollock

Mar 27 2006, 12:14 AM

QUOTE(Phil Mossop @ Mar 26 2006, 01:36 PM)

See if this works:

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where (first_name, ' ', last_name) like '%something%'

Make sure you try it with Kenny, Pollock and Kenny Pollock. It might work, I'm not sure!

If it does work, you might like to try this for a neater looking query, Again, I'm not sure if it'll work:

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where name like '%something%'

Neither

Stewart

Mar 27 2006, 12:19 AM

QUOTE(Kenny Pollock @ Mar 26 2006, 07:16 PM)

Hmm, Kenny works, Pollock works, but Kenny Pollock does not. Any ideas?

I think you need:

SQL

select CONCAT(first_name, ' ', last_name) AS name from users where CONCAT(first_name, ' ', last_name) like '%something%'

(Missing concat in the where)

Kenny Pollock

Mar 27 2006, 12:35 AM

Ah, works great- didn't know what CONCAT actually did.

Grant

Mar 27 2006, 01:21 AM

QUOTE(Kenny Pollock @ Mar 26 2006, 10:07 AM)

CODE

Array ( [0] => Kenny Pollock [CONCAT(first_name, ' ', last_name)] => Kenny Pollock )

How can I grab data from that row? For example, I use the query you gave me Stewart and find myself, Kenny Pollock. Now I want my id in the table and the first name and the last name. Possible?

If you are just going to return things like that all the time, when you are selecting the database, it might be a good idea to put MYSQL_ASSOC in the second clause. This will return the arrays just like above.
$r['first_name'] instead of $r['0'].

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.