I have a method which inserts values to an external database. I have an array of team member names which is looped through and sent to the database one by one along with the team members team ID (which is a foreign key in the team_members table).
Below is my simple database design:
CREATE TABLE team (
team_id int(11) NOT NULL AUTO_INCREMENT,
unique_id varchar(23) NOT NULL,
teamName varchar(50) NOT NULL,
PRIMARY KEY (team_id)
);
CREATE TABLE team_member (
team_member_id int(11) NOT NULL AUTO_INCREMENT,
unique_id varchar(23) NOT NULL,
fullName varchar(50) NOT NULL,
team_id int (11) NOT NULL,
PRIMARY KEY (team_member_id),
FOREIGN KEY (`team_id`)
REFERENCES `scratchcard`.`team` (`team_id`)
);
The following is my method which I use to achieve this:
private void addTeamMember(final List teamMemberArray,final String team_id) {
//helps with debugging regarding post requests
Gson gson = new GsonBuilder()
.setLenient()
.create();
//Retrofit is a REST Client for Android and Java by Square.
//It makes it relatively easy to retrieve and upload JSON (or other structured data) via a REST based webservice
Retrofit retrofit = new Retrofit.Builder()
//directing to the localhost which is defined in the Constants Class as BASE_URL
.baseUrl(Constants.BASE_URL)
//Add converter factory for serialization and deserialization of objects.
//Gson passed as a parameter to help with debug
.addConverterFactory(GsonConverterFactory.create(gson))
//Create the Retrofit instance using the configured values.
.build();
//The Retrofit class generates an implementation of the RequestInterface interface.
RequestInterface requestInterface = retrofit.create(RequestInterface.class);
for (Object x : teamMemberArray) {
//create new Team object
TeamMember teamMember = new TeamMember();
//setter
teamMember.setFullName(String.valueOf(x));
teamMember.setTeam_id(team_id);
Toast.makeText(getActivity(), teamMember.getFullName(),
Toast.LENGTH_LONG).show();
//create new server object
final ServerRequest request = new ServerRequest();
//make a request to set the operation to Team_Member
request.setOperation(Constants.Team_Member);
//set values entered for the new teamMember to be sent to the server
request.setTeamMember(teamMember);
Call<ServerResponse> response = requestInterface.operation(request);
/**
* Enqueue is used to Asynchronously send the request and notify callback of its response or if an error occurred
* talking to the server, creating the request, or processing the response.
*/
response.enqueue(new Callback<ServerResponse>() {
@Override
public void onResponse(Call<ServerResponse> call, retrofit2.Response<ServerResponse> response) {
ServerResponse resp = response.body();
/*Snackbars provide lightweight feedback about an operation. They show a brief message at the
bottom of the screen on mobile and lower left on larger devices. Snackbars appear above all other
elements on screen and only one can be displayed at a time.
*/
Snackbar.make(getView(), resp.getMessage(), Snackbar.LENGTH_LONG).show();
if (resp.getResult().equals(Constants.SUCCESS)) {
SharedPreferences.Editor editor = pref.edit();
Log.d("TEST VALUE", "getTeamMemberName() = " + response.body().getTeamMember().getFullName() );
Log.d("TEST VALUE", "getTeamMemberUniqueID() = " + response.body().getTeamMember().getUnique_id());
Log.d("TEST VALUE", "getTeamMemberTeamID() = " + response.body().getTeamMember().getTeamID());
editor.putString(Constants.FULL_NAME, resp.getTeamMember().getFullName());
editor.putString(Constants.UNIQUE_ID, resp.getTeamMember().getUnique_id());
editor.putString(Constants.TEAM_ID, resp.getTeamMember().getTeamID());
editor.apply();
goToQuestions();
}
progress.setVisibility(View.INVISIBLE);
}
@Override
public void onFailure(Call<ServerResponse> call, Throwable t) {
progress.setVisibility(View.INVISIBLE);
Log.d(Constants.TAG, "failed" + t);
Snackbar.make(getView(), t.getLocalizedMessage(), Snackbar.LENGTH_LONG).show();
}
});
}
}
Because I am inserting these values one at a time, I have the following simple insert script (using PDO)
public function insertTeamMember($fullName, $team_id){
$unique_id = uniqid('', true);
$sql = 'INSERT INTO team_member SET unique_id =:unique_id,fullName =:fullName, team_id =:team_id';
$query = $this ->conn ->prepare($sql);
$query->execute(array('unique_id' => $unique_id, ':fullName' => $fullName, ':team_id' => $team_id));
$data = $query -> fetchObject();
if ($query) {
$teamMember["fullName"] = $fullName;
$teamMember["unique_id"] = $unique_id;
$teamMember["team_id"] = $team_id;
return $teamMember;
} else {
return false;
}
}
My log output using response.body confirms the values are reaching my insert script fine.
08-11 11:07:49.750 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberName() = 1
08-11 11:07:49.750 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberUniqueID() = 598d90061b7de5.56364485
08-11 11:07:49.750 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberTeamID() = 598d8ffd0b9969.94188022
08-11 11:07:49.754 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberName() = 2
08-11 11:07:49.754 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberUniqueID() = 598d90061d4347.93277262
08-11 11:07:49.754 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberTeamID() = 598d8ffd0b9969.94188022
08-11 11:07:49.759 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberName() = 3
08-11 11:07:49.759 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberUniqueID() = 598d90061d8bb2.78397401
08-11 11:07:49.759 3884-3884/com.test.practise D/TEST VALUE: getTeamMemberTeamID() = 598d8ffd0b9969.94188022
My callback confirms successful insertion of the data (I am using MYSQL) as shown via my Log outputs but the data is not actually inserting into my database.
I believe there must be a problem with my insert script somehow, but I am not sure what the solution is. I have tried to use Postman to help me but this again is not pointing me in the direction of why I am being told success but not actually getting my data to insert.
your insert query is wrong. it should be look like
$sql = 'INSERT INTO team_member (`unique_id`, `fullName`, `team_id`) VALUES (:unique_id, :fullName, :team_id)';
remove below line. its used for "SELECT" fetch records. you run insert query.
$data = $query -> fetchObject();
add below line at the same place and fetch last insert id.
$insert_id = $this ->conn->lastInsertId();//find last inserted id.
if($insert_id > 0){
$teamMember["fullName"] = $fullName;
$teamMember["unique_id"] = $unique_id;
$teamMember["team_id"] = $team_id;
return $teamMember;
}