Empty Field Check in SQL

• landed

• June 28, 2017 at 3:20 am

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

• John Huebner

• June 28, 2017 at 6:25 am

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

• landed

• June 28, 2017 at 9:24 am

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

• John Huebner

• June 28, 2017 at 11:23 am

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' => '='
    )
  )
)

• landed

• June 30, 2017 at 4:23 am

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