i am trying to get this little search to work by searching more than one column. with one column it works great like this $query = $db->query ("SELECT s_last FROM table_student WHERE s_last LIKE '$queryString%' LIMIT 10"); Code (markup): but when i try to look for two my union is not correct. $query = $db->query ("SELECT s_last FROM table_student WHERE s_last LIKE '$queryString%') UNION (SELECT s_city FROM table_student WHERE s_city LIKE '$queryString%'") ; Code (markup): can anyone tell me what i am doing wrong here? thank you
i did not get purpose. the union query is same except that they are fired on 2 different fields. I believe you want to search particular search term in either s_last or s_city field. I would put query as follows: $query = $db->query ("SELECT s_last, s_city FROM table_student WHERE s_last LIKE '$queryString%' or s_city LIKE '$queryString%'"); PHP:
Yes you are right thats exactly what i wanted. YOU ABSOLUTELY ROCK!! now if only someone could tell me how display that correctly !! echo '<li onClick="fill(\''.$result->s_last.'\');">'.$result->s_last.'</li>'; HTML: this show the s_last but does not show the city i would like it to show both s_last and s_city
how about this? echo '<li onClick="javascript:fill(\'' . $result->s_last . '\');">' . $result->s_city . '</li>'; PHP:
no that did not work, now it only returns the city. i need both to show both. echo '<li onClick="fill(\''.$result->s_last.'\',\''.$result->s_city.'\');">'.$result->s_last.','.$result->s_city.'</li>'; HTML: One more question if you do not mind. this code you gave me "SELECT s_last, s_city FROM table_student WHERE s_last LIKE '$queryString%' or s_city LIKE '$queryString%'" Code (markup): if i wanted to add another feild would it be like this ? "SELECT s_last, s_city, s_id FROM table_student WHERE s_last LIKE '$queryString%' or s_city LIKE '$queryString%' or s_id LIKE '$queryString%'" HTML: thanks again for your help! i was kinda starting to lose my mind.
like query on many fields with or condition might lower the performance. execute a query only if it is needed and give priorities to the fields using which you can break this query in 3 parts and execute them in sequence only if previous one does not give enough of records.
I'm sorry not totally understanding what you are saying there. I know Like and or take a performance hit. I need to search on 5 columns. So is there a better way for me to go about this. Basically I just need to find these student by last name or DOB or phone # ect. Then after I found that student I just want to do a search on that student id to get the full records of that student.
how many entries are expected inside this table? if they are just few thousands then you can go ahead with like query, it won't make a big difference..
Well it is not going to be that big. No more than 800 entires at start then grow 300 to 600 entries a year. Although, I would like to learn the right way to do it if you could point me in the right direction. Or show me an example of what the best way to go about this is. I just would like to have a nice "google like" search on my database.
well, for such small data set you need not worry about anything.. by the time your data reaches marginal big size it will be 20 years as far as best solution for this problem is concerned, it still depends on how the search form is made.
I am using http://nodstrum.com/2007/09/19/autocompleter/ , a example that someone suggest in this forum. I used it because it was simple and very well explained.
wait a minute.. you did all these for autosuggest?? you could have told this earlier. For autosuggest its always better to search on logical field. Its not so good to have autosuggest on name and city together. Rather give a drop down of what field to search on and execute specified field queries only. phew!!
search on logical field? what do you mean by that, sorry i know a stupid question. i just want a auto suggest that will narrow down a field (row) as i type. so how is the best way to go about this. sorry i know all stupid questions.
what i mean is, do not make autosuggest appear on all the fields together. Let them choose between search by name - last name or city or whichever fields you wish. Then after they select the field, fire a query based on that field only.
so your saying something like this http://mattberseth.com/blog/2007/12/creating_a_google_suggest_styl.html . If only I knew how to covert that to php mysql!!!
It shouldnt be too difficult to have the ajax for the autocomplete also pass a dropdown value to the php script which would allow it to select the appropriate table as mastermunj suggested.
samsung88. Yes, it should be similar to the link you have provided. There should be a drop-down list for fields on which user can make search and a text box to enter the search term. While doing ajax request, you will have to send both the details to server and php script at server will decide which query to execute based on the 2 input parameters.
ok, lets go step by step. consider your page as a book's home page which allows you to reach a particular page using index. However your book is special and has multiple indexes. One is based on article, other is based on author for that article and so on. Now when you use index on article, it shows you list of articles. when you use index on author, it shows you list of authors who wrote article in that book. similar way.. coming back to your problem.. when someone wants to search a student, following are searching indexes. 1. first name 2. last name 3. batch 4. city and so son... now what you do is? give two fields on your page where you wish to keep autosuggest. 1. select the index (field on which to search) 2. text box for writing search text. when you send ajax request to php script, pass field name & search text. at php level, take both inputs and see which field user has specified for searching. based on the field prepare a query and execute it and return back the output. hope this enlightens your idea about autosuggest phew!!