Solved

Show search results if keyword in any field

Posted on 2012-03-30
16
332 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

910 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

24 Experts available now in Live!

Get 1:1 Help Now