I have created a page that receives information and filters the posts by that choice. The page gets the information with _GET and I use the following code to send the query

$postss = new WP_Query([
                'post_type'      => 'post',
                'meta_query' => 
                array( 
                    array(
                        'key' => 'City:',
                        'value' => $city,
                        'compare' => 'LIKE',
                    ),
                    array(
                        'relation' => 'OR',
                        array('key' => 'Time:',
                        'value' => 'hour',
                        'compare' => 'LIKE',
                        ),
                        array('key' => 'Time:',
                        'value' => 'min',
                        'compare' => 'LIKE',
                        ),
                    ),
                ),
                'cat' => $category,
                'posts_per_page' => 9,
                'post_status'    => 'publish',
                'paged'          => $paged
            ]);

However my issue is, I have an input where the user can specify the city, then the category and then the time. The city and category work fine, but when we come to time I have placed few options with a drop down : under 1 hour, under 2 hours, under 3 hours etc . With this I can easily check with php if the time value is 1 2 3 etc and simply create the query

where time LIKE '%hour%'

however when the user chooses the 1, means that it's not just 1 hour, but it can also include minutes, so for example 30 minutes, so the results shold include 1 hour, but all minutes, so the query should be something like

where time like '%min%' OR time like '%hour%' 

Also there is an issue, I don't know how exactly to add the % symbol (because sometimes there is 10 min sometimes it's 20 minutes - plural) so

LIKE '%min%' 

would be okay, but not the same for hous, since if i do

LIKE '%hour%'

it will also include hours (2 3, not just 1 hour), so it should be

where time like '%hour'

in sql is easy, but with the wp_query I am not sure where to place the % symbol.

ps:I read that when I use the LIKE with meta_query automatically adds the % symbols at fron and end of the string, is there a way to remove them and just add it at front or in back, but not both?

Example values in meta: City:New York, Tokyo, London

Time:10 minutes, 5 minutes, 20 minutes, 1 hour, 1.5 hours, 2 hours, 3 hours

I have created a page that receives information and filters the posts by that choice. The page gets the information with _GET and I use the following code to send the query

$postss = new WP_Query([
                'post_type'      => 'post',
                'meta_query' => 
                array( 
                    array(
                        'key' => 'City:',
                        'value' => $city,
                        'compare' => 'LIKE',
                    ),
                    array(
                        'relation' => 'OR',
                        array('key' => 'Time:',
                        'value' => 'hour',
                        'compare' => 'LIKE',
                        ),
                        array('key' => 'Time:',
                        'value' => 'min',
                        'compare' => 'LIKE',
                        ),
                    ),
                ),
                'cat' => $category,
                'posts_per_page' => 9,
                'post_status'    => 'publish',
                'paged'          => $paged
            ]);

However my issue is, I have an input where the user can specify the city, then the category and then the time. The city and category work fine, but when we come to time I have placed few options with a drop down : under 1 hour, under 2 hours, under 3 hours etc . With this I can easily check with php if the time value is 1 2 3 etc and simply create the query

where time LIKE '%hour%'

however when the user chooses the 1, means that it's not just 1 hour, but it can also include minutes, so for example 30 minutes, so the results shold include 1 hour, but all minutes, so the query should be something like

where time like '%min%' OR time like '%hour%' 

Also there is an issue, I don't know how exactly to add the % symbol (because sometimes there is 10 min sometimes it's 20 minutes - plural) so

LIKE '%min%' 

would be okay, but not the same for hous, since if i do

LIKE '%hour%'

it will also include hours (2 3, not just 1 hour), so it should be

where time like '%hour'

in sql is easy, but with the wp_query I am not sure where to place the % symbol.

ps:I read that when I use the LIKE with meta_query automatically adds the % symbols at fron and end of the string, is there a way to remove them and just add it at front or in back, but not both?

Example values in meta: City:New York, Tokyo, London

Time:10 minutes, 5 minutes, 20 minutes, 1 hour, 1.5 hours, 2 hours, 3 hours

• plugins

• what are the values of your meta? These are highly unusual, e.g. I would expect a time to be stored as a standardised time type value that the database can understand, not a string of text such as "5 hours 3 min". You may also find a performance improvement by using a city taxonomy instead of using post meta. – Tom J Nowell ♦ Commented Feb 17, 2022 at 12:19
• Here are some example values: Time could be: 50 minutes; 10 minutes, 1 hour; 1.5 hours; 2 hours;3 hours And the point is if the user chooses - Under 1 hour we should show all that contains "min" and all that contain the word hour (meaning singular). When the user choses 2 hours for example we need to include again "min" the word "hour" but this time with % symbol, so it can be singular and plural. – Wordprez Commented Feb 17, 2022 at 13:51
• the words "hour" and "minute" etc do not need to be stored in your post meta, they can be added to the UI in the frontend/WP Admin. By including them in your stored values you are making life much harder, and eliminating lot of possible solutions. For example there is a trivial solution that could be used by telling WP it's a date/time value and doing a comparison, but this is not an option for you as long as you used this bespoke method of storing the value. Do not store "5 hours 20 minutes", store "5:20:00", giving you access to all the date/time comparison/math/operators – Tom J Nowell ♦ Commented Feb 17, 2022 at 15:48
• I reversed the LIKE to NOT LIKE and seems to be working, if I say NOT LIKE 'hours' this shows anything that is singular of hour, and any minutes, however when the user selects 2 hours for example then the not like comparison will become something like - not like "min" not like "1 hour" etc. I guess your idea is better, I should just change the hours mins to the standard format, and then when I show them I should convert them. What would be the standard for the following examples: 20 mins; 1.5 hours; 5 hours; 00:20:00, 01:30:00,05:00:00 ? – Wordprez Commented Feb 17, 2022 at 16:33
• YYYY-MM-DD HH:MM:SS, but even storing it as just the number of minutes e.g. 60 would let you use the math comparators such as BETWEEN or >. You can use WP functions such as human_time_diff or human_readable_duration to display it as human readable text, and it'll even localise it. Your current solution is unintelligable in say german or japanese – Tom J Nowell ♦ Commented Feb 17, 2022 at 17:06

1 Answer 1

Instead of storing your values as "5 hours 13 minutes" instead store them as timestamps, e.g. 5:13 for 5h 13m

This way you can use the date/time comparator operations to answer questions

E.g. to get items under 5 hours and 30 minutes:

$args = [
    ...
    'meta_query' => [
        [
            'meta_key'     => 'time',
            'meta_value'   => date( "5:30" ),
            'meta_compare' => '<',
            'type'         => 'TIME',
        ],
    ],
];
$query = new WP_Query( $args );

This will match a 3 hour post, or a 4:20 post, or a 5h 29min post.

You may need to add :00 for the seconds on to the end of all code and values.

For display you can use human_readable_duration. This will include the seconds though, but the function is very simple so copying and modifying it to your requirements should be straight forward.