Chris__T
|
| Posted: 01/16/2009, 12:09 PM |
|
Finally upgraded to MySQL 5.1, and switched my Access database over to MySQL.
Everything is running fine, and I was going to implement the MySQL searching
function that is like an advanced search. The one where you use
MATCH.....AGAINST...
http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
Has anyone implemented this successfully in a CCS created page? I figured I'd
have to edit some SQL in the visual query builder, but getting no such luck.
In the WHERE section I added my own (Condition Type - Expression)
MATCH
(Permit_No,Job_Site,Project_Type,Project_Desc,Owner_Name,Contractor_Firm_Name,Contractor_Name)
AGAINST ({s_Job_Site} IN NATURAL LANGUAGE MODE);
s_Job_Site being the textbox from the search form.
---------------------------------------
Sent from YesSoftware forum
http://forums.codecharge.com/
|
|
|
 |
Oper
Posts: 1195
|
| Posted: 01/19/2009, 5:08 AM |
|
Chris we havent tested 5.1 yes.
i guess we are wating a little bit so your expirience will be welcome :)
on the where issue do this:
on event before buildSelect
you could do this and see how it goes:
sender.where=" MATCH (col1,col2,...) AGAINST ( ' " & YourExpresion & " ' IN ......... ); "
let me know if not worked, i could install 5.1 later and test for you
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
 |
|
Chris__T
Posts: 339
|
| Posted: 01/19/2009, 8:15 AM |
|
Yeah, that just gave the good ol' "white screen"
sender.where = " MATCH (Job_Site, Contractor_Firm_Name, Permit_No) AGAINST ('" & {s_Job_Site} & "')"
I just used a few columns for testing purposes.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/19/2009, 9:40 AM |
|
I had to change sender.where to Permit1.Datasource.Where
Permit1.DataSource.Where = " MATCH (Applicant_Name, Contractor_Firm_Name, Contractor_Name, Owner_Name) AGAINST ('Homer Simpson')"
I stuck a static name (Homer Simpson) in there just to test. Still not working.
but this works as a test:
Permit1.DataSource.Where = " Applicant_Name = 'Homer Simpson' "
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/19/2009, 11:00 AM |
|
chirs what event are you using for this:
on BeoferBuildSelect you dont need the datasource. Property (cuase is already there)
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/19/2009, 11:21 AM |
|
Yeah, it's in the beforebuildselect. I changed it back to "sender.where" and put in my test line and it worked. Maybe CCS doesn't like MySQL query language? or is it just malformed code
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/19/2009, 11:34 AM |
|
I have it working properly in HeidiSQL (a mySQL front-end app). So must be something in the code that CCS doesn't like.
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/19/2009, 1:30 PM |
|
could you post here the all custom code onbeforebuildselect event?
also i get confused when you said you changed back to sender.where and it worked but the you said dont work.
explain pls :)
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/19/2009, 1:50 PM |
|
Regarding "changed back to sender.where":
You initially told me to try sender.where " MATCH............................
It wasn't working, and I tried Permit1.Datasource.Where with test code, which worked. Then I realized my test code also worked with sender.where when you told me I didn't need permit1.datasource since I was in the beforebuildselect. So currently, I am using sender.where with my test data:
sender.where = " Applicant_Name = 'Homer Simpson' "
And this works. It doesn't work when I plug in the mySQL stuff (MATCH..AGAINST.. .etc)
Here's all I have in my beforebuildselect event:
Sender.Where = " Applicant_Name = 'Homer Simpson' "
'response.Write sender.where
'response.end
'Sender.Where = " MATCH(Applicant_Name, Contractor_Firm_Name, Contractor_Name, Owner_Name) AGAINST('Homer Simpson')"
'response.Write sender.where
'response.end
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/19/2009, 6:35 PM |
|
could you as a test but this on the after execute select
response.write "Chris - END"
response.end
i think you are getting an error just you dont see
ps: i will install MySQL 5.1 tonight
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Oper
Posts: 1195
|
| Posted: 01/19/2009, 7:49 PM |
|
bahh! i though in 5.1 they added fulltext index to INNODB (No no no)
Are you using FULLTEXT index and MyISAM?
in Boolean Mode you dont need FULLTEXT index
(a little slow but depend in how big your table is)
anyway here is the explanation
data
FirstName LastName Representative
Joxx, Jose, Crazy
Juan Jose, Cosme, Cosme Joxx
select * from `test01` where match (Firstname,Lastname,Representative) AGAINST('juan' in boolean mode)
will bring 2nd
select * from `test01` where match (Firstname,Lastname,Representative) AGAINST('joxx' in boolean mode)
will bring Both
select * from `test01` where match (Firstname,Lastname,Representative) AGAINST('razy' in boolean mode)
will bring Nada
select * from `test01` where match (Firstname,Lastname,Representative) AGAINST('jo*' in boolean mode)
will bring both
also there is lot of limitation like:
* Excludes partial words
* Excludes words less than 4 characters in length (3 or less)
* Excludes words that appear in more than half the rows (meaning at least 3 rows are required)
* Hyphenated words are treated as two words
* Rows are returned in order of relevance, descending
Also the STOPWORD are not used
check link: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html
but it work.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/20/2009, 6:40 AM |
|
Wow! the 'in boolean mode' made the difference! From reading mySQL documentation at their website, I assumed just have your search criteria alone, ex. AGAINST ('keyword') would be a default that would work. didn't know I needed to add the 'in boolean mode'. but it worked! :)
Yes, my table was MYISAM as well. When I converted my tables from Access, it made them all InnoDB, so I converted them to MYISAM
Now that I have it working (Thanks to you Oper!) I just need to figure out how to get it to read from the search form (my textbox for the search form is s_Job_Site )
How do I make it read the keywords from my s_Job_Site?
Sender.Where = " MATCH(Applicant_Name, Contractor_Firm_Name, Contractor_Name, Owner_Name) AGAINST('" & {s_Job_Site} & "' in boolean mode)"
didn't work. Gives syntax error at the first &
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/20/2009, 10:57 AM |
|
i think s_job_site is an URL parameter:
Sender.Where = " MATCH(Applicant_Name, Contractor_Firm_Name, Contractor_Name, Owner_Name) AGAINST(" & dbconnection1.tosql(ccgetparam(s_job_site," "),ccsText) & " in boolean mode)"
i writed form scratch since not CCS here right now so maybe an error try this i will check whne i get back to office
if you get an error post again full line also full error.
dbconection1 mean to be you DB Connection
also check that i erase some single QUote in your String
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 01/20/2009, 2:49 PM |
|
I just had to add double quotes to s_job_site. Other than that , the code worked perfectly. 
Now I just have to read up on how to understand these searching techniques better.
Thank you Oper for the vast amount of time out of your schedule to help me with this (and my other issue).
They are both working perfectly now, and I can fine tune them and make life easier for several coworkers
|
|
|
|
Oper
Posts: 1195
|
| Posted: 01/20/2009, 3:48 PM |
|
Anytime Chris :)
Glad Everthing is Working!
Learn the MATCH sentence i may ask you abouit 
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
|
