如何仅从mySql表的指定字段中检索数据？

Question

I want to display a table (in a web page) containing the data from my database, but only want to display the table with fields selected by the user. For example: I have a total of 6 fields in my table(name, email, age, location, gender, occupation). Let's say that the user on my page selects name, email and age. I want a table to be displayed containing all the row entries, but with only the specified fields.

This is what I've written so far but it doesn't seem to be working.

include("init.php");

$fields = $_POST["fields"];
$sorting = trim($_POST["sorting"]);
$filter = trim($_POST["filter"]);

$sql_query = sprintf("select * from applicants where %s order by %s", $filter, $sorting);
$query = mysql_query($sql_query);
$num_rows = mysql_numrows($query);

echo "<table border='1'>";
echo "<tr>";
    foreach($fields as $field) {
        $field = ucfirst($field);
        echo "<th>$field</th>";
    }
echo "</tr>";

for($i = 0; $i < $num_rows; $i++) {
    echo "<tr>";
    while($field = mysql_fetch_field($query)) {
        if(in_array($field->name, $fields)) {
            $data = mysql_result($query, $i, $col);
            echo "<td>$data</td>";
        } else {
            echo "<td>false</td>";
        }
    }
    echo "</tr>";
}

echo "</table>";

Answer 1

You need to set the <option> with the value of the column name, then use this code:

include('init.php');

$fields = $_POST['fields'];
$sorting = trim($_POST['sorting']);
$filter = trim($_POST['filter']);

function filterFields($field) {
  if (in_array($field, array(/* Here you need to add the fields you allowed to post, to prevent attack */))) {
    return '`' . $field . '`';
  } else {
    return 'NULL';
  }
}

$escapedFields = array_map('filterFields', $fields);

// Make the query, here we use 'implode' function to join all array with ','
// Example if we have array('name', 'time'), then the function will return 'name,time'
$query = mysql_query('SELECT ' . implode(',', $escapedFields) . ' FROM `applicants` WHERE ' . $filter . ' ORDER BY ' . $sorting);
// If the query return something, then...
if (mysql_num_rows($query)) {
  echo '<table border='1'>
<tr>';
  // Here we print the table header.
  for ($i = 0, $fieldsLength = sizeof($field); $i < $fieldsLength; ++$i) {
    echo '<th>' . ucfirst($fields[$i]) . '</th>';
  }
  echo '</tr>';

  // Here we print the result.
  while ($result = mysql_fetch_assoc($query)) {
    echo '<tr>';
    $resultKeys = array_keys($result);
    for ($i = 0, $resultKeysLength = sizeof($resultKeys); $i < $resultKeysLength; ++$i) {
      echo '<td>' . $result[$resultKeys[$i]] . '</td>';
    }
    echo '</tr>';
  }
  echo '</table>';
}