Query to return only empty field results

8 posts by 2 authors in: Forums > CMS Builder
Last Post: June 12, 2012 (RSS)

Archived
Reply
Reply

By nmsinc - June 8, 2012

I have attempted without success to query and list only records where a particular field is empty!

Using the value Null or " " or the number 0 offers no success!

Anyone with suggestions on how this is done?

Thanks - nmsinc

nmsinc

Re: [nmsinc] Query to return only empty field results

Archived
Reply
Reply

By Jason - June 8, 2012

Hi,

The best way to do this would depend on what type of field you are looking at.

If it's a basic text field, you can try this:

EXAMPLE
$records = mysql_select("news", "author = '' ");

This would return records from the news category where author had no value.

Hope this helps

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Query to return only empty field results

Archived
Reply
Reply

By nmsinc - June 8, 2012 - edited: June 8, 2012

Hi Jason,

It's a browser or field search query where the field contains numeric!

nmsinc

Re: [nmsinc] Query to return only empty field results

Archived
Reply
Reply

By Jason - June 11, 2012

Hi,

Could you provide me with the current query you're using? If this query is the result of a search form, could you give some information about the form you're using?

Thanks,

Re: [Jason] Query to return only empty field results

Archived
Reply
Reply

By nmsinc - June 11, 2012

Hi Jason,

Here is the URL link that I'm using (I left off http://www in the URL below as a precaution):

/claims/active_claims_map.php?independent_adjuster=" "

I have also tried zero (0) and null and with zero records returned! The field "independent_adjuster" is filled from another record using the records $num field. If no choice is made the field should be remain Null or Blank - correct? If so, should not the above URL return only records where the field is empty?

Thanks for your help - nmsinc

nmsinc

Re: [nmsinc] Query to return only empty field results

Archived
Reply
Reply

By Jason - June 11, 2012

Hi,

If you are relying on getRecords() automatic search capabilities, this approach wont work. getRecords() in this case will only add independent_adjuster to the query IF a value is present. In your example, it would not be factored into the query at all. If, in this circumstance, you wanted to search for records where this field is blank, you will need to detect that this field has no value, add add it to the WHERE clause manually.

For example:

$where = "";
  
  if (!@$_REQUEST['independent_adjuster']) {
    $where = "independent_adjuster = '' ";
  }
  
  list($myRecords, $myMetaData) = getRecords(array(
    'tableName'    =>   'mySection',
    'where'        =>   $where,
  ));

Hope this helps

Re: [Jason] Query to return only empty field results

Archived
Reply
Reply

By nmsinc - June 11, 2012

Okay, got it,

How about with a search request, how is the option coded?

Thanks - nmsinc

nmsinc

Re: [nmsinc] Query to return only empty field results

Archived
Reply
Reply

By Jason - June 12, 2012

Hi,

getRecords() will treat the variables the same whether they are placed directly in the URL or posted through a form. The code from the previous post should work in either case.

Hope this helps