Searching for posts with custom data in a numeric range

  • Resolved utzutz

    (@utzutz)


    14 years, 1 month ago

    Based upon this:
    resolved post

    I’m stuck at the same problem, but my meta value starts with a currency sign. However, substring helped in this case, but now I encounter another problem -> the value in the meta_key is written like: $2.000 !
    Using this

    <?php
// Define the function that will create the join string
function mam_custom_field_range($keyname='',$min=false,$max=false) {
  global $mam_global_join,$wpdb;
  if ($keyname && ($min !== false || $max !== false)) {
    $mam_global_join =
      " JOIN $wpdb->postmeta acfr_$keyname ON
      ( $wpdb->posts.ID = acfr_$keyname.post_id
        AND acfr_$keyname.meta_key = '$keyname'";
    if ($min !== false) $mam_global_join .= " AND acfr_$keyname.meta_value >= $min";
    if ($max !== false) $mam_global_join .= " AND acfr_$keyname.meta_value <= $max";
    $mam_global_join .= ')';
  }
}
// Define the join filter function
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= $mam_global_join;
   return $join;
}
// Add the filter to the hooks
add_filter('posts_join','mam_posts_join');
?>

    The query treats 2.000 as 2… how do I solve this ?

    thanks in advance…

Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter utzutz

    (@utzutz)

    Okay, I tried a lot, but the lack of experience in php presents a huge hindrance !

    Additionally, I recognized another problem, as the price sometimes is written as $3.000-$3.200… this means smth like this:

    $parts = acfr_$keyname.meta_value;
$partsp = explode("-",$parts);
$pricem = $partsp[0];

    (the first part is enough for the sort)
    before

    if ($min !== false) $mam_global_join .= " AND (SUBSTRING(acfr_$keyname.meta_value,2)) >= $min";
    if ($max !== false) $mam_global_join .= " AND (SUBSTRING(acfr_$keyname.meta_value,2)) <= $max";

    After reading a bit in php.net, I tried to use smth like this:
    (ltrim(SUBSTRING(acfr_$keyname.meta_value,2)),"."))
    to, at least, delete the dot and the currency symbol -> didn’t work!

    Thread Starter utzutz

    (@utzutz)

    tried things like this:

    AND ltrim(acfr_$keyname.meta_value, ".") + (substring(acfr_$keyname.meta_value, 2)) >= $min";
    and
    (ltrim(substring(acfr_$keyname.meta_value, 2), "."))
    and, and …and !

    Can no one help me… simply reduce a meta_value of “$3.000” to smth that can be processed by mam_custom_field_range() !

    … and after that, a way to split a meta_value of “$2.000-$2.100” to “2000” – or “2,000” I’m not aware of what php needs to compare!

    The problem is that the string needs to be modified by the SQL, not by PHP. By the time the results are back to PHP, the SQL compare has already been done.

    The best solution is to store the unformatted numbers in the field and format with PHP after the query.

    A partial solution is to replace this (UNTESTED!):

    if ($min !== false) $mam_global_join .= " AND acfr_$keyname.meta_value >= $min";
    if ($max !== false) $mam_global_join .= " AND acfr_$keyname.meta_value <= $max";

    with this:

    if ($min !== false) $mam_global_join .= " AND REPLACE(REPLACE(acfr_$keyname.meta_value,',',''),'$','') >= $min";
    if ($max !== false) $mam_global_join .= " AND REPLACE(REPLACE(acfr_$keyname.meta_value,',',''),'$','') <= $max";

    That should take care of the single value. I don’t know of a way to fix the ‘$2,000 – $3,000’ case. You will have to edit those posts.

    Thread Starter utzutz

    (@utzutz)

    awesome… even works with combined prices. WP seems to take a $100-$200 as 100, thus even those show up in the query of 0-$100, or $200-$300 all $200 will be shown as well!

    Thanks, really, thank you very much!

    Best regards… ^^

    You are welcome. Now, please use the dropdown at top right to mark this topic ‘Resolved’ so that others researching a similar problem can see that there is a solution.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Searching for posts with custom data in a numeric range’ is closed to new replies.