Error In Performing This Mysql Class

[medicalboy]

i read this articlehttp://www.devshed.com/index2.php?option=c...0&hide_js=1and after i made the files and try the search engineigot this file

Error performing query SELECT firstname, lastname,comments FROM users WHERE MATCH(firstname,lastname,comments) AGAINST ('vista')

i checked the file processform.php for this error and i think is ok

$result=$db->query("SELECT firstname, lastname,comments FROMusers WHERE MATCH(firstname,lastname,comments) AGAINST('$searchterm')");

any help?

[student101]

Do all of these columns have fulltext indices defined for each of them?Not recommended to change variables that control MySQL fulltext index behaviour... MySQL config file would usually be found on Windows under c:\windows\my.ini and on Linuxunder /etc/my.cnf Edit the file and add [mysqld]ft_min_word_len = 3The default value is 4 and the above will change it to 3. You can change ft_max_word_len inthe same manner:[mysqld]ft_max_word_len = 150After changing the variable(s) in config file restart MySQL server and you're done. You canthen check the values in phpMyAdmin under "Show MySQL system variables" to make sure yourchanges have taken place.

[medicalboy]

Do all of these columns have fulltext indices defined for each of them?Not recommended to change variables that control MySQL fulltext index behaviour... MySQL config file would usually be found on Windows under c:\windows\my.ini and on Linuxunder /etc/my.cnf Edit the file and add [mysqld]ft_min_word_len = 3The default value is 4 and the above will change it to 3. You can change ft_max_word_len inthe same manner:[mysqld]ft_max_word_len = 150After changing the variable(s) in config file restart MySQL server and you're done. You canthen check the values in phpMyAdmin under "Show MySQL system variables" to make sure yourchanges have taken place.

yes----i search for this option, but i didn't find anything----i want to know is there is something wrong in this classor the problem is in the mysqli use appserv also i read comments of this articles with the same problem but the writer just say to make sure the db is myismand ofcourse is iam!

[justsomeguy]

I guess you could consider it a problem with the class that it doesn't give a decent error message. You can replace this line:throw new Exception('Error performing query '.$query);with this:throw new Exception('Error performing query '.$query.'<br>'.mysql_error($this->connId));and then it should at least tell you what the problem is.

[medicalboy]

I guess you could consider it a problem with the class that it doesn't give a decent error message. You can replace this line:throw new Exception('Error performing query '.$query);with this:throw new Exception('Error performing query '.$query.'<br>'.mysql_error($this->connId));and then it should at least tell you what the problem is.

i changed it but i got error due to changing this snippet

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in C:\AppServ\www\search\mysql.php on line 69Error performing query SELECT firstname, lastname,comments FROM users WHERE MATCH(firstname,lastname,comments) AGAINST ('vista')

[justsomeguy]

Sorry, connId should be conId.

[medicalboy]

Sorry, connId should be conId.

the error isCan't find FULLTEXT index matching the column listi made the db with atable users with 4 columns id(int) firstname(varchar) lastname(varchar) email(varchar) comments(varchar)and i insert adata like this and i search for the wrod vistaaslo in the processform.php file the same columns to select

$result=$db->query("SELECT firstname, lastname,comments FROMusers WHERE MATCH(firstname,lastname,comments) AGAINST('$searchterm')");

[justsomeguy]

You need to create a single fulltext index spanning the three columns you're searching. In phpMyAdmin, you can create a new index on the structure tab for the table, on the bottom in the details section.

[student101]

You could try;

ALTER TABLE users ADD FULLTEXT(firstname, lastname, comments);

[medicalboy]

You need to create a single fulltext index spanning the three columns you're searching. In phpMyAdmin, you can create a new index on the structure tab for the table, on the bottom in the details section.

thanxbut i don't know what's to do with this as i am abeginneri tried to made like thisbut i got the same errorcan u tell me steps to do with the 3 columns

[medicalboy]

You could try;

ALTER TABLE users ADD FULLTEXT(firstname, lastname, comments);

good but when i searched for the word vista , it gave me that No results were found. Go back and try a new search.vista is in the firstname column

[medicalboy]

thanx everything go well nowthanx mr justsomeguy for your helpappreciatedthanx mr student101

[justsomeguy]

i tried to made like this

You made an index with 4 columns, not 3. The index needs to be exactly the columns you're searching, and nothing more.Check the details for the search types:

A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 11.8.2, “Boolean Full-Text Searches”. A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given. A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 11.8.3, “Full-Text Searches with Query Expansion”.

Your search is a natural language search, because you're not telling it otherwise:

Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.

Notice this for natural language searches:

words that are present in 50% or more of the rows are considered common and do not match

If you only have one row, then "vista" appears in 100% of the rows, so it's not going to match. You could use a boolean match instead, or add more rows.

[medicalboy]

You made an index with 4 columns, not 3. The index needs to be exactly the columns you're searching, and nothing more.Check the details for the search types:Your search is a natural language search, because you're not telling it otherwise:Notice this for natural language searches:If you only have one row, then "vista" appears in 100% of the rows, so it's not going to match. You could use a boolean match instead, or add more rows.

i'll read it accuratellyreally i learned anew thing in this threadgreetings

[student101]

why bother with fulltext searching?This question is for my curiosity

[justsomeguy]

Fulltext searching provides better results for many types of searches. The boolean mode search is like a regular search, where it will return all results that have whatever you're looking for. So a boolean mode fulltext search would return results similar to this:SELECT * FROM table WHERE field LIKE '%some value%'The natural language and query expansion searches are often more useful. You can read descriptions here:http://dev.mysql.com/doc/refman/5.0/en//fulltext-search.htmlFor a query expansion search, it will often find more relevant results than a regular search. The way that works, assume that you have a database of books with entries like this:Programming PHPProgramming PHP and MySQLCreating Web Applications with PHP and MySQLUsing MySQL in a WebsiteAssume that you search for "PHP". With a query expansion search, it will first find results that have PHP in them, and then it will look for other terms that they also have. Since 2/3 of the entries with "PHP" also have "MySQL", then it will also return results for MySQL. So a query expansion search can find records that are possibly relevant, even though they don't contain the search terms.

[student101]

So a query expansion search can find records that are possibly relevant, even though they don't contain the search terms.

Since I hold relevant data and would prefer to find relevant data and not be confused with irrelevant results.

Programming PHPProgramming PHP and MySQLCreating Web Applications with PHP and MySQLUsing MySQL in a Website

$searchterm = mysql_real_escape_string($_POST['searchterm']);SELECT * FROM table WHERE field LIKE '%$searchterm%'

I search for PHP with the results:Programming PHPProgramming PHP and MySQLCreating Web Applications with PHP and MySQL

If I had the FULLTEXT applied, now I did a lazy search for "PH", no results would be returned.

On another note, it's less hassle as well as less code just to use;

$searchterm = mysql_real_escape_string($_POST['searchterm']);SELECT * FROM table WHERE field LIKE '%$searchterm%'

[justsomeguy]

If I had the FULLTEXT applied, now I did a lazy search for "PH", no results would be returned.

If the term appears in more than 50% of the records then it's going to be ignored. In a real data set you would have many rows, so searching for "PH" should bring up everything that contains "PHP", assuming that less than 50% of the records contain "PH".

[student101]

Thank you, I can honestly say that FULLTEXT is not for me, prefer results that show what I searched for.

I am in no way knocking it, just that I don't 2 million records to search through, it may very well be relevant and useful but not for me.

[justsomeguy]

That would be a boolean mode fulltext search. Fulltext indexing simply makes searching through very large data sets much faster. If you want to use boolean mode then use boolean mode, but you will see performance benefits from searching in a fulltext index vs. a non-indexed field.If you're not searching for keywords, if you're searching for a specific value in a specific field, e.g.:SELECT * FROM users WHERE username='Joe'Then a fulltext index on username would be more overhead than it's worth, in that case you would just use a regular index (or primary) on username. But, if you're searching for keywords which may appear anywhere in a field, instead of the entire value, it's going to be quicker to use a fulltext index.