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

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

douvcpx6526
douvcpx6526 您不应该在表格中为每个团队添加新列。您应该将表规范化为3FN并为团队制作新表,为关系用户和团队制作另一个表。如果用户是100个团队的成员,会发生什么?你会为每个人增加100个列吗?你将如何处理?
大约一年之前 回复
dqys98341
dqys98341 与stackoverflow.com/questions/37920413/...类似,但我会使用in_array。
大约一年之前 回复
dragon201401
dragon201401 您只能绑定值,而不能绑定列/表。使用白名单并检查该值是否为已知值。如果是这样,如果不拒绝请求,则将其添加到查询中。
大约一年之前 回复

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问