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

BrighteyesDesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.