0

I have an html form to read out data from an sql-database. According to the selected criteria, the results are shown in a dynamically generated table. This works well with the following code. The SELECT query is composed according to the selected criteria.

  $sql = "SELECT DISTINCT $selection FROM $tabelle WHERE $whereclause";

  $result = mysqli_query($db, $sql) or die("<b>No result</b>"); //Running 
     the query and storing it in result
  $numrows = mysqli_num_rows($result);  // gets number of rows in result 
     table
  $numcols = mysqli_num_fields($result);   // gets number of columns in 
     result table
  $field = mysqli_fetch_fields($result); // gets the column names from the 
     result table

  if ($numrows > 0) {

  echo "<table id='myTable' >";

  echo "<thead>";
  echo "<tr>";
  echo "<th>" . 'Nr' . "</th>";

  for($x=0;$x<$numcols;$x++){
          $key = array_search($field[$x]->name, $custom_column_arr);
          if($key !== false){
              echo "<th>" . $key . "</th>";
          }else{
              echo "<th>" . $field[$x]->name . "</th>";
          }
      }

  echo "</tr></thead>";

  echo "<tbody>";

  $nr = 1;

  while ($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $nr . "</td>";
    for ($k=0; $k<$numcols; $k++) {    //  goes around until there are no 
    columns left
          echo "<td>" . $row[$field[$k]->name] . "</td>"; //Prints the data
    }
  echo "</tr>";
  $nr = $nr + 1;


  }     // Ende der while-Schleife

  echo "</tbody></table>";
  }
  }

  mysqli_close($db);

Now, I want to display only those columns that contain at least one value and not those with NULL, 0, empty string or empty. My problem is, that $numcols counts the empty columns, too.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Columbus
  • 89
  • 1
  • 9
  • To know which columns have no data, you will have to go through all the selected rows before generating the table. – xzoert Jan 23 '17 at 18:45
  • Thank you xzoert, I think you are right! But how can I go through rows before they are generated? – Columbus Jan 23 '17 at 18:48
  • Your query MAY BE open to [sql injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) - hard to know, but as you are interpolating your table / where clause, it's likely. Please read the linked article to be sure you don't fall victim to a SQL injection attack. – random_user_name Jan 23 '17 at 18:50
  • Which columns do you want to check? For example, if you have an `id` column that is auto-increment, that will ALWAYS have data, so we need to know a bit more about your data structure in order to provide an answer. – random_user_name Jan 23 '17 at 18:52
  • Before printing any HTML, you loop over the rows and keep track of which columns are empty. An easy way would be to keep an array (initialized with zeroes) of length `$numcols` and while you loop through the rows you write 1 into the array whenever you find data in the corresponding column. After, when you write the headers and data rows, you skip those columns having still a 0 (which means no data were found). – xzoert Jan 23 '17 at 18:54
  • However, as cale_b correctly states, this is the most injection prone code i've ever seen! – xzoert Jan 23 '17 at 18:56
  • cale_b, thanks for pointing out the risk of SQL injection and the helpful link! In the table, the first 2 columns, containing id and names, always have values and do not have to be checked. All other columns might be without values (depending on the query and the results) and would have to be checked. – Columbus Jan 23 '17 at 19:01

0 Answers0