Forum

Thread tagged as: Problem

Perch Search No Results

Hello,

I'm trying to set up search but every query outputs "sorry no results for {query}"

I've tried resetting all templates to default incase i had errors there

I've tried this on the dev & live copy of the site.

I've checked that in the region options section "include in search results" is checked

<?php
      $query = perch_get('q');
      perch_content_search($query);
?>

I've tried this exact method on a clean install and it works perfectly, is there anything else you need to troubleshoot this?
Dexter Harrison

Dexter Harrison 14 points

  • 7 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Can you add debug to the page and let me know what it outputs when you search?
Dexter Harrison

Dexter Harrison 14 points

7 years ago

Sure here it is

EDIT - Sorry if you had already read this, i got rid of everything on the page but search so it was easier to read

DIAGNOSTICS:
Search term: lapsafe
SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'content' AS source, MATCH(ci.itemSearch) AGAINST('lapsafe') AS score, 
r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8
FROM perch2_content_regions r, perch2_content_items ci, perch2_pages p
WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 
AND (MATCH(ci.itemSearch) AGAINST('lapsafe') OR MATCH(ci.itemSearch) AGAINST('lapsafe') )
AND r.regionPage LIKE '/%' 
UNION 
SELECT 'PerchEvents_SearchHandler' AS source, MATCH(eventTitle, eventDescRaw) AGAINST('lapsafe') AS score, eventTitle, eventSlug, eventDateTime, eventDescHTML, eventID, "", "", ""
FROM perch2_events 
WHERE eventDateTime>'2014-08-21 19:04:14'
AND MATCH(eventTitle, eventDescRaw) AGAINST('lapsafe') 
UNION 
SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('lapsafe') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", ""
FROM perch2_blog_posts p, perch2_blog_sections s
WHERE postStatus='Published'
AND postDateTime<'2014-08-21 19:04:14'
AND p.sectionID=s.sectionID
AND MATCH(postTitle, postDescRaw, postTags) AGAINST('lapsafe') ORDER BY score DESC LIMIT 0, 10
Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cl51-lpl.perch2_blog_sections' doesn't exist
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'content' AS source, 0-(LENGTH(r.regionPage)-LENGTH(REPLACE(r.regionPage, '/', ''))) AS score, 
r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8
FROM perch2_content_regions r, perch2_content_items ci, perch2_pages p
WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 
AND ci.itemSearch REGEXP '[[:<:]]lapsafe[[:>:]]' 
AND r.regionPage LIKE '/%' 
UNION 
SELECT 'PerchEvents_SearchHandler' AS source, eventDateTime AS score, eventTitle, eventSlug, eventDateTime, eventDescHTML, eventID, "", "", ""
FROM perch2_events 
WHERE eventDateTime>'2014-08-21 19:04:14'
AND ( 
concat(" ", eventTitle, " ") REGEXP '[[:<:]]lapsafe[[:>:]]' 
OR concat(" ", eventDescRaw, " ") REGEXP '[[:<:]]lapsafe[[:>:]]' 
) 
UNION 
SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", ""
FROM perch2_blog_posts p, perch2_blog_sections s
WHERE postStatus='Published'
AND postDateTime<'2014-08-21 19:04:14'
AND p.sectionID=s.sectionID 
AND ( 
concat(" ", postTitle, " ") REGEXP '[[:<:]]lapsafe[[:>:]]' 
OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]lapsafe[[:>:]]' 
OR concat(" ", postTags, " ") REGEXP '[[:<:]]lapsafe[[:>:]]' 
) ORDER BY score ASC LIMIT 0, 10
Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cl51-lpl.perch2_blog_sections' doesn't exist
SELECT FOUND_ROWS() AS `count` 
Using template: /templates/search/search-result.html
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

In the Blog app, under Sections, what do you see?
Dexter Harrison

Dexter Harrison 14 points

7 years ago

"Listing Sections" & "Add Section"

And there are no sections,

When you add a section type a name click save, a blank screen so a PHP error
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

It sounds like your installation has a missing database table. Was this an update, or a fresh install?
Dexter Harrison

Dexter Harrison 14 points

7 years ago

An update, I've been running Perch for quite a while.

First time we've encountered this problem, we've only just decided to implement site search.
Dexter Harrison

Dexter Harrison 14 points

7 years ago

Is it possible to add the missing table?
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Yes - can you run these to SQL statements against your database?

CREATE TABLE `perch2_blog_sections` (
  `sectionID` int(11) NOT NULL AUTO_INCREMENT,
  `sectionTitle` varchar(255) NOT NULL DEFAULT '',
  `sectionSlug` varchar(255) NOT NULL DEFAULT '',
  `sectionPostCount` int(10) unsigned NOT NULL DEFAULT '0',
  `sectionDynamicFields` text,
  PRIMARY KEY (`sectionID`),
  KEY `idx_slug` (`sectionSlug`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

and then

INSERT INTO `perch2_blog_sections` (sectionID, sectionTitle, sectionSlug, sectionPostCount, sectionDynamicFields) 
VALUES ('1', 'Posts', 'posts', 0, '');
Dexter Harrison

Dexter Harrison 14 points

7 years ago

Sure, that's been done,

You can now see the posts section under sections,

When doing a search it now throws this error

Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.sectionID' in 'where clause'
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Ok, closer. It looks like the update hasn't run for some reason. Some more:

ALTER TABLE `perch2_blog_posts` ADD `sectionID` INT(10)  UNSIGNED  NOT NULL  DEFAULT '1'  AFTER `authorID`;

ALTER TABLE `perch2_blog_posts` ADD INDEX `idx_status` (`postStatus`);

ALTER TABLE `perch2_blog_posts` ADD INDEX `idx_section` (`sectionID`);
Dexter Harrison

Dexter Harrison 14 points

7 years ago

That did it, thank you very much for your help

It's why one of the many reasons I recommend Perch to everyone