Solved

Show search results if keyword in any field

Posted on 2012-03-30
16
331 Views
Last Modified: 2012-03-31
I have a search function set up where the name column is searched against the search term.

Is it possible to display results if the keyword is in the whole row rather than just the one column?

Here's what I use at the moment...

$search = $_GET["search"];

mysql_select_db($database_acs, $acs);
$query_product = "SELECT * FROM products WHERE MATCH(name) AGAINST ('$search')";
$query_limit_product = sprintf("%s LIMIT %d, %d", $query_product, $startRow_product, $maxRows_product);
$product = mysql_query($query_limit_product, $acs) or die(mysql_error());
$row_product = mysql_fetch_assoc($product);

Open in new window

0
Comment
Question by:BrighteyesDesign
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 37788328
What happens when you specify multiple column names in the match function

MATCH(name description, color, size, etc)
0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 37788336
0
 

Author Comment

by:BrighteyesDesign
ID: 37788389
When i try adding other column names in the brackets i get the following error...

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_swedish_ci,IMPLICIT) for operation 'match'
0
 

Author Comment

by:BrighteyesDesign
ID: 37788398
It is a MyISAM table too by the way
0
 

Author Comment

by:BrighteyesDesign
ID: 37788404
Als, when i try...

MATCH(description)

i get...

Can't find FULLTEXT index matching the column list
0
 

Author Comment

by:BrighteyesDesign
ID: 37788410
Do I have to create a fulltext index?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37789500
Do I have to create a fulltext index?

YES.  Please read the documentation.  You need to create an index for each combination of columns you want to search at the same time.   So if it is for all columns, then create a fulltext index for all columns.  Note that you won't be able to use the same index in case you decide later on to give the user an option to search only certain columns - you will need to create another index for that.

I believe I mentioned this to you before - please note that you can only go so far with mysql fulltext search.  It doesn't scale and perform very well with large data, unless you have huge memory allocation and mysql is tuned to use it.  So proceed with caution.
0
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37789777
MATCH requires a full-text index.
One other option is using this:

 $query_product = "SELECT * FROM products WHERE name LIKE '%$search%' OR  description LIKE '%$search%' COLLATE utf8_swedish_ci ";

(notice that in the above example I invented the column `description` -- replace with your own).
The explicit COLLATE should get rid of that error you mentioned.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:johanntagle
ID: 37789784
@designatedinitializer - you cannot expect to scale your application with that, as LIKE '%keyword%' doesn't make use of any index.  It's only good for a small data set.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37789790
When i try adding other column names in the brackets i get the following error...

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_swedish_ci,IMPLICIT) for operation 'match'

Missed this part.  Please check the column definitions on your table as you seem to have used different collations for the columns.  See http://dev.mysql.com/doc/refman/5.1/en/charset-column.html
0
 

Author Comment

by:BrighteyesDesign
ID: 37790202
I have read through that link but cannot get my head around it.

I'm trying different things with no joy.

Here's the database and the code i'm using...
Screen-shot-2012-03-31-at-09.45..png
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37790639
Can you please export the table, even without data, to an sql file and attach that file here?  That would be more useful than a screenshot and will allow me to easily replicate your setup.  Thanks.
0
 

Author Comment

by:BrighteyesDesign
ID: 37790667
Sure John, here it is...
products.sql
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37790857
Uhmm, my name is Johann, not John.  Anyway, after importing your table, this is what I saw:

mysql> show create table products;
| products | CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `cat1` text NOT NULL,
  `cat2` text,
  `cat3` text,
  `specialoffer` text NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `prefix` text,
  `suffix` text,
  `description` text CHARACTER SET utf8 COLLATE utf8_swedish_ci,
  `image` text,
  `image2` text,
  `romancart` text,
  `rel1` text,
  `rel2` text,
  `rel3` text,
  `rel4` text,
  `rel5` text,
  `rel6` text,
  `rel7` text,
  `rel8` text,
  `category` text NOT NULL,
  `active` text,
  `keywords` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `keywords` (`keywords`),
  FULLTEXT KEY `keywords_2` (`keywords`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=363 DEFAULT CHARSET=latin1 |

Open in new window


This will really cause a problem if you use the description column as part of the index, because it has different collation:

mysql> create fulltext index ft_test on products (name, description);
ERROR 1283 (HY000): Column 'description' cannot be part of FULLTEXT index

Open in new window


After modification, creation goes well:
mysql> alter table products modify description text;
Query OK, 306 rows affected (0.12 sec)
Records: 306  Duplicates: 0  Warnings: 0

mysql> show create table products;
| products | CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `cat1` text NOT NULL,
  `cat2` text,
  `cat3` text,
  `specialoffer` text NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `prefix` text,
  `suffix` text,
  `description` text,
  `image` text,
  `image2` text,
  `romancart` text,
  `rel1` text,
  `rel2` text,
  `rel3` text,
  `rel4` text,
  `rel5` text,
  `rel6` text,
  `rel7` text,
  `rel8` text,
  `category` text NOT NULL,
  `active` text,
  `keywords` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `keywords` (`keywords`),
  FULLTEXT KEY `keywords_2` (`keywords`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=363 DEFAULT CHARSET=latin1 |

mysql> create fulltext index ft_test on products (name, description);
Query OK, 306 rows affected (0.15 sec)
Records: 306  Duplicates: 0  Warnings: 0

mysql> select name from products where match(name, description) against ('bright eyes');
+-------------------------------------+
| name                                |
+-------------------------------------+
| Please Be Silent Before Mass Notice |
| Priest On Call                      |
+-------------------------------------+
2 rows in set (0.00 sec)

Open in new window


One other thing: why are you using TEXT datatype all throughout?  That's very inefficient.  You should use TEXT only for strings that can get very long and use varchar for the rest.  See http://www.pythian.com/news/7129/text-vs-varchar/
0
 

Author Comment

by:BrighteyesDesign
ID: 37790888
Sorry Johann, i'm rushing around so much I lazily typed John.

Thanks a lot for your help, that seems to have done the trick.

No reason why i'm using TEXT by the way, just inexperience! Will that slow things down?

Also, if I change eveything to varchar I won't lose anything will I?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37790907
yes, reading and writing TEXT colums are generally slower.  As long as you convert a text column to varchar(nn) where nn <= max(char_length(column_name)) you won't lose data.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now