I am working with PHP slim framework to develop API. I can GET and DELETE but with the inserting (POST) and updating (PUT), I am facing problems.
class db {
//Propertise
private $dbhost = 'localhost';
private $dbuser = 'root';
private $dbpass = '';
private $dbname = 'bralivnara-api';
//Connect
public function connect(){
try {
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', 'CHARSET utf8');
$dbConnection = new PDO("mysql:host=$this->dbhost;dbname=$this->dbname;", $this->dbuser, $this->dbpass, $options);
// set the PDO error mode to exception
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
return $dbConnection;
}
}
When I use rest easy I receive the 500 error.
Response Status: 500 (Internal Server Error)
When I passed the URL and press enter in browser, it shows the error
Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'add' at line 1
Before I had an issue with Swedish letters also, it was not displaying in browser correctly. If anyone has same issue you can fix this issue with that header. It will work for all non-English letters
return $response->withStatus(200)
->withHeader('Content-Type', 'application/json;charset=utf-8;')
->write(json_encode($tranasVcDatainsamling, JSON_UNESCAPED_UNICODE));
If you want to see complete get code you can join our discussion.
Now the last problem which still remains is inserting to database. Here is the code for insertion to the database.
$app->post('/api/tranas-vc/add', function (Request $request, Response $response){
$besöksdatum = $request->getParam('besöksdatum');
$kön = $request->getParam('kön');
$ålder_år = $request->getParam('ålder_år');
$sökorsak = $request->getParam('sökorsak');
$vp_objektiv_prio = $request->getParam('vp_objektiv_prio');
$vp_objektiv_variabel_med_högst_prio = $request->getParam('vp_objektiv_variabel_med_högst_prio');
$vp_mätning_prio = $request->getParam('vp_mätning_prio');
$vp_mätning_variabel_med_högst_prio = $request->getParam('vp_mätning_variabel_med_högst_prio');
$ess_val = $request->getParam('ess_val');
$prio_ess = $request->getParam('prio_ess ');
$prio_total = $request->getParam('prio-total');
$utskrivning_hänvisning_återbesök = $request->getParam('utskrivning_hänvisning_återbesök');
$åtgärd_remiss = $request->getParam('åtgärd_remiss');
$om_lab_vilka_prover = $request->getParam('om-lab-vilka-prover');
$sql = "INSERT INTO
`tranasvcdatainsamling`
(
`besöksdatum`,
`kön`,
`ålder_år`,
`sökorsak`,
`vp_objektiv_prio`,
`vp_objektiv_variabel_med_högst_prio`,
`vp_mätning_prio`,
`vp_mätning_variabel_med_högst_prio`,
`ess_val`,
`prio_ess`,
`prio_total`,
`utskrivning_hänvisning_återbesök`,
`åtgärd_remiss`,
`om_lab_vilka_prover`
)
VALUES
(
:`besöksdatum`,
:`kön`,
:`ålder_år`,
:`sökorsak`,
:`vp_objektiv_prio`,
:`vp_objektiv_variabel_med_högst_prio`,
:`vp_mätning_prio`,
:`vp_mätning_variabel_med_högst_prio`,
:`ess_val`,
:`prio_ess`,
:`prio_total`,
:`utskrivning_hänvisning_återbesök`,
:`åtgärd_remiss`,
:`om_lab_vilka_prover`
)";
try {
// Get DB Objects
$db = new db();
// Connect
$db = $db->connect();
// Execute Query
$stmt = $db->prepare($sql);
$stmt->bindParam(':besöksdatum', $besöksdatum);
$stmt->bindParam(':kön', $kön);
$stmt->bindParam(':ålder_år', $ålder_år);
$stmt->bindParam(':sökorsak', $sökorsak);
$stmt->bindParam(':vp_objektiv_prio', $vp_objektiv_prio);
$stmt->bindParam(':vp_objektiv_variabel_med_högst_prio', $vp_objektiv_variabel_med_högst_prio);
$stmt->bindParam(':vp_mätning_prio', $vp_mätning_prio);
$stmt->bindParam(':vp_mätning_variabel_med_högst_prio', $vp_mätning_variabel_med_högst_prio);
$stmt->bindParam(':ess_val', $ess_val);
$stmt->bindParam(':prio_ess', $prio_ess);
$stmt->bindParam(':prio_total', $prio_total);
$stmt->bindParam(':utskrivning_hänvisning_återbesök', $utskrivning_hänvisning_återbesök);
$stmt->bindParam(':åtgärd_remiss', $åtgärd_remiss);
$stmt->bindParam(':om_lab_vilka_prover', $om_lab_vilka_prover);
$stmt->execute();
echo '{"notice": {"text": "Tranås VC Entry Added"}';
} catch(PDOEception $e) {
echo '{"error": {"text": '.$e->getMessage().'}';
}
});
We did all possible experiments on that code, you can see those suggestions in comments, but we still had the problem in POST and PUT.
I commented the $stmt->execute(); to stop the execution of the query and check, I got 200 success in rest easy, but data was not inserted. I browser the error was same. It shows that the problem is in execution. I checked that code on English letters kind of data, and it was working.
I am quite sure that I had the problem because of Swedish letter, which is non-English.
I database those columns which contain Swedish letters look like that
kön varchar(125) latin1_swedish_ci
sökorsak varchar(125) latin1_swedish_ci
Which show latin1_swedish_ci, that I want you to consider also. I will appreciate your help, and please join this discussion. If we fix this issue it will be very good research for non-English letters issue. I google almost whole day but did not got solution.
