Support

Account

Register Login

Home › Forums › General Issues › Query not running as expected

Solved

Query not running as expected

  • Hi all,

    I’m currently having issues running queries based on the Date Picker field and comparing this with the current date. I’ve opted to set the Return Format to “U” so that it returns a string which I can then compare to a string version of the current date.

    What I would like to do is display content on certain pages based on this comparison (greater than current date for “coming soon” and also the opposite for “now showing”). However, it seems that the data is getting very mixed up as I have content that should be on “now showing” which is displaying on “coming soon” instead.

    In addition I have the page split so that I can use an ACF to set certain films to be “featured” and therefore not show up in the lower results as well.

    Below is the code I have so far and a live deployment can be viewed on the following.

    <?php
get_header();
?>
<!-- START - Coming Soon -- Header -->
<section class="comingsoon-header">
    <h1 class="section__heading">Coming Soon</h1>
    <!-- START - Coming Soon Slider -->
    <?php
    $dateToday = date("U"); ?>
    <p><?php echo $dateToday ?></p>
<?php
    $comingSoonFilms = new WP_Query(array(
        'post_type' => 'film',
        'posts_per_page' => -1,
        'order' => 'ASC',
        'meta_query' => array(
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'type' => 'DATE',
                'compare' => '>',
            ),
            array(
                'key' => 'film_active',
                'value' => '1',
            ),
        )
    ));
    ?>
    <?php if ($comingSoonFilms->have_posts()) : ?>
        <!-- START - Coming Soon Slider -->
        <div class="section__slider-container text-center">
            <div class="swiper swiper-container swiper--section swiper--section--filmswide swiper--section--filmswide--comingsoon">
                <div class="swiper-wrapper">
                    <!-- Slides -->

                    <?php while ($comingSoonFilms->have_posts()) {
                        $comingSoonFilms->the_post();

                        if (get_field('film_comingsoon_featured_status') == true) {
                    ?>

                            <div class="swiper-slide">
                                <a href="<?php the_permalink(); ?>">
                                    <img src="<?php the_field('film_comingsoon_featured_image'); ?>" alt="" title="" class="swiper__imgbg" />
                                    <div class="comingsoon-header__details">
                                        <p class="comingsoon-header__details__heading"><?php the_title() ?> </p>
                                        <span class="cta">View More</span>
                                        <p class="comingsoon-header__releasedate"><i class="fas fa-clock"></i>Coming: <?php echo date('d/m/Y', get_field('film_releasedate')) ?><br />
                                        <br /><?php echo get_field('film_releasedate') ?></p>
                                    </div>
                                </a>
                            </div>
                    <?php
                        }
                    }
                    ?>

                </div>
                <div class="swiper-button-prev"></div>
                <div class="swiper-button-next"></div>
            </div>

        </div>
        <!-- END - Coming Soon Slider -->
</section>
<!-- START - Coming Soon -- More -->
<section>
    <h2 class="section__heading">More Coming Soon</h2>
    <div class="filmpanels filmpanels--grid">
        <?php while ($comingSoonFilms->have_posts()) {
            $comingSoonFilms->the_post();
            if (get_field('film_comingsoon_featured_status') == false) {
        ?>
                <div>
                    <a href="<?php the_permalink(); ?>" title="<?php the_title(); ?>">
                        <img src="<?php echo wp_get_attachment_url(get_field('film_poster')); ?>" alt="" title="" class="swiper__imgbg" />
                        <p><?php the_title(); ?></p>
                        <?php echo date('d/m/Y', get_field('film_releasedate')) ?>
                        <p><?php echo get_field('film_releasedate') ?></p>
                    </a>
                </div>

        <?php
            }
        }
        ?>
    </div>
</section>
<section class="results--pagination">
    <?php

        echo paginate_links(array(
            'total' => $comingSoonFilms->max_num_pages
        ));

        wp_reset_query();

    ?>
<?php else : ?>
    <p>No films currently coming soon!</p>
