Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a meta_query that gets all the items with a price range between 2 variables. My problem is that 10 000 is not the same as 10000. How can I fix this?

I have been googling but I'n not sure what to google so I'm not really getting anywhere.

Help would be appreciated

Thanks Hanè

Edit - Code Added

    $kw_min = mysql_real_escape_string($_GET['kw_min']);
    $kw_max = mysql_real_escape_string($_GET['kw_max']);
    $pr_min = mysql_real_escape_string($_GET['pr_min']);
    $pr_max = mysql_real_escape_string($_GET['pr_max']);
    if ( !empty( $kw_min ) && !empty( $kw_max ) && !empty( $pr_min ) && !empty( $pr_max ) ) {
        $args = array(
            'post_type' => 'exc_agriq_equipment', 
            'exc_equipment_cat' => $term->slug,
            'order' => 'DESC',
            'posts_per_page' => 12, 
            'paged'=>$paged,
            'meta_query' => array(
                array(
                    'key' => 'exc_agriq_equipment_kw',
                    'value' => array( $kw_min, $kw_max ),
                    'type' => 'numeric',
                    'compare' => 'BETWEEN'
                ),
                array(
                    'key' => 'exc_agriq_equipment_price',
                    'value' => array( $pr_min, $pr_max ),
                    'type' => 'numeric',
                    'compare' => 'BETWEEN'
                ),
              ),
         );
    }
share|improve this question
    
Drop the mysql_real_escape_string. All that gets handled via addslashes (and its relevants) internally. Question: Where comes the 10 000 vs. 10000 into the game? Is this a search string? How does it get entered? Currently your question doesn't make any sense to me. –  kaiser Nov 21 '12 at 17:35
    
@kaiser: the comments thread is not the appropriate place for providing answers... ;) –  Chip Bennett Nov 21 '12 at 17:37
    
I have a search box where I select a custom taxonomy and I can enter a min and max price it gets submitted and then my custom post must be filtered by the custom tax and the meta query. If I enter min price as 10000 and the max price as 20000 I get 0 results but if I enter 10 000 and 20 000 the results show. –  HaneD Nov 21 '12 at 17:44
    
So, this really isn't a WordPress question. You're really asking how to sanitize two text strings so that they can be compared. –  Chip Bennett Nov 21 '12 at 17:53
2  
Could you please provide examples of stored prices (does it include spaces? why?) and what user input can be (is it just text? can user type in more things other than numbers and space?). –  Rarst Nov 21 '12 at 19:20
show 3 more comments

3 Answers

up vote 1 down vote accepted

The problem is that you're comparing 10000 and 10 000 as strings, and as such they are not the same value.

So, you either need to sanitize the strings to ensure that both return an equivalent value, or you'll need to force them to be integers, so that they'll be evaluated as the same value.

Edit

Your best solution is probably to replace your text form field with a select, that includes pre-determined entry values (e.g. 10,000, 20,000, 30,000, etc.). That way, you will have reliable data to use in your meta_query.

share|improve this answer
    
That's what I thought but I have no idea how to do it –  HaneD Nov 21 '12 at 17:46
    
How are the values being input? Don't you have control over that? –  Chip Bennett Nov 21 '12 at 17:50
    
Because the value is a price I prefer to add it as 10 000 (easier to read) but if someone is doing a search they wont put the space. I have a custom meta box with custom field price and then just an input field on my search form. Hope that helps –  HaneD Nov 21 '12 at 17:54
    
See updated answer. I would recommend replacing your text entry field with a select, so that you can pre-define, and thus control, the user input. –  Chip Bennett Nov 21 '12 at 17:55
add comment

Well, "10 000" is not the same as "10000". "10 000" isn't even a number, strictly speaking, as a space is not a digit. In PHP, (int)"10 000" returns 10 so you can't even force it reliably. WordPress's absint does the same. There may be a built-in function that will handle that in a human-like way but I'd have to look around for it.

I would recommend normalizing your data as it goes into the database, as it is inserted, so that you don't have to do any data manipulation as you are trying to retrieve the information. That is, figure out what your data should look like and make sure it fits before you insert it. If your prices are numbers, as I assume they are, check your data for anything that isn't a digit and either reject it (if you are paranoid) or remove everything that isn't a digit, then insert that. Something like...

$price = preg_replace('/[^0-9]/','',"$price");

Now when you need to search you know what your format is and you can do the same thing to any user supplied string before you send it to the database query, or reject data that doesn't match (if you are paranoid, which I usually am, by the way). That should get you much more reliable results.

You can use MySQL's REPLACE when you retrieve the data but I don't think you can do that using core functions, unless you start messing with query filters, but that is really the backwards to do it.

share|improve this answer
    
I will give this a try. Is there away to do this and make my values 1 000, 10 000 , 100 000 and 1 000 000? Or am I asking to much? –  HaneD Nov 21 '12 at 18:00
    
You can do that, but do that when you display. Keep things clean in the DB. Try this: php.net/manual/en/function.number-format.php –  s_ha_dum Nov 21 '12 at 18:06
    
Cool this works, now I just need to update my whole database. What would you recommend to change all the values in my database? –  HaneD Nov 21 '12 at 18:20
add comment

Preparing prices

WP has some stuff built in, as well as PHP has. The really tricky stuff is to regard all possible user behavior. You won't catch everything, but you can catch alot, like unintentional white spaces, currency signs, etc. The function below is well commented and will show you how I think the user experience can be made better. I assume that your users will have a similar language as the one set in your CMS.

/**
 * Convert a number to a price, formatted according to the blogs
 * currency (which is the value returned by the WPLANG constant)
 * 
 * @param  unknown_type $price 
 * @return integer      $price
 */
function wpse73492_validate_price( $price )
{
    global $wp_locale;

    // Avoid wrong user input: The decimals can only be an integer, no float or string
    $decimals = absint( $decimals );

    // Users might add leading/trailing white space:
    $price = trim( $price );

    // Users might use a lot of funky things in the search
    // Example "shoes 10000" - they simply don't know better
    // But: We allow a decimal point and the thousands separator
    $regex = sprintf( 
         "0-9"
        ,$wp_locale->number_format['decimal_point']
        ,$wp_locale->number_format['thousands_sep']
    );
    preg_replace( "/[^{$regex}]/i", "", $string );

    // Lets convert it to a "money unit"
    // A "money unit" is something extremely different in a lot of contries.
    // Let's take that behavior into account
    // First we need to set the local and the charset: "en_EN.UTF-8"
    setlocale( 
         LC_MONETARY
        ,sprintf(
             '%s.%s'
            ,get_locale()
            ,get_bloginfo( 'charset' )
    );
    // Second, we need to convert it to local currency
    // The WP function is a wrapper for the PHP fn `number_format()`
    // The 2nd argument defines the number of decimals, which should be 0
    // This cares about removing every part that is no absolute integer, but a float.
    $price = number_format_i18n( $price, 0 );

    // Now we need some more funkieness:
    // The DB isn't Babylon. Chinese and such is of our way
    // Remove the thousands separator
    $price = str_replace( $wp_locale->number_format['thousands_sep'], '', $price );

    // Last, we help the DB, and talk to it English
    // This means that we need to get rid of ALOT: Everything non-numeric
    preg_replace( "/[^0-9]/i", "", $string );

    // Just to be sure, we then go and make it an absolute integer
    return absint( $price );
}

Then simply run the function on all needed values.

// Call it like this:
wpse73492_validate_price( $_GET['pr_max'] );

// Or: If you're sure you got nothing than prices from $_GET
// (Hint: If you got other stuff, you can extract it up front)
$prices = array_map( 'wpse73492_validate_price', $_GET );
share|improve this answer
1  
I will try this tomorrow thanks –  HaneD Nov 21 '12 at 19:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.