Check how many columns aren't NULL in sql

Question

As the title says

enter image description here

So in this example I would get 2 from the first row and 1 from the second.

$sql = "SELECT Passive, Q, W, E, R FROM champions WHERE Passive, Q, W, E, R IS NOT NULL AND Patch_No ='".$_GET['Patch_No']."' AND Champion = ".$row['champion']."";
$result = $conn->query($sql);
$rows = $result->num_rows;

EDIT Bigger part of code I'm trying to figure out no errors are showing up although this echo doesn't seem to be showing the numbers I want it to

if($result = $conn->query($sql)){
    $champ_number = $result->num_rows;
    $i = 1;
    $champion = array();
    $noofspellschamp = array();
    while($row = $result->fetch_assoc()){

        $champion[$i]=$row['champion'];

        $sql2 = "
        SELECT Passive, Q, W, E, R,
                ((Passive is not null) + (Q is not null) + (W is not null) + (E is not null) + (R is not null)
                ) as NumNotNull
        FROM champions
        WHERE Patch_No ='".$_GET['Patch_No']."' AND
              Champion = ".$row['champion']."";
        $result2 = $conn->query($sql2);
        echo $result2;
        $noofspellschamp[$i] = $result2;                            

        $i+=1;
        //echo $champion[count($row)];
    }

Do you expect to get the total (i.e. `3`) or a pair of rows (i.e. `{2,1}`) — Sergey Kalinichenko, Jan 01 '15 at 23:57
Please use prepared statements. PDO is developed so you do not have to put php variables inside the query. Add them as param value. — Mouser, Jan 01 '15 at 23:58
possible duplicate of [Count the Null columns in a row in SQL](http://stackoverflow.com/questions/8596500/count-the-null-columns-in-a-row-in-sql) — Tutleman, Jan 02 '15 at 00:11

score 0 · Accepted Answer · answered Jan 01 '15 at 23:57

0

Here is one method:

SELECT Passive, Q, W, E, R,
      ((Passive is not null) + (Q is not null) + (W is not null) + (E is not null) + (R is not null)
      ) as NumNotNull
FROM champions
WHERE Patch_No ='".$_GET['Patch_No']."' AND
      Champion = $row['champion'];

MySQL treats booleans -- in a numeric context -- as numbers, with 1 being true and 0 being false. You can just add the values together to get the count that you want.

I removed the first expression in the where clause; it was syntactically incorrect and I'm not sure what it was intended to do.

answered Jan 01 '15 at 23:57

Gordon Linoff

1,242,037
58
646
786

Right I read it wrong, that's why I deleted my comment. Anyway, that's really smart to use booleans like that. – Shahar Jan 02 '15 at 00:00
could you check the edit I'm trying to use it the way you showed me – higeat Jan 02 '15 at 00:12
@higeat . . . That looks reasonable. By the way, you should learn to use parameters rather than embedding constants in the SQL statement. You need to read the value back from the column `NumNotNull`, not the count of the values being returned. – Gordon Linoff Jan 02 '15 at 00:15
My code is really unsafe I don't really know what's the best place to learn best practices so pretty much I learn as I go – higeat Jan 02 '15 at 00:20
you could also help me out returning NUMNOTNULL value :p – higeat Jan 02 '15 at 00:27

Check how many columns aren't NULL in sql

1 Answers1