Full text search for MS SQL Server

[dhoechst]

Am I correct that if you use SQL Server, there isn't full text search capability? has anybody changed the code to try and get full text? I'd rather not have to type in keywords.

Thanks,
Daniel

[Thorsten]

Hi Daniel,

due to the fact I do not have a MS SQL server, I couldn't tried the fulltext search function in the database class. What does not work exactly?

bye
Thorsten

[dhoechst]

The search only uses the keywords. Here is the debug info for the search:

Code: Select all

SELECT faq.faqdata.id, faq.faqdata.lang, faq.faqcategoryrelations.record_id, faq.faqdata.thema, faq.faqdata.content FROM faq.faqdata LEFT JOIN faq.faqcategoryrelations ON faq.faqdata.id = faq.faqcategoryrelations.record_id AND faq.faqdata.lang = faq.faqcategoryrelations.record_lang WHERE ((faq.faqdata.keywords LIKE "%search%") AND faq.faqdata.active = 'yes') AND active = 'yes'

Everything else seems to be working in MS SQL - I haven't done extensive testing yet, but I'm able to add categories, content, users, etc.

If you want to test using SQL Server, you can download MSDE - their free version of SQL Server at http://www.microsoft.com/sql/msde/downl ... wnload.asp

[Thorsten]

Hi,

your bug is fixed in CVS.

I know how to get a free MS SQL server but I didn't found out how to create databases and users.

bye
Thorsten

[dhoechst]

Still not quite right. It is looking in the keyword column three times now:

Code: Select all

SELECT faq.faqdata.id, faq.faqdata.lang, faq.faqcategoryrelations.record_id, faq.faqdata.thema, faq.faqdata.content FROM faq.faqdata LEFT JOIN faq.faqcategoryrelations ON faq.faqdata.id = faq.faqcategoryrelations.record_id AND faq.faqdata.lang = faq.faqcategoryrelations.record_lang WHERE ((faq.faqdata.keywords LIKE '%search%' OR faq.faqdata.keywords LIKE '%search%' OR faq.faqdata.keywords LIKE '%search%') AND faq.faqdata.active = 'yes') AND active = 'yes'

I'll see if I can get you some instructions on using MSDE. I've got SQL Server and its been a while since I've used the desktop engine.

[Thorsten]

Hi,

I fixed my silly bugfix and now it should work. Can you post the query before your posted one? The query you posted is just the fallback query...

Help from you were great because I could remove the EXPERIMENTAL tag from the MS SQL support.

bye
Thorsten

[dhoechst]

Hi,

this is the wrong search query:

Code: Select all

SELECT faq.faqdata.id, faq.faqdata.lang, faq.faqcategoryrelations.category_id, faq.faqdata.thema, faq.faqdata.content FROM faq.faqdata LEFT JOIN faq.faqcategoryrelations ON faq.faqdata.id = faq.faqcategoryrelations.record_id AND faq.faqdata.lang = faq.faqcategoryrelations.record_lang WHERE AND (faq.faqdata.active = 'yes'); 

Thanks for adding MS SQL support to phpMyFAQ - I wouldn't have been able to use it here at work because we are mostly a Microsoft shop and it is hard enough to just sell PHP to management. This has been the best open source FAQ I've found. Nobody has created anything close to it in .NET yet.

Thanks for using it and thanks for your debugging!

bye
Thorsten

[dhoechst]

Almost got it - you've got a typo in the build of the SQL statement - in the where statement you have" faqdata.contents like". It should be "faqdata.content like". I changed this in functions.php and voila! It works!

[Thorsten]

Hi,

thanks... I fixed the typo. Is it possible for you to debug the function search() in the file inc/mssql.php for me? This is the main search function...

bye
Thorsten

[dhoechst]

It looks like you aren't using the variable $string at all in the function &search in mssql.php. The only thing you've got that creates the where part of the SQL statement is here:

Code: Select all

foreach($cond as $field => $data) {
			
			if (empty($where)) {

				$where = $field." = '".addslashes($data)."'";

            } else {

				$where .= " OR ".$field." LIKE '".addslashes($data)."'";

            }

		}

This won't do much good since this is only the active flag in the $cond variable. You need to parse $string and build it into the SQL statement much as you do in the fall back code.

I could try to code a solution, but I'm a newbie when it comes to PHP!

Thanks,
Daniel

[Thorsten]

Hi Daniel,

it would be fine if you can do it because you could debug it at the same time. If you have a patch, please send it to me and I'll add your name to the credits.

Thanks for your help!

bye
Thorsten