Searching anything but entry name returns nothing
Hello,
I've used the search posted by Brandon here to search pages on my site.
It works perfectly when searching name fields, but never seems to return anything when searching something I've picked out of a paragraph text field, or anything else - like the slug and other fields.
If I modify the code further to only search a specific column (not name), nothing is ever found in the search - despite me copy/pasting words directly from the entry.
This is fine:
(t.name LIKE '%" .$search_term. "%')
However anything else isn't.
(t.slug LIKE '%" .$search_term. "%')
or
(t.slug LIKE '%" .$search_term. "%') || (t.thecontent LIKE '%" .$search_term. "%')
etc.
Why? I believe this is the correct way to do this. Is it not? I'd really appreciate your input.
EDIT: If I use a column that doesn't exist (t.doesnotexist LIKE '%" .$search_term. "%'), a sql error is given - so using t.slug and t.thecontent must be getting somewhere, as no error is given..?
Thanks,
Harley.
edited Oct 4 '11 at 5:42
3 Answers
Searching Pods is pretty kludgy. My recommendations would be either to: use Google Custom Search (only works if Google indexes all of your Pods pages), or set up a FULLTEXT index on your columns and use MATCH() AGAINST().
Setting up the FULLTEXT indexes is easiest from phpMyAdmin. From the Table > Structure tab, there is an icon at the far right in my version of phpMyAdmin (v 2.11.11.3) with a "T" on it and the tooltip "FULLTEXT." Click this for each field you want to build a FULLTEXT index. When you are done, scroll to the bottom of the screen and make sure all those fields appear in the "Indexes" table.
A word of caution: I'm not sure if these indexes are preserved when you update Pods or WP. Add a check of this to your upgrade workflow.
Now you can use syntax like this:
$pods->findRecords('t.name ASC', -1, "MATCH(t.body, t.slug) AGAINST('this phrase')");
or this:
$pods->findRecords('t.name ASC', -1, "MATCH(t.body, t.slug) AGAINST('+this -\"not that\"' IN BOOLEAN MODE)");
There is a good tutorial on building a site search engine at devzone.zend.com that explains everything much better than I can.
edited Oct 5 '11 at 1:47
Any help regarding my comment there would be great if possible.
Thanks again
edited Oct 12 '11 at 7:30
@Guil: The error you are getting is because you are trying to INDEX a text field. You can only add a FULLTEXT. They are different. FULLTEXT has been available since MySQL 3.23, so unless your web host is WAY out of date, it should be there.
If you can't add it via the list, scroll down to the bottom of the screen, where the "Indexes" table is and use the "Create an index on [1] columns [GO]" form. The index name should be the same as the column name, and you want to select FULLTEXT (not INDEX and especially not UNIQUE or PRIMARY) from the drop down.
If you can't add one via phpMyAdmin, you can try this in a Pods page:
$sql = 'ALTER TABLE `wp_pod_tbl_my_pod` ADD FULLTEXT(`name`)'; pod_query($sql);
