wpdb - Sql query triggered twice

admin管理员组

文章数量:1026989

I am trying to collect data from a search box. Conditions; if the value exists, update the counter field based on how many times it's been searched.

Problem: when a duplicate value searched in the form, the counter value increases by 2 not 1. So, if its current value is 5 and the term has been searched again the value in the database updated to 7, not 6. Any idea on the cause?

function content_filter( $where, $wp_query ){

    global $wpdb;
    if ( $search_term = $wp_query->get( 'search_prod_content' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_content LIKE \'%' . esc_sql(  $search_term  ) . '%\'';        

    }


    // first check if data exists with select query

    $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
    if($wpdb->num_rows > 0) {


    $sql = $wpdb->prepare(
    "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");

    $wpdb->query($sql);


    }
    // if not exist in the database then insert it
    else{

        $now = new DateTime();
        $datesent=$now->format('Y-m-d H:i:s');  
        $sql = $wpdb->prepare(
         "INSERT INTO `search_product` (`TERM`,`DATE`) values (%s,%s)", 
          $search_term, $datesent);

        $wpdb->query($sql);
      }



    return $where;
}

I am trying to collect data from a search box. Conditions; if the value exists, update the counter field based on how many times it's been searched.

Problem: when a duplicate value searched in the form, the counter value increases by 2 not 1. So, if its current value is 5 and the term has been searched again the value in the database updated to 7, not 6. Any idea on the cause?

function content_filter( $where, $wp_query ){

    global $wpdb;
    if ( $search_term = $wp_query->get( 'search_prod_content' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_content LIKE \'%' . esc_sql(  $search_term  ) . '%\'';        

    }


    // first check if data exists with select query

    $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
    if($wpdb->num_rows > 0) {


    $sql = $wpdb->prepare(
    "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");

    $wpdb->query($sql);


    }
    // if not exist in the database then insert it
    else{

        $now = new DateTime();
        $datesent=$now->format('Y-m-d H:i:s');  
        $sql = $wpdb->prepare(
         "INSERT INTO `search_product` (`TERM`,`DATE`) values (%s,%s)", 
          $search_term, $datesent);

        $wpdb->query($sql);
      }



    return $where;
}

• wpdb

Share Improve this question edited Mar 22, 2019 at 4:05 zgnky asked Mar 21, 2019 at 19:55 zgnkyzgnky 333 bronze badges 3

• You should first check if the value already exists in the database and if not, only then you insert the value. – Sally CJ Commented Mar 22, 2019 at 1:13
• Did what you suggested but getting a different error this time. Please check the description. – zgnky Commented Mar 22, 2019 at 3:06
• Please check and try the suggestions in my answer. And do let me know if the problem is solved.. OK? :) – Sally CJ Commented Mar 22, 2019 at 4:22

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

Try Applying These Changes

1. Add this conditional before the // first check if data exists with select query:

   // Do nothing else if the search term is empty.
   if ( empty( $search_term ) ) {
       return $where;
   }
   

   Update: You may also want to check if it's the main query and if not, exit the function:

   if ( empty( $search_term ) ||        // missing search term
       ! $wp_query->is_main_query() ) { // or not the main query
       return $where;
   }
   

2. I would not do SELECT * (select all) records use $wpdb->get_results() just to check if an existing entry for a search term exists in the database or not. Secondly, you should've used $wpdb->prepare() instead of the TERM = '".$search_term."'. So I'd replace this:

   $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
   if($wpdb->num_rows > 0) {
   

   with this:

   $sql = $wpdb->prepare( "SELECT TERM FROM search_product WHERE TERM = %s LIMIT 1", $search_term );
   if ( $wpdb->get_var( $sql ) ) {
   

3. Again in this code, you should've not used TERM = '".$search_term."'. So I'd replace this:

   $sql = $wpdb->prepare(
   "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");
   

   with this:

   $sql = $wpdb->prepare( "UPDATE search_product SET COUNTER = COUNTER + 1 WHERE TERM = %s", $search_term );
   

4. And the code after the // if not exist in the database then insert it can be rewritten to:

   else {
       $wpdb->insert( 'search_product', [
           'TERM'    => $search_term,
           'DATE'    => current_time( 'mysql' ),
       //  'COUNTER' => 1, // <- uncomment if the default value of the column is not 1
       ] );
   }
   

You should check the Search form inserted twice in your website. It may stupid answer but you should check it first.

Problem solved. Thank you very much for your help with this.

PS: I found another article on this topic related to prefetches. Basically, to avoid the query runs twice on your page, folks are mentioning to add the following code to your function.php file.

function remove_dns_prefetch($hints, $relation_type)
{
    if ('dns-prefetch' === $relation_type) {
        return array_diff(wp_dependencies_unique_hosts(), $hints);
    }
    return $hints;
}

Another quick note: when I search the same term on the same browser, the counter is not increasing. However, if I go incognito or use another browser and search the same term, then the counter is increasing.

I am trying to collect data from a search box. Conditions; if the value exists, update the counter field based on how many times it's been searched.

Problem: when a duplicate value searched in the form, the counter value increases by 2 not 1. So, if its current value is 5 and the term has been searched again the value in the database updated to 7, not 6. Any idea on the cause?

function content_filter( $where, $wp_query ){

    global $wpdb;
    if ( $search_term = $wp_query->get( 'search_prod_content' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_content LIKE \'%' . esc_sql(  $search_term  ) . '%\'';        

    }


    // first check if data exists with select query

    $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
    if($wpdb->num_rows > 0) {


    $sql = $wpdb->prepare(
    "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");

    $wpdb->query($sql);


    }
    // if not exist in the database then insert it
    else{

        $now = new DateTime();
        $datesent=$now->format('Y-m-d H:i:s');  
        $sql = $wpdb->prepare(
         "INSERT INTO `search_product` (`TERM`,`DATE`) values (%s,%s)", 
          $search_term, $datesent);

        $wpdb->query($sql);
      }



    return $where;
}

I am trying to collect data from a search box. Conditions; if the value exists, update the counter field based on how many times it's been searched.

Problem: when a duplicate value searched in the form, the counter value increases by 2 not 1. So, if its current value is 5 and the term has been searched again the value in the database updated to 7, not 6. Any idea on the cause?

function content_filter( $where, $wp_query ){

    global $wpdb;
    if ( $search_term = $wp_query->get( 'search_prod_content' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_content LIKE \'%' . esc_sql(  $search_term  ) . '%\'';        

    }


    // first check if data exists with select query

    $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
    if($wpdb->num_rows > 0) {


    $sql = $wpdb->prepare(
    "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");

    $wpdb->query($sql);


    }
    // if not exist in the database then insert it
    else{

        $now = new DateTime();
        $datesent=$now->format('Y-m-d H:i:s');  
        $sql = $wpdb->prepare(
         "INSERT INTO `search_product` (`TERM`,`DATE`) values (%s,%s)", 
          $search_term, $datesent);

        $wpdb->query($sql);
      }



    return $where;
}

• wpdb

Share Improve this question edited Mar 22, 2019 at 4:05 zgnky asked Mar 21, 2019 at 19:55 zgnkyzgnky 333 bronze badges 3

• You should first check if the value already exists in the database and if not, only then you insert the value. – Sally CJ Commented Mar 22, 2019 at 1:13
• Did what you suggested but getting a different error this time. Please check the description. – zgnky Commented Mar 22, 2019 at 3:06
• Please check and try the suggestions in my answer. And do let me know if the problem is solved.. OK? :) – Sally CJ Commented Mar 22, 2019 at 4:22

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

Try Applying These Changes

1. Add this conditional before the // first check if data exists with select query:

   // Do nothing else if the search term is empty.
   if ( empty( $search_term ) ) {
       return $where;
   }
   

   Update: You may also want to check if it's the main query and if not, exit the function:

   if ( empty( $search_term ) ||        // missing search term
       ! $wp_query->is_main_query() ) { // or not the main query
       return $where;
   }
   

2. I would not do SELECT * (select all) records use $wpdb->get_results() just to check if an existing entry for a search term exists in the database or not. Secondly, you should've used $wpdb->prepare() instead of the TERM = '".$search_term."'. So I'd replace this:

   $datum = $wpdb->get_results("SELECT * FROM search_product WHERE TERM = '".$search_term."'");
   if($wpdb->num_rows > 0) {
   

   with this:

   $sql = $wpdb->prepare( "SELECT TERM FROM search_product WHERE TERM = %s LIMIT 1", $search_term );
   if ( $wpdb->get_var( $sql ) ) {
   

3. Again in this code, you should've not used TERM = '".$search_term."'. So I'd replace this:

   $sql = $wpdb->prepare(
   "UPDATE search_product SET COUNTER = COUNTER + 1  WHERE TERM = '".$search_term."'");
   

   with this:

   $sql = $wpdb->prepare( "UPDATE search_product SET COUNTER = COUNTER + 1 WHERE TERM = %s", $search_term );
   

4. And the code after the // if not exist in the database then insert it can be rewritten to:

   else {
       $wpdb->insert( 'search_product', [
           'TERM'    => $search_term,
           'DATE'    => current_time( 'mysql' ),
       //  'COUNTER' => 1, // <- uncomment if the default value of the column is not 1
       ] );
   }
   

You should check the Search form inserted twice in your website. It may stupid answer but you should check it first.

Problem solved. Thank you very much for your help with this.

PS: I found another article on this topic related to prefetches. Basically, to avoid the query runs twice on your page, folks are mentioning to add the following code to your function.php file.

function remove_dns_prefetch($hints, $relation_type)
{
    if ('dns-prefetch' === $relation_type) {
        return array_diff(wp_dependencies_unique_hosts(), $hints);
    }
    return $hints;
}

Another quick note: when I search the same term on the same browser, the counter is not increasing. However, if I go incognito or use another browser and search the same term, then the counter is increasing.

本文标签： wpdbSql query triggered twice