Selecting first NOT NULL column, then getting name of that column

Question

I have a database table set up in the following way:

table
--------
id: 123
name: test
description: lorem ipsum
a: NULL
b: NULL
c: NULL
d: lorem ipsum
e: NULL
f: lorem ipsum

Then I have a variable on my PHP page, $id = 123

How can I return the name of the first NOT NULL column, searching in a, b, c, d, e, f only? So in this instance, the query would return d or f into a string. (NOT lorem ipsum)

Here's something I've tried for you to get a better understanding of what I need:

(as far as I know, COALESCE() will "return the first non-NULL argument")

$cat = mysql_query("SELECT coalesce(a, b, c, d, e, f) FROM table WHERE `id`='$id' LIMIT 1");
$cat = mysql_fetch_assoc($cat);
$cat = array_shift(array_values($cat));

The query above almost does what I want, it returns the information of the first NOT NULL column (so in this instance, it returns lorem ipsum)... What I need is the NAME of that column.

So then I tried $cat = mysql_field_name($cat, 0); (see below for info) - but apparently this needs to be ran on the query, not the string returned from mysql_fetch_assoc. So I tried doing exactly that and running mysql_field_name on the query, and it returns part of the original query (coalesce(a, b, c, d, e, f)) when I echo it. Weird huh?

(info —mysql_field_name — Get the name of the specified field in a result)

For recap: The 3 lines of PHP I provided are working and provide information from a column, but I need a way to get the NAME of that column and store it into a string. Thanks

After following Gordon's method I changed the first line of my PHP in the example above (the query) to $cat = mysql_query("select (case when a is not null then 'a' when b is not null then 'b' when c is not null then 'c' when d is not null then 'd' when e is not null then 'e' when f is not null then 'f' end) FROM directory WHERE id='$id' LIMIT 1") but it returns a boolean (so I can't run mysql_fetch_assoc as it isn't actually returning anything), I have checked all names of columns are correct etc.

Please see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/q/12859942/214577). This code uses *very* outdated functions that are **no longer part of PHP**. You might want to (read: should really, without any excuses) step away from them. — Oldskool, Apr 11 '16 at 12:38

score 1 · Answer 1 · edited Apr 11 '16 at 12:05

1

Just use case:

select (
         case 
               when a is not null then 'a'
               when b is not null then 'b'
               when c is not null then 'c'
               when d is not null then 'd'
               when e is not null then 'e'
               when f is not null then 'f'
         end
      )

edited Apr 11 '16 at 12:05

user1784624

27
5

answered Apr 11 '16 at 11:17

Gordon Linoff

1,242,037
58
646
786

1

this is returning a boolean for me, when I try to run `mysql_fetch_assoc` on the above query. I have slightly changed the query, `select (case when a is not null then 'a' when b is not null then 'b' ... end) FROM directory WHERE id='$id' LIMIT 1` – nulled Apr 11 '16 at 11:25
@RyanButterworth . . . This `case` expression should be returning a string. I don't see how it could be returning a boolean. – Gordon Linoff Apr 12 '16 at 02:16
Well it would suggest there is human error (typing the table names wrong etc) although I checked thoroughly – nulled Apr 12 '16 at 12:51

score 1 · Answer 2 · answered Apr 11 '16 at 11:25

1

The easiest solution would be with the help of array_filter (array_filter) You can give it an array and without an callback, it will give you alle elements that evaluate to true. The column name remains so you can use the key function.
So something like this:

$keys = key(array_filter($cat))

And on $keys[0] you find your name.

answered Apr 11 '16 at 11:25

Shimu

1,137
11
25

The problem is that my original query is returning `coalesce(a, b, c, d, e, f)` - so when using your method `$keys[0]` would return `c`.... `$keys[1]` would return `o`.... `$keys[2]` would return `a`..... `$keys[3]` would return `l` etc etc etc... I'm pretty sure your method would work but my query is wrong, `coalesce()` isn't working properly... – nulled Apr 11 '16 at 11:36
What if you just use the sql query without coalesce and fetch the name in php? Then you can use this method. Or the method mention from @gordon. But this has the disadvantage that you are not so flexible. E.g. when the schema changes you need to adjust your query. – Shimu Apr 11 '16 at 12:46

Selecting first NOT NULL column, then getting name of that column

2 Answers2