Exclude a field from a SELECT * FROM $table

Question

I need to exclude a field 'password' from a SELECT * FROM $table; where $table is a PHP variable.

Only one of the four posible tables has a 'password' field.

Here is what I have:

function myData($table)
{
  include "conf.php";
  $con   = mysqli_connect($host, $user, $password, $db);
  $sql   = "SELECT * FROM $table;";
  $resul = mysqli_query($con, $sql);
  return $resul;
}

Any ideas?

EDIT: This is how I treat the data returned:

$resulFields    =   myFields($table);
$resulData      =   myData($table);

while ($fields = mysqli_fetch_array($resulFields, MYSQLI_ASSOC)) {
    $field      =   $fields['Field'];
    $header  .=   "<th>$field</th>";

    while ($data_array = mysqli_fetch_array($resulData, MYSQLI_ASSOC) ) {
        $body .=  "<tr id='$data_array[id]'>";
        foreach ($data_array as $data){
            $body .= "<td>$data</td>";
        }
    }
}

Sorry if it's a little bit messy, I'm just starting to learn programming.

Select what you DO want `SELECT id, name, whatever FROM $table` — AbraCadaver, Apr 21 '16 at 15:04
I need it to be like that in order to use it with different tables. — miguelmald, Apr 21 '16 at 15:05
Not possible with simple SELECT. @AbraCadaver is correct, that is the way to do it. — Jonnix, Apr 21 '16 at 15:09
What are you trying to achieve? If the table has the 'password' field & you don't need it, just ignore it in the data returned. — PaulF, Apr 21 '16 at 15:09
There is a possible solution here : http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql or second answer here : http://stackoverflow.com/questions/14253994/selecting-all-fields-except-only-one-field-in-mysql — PaulF, Apr 21 '16 at 15:12
NOTE - if you do look at the links I posted - neither show you need to deallocate the prepared statement after use. _DEALLOCATE PREPARE stmt1;_ — PaulF, Apr 21 '16 at 15:27

score 2 · Answer 1 · answered Apr 21 '16 at 16:00

I understand that you're wanting to have a single PHP function that will return all the results in a given table. Perhaps instead of returning the $resul variable and parsing the data after the return, you should parse it into an associative array prior to returning it. You can try something like this:

function myData($table) {
    include "conf.php";
    $con =   mysqli_connect($host, $user, $password, $db);
    $sql =   "SELECT * FROM {$table}";

    $resul =  mysqli_query($con, $sql);
    $row = $resul->fetch_assoc();
    unset( $row['password'] );

    return $resul;
}

Though I feel it's important to note that in the interests of proper coding practices and single responsibility, you should have specific data access functions for each query you wish to run. I don't recommend having a single function that just returns everything from a table. Functions should also be named such that you know what they're doing. "myData" is very non-descriptive and as such a very poor name for a data access function.

Also, if you're going to name a variable $resul, just go ahead and type the "t" and name it $result FFS.

I'm going to use the @Adrián solution. Thank you for your advice. — miguelmald, Apr 21 '16 at 16:07
+10. If the specification is to have a single generic function that queries from any table (with the name of the table supplied to the function as an argument), and that includes a requirement is to display all of the columns except a column named "password", then this is the approach I would take. (I wholeheartedly agree with questioning the specification. I am in fear that the next specification will be for a generic function that updates any table.) — spencer7593, Apr 21 '16 at 18:48

spencer7593 · Answer 2 · 2016-04-21T16:16:46.140

In the foreach loop, get the key and the data from the array. (The current code is getting only the data.)

Inside the foreach loop, do a conditional test on the value of key.

If the value of the key matches "password", then skip over outputting anything for that element of the array. If it doesn't match key, then output it (like the current code is doing.)

Look at the alternative syntax for foreach:

References: http://php.net/manual/en/control-structures.foreach.php

And for simple conditional tests

Reference: http://php.net/manual/en/control-structures.if.php

Consider whether you want to match "password", "PASSWORD", "Password", etc. You might want a case insensitive match.

Thats just where I was going, but it all seemed to much hassle to code it all. Much prefer this more generic approach. Glad you answered before I did all that thinking for him — RiggsFolly, Apr 21 '16 at 15:23

score 0 · Accepted Answer · answered Apr 21 '16 at 15:10

0

Maybe you can do something like this:

function myData($table) {
    include "conf.php";
    $con =   mysqli_connect($host, $user, $password, $db);
    $sql =   "SELECT field1, field2";

    if ($table == "TABLE_WITH_PASSWORD") {
        $sql.=",password";
    }
    $sql.=" FROM $table;";

    $resul =  mysqli_query($con, $sql);
    return $resul;
}

answered Apr 21 '16 at 15:10

Adrián

419
2
17

1

you just don't understand the question – Pham X. Bach Apr 21 '16 at 15:12
@NayruLove Your answer is very similar to mine. Why do you say I didn't understand question? – Adrián Apr 21 '16 at 16:02
Because @miguelmald said he need to **exclude** field `password` but you **include** it. You also `fix fields`, delete the `select *`. And now he accept your answer, so I think @miguelmald didn't understand **his** question, too – Pham X. Bach Apr 21 '16 at 16:17
Maybe it's simpler: you didn't understood his question :) I'm including password field only if $table is the table with password. The "select *" is not needed if you define needed fields (which may be the same in all other tables). – Adrián Apr 21 '16 at 17:28

AbraCadaver · Answer 4 · 2016-04-21T15:17:35.610

Obviously the best bet is to select what you do want:

SELECT id, name, whatever FROM $table

Hopefully I'm not going down the wrong path, but here is one way other than querying for the fields and removing the password:

$columns['tableA'] = array('id', 'name', 'whatever');
$columns['tableB'] = array('id', 'user', 'something');

Then do:

$select = implode(',', $columns[$table]);
$sql = "SELECT $select FROM $table";

score 0 · Answer 5 · answered Apr 21 '16 at 15:51

IMO i think the simplest way in this case is to use special case for php

function myData($table) {
    include "conf.php";
    $con  =   mysqli_connect($host, $user, $password, $db);
    if($table == "special_case_table"){
        $sql    =   "SELECT col1, col2, col3 FROM special_case_table;"; //except "password" column
    }
    else  $sql    =   "SELECT * FROM $table;";
    $resul  =   mysqli_query($con, $sql);
    return $resul;

No need more function or go search in INFORMATION_SCHEMA of database to find column password.

Exclude a field from a SELECT * FROM $table

5 Answers5