Title: Intranet breaking with Custom SQL code
Last modified: January 14, 2020

---

# Intranet breaking with Custom SQL code

 *  Resolved [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/)
 * I have written a custom sql statement that will act as the filter for my posts,
   pages and documents. Unfortunately when i am trying to transform my sqlquery 
   to work in WordPress it is breaking our site and there is no way for me to debug
   it.
 * Could anyone suggest the best way to imbed this SQL statement into my functions.
   php page in order for it to work? Thanks!
 *     ```
       function wpb_search_filter($query) {
       	global $current_user;
       	global $current_user_Location;
   
       	$current_user = wp_get_current_user();
       	$current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
   
       	global $wdpb;
       	global $sqlquery;
       	global $strSite1;
       	global $strSite2;
       	global $strSite3;
   
       	$sqlquery = "SELECT DISTINCT wp_posts.id
       	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       	WHERE wp_posts.id = wp_term_relationships.object_id
       	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       	AND wp_term_taxonomy.term_id = wp_terms.term_id
       	AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
   
       	UNION ALL
   
       	SELECT DISTINCT posts.id
       	FROM wp_posts posts
       	WHERE NOT EXISTS(
       	SELECT *
       	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       	WHERE posts.id =  wp_posts.id
       	AND wp_posts.id = wp_term_relationships.object_id
       	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       	AND wp_term_taxonomy.term_id = wp_terms.term_id)
       	AND posts.post_type IN ('wpfd_file', 'page', 'post')
       	AND posts.post_status NOT IN ('auto-draft', 'trash')";
   
       	if ($query->is_search) {
               if ($current_user_Location == 'CR'){
       		$strSite1 = "'Craigavon'";
       		$strSite2 = "'All'";
       		$strSite3 = "'UK'";	
           	}
   
       	$query = $wpdb->get_results($sqlquery);
       	return $query;
       }
       add_filter('pre_get_posts','wpb_search_filter');
       ```
   
 * Or can anyone see where I am going wrong?
    -  This topic was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).
    -  This topic was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).

