Anyone able to help me - MySql Search

Glad I could help. You may want to strip out commas and punctuation from searches as well. Also, if strlen(q) > the item in the database, they won't match left to right.

It gives a parse error: Unexpected T_IF in W:\www\chazco\index.php on line 179 (thats the if(isarray()) line) but i cant see any problems that would cause it.

Thanks again for all the help everyone...

Edited by chazco 2006-04-29 11:15 AM

Fixed it - missing semi colon off the end of the line (i never spot those...). Checking to see if it works now...

glad to help

It doesnt work

I'm having a look now. This is really annoying.

Do you have full text search support installed on your MySQL instance?

Tried it a while ago, causes problems with repeated updation of site and also uses up quite a bit of storage. I'll have a look at that code, i'll get it to work eventually (i usually do).

I've got it working (i'll post the code when its completly finished, in case it helps anyone else).

One thing: is there any way to combine AND and OR in a mysql query?

You mean like:
WHERE (something = var) AND (something = var) OR (something = var)

Yes, i tried that but got the sytax wrong, it works now. The search also works properly as well. Thanks to everyone...

Glad to hear you got it working.

C:Amie, what do you mean by fulltext search? I've never heard of it, but if it exists I'd be really interested. Is that implemented in MSSQL?

Under MSSQL you need to install the Microsoft Search service in order to get it. It isn't available under MSDE. Under MySQL it's there if you install the default binary, if you opt for a self compile or something fancy then it *may* not be there and should check that it's running.

Out of interest here abyssknight, why did you suggest the array construction? It was about 5th on my list as a ASP coder, is there something in PHP that causes problems with the wildcard?
I keep having niggles to look into PHP in more detail becuase it might be useful. I prefer ASP though. What is your stance on it?

Cheers,

In MySQL the wildcard syntax may be different. % matches any or no characters, while _ matches 1 character or no characters. I'm guessing * is how MSSQL does wildcards?

As for the array construction, that's what we usually do at work. We usually run it through a filter to remove words like 'the' and conjunctions first, then do a relevancy calculation. One odd thing with MySQL is that say the Headline is "This is a test", if you do a Headline like '%This is a test 2%', then it won't match because the second argument is longer but if you do '%This is a test 2%' like Headline, it will match.

Also, doing it that way allows for some words (if you use OR instead of AND, just realized I made a mistake there!) to be found when others are not and allows for different orders of words etc. One downfall with using any search like this is if the field doesn't have an index, the query time will be higher so it is best to limit the number of rows to return so the query stops executing.

I'd love to get to know ASP a bit better (used it back in 3.0, before .NET), but as it stands my work uses only PHP/MySQL/XHTML/JS/CSS. I've discussed it with them and it is primarily a training and cost issue. If you know C, you can probably use PHP. The major difference is in syntax, and syntax is just... syntax.

Edited by abyssknight 2006-04-29 11:47 PM

All the Microsoft engines perform wildcard searches based upon *. I forgot to change it back to % in one of my lines above.

The Microsoft Wildcard string expression logic is:
* Matches zero or more characters. It can be used as the first or last character in the character string. wh* finds wh, what, white, and why
? Matches any single alphabetic character. b?ll finds ball, bell, and bill
[ ] Matches any single character within the brackets. b[ae]ll finds ball and bell but not bill
! Matches any character not in the brackets. b[!ae]ll finds bill and bull but not ball or bell
- Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd
# Matches any single numeric character. 1#3 finds 103, 113, and 123

Facinating on the headline like conundrum. My MySQL experience is nothing more than hours really. There's me wrapped up in my own little Microsoft world

The array is probably a better way to do it than a straight implantation of keywords into a wildcard string, like with the SELECT * there is probably an overhead involved while the system generates its own SQL from it. I shall see about experimenting with the HCL's search system at some point, that's a bit clunky (still the HCL3 RTM code from when the index was first automated).

I'm not the worlds greatest programmer, I never will be. In fact I'm really a newbie+9 these days. Which is better than I was 12 months ago. Doesn't exactly come naturally to me, but I seem to have managed to hand write this site and all it's automation. So I guess I cannot be all that bad. It is all ASP3.

I don't know C, probably should get a book. I would like to be able to do something in the eVC++ IDE.

They teach C at my University as the baseline, and truly it is the hardest most stringent language. Java is the other one they teach for Object Oriented. Both are useful for learning syntax, but it would be nice if they would throw us C# or a web scripting language like ASP or PHP. I learned most of my PHP from on the job training, as well as MySQL.