<?php endif; ?>
</section>
<?php
get_footer();
?>

    https://apexcinemas.andrewcourtney.co.uk/coming-soon/

    Thanks in advance

  • Ok so on review I think it must be something to do with the conditions within the query I’m running as I’ve double-checked the Return Format of the ACF and it’s set to a Custom value of “U” which should match back to my dateToday variable value.

    $comingSoonFilms = new WP_Query(array(
        'post_type' => 'film',
        'posts_per_page' => -1,
        'order' => 'ASC',
        'meta_query' => array(
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'type' => 'DATE',
                'compare' => '>',
            ),
            array(
                'key' => 'film_active',
                'value' => '1',
            ),
        )
    ));

    As you can see from the live deployment, I’ve printed both the formatted and unformatted data for the release date. Therefore I don’t think this is related to the Advanced Custom Field itself.

    Any thoughts please or things I could try to debug the issue with?

  • Ok, so after some thought I had a feeling that I had to specify that I wanted to have both queries return true for it to display the relevant data. I adjusted my query to include a “relation” but still the incorrect results are showing for Coming Soon, and no results are pulling through for Now Showing.

    https://apexcinemas.andrewcourtney.co.uk/coming-soon/
    https://apexcinemas.andrewcourtney.co.uk/now-showing/

    Below are the amended queries for each page:

    Coming Soon

        $comingSoonFilms = new WP_Query(array(
        'post_type' => 'film',
        'posts_per_page' => -1,
        'order' => 'ASC',
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'type' => 'DATE',
                'compare' => '>',
            ),
            array(
                'key' => 'film_active',
                'value' => '1',
            ),
        )
    ));

    Now Showing

        $nowShowingFilms = new WP_Query(array(
        'post_type' => 'film',
        'posts_per_page' => -1,
        'order' => 'DESC',
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'type' => 'DATE',
                'compare' => '<',
            ),
            array(
                'key' => 'film_active',
                'value' => '1',
            ),
        )
    ));

    If any other details would be helpful let me know!

    Thanks in advance.

  • Ok, so it would appear this was due to the Return Format I had setup prior to the query being run.

    I’m still having problems understanding the compare, especially when the type is set to “DATE”, however.

    If I set the Return Format for the ACF to “Ymd” then it breaks the release date set on the individual films, whereas if I use the Custom value of “U” and run my query then it displays correctly (I then have to change the formatting later so that it displays in a “d/m/Y” format. For example, one film changes the set release date from 21/07/2028 to 23/08/1970?

        $dateToday = date('Ymd');

    $comingSoonFilms = new WP_Query(array(
        'post_type' => 'film',
        'posts_per_page' => -1,
        'order' => 'ASC',
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'type' => 'DATE',
                'compare' => '>',
            ),
            array(
                'key' => 'film_active',
                'value' => '1',
            ),
        )
    ));

  • The return value doe not matter and the “type” value of the field in the meta query cannot be “DATE”. You must query using the format the ACF stores in the DB. In ACF a date field is always stored in the format “Ymd”. Because this is not a valid DB Date format the field type must be set to “NUMERIC” or “CHAR”.

    
$dateToday = date('Ymd');

......

        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'film_releasedate',
                'value' => $dateToday,
                'compare' => '>',
            ),
            array(.........

  • Ah ok thanks for the explanation! I’ve now changed the Return Format to Ymd to make the comparison and any if statements further down the page easier to work with. I’ve also removed the following line from all queries.

    'type' => 'DATE',

    My next task is to simply re-format the date in terms of how it’s displayed as it’s just using Ymd for the moment. I presume I can use something like the following (haven’t got time to test at the moment until later today)?

    $date = get_field('date');
$date2 = date("F j, Y", strtotime($date));

    https://apexcinemas.andrewcourtney.co.uk/coming-soon/
    https://apexcinemas.andrewcourtney.co.uk/films/no-time-to-die-2/

    Thanks in advance!

  • As I said, the return format of the field will not affect a query on that field. You can set the return format for the ACF field to whatever you need ti to be for display.

  • Thanks John! I ended up formatting the date using the following code since I’m using the field to run several if statements inline between today’s date and the field results elsewhere on the page to show/hide content.

    <?php echo date("d/m/Y", strtotime(get_field('film_releasedate'))); ?>

    Please let me know if you have any further advice though – much appreciated!

  • It you are going to do date formatting yourself then you should set the return format to a valid PHP date format. I generally use ‘Y-m-d’.

    ‘Ymd’ is not a valid date format for strtotime() or any of the other PHP function that take date values.

  • Thanks John! I’ve now switched the Return Format to a custom value of “Y-m-d”.
    I’ve also updated the dateToday variable set for the comparison to match the same format.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.