php - Function wpdb::prepare was called incorrectly. The query argument of wpdb::prepare() must have a placeholder

admin管理员组

文章数量:1122832

I am getting the following error: Function wpdb::prepare was called incorrectly. The query argument of wpdb::prepare() must have a placeholder. I have gone through plenty of other similar questions here on StackExchange, but I am still not understanding how I am getting the error. I am using placeholders in the query arguments.

EDIT: I should be clear, it's still working to get the db records, but I'm seeing this notice in my debug log on every page refresh.

Here is my code:

public function get_local_records( $filters = [] ) {
    global $wpdb;
    $table_name = $wpdb->prefix.$this->table_suffix;

    // Generate a unique cache key based on the filters
    $cache_key = 'local_records_' . md5( serialize( $filters ) );

    // Try to get cached results
    $cached_results = wp_cache_get( $cache_key );

    if ( $cached_results !== false ) {
        return $cached_results;
    }

    // Start building the SQL queries
    $base_sql = "SELECT * FROM $table_name WHERE 1=1";
    $count_sql = "SELECT COUNT(*) FROM $table_name WHERE 1=1";
    
    // Prepare the filters and query parameters
    $query_params = [];
    // $query_format = [];

    // Loop through the filters to add conditions dynamically
    foreach ( $filters as $key => $value ) {
        if ( !empty( $value ) ) {
            switch ( $key ) {
                case 'value':
                    $base_sql .= " AND $key LIKE %s";
                    $count_sql .= " AND $key LIKE %s";
                    $query_params[] = '%' . sanitize_text_field( $value ) . '%';
                    break;
                case 'type':
                case 'action':
                case 'site':
                    $base_sql .= " AND $key = %s";
                    $count_sql .= " AND $key = %s";
                    $query_params[] = sanitize_text_field( $value );
                    break;
                case 'user':
                    $base_sql .= " AND $key = %d";
                    $count_sql .= " AND $key = %d";
                    $query_params[] = intval( $value );
                    break;
            }
        }
    }

    // Prepare and execute the count query
    $count_query = $wpdb->prepare( $count_sql, ...$query_params );
    $total_count = $wpdb->get_var( $count_query );

    // Pagination parameters
    $paged = isset( $filters[ 'paged' ] ) ? max( 1, intval( $filters[ 'paged' ] ) ) : 1;
    $per_page = isset( $filters[ 'per_page' ] ) ? intval( $filters[ 'per_page' ] ) : 25;

    // Calculate offset
    $offset = ( $paged - 1 ) * $per_page;

    // Add LIMIT and OFFSET to the SQL query
    $paginated_sql = $base_sql . $wpdb->prepare( " LIMIT %d OFFSET %d", $per_page, $offset );

    // Prepare and execute the paginated query
    $query = $wpdb->prepare( $paginated_sql, ...$query_params );
    $results = $wpdb->get_results( $query );

    // Convert to array of arrays
    $results = array_map( function( $object ) {
        return (array) $object;
    }, $results);

    // Cache the results
    $cached_results = [
        'results' => $results,
        'count'   => $total_count
    ];
    wp_cache_set( $cache_key, $cached_results );

    return $cached_results;
} // End get_local_records()

I am getting the following error: Function wpdb::prepare was called incorrectly. The query argument of wpdb::prepare() must have a placeholder. I have gone through plenty of other similar questions here on StackExchange, but I am still not understanding how I am getting the error. I am using placeholders in the query arguments.

EDIT: I should be clear, it's still working to get the db records, but I'm seeing this notice in my debug log on every page refresh.

Here is my code:

public function get_local_records( $filters = [] ) {
    global $wpdb;
    $table_name = $wpdb->prefix.$this->table_suffix;

    // Generate a unique cache key based on the filters
    $cache_key = 'local_records_' . md5( serialize( $filters ) );

    // Try to get cached results
    $cached_results = wp_cache_get( $cache_key );

    if ( $cached_results !== false ) {
        return $cached_results;
    }

    // Start building the SQL queries
    $base_sql = "SELECT * FROM $table_name WHERE 1=1";
    $count_sql = "SELECT COUNT(*) FROM $table_name WHERE 1=1";
    
    // Prepare the filters and query parameters
    $query_params = [];
    // $query_format = [];

    // Loop through the filters to add conditions dynamically
    foreach ( $filters as $key => $value ) {
        if ( !empty( $value ) ) {
            switch ( $key ) {
                case 'value':
                    $base_sql .= " AND $key LIKE %s";
                    $count_sql .= " AND $key LIKE %s";
                    $query_params[] = '%' . sanitize_text_field( $value ) . '%';
                    break;
                case 'type':
                case 'action':
                case 'site':
                    $base_sql .= " AND $key = %s";
                    $count_sql .= " AND $key = %s";
                    $query_params[] = sanitize_text_field( $value );
                    break;
                case 'user':
                    $base_sql .= " AND $key = %d";
                    $count_sql .= " AND $key = %d";
                    $query_params[] = intval( $value );
                    break;
            }
        }
    }

    // Prepare and execute the count query
    $count_query = $wpdb->prepare( $count_sql, ...$query_params );
    $total_count = $wpdb->get_var( $count_query );

    // Pagination parameters
    $paged = isset( $filters[ 'paged' ] ) ? max( 1, intval( $filters[ 'paged' ] ) ) : 1;
    $per_page = isset( $filters[ 'per_page' ] ) ? intval( $filters[ 'per_page' ] ) : 25;

    // Calculate offset
    $offset = ( $paged - 1 ) * $per_page;

    // Add LIMIT and OFFSET to the SQL query
    $paginated_sql = $base_sql . $wpdb->prepare( " LIMIT %d OFFSET %d", $per_page, $offset );

    // Prepare and execute the paginated query
    $query = $wpdb->prepare( $paginated_sql, ...$query_params );
    $results = $wpdb->get_results( $query );

    // Convert to array of arrays
    $results = array_map( function( $object ) {
        return (array) $object;
    }, $results);

    // Cache the results
    $cached_results = [
        'results' => $results,
        'count'   => $total_count
    ];
    wp_cache_set( $cache_key, $cached_results );

    return $cached_results;
} // End get_local_records()

• php
• functions
• database
• query
• wpdb

Share Improve this question asked Sep 19, 2024 at 22:21 AristoclesAristocles 13977 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

There are 2 places that could have issues:

1. Your first wpdb::prepare() here:

// Prepare and execute the count query
$count_query = $wpdb->prepare( $count_sql, ...$query_params );

The initial value you set for $count_sql is "SELECT COUNT(*) FROM $table_name WHERE 1=1", and if $filters doesn't have any key matching your switch, the value of $count_sql will remain as its initial value, and as you can see, there are no placeholders in your initial value, which leads the the error. You can avoid it by giving default placeholder(s) in $count_sql, and default value(s) in $query_params.

2. The following code (your last wpdb::prepare()) definitely leads to error:

// Prepare and execute the paginated query
$query = $wpdb->prepare( $paginated_sql, ...$query_params );

The reason for this is that you have this query for $paginated_sql:

// Add LIMIT and OFFSET to the SQL query
$paginated_sql = $base_sql . $wpdb->prepare( " LIMIT %d OFFSET %d", $per_page, $offset );

Since you already use $wpdb->prepare() for $paginated_sql, all of its placeholders have been updated with the given values. As a result, there are no placeholders in $paginated_sql anymore, leading to the error when you run $wpdb->prepare() again.

本文标签： phpFunction wpdbprepare was called incorrectly The query argument of wpdbprepare() must have a placeholder