Easy Profile - Joomla Extension

Support

Support is currently Offline

Support Offline

It's past support hour. We are taking some rest and will come back stronger tomorrow. Promise!

Official support hours:

Monday To Friday except Saturday and Sunday
From 09:30 To 17:00
Our current time: Monday 9:48 pm

Add a reply View Replies (4)

Resolved Select statement for LIKE one field and NOT LIKE another

arcascomputing
Support
Tuesday, September 05 2017, 12:59 PM

I'm currently trying to get an SQL select query to work for a users listing module but am having difficulties.

I have two fields, both checkbox lists with several options. The first notes which research groups the users are in and the second selects if they are group lead for any research groups, so the first field is called "cluster" and the second is "group_coordinator".

I had the query running okay to select from the research group (cluster LIKE "%Cultural Heritages%") but now I also need to add in a selector for group_coordinator NOT LIKE "%Cultural Heritages%". So the query should select all folk who have been checked as being in the Cultural heritages group but should exclude those noted as being the group coordinator.

I tried ((cluster LIKE "%Cultural Heritages%" AND (group_coordinator NOT LIKE "%Cultural Heritages%))" without any joy.

Responses (4)

admin Accepted Answer

Admin

Hi,
there is an error at the end of your query, you should replace this:



Heritages%))"

with this



Heritages%"))

more than a month ago
Support
# 1

arcascomputing Accepted Answer

Pro

Apologies that was just me typing it in wrong (i also said both were checkboxes when they are in fact both select lists with multiples allowed).

The query I'm using is:



((cluster LIKE "%Cultural Heritages%") AND (group_coordinator NOT LIKE "%Cultural Heritages%"))

Which would select all users for whom the option "Cultural Heritages" has been selected in the "cluster" field and the option "Cultural Heritages" has not been selected in the "group_coordinator" field.

The above query, however, brings up a blank listing just now so still doesn't seem right.

more than a month ago
Support
# 2

admin Accepted Answer

Admin

Hi,
query seems correct, please check these:
- "group_coordinator" alias is correct
- your mysql DB do not recognize "NOT LIKE", you can try same query without "NOT", only to check if it works.

No results mean 2 possibilities:
1 No result (of course, but in your case this should not be possible because query cluster LIKE "%Cultural Heritages%" works)
2 Db error

more than a month ago
Support
# 3

arcascomputing Accepted Answer

Pro

Have managed to resolve this with the following query instead:



cluster LIKE "%Cultural Heritages%" AND ((group_coordinator IS NULL) OR (group_coordinator NOT LIKE "%Cultural Heritages%"))

I suspected that perhaps the query was having an issue with null values, so was matching if the field had a value selected for "group_coordinator" other than the one in the statement but had a value set. It, however, wasn't matching if there was no value set at all for that field, so I added in the "or null" part of the statement and this works now.

more than a month ago
Support
# 4

Page :
1

There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Request Support

Ask a Question

Support Availability

Working days: Monday to Friday. The support staff is not available on weekends; in the most of cases tickets will not be answered during that time.

Reply time: Depending on the complexity of your support issue it's usually between a few minutes and 24 hours for paid members and about one week for free members. When we expect longer delays we will notify you.

Guidelines

Before you post: read the documentation and search the forums for an answer to your question.

When you post: include Site Details if you request a support (you can use the form below the reply in Site Details tab).

Auto Solved Question: If after a week the author of the post does not reply to a request by moderator, the question will be marked as resolved.

Language: only English

Search Users

Easy Profile® is not affiliated with or endorsed by Open Source Matters or the Joomla Project. Joomla is Free Software released under the GNU/GPL License.