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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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)
Scott MadeiraCommented:
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'
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

MATCH(description)

i get...

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

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?
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.
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.