Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

MYSQL Query help?

E

e7e

macrumors member
Original poster
Dec 15, 2007
53
0
Not sure if I am posting in the right section, but here it goes.

I am currently trying to learn MYSQL, trying various task using PHP and javascript. So I created a search function on my web-site. My Person table looks like this:

Code: 
ID
fname
lname

And when the user is searching for a person, he can input first name or last name in the search fiend and that works fine. The problem occurs when the user is typing the full name of the person, like for instance "Steve Jobs".

My SQL query code looks like this:
Code: 
SELECT * FROM person WHERE fname LIKE "%$QueryString%" OR lname LIKE "%$QueryString%"

The problem is, if I input full name, I wont get anything. For example if I type "Steve Jobs" I wont get a single result, even though I know it exists in the database.

So can anybody help me with this SQL query?
 
plinden

plinden

macrumors 601
Apr 8, 2004
4,029
142
So you're saying if you enter "Steve Jobs", your query is:
Code: 
SELECT * FROM person WHERE fname LIKE "%Steve Jobs%" OR lname LIKE "%Steve Jobs%"

You don't see why it's failing?
 
Cabbit

Cabbit

macrumors 68020
Jan 30, 2006
2,128
1
Scotland
Try splitting the words, if your sending it Steve Jobs then it is looking for a match of "Steve Jobs" on first name and last name.

What your wanting to do it split "Steve Jobs" into "Steve" and "Jobs" and search for them individually.
 
E

e7e

macrumors member
Original poster
Dec 15, 2007
53
0
plinden said:
So you're saying if you enter "Steve Jobs", your query is:
Code: 
SELECT * FROM person WHERE fname LIKE "%Steve Jobs%" OR lname LIKE "%Steve Jobs%"

You don't see why it's failing?
Click to expand...

I see why its failing, because it tries to search for "Steve Jobs" in fname and lname, I am just not sure how to write a proper query this. I want to somehow combine the fname+lname.

So I can perhaps do something like: Query LIKE %FNAME+LNAME%
 
jaikob

jaikob

macrumors 6502
Jul 1, 2008
429
0
Freeland, MI
Code: 
$query = "John Doe";

$mysql_query = "SELECT *, UPPER(CONCAT(fname,' ',lname)) as fullName, UPPER(fname) as firstName, UPPER(lname) as lastName FROM person HAVING firstName LIKE '%".$query."%' OR lastName LIKE '%".$query."' OR fullName LIKE '%".$query."%'";

$result = mysql_query($mysql_query) or die(mysql_error());

Try this. The variable $query is your search variable.

What it does is selects all records, converts first name and last name to upper case, giving them each their own sql variable, and then combining them creating a new variable called fullname. So it's basically searching for a match in three different ways.
 
E

e7e

macrumors member
Original poster
Dec 15, 2007
53
0
jaikob said:
Code: 
$query = "John Doe";

$mysql_query = "SELECT *, UPPER(CONCAT(fname,' ',lname)) as fullName, UPPER(fname) as firstName, UPPER(lname) as lastName FROM person HAVING firstName LIKE '%".$query."%' OR lastName LIKE '%".$query."' OR fullName LIKE '%".$query."%'";

$result = mysql_query($mysql_query) or die(mysql_error());

Try this. The variable $query is your search variable.

What it does is selects all records, converts first name and last name to upper case, giving them each their own sql variable, and then combining them creating a new variable called fullname. So it's basically searching for a match in three different ways.
Click to expand...

Fantastic, so this is how you solve it. Thanks alot :D
 
S

stomer

macrumors 6502a
Apr 2, 2007
608
1
Leeds, UK
jaikob said:
Code: 
$query = "John Doe";

$mysql_query = "SELECT *, UPPER(CONCAT(fname,' ',lname)) as fullName, UPPER(fname) as firstName, UPPER(lname) as lastName FROM person HAVING firstName LIKE '%".$query."%' OR lastName LIKE '%".$query."' OR fullName LIKE '%".$query."%'";

$result = mysql_query($mysql_query) or die(mysql_error());
Click to expand...