Viewing 11 replies - 1 through 11 (of 11 total)

 *  [Jogesh](https://wordpress.org/support/users/jogesh_pi/)
 * (@jogesh_pi)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12322957)
 * I guess the issue is with the following line in case of string value:
 *     ```
       AND wp_terms.name IN ($strSite1, $strSite2, $strSite3)
       ```
   
 * That should be
 *     ```
       AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
       ```
   
 * Alternative option is to print the query and check to run it on phpmyadmin;
 *     ```
       $query = "SELECT DISTINCT wp_posts.id .......";
   
       // Perform print in case of Error
       // print( $query );
   
       $sqlquery = $wpdb->get_results($query);
       ```
   
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12322969)
 * [@jogesh_pi](https://wordpress.org/support/users/jogesh_pi/) thanks for the reply!
   see in terms of my return statement at the end – what is the correct way for 
   me to set the query returned equal to my sql statement?
 * Is it $query = $wpdb->get_results($sqlquery)?
 * Thanks for the tip about stating the variables also!
 *  [Jogesh](https://wordpress.org/support/users/jogesh_pi/)
 * (@jogesh_pi)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12322992)
 * You could change the variable accordingly.
 *     ```
       $raw_query = "SELECT DISTINCT wp_posts.id
       	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       	WHERE wp_posts.id = wp_term_relationships.object_id
       	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       	AND wp_term_taxonomy.term_id = wp_terms.term_id
       	AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
   
       	UNION ALL
   
       	SELECT DISTINCT posts.id
       	FROM wp_posts posts
       	WHERE NOT EXISTS(
       	SELECT *
       	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       	WHERE posts.id =  wp_posts.id
       	AND wp_posts.id = wp_term_relationships.object_id
       	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       	AND wp_term_taxonomy.term_id = wp_terms.term_id)
       	AND posts.post_type IN ('wpfd_file', 'page', 'post')
       	AND posts.post_status NOT IN ('auto-draft', 'trash')";
   
       // Perform print in case of Error
       // print( $raw_query );
   
       $sqlquery = $wpdb->get_results($raw_query);
       ```
   
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12323008)
 * [@jogesh_pi](https://wordpress.org/support/users/jogesh_pi/) ive updated my code
   in the post to match what you have shown me, I also ran the SQL statements into
   PhpMyAdmin and they are definitely correct and working.
 * Site is still breaking 🙁
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12324659)
 * Hi [@jogesh_pi](https://wordpress.org/support/users/jogesh_pi/) !
    So ive made
   a lot of progress with the fact that it now works and my sql is returning the
   post ids of the posts/pages i want to display.
 * The problem I am having now is that although I have an array with these IDs in
   it – it does not seem to be applying it to the filter when the user searches?
 * I have added my updated code for below. Is my code around the “return $query”
   correct? or how do I actually apply the results from my sql to the filtering 
   process.
 * Id really appreciate the help 🙂
 *     ```
       function wpb_search_filter($query) {
       global $current_user;
       global $current_user_Location;
   
       $current_user = wp_get_current_user();
       $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
   
       global $wpdb;
       global $sqlquery;
       global $strSite1;
       global $strSite2;
       global $strSite3;
   
       if ($query->is_search) {
           if ($current_user_Location == 'CR'){
           $strSite1 = 'Craigavon';
           $strSite2 = 'All';
           $strSite3 = 'UK';
       }
   
       $sqlquery = "SELECT DISTINCT wp_posts.id
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id
       AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
   
       UNION ALL
   
       SELECT DISTINCT posts.id
       FROM wp_posts posts
       WHERE NOT EXISTS(
       SELECT *
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE posts.id =  wp_posts.id
       AND wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id)
       AND posts.post_type IN ('wpfd_file', 'page', 'post')
       AND posts.post_status NOT IN ('auto-draft', 'trash')";
   
       echo $strSite1;
       echo "<br>";
       echo $strSite2;
       echo "<br>"; 
       echo $strSite3;
       echo "<br>";
       echo $sqlquery;
       echo "<br>";
   
       $query = array();
       $query = $wpdb->get_results($sqlquery);
           foreach ($query as $details) {
             echo $details->id . ",";
             } 
       }
       return $query;
       }
       add_filter('pre_get_posts','wpb_search_filter');
       ```
   
    -  This reply was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).
    -  This reply was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12325119)
 * You’re having trouble despite a working query because you are not using “pre_get_posts”
   correctly. To start with, it’s an action, not a filter, so there is little point
   in returning anything. Your callback is passed the WP_Query object by reference,
   so any changes you want done with the query needs to be done through object methods,
   usually $query->set(), which sets values for specified query vars.
 * If you want the overall query results to be the results of your custom query,
   you can set the `'posts__in'` query var to an array of post IDs.
 * Running a separate query in order to run a query is not optimal. If at all possible
   you should set the appropriate query vars to achieve your goals so that it’s 
   all taken care of in one query. If what you desire is not possible by setting
   query vars, there are other filters (not actions 😉 ) you can use to modify the
   actual SQL that WP_Query will run. There are filters for each individual query
   clause (where, join, order by, etc.) as well as “posts_request” which filters
   the entire SQL query to be run.
 * If none of those would work for you, you should consider if WP_Query is really
   the best route. Perhaps a completely custom query would make more sense.
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12327424)
 * Hi [@bcworkz](https://wordpress.org/support/users/bcworkz/) [@jogesh_pi](https://wordpress.org/support/users/jogesh_pi/),
   
   Taking your advice I’ve updated my code to this:
 *     ```
       add_action('pre_get_posts','wpb_search_filter');
   
       function wpb_search_filter($query) {
       global $current_user;
       global $current_user_Location;
   
       $current_user = wp_get_current_user();
       $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
   
       global $wpdb;
       global $sqlquery;
       global $strSite1;
       global $strSite2;
       global $strSite3;
   
       if ($query->is_search) {
        if ($current_user_Location == 'CR'){
           $strSite1 = 'Craigavon';
           $strSite2 = 'All';
           $strSite3 = 'UK';
       }
   
       $sqlquery = "SELECT DISTINCT wp_posts.id
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id
       AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
   
       UNION ALL
   
       SELECT DISTINCT posts.id
       FROM wp_posts posts
       WHERE NOT EXISTS(
       SELECT *
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE posts.id =  wp_posts.id
       AND wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id)
       AND posts.post_type IN ('wpfd_file', 'page', 'post')
       AND posts.post_status NOT IN ('auto-draft', 'trash')";
   
       $postIds = array();
       $postIds = $wpdb->get_results($sqlquery);
           foreach ($postIds as $details) {
             echo $details->id . ",";
             }
   
           if ($current_user_Location == 'CR'){
           echo "<Br>";
           echo "you have reached this point and it is a search!";
   
           $query->set('posts__in', $query);
   
       }
       }
       return $query;
       }
       ```
   
 * I’ve also tried it this way:
 *     ```
       if ($current_user_Location == 'CR'){
           echo "<Br>";
           echo "you have reached this point and it is a search!";
   
           echo "<Br>";
           echo "Query has been set!";
   
           $query = new WP_Query(array(
           'post__in' => $postIds
       ));
       ```
   
 * (I am displaying all of my echos, which are displaying a list of comma separated
   Ids so I am getting to the point of setting the queries, so I only want to display
   posts/pages that IDs exist in that array, if doesn’t exist – don’t show)
    Both
   having no luck as it seems just to be displaying all posts and pages and still
   not applying the filter. Am I still making errors?
 * Really appreciate this help by the way, would be lost without it
    -  This reply was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12327551)
 * [@bcworkz](https://wordpress.org/support/users/bcworkz/) , my bad its supposed
   to be `"Post__in",`you see when we do the `get_results`from the query it wasn’t
   comma separated and that’s why we had to add that into the echo, do you think
   our `$postIds`array will consist of them all as one long string e.g. `array('
   983574845475845863296373834')` or will it be smart enough to comma separate these
   based on the Ids?
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12329094)
 * Yeah, you cannot insert a PHP array into a SQL string. Two incompatible data 
   types. Concatenating array elements into a string is OK, but when doing so in
   a loop you end up with a trailing comma which is frequently a syntax error in
   some languages. SQL is probably one of them. Better is to use PHP’s `implode()`
   to “stringify” an array. It avoids the trailing comma problem.
 * Don’t create a new WP_Query instance within a callback for “pre_get_posts”. It
   causes an infinite loop. While there are ways to prevent the looping, a new instance
   is unnecessary. You’re still not using “pre_get_posts” correctly. I know doing
   it right is a tricky concept to grasp. Once you really understand it seems simple,
   but getting there can be difficult.
 * Don’t bother returning any value. The function calling your callback ignores 
   it. It does no harm, but it demonstrates you’re still unclear on the concept 
   🙂 Instead, you need to make changes to the passed object. Because it’s passed
   by reference, changing the object affects the query being run. This is a bit 
   unusual. Most action callback arguments are passed by value and altering them
   has no effect.
 * Your `$query->set('posts__in', $query);` is the right idea, but `$query` is the
   wrong value to pass. The value to pass is an array of ID values. Passing `$postIds`
   is closer to the right value, but still not right because it is an array of post
   data arrays, not IDs. If you change your SELECT clause to only collect the ID
   field instead of `*` you can use `$postIds`.
 * I saw your response in your other related topic. To allow future readers landing
   from search results the ability to follow along, I’m directing them to this topic
   and closing that other topic so all relevant discussion remains here.
 *  Thread Starter [aralmac123](https://wordpress.org/support/users/aralmac123/)
 * (@aralmac123)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12329112)
 * [@bcworkz](https://wordpress.org/support/users/bcworkz/) – I’ve finally cracked
   it! Here is my code for anyone else struggling with this:
 *     ```
       function wpb_search_filter($query) {
       global $current_user;
       global $current_user_Location;
   
       $current_user = wp_get_current_user();
       $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
   
       global $wpdb;
       global $sqlquery;
       global $strSite1;
       global $strSite2;
       global $strSite3;
   
       if ($query->is_search) {
       if ($current_user_Location == 'CR'){
           $strSite1 = 'Craigavon';
           $strSite2 = 'All';
           $strSite3 = 'UK';
       }
       $sqlquery = "SELECT DISTINCT wp_posts.id
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id
       AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
   
       UNION ALL
   
       SELECT DISTINCT posts.id
       FROM wp_posts posts
       WHERE NOT EXISTS(
       SELECT *
       FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
       WHERE posts.id =  wp_posts.id
       AND wp_posts.id = wp_term_relationships.object_id
       AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
       AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
       AND wp_term_taxonomy.term_id = wp_terms.term_id)
       AND posts.post_type IN ('wpfd_file', 'page', 'post')
       AND posts.post_status NOT IN ('auto-draft', 'trash')";
   
       $postIds = array();
       $idArray = array();
       $postIds = $wpdb->get_results($sqlquery);
           foreach ($postIds as $details) {
             array_push($idArray, $details->id . ",");
       }
           if ($current_user_Location == 'CR'){
   
           $query->set('post__in', $idArray);
       }
       }
   
       return $query;
   
       }
       add_filter('pre_get_posts','wpb_search_filter');
       ```
   
 * [@bcworkz](https://wordpress.org/support/users/bcworkz/) – Thank you for all 
   your guidance, been excellent help!! 🙂
    and i’m sure you will come across some
   of my questions in the future lol
    -  This reply was modified 6 years, 4 months ago by [aralmac123](https://wordpress.org/support/users/aralmac123/).
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12333492)
 * You’re welcome! Always happy to help where I can.

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Intranet breaking with Custom SQL code’ is closed to new replies.

## Tags

 * [query](https://wordpress.org/support/topic-tag/query/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)
 * [wpdb](https://wordpress.org/support/topic-tag/wpdb/)

 * In: [Developing with WordPress](https://wordpress.org/support/forum/wp-advanced/)
 * 11 replies
 * 3 participants
 * Last reply from: [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * Last activity: [6 years, 4 months ago](https://wordpress.org/support/topic/intranet-breaking-with-custom-sql-code/#post-12333492)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics