Using count(*) for table column's?

[Solarpitch]

Ok,

 

lets see if I can explain what I am trying to do. I have 4 column's in my database...

 

slot_id1      slot_id2       slot_id3      slot_id4
Patrick       NULL           NULL         NULL

 

I want to be able to count to see how many of the above column's are = to "" .. (or null) So in the above case there's 3 slots available and 1 occupied. Is there any way to check this using a script to count how many of these 4 columns are empty with no value?

 

I was using an extra column called avail_slots which incremented and decremented everytime a value was inserted or deleted. But that method wont work for my application.

 

Thanks.

 

 

 

 

[Lumio]

SELECT COUNT(*) AS slotCount WHERE slot_id1 = NULL || slot_id2 = NULL ...;

[ucffool]

SELECT COUNT(*) AS slotCount WHERE slot_id1 = NULL || slot_id2 = NULL ...;

That will tell him the number of rows that have at least 1 NULL column, but I think he also needs, "to count how many of these 4 columns are empty with no value?"

I would use array_count_value() to get the number of NULL values in each row.

$null_count = array();
$result = mysql_query("SELECT * FROM table WHERE slot_id1 = '' || slot_id2 = '' || slot_id3 = '' || slot_id4 = '' ");
while ($row = mysql_fetch_assoc($result)){
  $count_array = array_count_values($row);
  $null_count[] = $count_array[''];
}

The key or problem here is that array_count_values() can only count integers or strings ( because it makes the value the KEY in the array), and thus if it is an empty string ('') it can count it, but if it is NULL, it will error out.

[Barand]

A better solution would be to use normalized tables.

[ucffool]

A better solution would be to use normalized tables.

Can't argue with that, better by design rather than by force.