Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Searching Text Fields that aren't NULL

Quote Reply
Searching Text Fields that aren't NULL
Is there a search command that allows you to search records and display them if the text field isn't NULL. I'm not looking for a <%if%<%else%> in the template itself. That doesn't do much good because the same amount of hits are produced. I need something in the search_results URL string and searches based on field containing something (e.g. NOT NULL).

Any suggestions on how this may be accomplished would be great?

Jay
Quote Reply
Re: [jayland] Searching Text Fields that aren't NULL In reply to
Hi,

I cannot help you. Frown But I have a similar question: how can I search for entries where a specific column is NULL?

Hope someone can help.....

Jasper

http://www.bookings.org
Quote Reply
Re: [jayland] Searching Text Fields that aren't NULL In reply to
Hi,

There is no way to accomplish NOT NULL issue through your search engine. But you can create a plugin to handle it by using GT::SQL::Condition->new('field_name', '=', undef ) command.

TheStone.

B.
Quote Reply
Re: [TheStone] Searching Text Fields that aren't NULL In reply to
Hi Stone,

In Reply To:
There is no way to accomplish NOT NULL issue through your search engine. But you can create a plugin to handle it .....

Thanks for your suggestion! I am definitely planning to write this plugin. Could you give me a few more tips as I have no idea how to do it... Crazy

  • Should it be a PRE-search_results plugin?

  • Can I modify the state in a way that it checks whether a specific column is NULL or NOT NULL? Or should I do all calculations myself and call GT::Plugins->action ( STOP )?

  • If I can change the state in some way, which variables do I have to change?

  • If I would do the search myself, should I copy all code in Dbsql::Home::search_results(), SQL::Table::_query() and SQL::Base::build_query_cond() and make some modifications in SQL::Base::build_query_cond()? Seems like a lot of work for a small change. Frown

I hope answering these questions does not take you more time then writing the plugin yourself...Blush

In Reply To:
.....by using GT::SQL::Condition->new('field_name', '=', undef ) command.
Are you sure this works? I thought this would translate in 'field_name = NULL' . I thought the correct SQL would be 'field_name IS NULL'. I use
Code:
GT::SQL::Condition->new('field_name', 'IS', \'NULL' )

http://www.bookings.org
Quote Reply
Re: [jayland] Searching Text Fields that aren't NULL In reply to
Hi jayland,

I might have a solution for your problem (but not for mine Frown). If you know a value that your records cannot have, you can search for all entries where the value is not equal to that given value and you will get all entries with not null values. Hmm, hard to explain. Crazy
Let me give you an example. Let's say your table has a field called 'modification_date', which tracks when an entry has been changed and you are sure none of the entries will have '1990-02-27' as modification date. A search for
Code:
...&modification_date=!1990-02-27&...
would give you all entries that are not null.

Hope this works for you (I didn't test it).

Jasper

http://www.bookings.org
Quote Reply
Re: [TheStone] Searching Text Fields that aren't NULL In reply to
Hi Stone,

I added
Code:
if (exists $opts->{"$field-null"} and ($opts->{"$field-null"} ne "")) {
push @ins, [$field, 'IS', \'NULL'];
}
to GT::SQL::Base::build_query_cond() (line 499, see attachment) which seems to do exactly what I want.
GT, is this something that you guys want to support in future versions?

Jasper

http://www.bookings.org

Last edited by:

jaspercram: Nov 13, 2002, 8:21 AM
Quote Reply
Re: [jaspercram] Searching Text Fields that aren't NULL In reply to
Jasper,

Thanks I'll give that a try and let you know how it works out!

Jay