Forum
Search SQL error when searching just Blog
Trying to search just the Blog app and getting no results. If I search the entire sites results come in.
$query = perch_get('q');
perch_content_search($query, array(
'apps' => array('PerchBlog')
));
I don't know what to make of the Invalid Query in the debug output:
SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='6cb1b86c6fec54451adc7e7e24eb9fa3' LIMIT 1
UPDATE perch2_users SET userHash='553a29dc746fa942fcf084ce5b210ee1' WHERE userID=1
SELECT p.privKey FROM perch2_user_privileges p
SELECT * FROM perch2_pages WHERE pagePath='/blog-search.php' LIMIT 1
Using template: /templates/pages/attributes/seo_content_display.html
SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 ORDER BY pageTreePosition ASC
SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/blog-search.php' LIMIT 1
Using template: /templates/navigation/main_top.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Search term: marriage
SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('marriage') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-04-21 09:45:07' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('marriage') ORDER BY score DESC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTa' at line 1
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 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<'2015-04-21 09:45:07' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]marriage[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]marriage[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]marriage[[:>:]]' ) ORDER BY score ASC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, po' at line 1
SELECT FOUND_ROWS() AS `count`
Using template: /templates/search/search-result.html
Why is there an "invalid query"? What can I do about it?
Perch: 2.8.5, PHP: 5.4.26, MySQL: 5.5.34, with PDO
Server OS: Darwin, apache2handler
Installed apps: content (2.8.5), assets (2.8.5), categories (2.8.5), perch_blog (4.6), perch_forms (1.8.3)
Do you have any sections configured?
Just the default "Posts". I saw a previous forum thread where adding a section cleared the issue, but wasn't able to make that work (added a section, added a post to that new section, and search did not return the new post).
Can you post your diagnostics report?
Sure:
I've no idea why that's failing, but I'll see what I can find out.
Okay, thanks. Certainly the SQL "invalid query"s seem troubling, but SQL is a stretch for me. Here's a new one that has two such errors:
Right - I need to figure out how on earth your server is finding that query to be invalid when it works for everyone else. This is not an easy problem.
I have it on a MAMP setup and on a Bluehost server. What details can I share to help the investigation?
I'm not sure just yet. I'll have to let you know once I'm back in the office tomorrow morning.