如何使用绑定变量来指示mySql表的列？

Question

i would use SELECT QUERY for PHP but i don't know how many columns has my table. The table (name Player) has, for now, 6 colums (ID, name, surname, dathe of bird, fiscal code and team1 that is a FOREIGN KEY of column 'id' of table TEAM) but in my Android program the user can add more team for each player (using QYERY ALTER TABLE).

Now i would create a fragment for see players divided by team ... so in a public function i create the SELECT QUERY where i take only players that have the same ID of team thath the user woulds see.

Now this is the problem: the ID could be in one of unknown column whose name start for "team" + a number. So i've try use the bind_param for create the QUERY.

    //Code for get how many times i need to call showPlayerList methods
    //return the number of columns "team+NUMBER"
public function numberColumnsSquadra() {
    $stmt = $this -> con -> prepare("SHOW COLUMNS FROM player");
    $stmt -> execute();
    $stmt -> store_result();
    return ($stmt -> num_rows) - 5;
}

//code for find who player has the correct id of team in column "team+NUMBER"
//$id (int) is the team id
//&team (int) from 0 to ...
public function showPlayerList($id, $team) {
    $column = "team".$team;
    $stmt = $this -> con -> prepare("SELECT id, name, surname, 'date of birth', 'fiscal code' FROM Player WHERE ? = ?");
    if($stmt -> bind_param("si", $column, $id)) {
        if($stmt -> execute()) {
            $response = $stmt -> get_result();
            return $response;
        } else {
            return null;
        }
    } else {
        echo "bind_param failure";
    }
}

//In another file I call showPlayerList
...
    $t=0;
    $squadra = $db -> numberColumnsSquadra();
    for($i=1; $i <= $squadra; $i++) {
        $result = $db -> showPlayerList($_POST['id'], $i);
        //Save the result in &response array precedently create
        for($row = $result->fetch_assoc(); $row == true; $row = $result->fetch_assoc()) {
            $response['id '.$t] = $row['id'];
            $response['name'.$t] = $row['name'];
            $response['surname'.$t] = $row['surname'];
            $response['date of birth '.$t] = $row['date of birth'];
            $response['fiscal code '.$t] = $row['fiscal code'];
            $t++;
        }
...

If i try see the $result (after encapsulation in json_format) i don't see nothing. After a lot of times that i remake the code (and i done a lot of debug) i understood that the problem is in bind_param(). MySQL don't accept the name of a colums as string type! How could i do?? Ty

Answer 1

You shouldn´t add a new column in your table for each team. You should normalize your table to 3FN and make a new table for teams and another one for the relation user & team. What would happen if a user is member of 100 teams?? Will you have 100 extra columns for everyone?? How will you handle it??.

Or worst, if you add a new column (for everybody) for each team, if you have 500 teams to manage, you will have 500 columns for every row. How will you know wich member are in each team? Will you make a query asking for the 500 columns?

And answering your question, you can´t bind the name of columns, you will need to concat string like here:

$stmt = $this -> con -> prepare("SELECT id, name, surname, `date of birth`, `fiscal code` FROM Player WHERE ".$column." = ?");
if($stmt -> bind_param("i", $id)) {

Also, you need to change your ' and use backticks ` for your column names