How to find the column name of the first column with a null value?

how to find first column name with null value in specific row

• The query below works fine for returning the column names that are null, what I am trying to accomplish is have it return to the first column name with a null value. I also have another column named email that has a unique email address for each row. My problem is I cannot figure out the appropriate syntax for doing this. any help is appreciated! I am also using php5.2 for this. SELECT col FROM (SELECT 'GAME0' AS col, GAME0 AS value FROM USERNAME UNION ALL SELECT 'GAME1', GAME1 FROM USERNAME UNION ALL SELECT 'GAME2', GAME2 FROM USERNAME UNION ALL SELECT 'GAME3', GAME3 FROM USERNAME UNION ALL SELECT 'GAME4', GAME4 FROM USERNAME UNION ALL SELECT 'GAME5', GAME5 FROM USERNAME UNION ALL SELECT 'GAME6', GAME6 FROM USERNAME UNION ALL SELECT 'GAME7', GAME7 FROM USERNAME UNION ALL SELECT 'GAME8', GAME8 FROM USERNAME UNION ALL SELECT 'GAME9', GAME9 FROM USERNAME UNION ALL SELECT 'GAME10', GAME10 FROM USERNAME UNION ALL SELECT 'GAME11', GAME11 FROM USERNAME UNION ALL SELECT 'GAME12', GAME12 FROM USERNAME UNION ALL SELECT 'GAME13', GAME13 FROM USERNAME UNION ALL SELECT 'GAME14', GAME14 FROM USERNAME UNION ALL SELECT 'GAME15', GAME15 FROM USERNAME UNION ALL SELECT 'GAME16', GAME16 FROM USERNAME UNION ALL SELECT 'GAME17', GAME17 FROM USERNAME UNION ALL SELECT 'GAME18', GAME18 FROM USERNAME UNION ALL SELECT 'GAME19', GAME19 FROM USERNAME ) allValues WHERE value is null and email='emailaddress' EDIT: if the query above is unneeded, I need something like below, where I can change the value of the first null value. Again syntax is my biggest problem. update username SET CASE GAME0 WHEN GAME0 IS NOT NULL THEN GAME1 WHEN GAME1 IS NOT NULL THEN GAME2 WHEN GAME2 IS NOT NULL THEN GAME3 WHEN GAME3 IS NOT NULL THEN GAME4 WHEN GAME4 IS NOT NULL THEN GAME5 WHEN GAME5 IS NOT NULL THEN GAME6 WHEN GAME7 IS NOT NULL THEN GAME8 WHEN GAME8 IS NOT NULL THEN GAME9 WHEN GAME9 IS NOT NULL THEN GAME10 WHEN GAME10 IS NOT NULL THEN GAME11 WHEN GAME11 IS NOT NULL THEN GAME12 WHEN GAME12 IS NOT NULL THEN GAME13 WHEN GAME13 IS NOT NULL THEN GAME14 WHEN GAME14 IS NOT NULL THEN GAME15 WHEN GAME15 IS NOT NULL THEN GAME16 WHEN GAME16 IS NOT NULL THEN GAME17 WHEN GAME17 IS NOT NULL THEN GAME18 WHEN GAME18 IS NOT NULL THEN GAME19 END where EMAIL='emailaddress'

• Answer:

  You can make this query much simplier and do some of the calculations through php: $arr_field = array(); foreach(range(0, 19) as $i) $arr_field[] = "GAME{$i}"; $str_add = implode(' + ', $arr_field); $str_select = implode(', ', $arr_field); $query = mysql_query(" SELECT {$str_select} FROM USERNAME WHERE ({$str_add}) IS NULL AND email = 'emailaddress' "); // Then get the NULL column through a loop while($row = mysql_fetch_assoc($query)) foreach($arr_field as $h) if ( ! isset($row[$h])) { echo "Found column! {$h}<br />"; break; } Baiscly I add all the 20 fields together, and if any of these 20 values are NULL, the result becomes NULL. I fetch all 20 values, then loop through php to find NULL fields. To be honest though, I would have made another stucture having a second table handling these GAME0 - GAME19 fields instead.