dongzhou1865 2019-07-12 15:18
浏览 154

如何使用绑定变量来指示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

  • 写回答

1条回答 默认 最新

  • dongwaner1367 2019-07-12 15:53
    关注

    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

    评论

报告相同问题?

悬赏问题

  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常