SQL check rows for empty columns - compare them

Question

I want a list with columns that contains content. So i try that. I will check each field in my database do figure out which of them has no content.

$arrAllField is a list of available fields

$strTable is the table where im looking in

    foreach ($arrAllField as $arrField)
            {
                $Result = $this->Database->prepare("SELECT * FROM ".$strTable." WHERE ".$arrField."  <> '' AND ".$arrField." IS NOT NULL ")->execute();
                while($Result->next())
                {   
                    $arrAllowedField[$arrField] = $arrField;    
                }
 }

Oky but the problem is, i need to compare them, so that i only get back the columns where all rows contains content.

Maybe there is a better way to do that via SQL directly, like looking in the table and get only columns back where alle entries (rows) has content.

Best Regards

Peter

[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a class for](https://github.com/GrumpyCrouton/GrumpyPDO) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](https://phpdelusions.net/pdo/mysqli_comparison) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Oct 25 '17 at 17:11
Why are you using `prepare()` but NOT parameterized variables??? — GrumpyCrouton, Oct 25 '17 at 17:12
The variables the questioner substitutes into his query are the names of columns. Those cannot be bind variables. — O. Jones, Oct 25 '17 at 18:46

Cleber Alcântara · Answer 1 · 2017-10-25T19:45:53.897

You can try the following approach: check if the total number of records of the table is the same of the number of records where the value for that column has some content, as you say. This way, your query will look like this:

foreach ($arrAllField as $arrField) {
    $query = "SELECT (SELECT count($arrField) FROM `$strTable`) = (SELECT count($arrField) FROM `$strTable` WHERE $arrField <> '' AND $arrField IS NOT NULL) AS has_content";
    //Perform the query and process the result.
}

When executed, the query above returns a column named 'has_content' with value 1 if the column $arrField has all records with content or 0 if some record of the column $arrField has no content. Then you can process the result of the query the way you prefer by checking the value of the has_content column.

If you want to retrieve all the information at once instead of performing multiple queries and processing them, just create an array with the sub queries and concatenate them in a big one, like below:

$columnQueries = array();
foreach ($arrAllField as $arrField) {
    array_push($columnQueries, "(SELECT count($arrField) FROM `$strTable`) = (SELECT count($arrField) FROM `$strTable` WHERE $arrField <> '' AND $arrField IS NOT NULL) AS " . $arrField . "_has_content");
}
$finalQuery = "SELECT " . implode(", ", $columnQueries);
//Perform the $finalQuery and process the result.

This will return one line with the columns from $arrAllField concatenated with "_has_content". The value for each column follow the same logic I mentioned before.

I hope this helps you.

Good luck.

SQL check rows for empty columns - compare them

1 Answers1