...

View Full Version : Search in more than one field won't work...



Dhuan
04-17-2009, 03:36 AM
SELECT * FROM persons WHERE name AND last_name LIKE '%$_POST[search]%'


So, I made the search box which is used to search for names, what i want to do is, make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.

BTW, i'm using PHP too...

oesxyl
04-17-2009, 04:29 AM
SELECT * FROM persons WHERE name AND last_name LIKE '%$_POST[search]%'


So, I made the search box which is used to search for names, what i want to do is, make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.

BTW, i'm using PHP too...
- you must specifiy the condition for both name and last_name, only name is not a expression
- quote keys in arrays, is $_POST['search']
- don't pass POST data to mysql without checking before, is a bad idea.
- don't fetch *, usualy you don't need all fields from that table



$mysearch = $_POST['search'];
$query = "select * from persons where name like '%" . $mysearch . "%' and last_name like '%". $mysearch . "%'";


best regards

Old Pedant
04-17-2009, 06:19 AM
Right on.

Excepting only that, if you read his message, and not his bogus SQL, you find that what he *REALLY* needs is an OR, not an AND.

make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.
It wouldn't make sense to look for "JON" in both the first name and last name of "Mary Jones"; it would make sense to look for "JON" in *either* the first OR last name.

So:


$mysearch = $_POST['search'];
... should have code here for sanitizing $mysearch...
$query = "select * from persons "
. "where name like '%" . $mysearch . "%' "
. " OR last_name like '%". $mysearch . "%'";

oesxyl
04-17-2009, 06:35 AM
Right on.

Excepting only that, if you read his message, and not his bogus SQL, you find that what he *REALLY* needs is an OR, not an AND.
you could be right, but I'm not sure. That "not only in" could mean an 'and'.
Anyway both are assumption so is a good idea that you rise this problem. Op could this way think what realy want. :)


It wouldn't make sense to look for "JON" in both the first name and last name of "Mary Jones"; it would make sense to look for "JON" in *either* the first OR last name.
you assume that is english, :) I don't think this apply to any country, :)

best regards.

Old Pedant
04-17-2009, 08:19 AM
Yes, and his fields are "Name" and "last_name", not "first_name" and "last_name". I was just going with the odds, I admit it.

Seems to be what he wanted.

Dhuan
04-17-2009, 06:16 PM
The 'OR' command was what i nedeed, thank you guys!
Actually, I had tried 'OR' before, but in a wrong way:



SELECT * FROM persons WHERE name OR last_name LIKE '%$_POST[search]%'


that, as you may have expected, didn't quite work...

then, after reading your posts...


$search=$_POST['search'];

$query="SELECT * FROM persons WHERE name LIKE '%$search%' OR last_name LIKE '%$search%'";


:D


Yes, and his fields are "Name" and "last_name", not "first_name" and "last_name". I was just going with the odds, I admit it.

Seems to be what he wanted.

hmm... the 'name' field is for first names only... should I name it first name then(though i dont think its necessary)?


- don't fetch *, usualy you don't need all fields from that table

Is there any risk in doing so? Will the page load slower or something like that?

Old Pedant
04-17-2009, 08:47 PM
hmm... the 'name' field is for first names only... should I name it first name then(though i dont think its necessary)?
Doesn't matter at all so long as YOU know what it all means. You could use "foo" and "bar" to mean address and zip code and it wouldn't matter.



- don't fetch *, usualy you don't need all fields from that table
Is there any risk in doing so? Will the page load slower or something like that?

No risk. Could things run slower? Yes, certainly.

Example: Say you were getting 1000 records and each record had an image in it so that the average record size was maybe 200KB, then you just transferred 200MB of data from the DB server to the Web server. If all you really needed was the name of each image--maybe 30 bytes each?--for a total of 30KB, then you just transferred 199.97 extra megabytes.

And, I will point out that your query *COULD* return a *MASSIVE* amount of data. If you have (say) 10,000 people in that table and somebody enters a search term of "e", then you will be returning a record for every person who has the letter "e" in his/her first or last name.

If you were only getting 1 or 2 records, the SELECT * wouldn't be such a big deal. But with this query, yes, I'd be careful about only getting the fields you actually will be using.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum