472,356 Members | 2,022 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,356 software developers and data experts.

FULLTEXT Searching

I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative.

I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query:

SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity, p.products_image, p.products_bimage,
p.products_price, p.products_date_added, p.products_last_modified,
p.products_date_available, p.products_status
FROM products p, products_description pd, products_to_categories p2c
WHERE MATCH(p.products_model,pd.products_name,pd.product s_description)
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
ORDER BY pd.products_name;

This is how my table structures look:

CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(12) default NULL,
`products_image` varchar(64) default 'image_na.gif',
`products_bimage` varchar(64) default 'image_na.jpg',
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model` (`products_model`)

CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`),
FULLTEXT KEY `products_name_2`

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 1681

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: JT | last post by:
I have a MySQL fulltext search form in place and I now want to filter the results further. I've added a few multiple select boxes on my form (ResourceType and Topic), and I'm able to build and...
by: Phil Powell | last post by:
Relevancy scores are normally defined by a MySQL query on a table that has a fulltext index. The rules for relevancy scoring will exclude certain words due to their being too short (minimum...
by: Matt W | last post by:
Hi all, I'm planning to use MySQL's full-text search for my forum system (possibly 5+ million posts). I've been playing with it a lot lately to see the performance and functionality and have...
by: Radim B | last post by:
Hi, can anyone help me if it is possible to make indexing for fulltext from formats in: 1) *.doc 2) *.xls 3) *.html 4) *.pdf Many thanx for your help
by: Phil Powell | last post by:
Retracing my problem leads me to believe I never successfully created fulltext indexes for MySQL 3.23.58 MyISAM tables. I went to the MySQL manual and was able - or so I thought - to create them,...
by: Mike Reef | last post by:
I'm trying fulltext searching for the first time , but I can't figure out why the example in the mySQL manual is *not* working. mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST...
by: Alex Glass | last post by:
I have a large contacts table with about 30 columns of text fields and 5 fulltext indexes spanning the different sections of the table. I'm curious if anyone could suggest a better way to find...
by: Robert Oschler | last post by:
I read a while back that MySQL will only use one index per query. (If this is not so, please tell me and point me to a doc that gives a good explanation of MySQL's current index usage policy). ...
by: luckysingh | last post by:
I've tried searching alot on the net but am not able to find how to allow a user (not admin/owner) to be able to create & populate the FullText Indexing Catalogs on the tables he creates. Currently...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.