0

I have a table member1 which looks like this. I am editing my table why I want to return all values from pst.

id  club_id mobile      pst
1     1     9437054788  6
2     1     9437345602  NULL
3     2     9437056738  35
4     2     9437064876  39
5     1     9437059927  NULL
6     2     9437965276  40
7     1     9437121455  NULL

I have a drop-down fields one for club_id and another check-box field for pst with a value of 'select All' on both fields. 'select All' equals '%'.

The solution provided by @TheConstructor works well in this query

SELECT group_concat(mobile) FROM `member1` WHERE COALESCE(pst, '') 
LIKE '%' and club_id=1

or 

SELECT group_concat(mobile) FROM `member1` WHERE COALESCE(pst, '') 
LIKE '6' and club_id=1

Now I want to have multiple values in the query in place of '6' for example

SELECT group_concat(mobile) FROM `member1` WHERE COALESCE(pst, '') 
in (6,35,39,40) or in ('%') and club_id=1

How I can achieve that, as at the moment I can return the result with just one value i.e. 6 or 35 or 39 or 40

Improve this question
1
  • Why do you want NULL to match one of those four values? Commented Aug 17, 2014 at 12:21

3 Answers 3

Reset to default
3

As you will have noticed NULL LIKE '%' will not evaluate to TRUE and rows where pst is NULL are excluded from the result. Now there a different approaches to include rows where pst is NULL; here are three of them:

  1. If you want to get all rows you can just leave out the WHERE all together.

    SELECT group_concat(mobile) FROM `member1`;

  2. If you want to always include NULL you can add OR pst IS NULL. (Can use index on column pst)

    SELECT group_concat(mobile) FROM `member1` WHERE pst LIKE '%' OR pst IS NULL;

    If you plan on changing LIKE '%' for another predicate, this is probably the fastest SQL on bigger tables.

  3. If you want to treat NULL as if it was an empty string you can use: (will not use index on column pst)

    SELECT group_concat(mobile) FROM `member1` WHERE COALESCE(pst, '') LIKE '%'

    COALESCE(pst, '') will give the value inside column pst if it is not NULL or '' if pst is NULL.

    While I regard this to be the most flexible one as you only need to change '%' to something different to start filtering and exclude NULL-values, MySQL's optimizer will sadly handle this differently from the second query which will cost you performance on big tables where pst has an index.

As a last caveat note that GROUP_CONCAT will by default only return 1024 characters. This can be changed by setting group_concat_max_len to a higher value.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

WHERE COALESCE(pst, '') LIKE '%'.. invalidates any index on pst and will always return true.
@TheConstructor - is it possible to modify ur suggestion to replace the like example in ('%') or in (6,35,39,40)
IN ('%') will only select the exact value '%'. You can of course use both IN-predicates instead of LIKE '%'. With 2. you will still get rows where pst is NULL with 3. you will no longer get them if your IN does not contain ''.
@TheConstructor Actually ur suggestion at pt.3 is working as expected. only I wanted additional result but when I am trying to use the query - SELECT group_concat(m.mobile)from member m where COALESCE(m.pst, '') in ('%') it is returning null, but with like it is working all-right
@Arth as they stand all three SQLs are returning the same result, but depending on where you want to go from there you will want to use a different one.
|
1

Forgive me if I've wildly misunderstood your question, but I think you want just:

SELECT group_concat(mobile) FROM member1

The % in WHERE pst LIKE '%' is a wildcard for zero or more of any character, thus the query will return any NOT NULL pst records.. you are way better off using WHERE pst IS NOT NULL if you need that logic.

If you need to check the actual character % you'll need to escape it; WHERE pst LIKE '\%'.. but this is the same logic as WHERE pst = '%' as you are checking for equality.

I'm still struggling to understand you but if you are checking for existence in the database of a mobile number the simplest query is:

SELECT 1 FROM member1 WHERE mobile = ?

Where ?, is your pass in variable and any rows returned means the record exists. Make sure you use a binding method that is safe from SQL injection.

I would also shy away from using GROUP_CONCAT in every case except the one when I am displaying the data as CSV. If you are building a drop-down from the results.. just pull in the separate rows in an array and run through it as you build the HTML.

Improve this answer

1 Comment

As I said I need this value to be compared against the parameter passed from drop-down values of a form. How I can achieve that.
0

It seems you are using that value as-is, right from the form data. Are you sure this won't result in security issues?

I would grab the value from $_POST, test it, create a variable with the correct WHERE part of the query dependent on the test and use that in the query string.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.