I have some databases that contains tables, and in those tables there are some columns which are never used hence its all NULL.
My aim is to find and return the names of such columns using php program so i can write an update query specifying the returned column name for deletion.
first i want to get the names of the columns that is all null and
here is a little script i wrote but it doen't do what i want because mysql_fetch_array($result) returns Row after Row and not Column by Column
//try for one DB first
$db = new DBConnection($DB_HOST, "readonly", "readonly", "DB_Name");
$sSQL = "select * from master";
$result = mysql_query($sSQL);
if (!$result){
return array("ERROR", mysql_error());
}
$allNullColumn = array();//array to store all NULL col
$num_rows = mysql_num_rows($result); //get the number of rows
$numfields = mysql_num_fields($result);//number of columns
for($y =0; $y < $numfields; $y++){
for ($x = 0; $x < $num_rows; $x++){
$row = mysql_fetch_array($result);//this is where i am stock. Only if this php function was returning Column by Column and not Row by Row
if(strlen($row[$y][$x]) > 0){//check if the index contains a value
break;//break if a value is found
}else {//otherwise store the column name
array_push($allNullColumn, mysql_field_name($result, $y));//.'</br>';
}
}
}
print_r($allNullColumn) ;
Please help.
