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