ok here's my problem i have a database and im now starting it in mysql then i will include the php part. i just want my query to work. i have an MYISAm table and i have created a full text index inculding columns company_name, address, city, state, country, phone_number, contact, sic, fax_number. the thing is that when i try to do a simple query like *correct this statement if im wrong and i dunno if i have to turn on something or the other i did as i stated above nuthin else im new to mysql* anyway the query is SELECT city from testTable where match (city, country) against('REGINA');//where REGINA is a city in the table. PHP: it tells me CANT find FULLTEXT index matching the column list PHP: .?? i indexed it but why is it still giving me this ?? any help from you guys is greatly appreciated. thank you in advance
not sure. but it could happen if you fail to define a fulltext index like FULLTEXT(city,state,country) in your table definition.
You must create a fulltext index across the columns city and country only. You can't use any other columns in the index.
vishwaa***i defined like 8 different fields but i only want to use those like to try out as an example, do i need to state all the fields of the full text index? mad4** lets just say that i had only wanted a full text search for city and country do i have to define a full text search for those two alone?? basically the same question as the first.... wwm** which .ini file? for mysql or php? because i didnt start the php part yet im just trying to make sure this works in mysql first. thank you can you please elaborate on this i dont fully understand **everyone- thank you for your help so far its really appreciated.
Yes. You have to define the index across the exact same columns you want to search acrosss. Otherwise it fails.
oops i meant my.cnf (its my.ini on windows servers) its the configuration for mysql on linux servers usually located in /etc/my.cnf you neet to add to my.cnf thi line ft_min_word_length=2 Code (markup): and restart mysql deamon otherwise mysql doesnt search words smaller than 4characters (which could be a pain if someone want to search for "php" lets say) google for this youll get more info than i can provide from the top of my head also the u will neeed to create a full text index
it says empty set . ihave data inside my database though.. and i changed the word size to 3 and i created an index for city and country alone and ran this statement.. ALTER TABLE indexing FULLTEXT(city, country); Code (markup): select city from indexing where match(city, country) against('burdin');//made up city mind you. Code (markup): it gives me empty set any ideas?
well i tried this on a much smaller table with only 4 columns and like 5 entries but still it wokred fine whhen i had searched for a record. that was a testtable now im trying the real thing with only these two columns and now it dont work. i dont know why.
ALTER TABLE indexing ADD FULLTEXT(city, country); Code (markup): thats how i did the index for the two and the query is lited above. i use mysql front to view it and the fulltext index is there. is there something additional that i forgot?
Sample code for you. This code will create a fulltext index when you create the table. Following code will create fulltext index in the existing table If you want to drop the existing fulltext index and redefine, then this might help. When you have created the fulltext index, you will be shown like: It doesnt matter how many records are there in table to create the index. Hope this helps.
THANK YOU IT WORKS.......but i didnt had to create back the table thank god. but thank you guys. this was a very big help....no wi have to go onto the php part.. hope i can get some input(help here too if i need any)