Forum

Thread tagged as: Problem, Error

Blog Search Not Working

Hi, I'm trying to implement search on a blog and it's not finding any results event though the words are clearly there. In fact even if I try it using the default search.php that comes in Perch, I don't get any results. The example in the screenshot shows the word I'm searching for is in the very first blog post but Perch doesn't find it.

Broken blog search

This project is still running Perch 2, but I believe is as up to date as it can be.

I'd be grateful for some help with this please.

My blog index template:

<?php include('../perch/runtime.php'); ?>
<?php perch_layout('blog.header'); ?>

<div class="page-header blog-header">
    <div class="container question-title">
        <h1>Blog</h1>
    </div>
</div>

<div id="intro">
    <div class="container">
    <?php perch_content('text'); ?>
    </div>
</div>

    <!--  change cols2-nav-right to cols2-nav-left if you want the sidebar on the left -->
<div id="blog">
    <div class="container">

        <div class="primary-content">

            <?php
                $query = perch_get('q');  // 'q' query string argument e.g. search.php?q=apples
                perch_content_search($query, array(
                        'from-path'=>'/blog',
                    ));
            ?>

            <?php perch_blog_custom(array(
                'section' => 'posts',
        'sort' => 'postDateTime',
        'sort-order' => 'DESC',
                'count' => '10',
                'template' => 'post_in_list.html',
        )); ?>

        </div>

        <div class="sidebar">

                <?php perch_search_form(); ?>

                <h5>Sections</h5>
                <?php perch_content('Blog Sections Intro'); ?>

            <?php perch_blog_categories(array(
                        'template' => 'category.html',
                    ));
                    ?>

                    <div class="archive-link">
                        <a href="/blog/archive.php"><h5>Archive</h5>
                        <p>Here you can browse the blog in an alphabetical list, by tag or by month.</p></a>
                    </div>

            <div class="twitter">
            <h5>Twitter</h5>
            <a class="twitter-timeline" width="340" height="400" href="https://twitter.com/CMJ1809" data-widget-id="593375484454502400">Tweets by @CMJ1809</a>
            </div>

        </div>

        <?php PerchUtil::output_debug(); ?>


    </div>

</div>

<?php perch_layout('global.footer'); ?>

</body>
</html>

The debug:

Debug Message
[1] SELECT * FROM perch2_pages WHERE pagePath='/blog/index.php' LIMIT 1
[9] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
[1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/blog/index.php' LIMIT 1
[1] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-006', '000') ORDER BY pageTreePosition DESC
[9] Using template: /templates/navigation/item.html
[6] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/blog/index.php' OR regionPage='*' ORDER BY regionPage DESC
Search term: blossoms
[45] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, MATCH(ci.itemSearch) AGAINST('blossoms') 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('blossoms') OR MATCH(ci.itemSearch) AGAINST('blossoms') ) AND r.regionPage LIKE '/blog%' UNION SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('blossoms') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2017-10-18 17:06:14' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('blossoms') UNION SELECT 'PerchShop_SearchHandler' AS source, MATCH(productTitle, productDescRaw) AGAINST('blossoms') AS score, productTitle, productSlug, productPrice, productDescHTML, productID, "", "", "" FROM perch2_shop_products WHERE productStatus='Live' AND MATCH(productTitle, productDescRaw) AGAINST('blossoms') ORDER BY score DESC LIMIT 0, 10
Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'productTitle' in 'field list'
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' 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 '[[:<:]]blossoms[[:>:]]' AND r.regionPage LIKE '/blog%' 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<'2017-10-18 17:06:14' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]blossoms[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]blossoms[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]blossoms[[:>:]]' ) UNION SELECT 'PerchShop_SearchHandler' AS source, productPrice AS score, productTitle, productSlug, productPrice, productDescHTML, productID, "", "", "" FROM perch2_shop_products WHERE productStatus='Live' AND ( productTitle REGEXP '[[:<:]]blossoms[[:>:]]' OR productDescRaw REGEXP '[[:<:]]blossoms[[:>:]]' ) ORDER BY score ASC LIMIT 0, 10
Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'productPrice' in 'field list'
[45] SELECT FOUND_ROWS() AS `count`
Using template: /templates/search/search-result.html
[1] SELECT * FROM perch2_blog_sections WHERE sectionSlug='posts' LIMIT 1
[10] SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_blog_index idx JOIN perch2_blog_posts main ON idx.itemID=main.postID AND idx.itemKey='postID' JOIN perch2_blog_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='postID' AND idx2.indexKey='postDateTime' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE ( sectionID='1' AND postStatus='Published' AND postDateTime<='2017-10-18 17:06:00' ) GROUP BY itemID, sortval ORDER BY sortval DESC LIMIT 0, 10
[1] SELECT FOUND_ROWS() AS `count`
[10] Using template: /templates/blog/post_in_list.html
[2] SELECT * FROM perch2_blog_sections ORDER BY sectionTitle ASC
[1] SELECT * FROM perch2_blogs ORDER BY blogTitle ASC
Using template: /templates/search/search-form.html
Fetching from cache: perch_blog_categories9719f03f9f2ec65189d152a8a978942f
Phil Bowell

Phil Bowell 0 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

You're getting SQL errors, nothing to do with the quality of the search result.

Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'productPrice' in 'field list'

Do you have one of the shop apps installed?

Thanks Drew, sorry managed to miss your reply alert email somehow.

I do have one of the shop apps installed. I have recently begun setting up the Shop app but the client asked me to divert attention to other features first.

Drew McLellan

Drew McLellan 2638 points
Perch Support

Ok, you'll need to finish configuring Shop or remove it.

Thanks Drew! Always something obvious, knew it wasn't the quality of the search implementation and an issue with me!