Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Show search results if keyword in any field

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
BrighteyesDesign
Asked:
BrighteyesDesign
  • 7
  • 6
  • 2
  • +1
1 Solution
 
Scott MadeiraCommented:
What happens when you specify multiple column names in the match function

MATCH(name description, color, size, etc)
0
 
Scott MadeiraCommented:
0
 
BrighteyesDesignAuthor Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
BrighteyesDesignAuthor Commented:
It is a MyISAM table too by the way
0
 
BrighteyesDesignAuthor Commented:
Als, when i try...

MATCH(description)

i get...

Can't find FULLTEXT index matching the column list
0
 
BrighteyesDesignAuthor Commented:
Do I have to create a fulltext index?
0
 
johanntagleCommented:
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
 
designatedinitializerCommented:
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
 
johanntagleCommented:
@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
 
johanntagleCommented:
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
 
BrighteyesDesignAuthor Commented:
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
 
johanntagleCommented:
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
 
BrighteyesDesignAuthor Commented:
Sure John, here it is...
products.sql
0
 
johanntagleCommented:
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
 
BrighteyesDesignAuthor Commented:
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
 
johanntagleCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now