That query is grossly inefficient. If the person table ever has a non-trivial amount of records, lets say a million records, then that query will be guaranteed to have serious performance issues.

- You're asking MySQL to retrieve *every single* record from person and so MySQL will not be able to use any indexing to speed up the query.
- If person has a million records, then you're asking MySQL to perform 3m UPPER()s, 1m CONCAT()s and 3m LIKEs!!!
- Even if person doesn't have lots of records, you're still asking MySQL to do more work than it needs to. If your site/app usage results in a non-trivial number of name searchs, then your site/app could grind to a halt.

If you want to perform natural language searches on MySQL table, then MySQL provides a certain type of index for that. http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

Code: 
ALTER TABLE `fullname` ADD FULLTEXT (fname,lname);
SELECT * FROM person WHERE MATCH(fname,lname) AGAINST ('foo bar');

I've not tested the above, but it's the direction where you should be heading. My main point is, only ever use LIKEs on small resultsets, you definitely DO NOT want to be performing likes on all the records in a table.
 
jaikob

jaikob

macrumors 6502
Jul 1, 2008
429
0
Freeland, MI
stomer said:
That query is grossly inefficient. If the person table ever has a non-trivial amount of records, lets say a million records, then that query will be guaranteed to have serious performance issues.

- You're asking MySQL to retrieve *every single* record from person and so MySQL will not be able to use any indexing to speed up the query.
- If person has a million records, then you're asking MySQL to perform 3m UPPER()s, 1m CONCAT()s and 3m LIKEs!!!
- Even if person doesn't have lots of records, you're still asking MySQL to do more work than it needs to. If your site/app usage results in a non-trivial number of name searchs, then your site/app could grind to a halt.

If you want to perform natural language searches on MySQL table, then MySQL provides a certain type of index for that. http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

Code: 
ALTER TABLE `fullname` ADD FULLTEXT (fname,lname);
SELECT * FROM person WHERE MATCH(fname,lname) AGAINST ('foo bar');

I've not tested the above, but it's the direction where you should be heading. My main point is, only ever use LIKEs on small resultsets, you definitely DO NOT want to be performing likes on all the records in a table.
Click to expand...


I'm sure he isn't going to be storing 1 million people. I'm also sure you won't even be storing that much data to notice any problems. :rolleyes:
 
S

stomer

macrumors 6502a
Apr 2, 2007
608
1
Leeds, UK
jaikob said:
I'm sure he isn't going to be storing 1 million people. I'm also sure you won't even be storing that much data to notice any problems. :rolleyes:
Click to expand...

It probably wont take 1 million records to slow down his site, mostly likely at lot less. 1 million was just an example.

Yes, he'll most likely get away with it, but sometime down the line, he'll be caught out. Since it's only 2 lines of code, you might as well do it right from the beginning.

If any of my developers wrote such code, they'd get a slap around the face.
 
E

e7e

macrumors member
Original poster
Dec 15, 2007
53
0
stomer said:
That query is grossly inefficient. If the person table ever has a non-trivial amount of records, lets say a million records, then that query will be guaranteed to have serious performance issues.

- You're asking MySQL to retrieve *every single* record from person and so MySQL will not be able to use any indexing to speed up the query.
- If person has a million records, then you're asking MySQL to perform 3m UPPER()s, 1m CONCAT()s and 3m LIKEs!!!
- Even if person doesn't have lots of records, you're still asking MySQL to do more work than it needs to. If your site/app usage results in a non-trivial number of name searchs, then your site/app could grind to a halt.

If you want to perform natural language searches on MySQL table, then MySQL provides a certain type of index for that. http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

Code: 
ALTER TABLE `fullname` ADD FULLTEXT (fname,lname);
SELECT * FROM person WHERE MATCH(fname,lname) AGAINST ('foo bar');

I've not tested the above, but it's the direction where you should be heading. My main point is, only ever use LIKEs on small resultsets, you definitely DO NOT want to be performing likes on all the records in a table.
Click to expand...

Thanks for this tip, as mention I am only a beginner at MYSQL, I appreciate tips like these. :)
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom