WordPress.org

1. utzutz
   Member
   Posted 1 year 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...

2. utzutz
   Member
   Posted 1 year ago #

   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!

3. utzutz
   Member
   Posted 1 year ago #

   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!

4. vtxyzzy
   Member
   Posted 1 year ago #

   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.

5. utzutz
   Member
   Posted 1 year ago #

   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... ^^

6. vtxyzzy
   Member
   Posted 1 year ago #

   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.

Topic Closed

This topic has been closed to new replies.