I have created one API using php slim framework which has login.register.task add/update/delete functionality.
The module working fine on live server here. But when i try to call the api from my another server here or localhost login/register working fine but it's giving following error when creating particular user task which creates task_id & user_id.task - method post error is
execute() failed: Column 'user_id' cannot be null
My api url is
'http://creativepixel.co.in/task_manager_angular/api2/v1/';
& task method get response is null
{"error":false,"tasks":[]}
My mysql table structure is follow:
user = id,name,email,password,api_key
task = id, task
user_tasks = id,user_id,task_id
My html ajax call is here (after creating a task using textfield, onsubmit i reload the page and page loads all tasks in below the textfield but after creating task the third table "user_tasks" get empty and it's giving above error)
$scope.createtask = function(add){
var data= {
"task":$scope.createtask.createtask_input
}
$http({
method: 'POST',
url: 'api2/v1/tasks',
crossDomain: true,
dataType: "json",
contentType: 'application/x-www-form-urlencoded',
transformRequest: function(obj) {
var str = [];
for(var p in obj)
str.push(encodeURIComponent(p) + "=" + encodeURIComponent(obj[p]));
return str.join("&");
},
data:data,
}).success(function (response, data, textStatus, jqXHR) {
console.log(response);
console.log(textStatus);
console.log(jqXHR);
$route.reload();
});
};
my mysql call is here
//Creating new task
//@param String $user_id user id to whom task belongs to
//@param String $task task text
public function createTask($user_id, $task) {
$stmt = $this->conn->prepare("INSERT INTO tasks(task) VALUES(?)");
$stmt->bind_param("s", $task);
$result = $stmt->execute();
$stmt->close();
if ($result) {
// task row created
// now assign the task to user
$new_task_id = $this->conn->insert_id;
$res = $this->createUserTask($user_id, $new_task_id);
if ($res) {
// task created successfully
return $new_task_id;
} else {
// task failed to create
return NULL;
}
} else {
// task failed to create
return NULL;
}
}
//Function to assign a task to user
//@param String $user_id id of the user
//@param String $task_id id of the task
public function createUserTask($user_id, $task_id) {
$stmt = $this->conn->prepare("INSERT INTO user_tasks(user_id, task_id) values(?, ?)");
$stmt->bind_param("ii", $user_id, $task_id);
$result = $stmt->execute();
if (false === $result) {
die('execute() failed: ' . htmlspecialchars($stmt->error));
}
$stmt->close();
return $result;
}
Here is my php api call functions
<?php
header('Access-Control-Allow-Origin: *');
require_once '../include/DbHandler.php';
require_once '../include/PassHash.php';
require '.././libs/Slim/Slim.php';
\Slim\Slim::registerAutoloader();
$app = new \Slim\Slim();
// User id from db - Global Variable
$user_id = NULL;
function authenticate(\Slim\Route $route) {
// Getting request headers
$headers = apache_request_headers();
$response = array();
$app = \Slim\Slim::getInstance();
// Verifying authorization Header
$db = new DbHandler();
$session = $db->getSession();
$email = $session['email'];
//if (isset($headers['authorization'])) {
if (!$db->isValidApiKey($email)) {
// api key is not present in users table
$response["error"] = true;
$response["message"] = "Access Denied. Invalid Api key";
echoRespnse(401, $response);
$app->stop();
} else {
global $user_id;
// get user primary key id
$user_id = $db->getUserId($email);
}
}
// Creating new task in db
// method POST
// params - name
// url - /tasks/
$app->post('/tasks', 'authenticate', function() use ($app) {
// check for required params
verifyRequiredParams(array('task'));
$response = array();
$task = $app->request->post('task');
global $user_id;
$db = new DbHandler();
// creating new task
$task_id = $db->createTask($user_id, $task);
if ($task_id != NULL) {
$response["error"] = false;
$response["message"] = "Task created successfully";
$response["task_id"] = $task_id;
$response["task"] = $task;
echoRespnse(201, $response);
} else {
$response["error"] = true;
$response["message"] = "Failed to create task. Please try again";
echoRespnse(200, $response);
}
});
// Verifying required params posted or not
function verifyRequiredParams($required_fields) {
$error = false;
$error_fields = "";
$request_params = array();
$request_params = $_REQUEST;
// Handling PUT request params
if ($_SERVER['REQUEST_METHOD'] == 'PUT') {
$app = \Slim\Slim::getInstance();
parse_str($app->request()->getBody(), $request_params);
}
foreach ($required_fields as $field) {
if (!isset($request_params[$field]) || strlen(trim($request_params[$field])) <= 0) {
$error = true;
$error_fields .= $field . ', ';
}
}
if ($error) {
// Required field(s) are missing or empty
// echo error json and stop the app
$response = array();
$app = \Slim\Slim::getInstance();
$response["error"] = true;
$response["message"] = 'Required field(s) ' . substr($error_fields, 0, -2) . ' is missing or empty';
echoRespnse(400, $response);
$app->stop();
}
}
in dbhandler.php i supply
//Validating user api key
//If the api key is there in db, it is a valid key
//@param String $api_key user api key
//@return boolean
public function isValidApiKey($email) {
$stmt = $this->conn->prepare("SELECT id from users WHERE email = ?");
$stmt->bind_param("i", $email);
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
$stmt->close();
return $num_rows > 0;
}
/**
* Fetching user id by api key
* @param String $api_key user api key
*/
public function getUserId($email) {
$stmt = $this->conn->prepare("SELECT id FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
if ($stmt->execute()) {
$stmt->bind_result($user_id);
$stmt->fetch();
// TODO
// $user_id = $stmt->get_result()->fetch_assoc();
$stmt->close();
return $user_id;
} else {
return NULL;
}
}
// Fetching all user tasks
// @param String $user_id id of the user
public function getAllUserTasks($user_id) {
$stmt = $this->conn->prepare("SELECT t.* FROM tasks t, user_tasks ut WHERE t.id = ut.task_id AND ut.user_id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$tasks = $stmt->get_result();
$stmt->close();
return $tasks;
}
Please check, you can register with fake id and analyze the code...