Support

Account

Home Forums ACF PRO Empty Field Check in SQL

Solved

Empty Field Check in SQL

  • Apologes if this is in the docs or forum, I found some things relating to checking against an empty field but not what I am looking for.

    When I am looping I want to check that a field doesn’t have a value as well as a value being below a threshold. Here is the code I am currently trying

    'meta_query'	=> array(
    		'relation'		=> 'AND',
    		array(
          'key'		=> 'sale_price',
    			'value'		=> 300000,
    			'type'		=> 'NUMERIC',
    			'compare'	=> '<'
        ),
    		array(
    			'key'		=> 'status',
    			'value'		=> '',
    			'compare'	=> 'LIKE'
    		)

    I also tried this ` ‘meta_key’ => ‘status’,
    ‘meta_value’ => null,`

    So I have checked to see if like ” and also checked if it is NULL.
    I have inherited the code and am trying to get up to speed with the setup.
    Thanks

  • You need to check that the value “EXISTS” or “NOT EXISTS” for meta values that have never been set as well has empty string “” values for those that have been set but do not have a value. https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

  • Thanks it gave me more to experiment with but even then I still can’t seem to get it right I have this if anyone knows why..

    array(
          'key'		=> 'sale_price',
    			'value'		=> 300000,
    			'type'		=> 'NUMERIC',
    			'compare'	=> '<'
        ),
    		array(
    			'key'		=> 'status',
    			'value'		=> '',
    			'compare'	=> 'EXISTS'
    		)

    So I am saying nothing exists sort of thing..TY

  • I’m not exactly sure what it is you’re trying to do really.

    You’re looking for posts that do not have any value set for “status” and a value in “sale_price” that is < 300000?

    If this is correct then:

    
    'meta_query' => array(
      'relation' => 'AND',
      array(
        'key' => 'sale_price',
        'value' => 300000,
        'type' => 'NUMERIC',
        'compare' => '<'
      ),
      array(
        'relation' => 'OR',
        array(
          'key' => 'status',
          'compare' => 'NOT EXISTS'
        ),
        array(
          'key' => 'status',
          'value' => '',
          'compare' => '='
        )
      )
    )
    
  • Thanks https://support.advancedcustomfields.com/forums/users/hube2/
    This worked but was still not entirely the solution as I found I had yet another custom field to check against..and applying the way you solved this I thought I had it…

    What I need is something like this but it isn’t working..

    Pseudo code

    IF all are relation are true (AND)
    AND
    sale price <300
    AND
    either status NOT EXISTS
    OR
    either status =”
    AND
    either sp-alt NOT EXISTS
    OR
    either sp-alt =”

    code..` ‘meta_query’ => array(
    ‘relation’ => ‘AND’,
    //AND
    array(
    ‘key’ => ‘sale_price’,
    ‘value’ => 300000,
    ‘type’ => ‘NUMERIC’,
    ‘compare’ => ‘<‘
    ),
    //AND
    array(
    ‘relation’ => ‘OR’,
    array(
    ‘key’ => ‘status’,
    ‘compare’ => ‘NOT EXISTS’
    ),
    array(
    ‘key’ => ‘status’,
    ‘value’ => ”,
    ‘compare’ => ‘=’
    )
    ),
    //AND
    array(
    ‘relation’ => ‘OR’,
    array(
    ‘key’ => ‘sp-alt’,
    ‘compare’ => ‘NOT EXISTS’
    ),
    array(
    ‘key’ => ‘sp-alt’,
    ‘value’ => ”,
    ‘compare’ => ‘=’
    )
    ),`

    I get that we need to check OR for each field as it can be one or the other or at least one if it is both..

    I only want to see an item if it is less than 300 and doesn’t have a value in any of the fields sp-alt or status..if anything is in either of those fields I DON’T want to show the item. My logic seems right after you helped me get this far.

    TY anyway I am going to play more..

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

The topic ‘Empty Field Check in SQL’ is closed to new replies.