I have an issue with php/mysql. When I want to update or delete an entry in a table with php, I have this error :
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
I tried to set MYSQL_SAFE_UPDATES to 0 but it still doesn't work.
I also read this post : MySQL error code: 1175 during UPDATE in MySQL Workbench and it didn't solve my problem
Does anyone have a tip ?
code:
$sql2 = "update $table set ";
$sql3 = "insert into $table"."_ (";
$find;
foreach ($arr2 as $name) {
if($name != "user"){
$id2 ='inp'.$k;
if(isset($_POST[$id2])){
$val = $_POST[$id2];
$sql3.= $name.",";
}
$k++;
}
else{
$find = $k;
//$k++;
}
}
$sql3 = substr($sql3,0,-1);
$sql3.=") ";
$sql3.= " values( ";
$check = "select * from $table where ";
$k=0;
$f=0;
foreach ($arr as $name) {
if($arr2[$k] != "user"){
$id2 ='inp'.$f;
if(isset($_POST[$id2])){
$val = $_POST[$id2];
// echo $val." ";
if(isset($_POST[$id2])){
if ((strpos($name, 'int') !== false || strpos($name,'float') !== false) && (!empty($val) && is_numeric ($val))){
$sql2.= $arr2[$k]."=".$val.", ";
$check.= $arr2[$k]."=".$val." and ";
$sql3.= $val.",";
}
else{
if(strpos($name, 'varchar') !== false){
$sql2.= $arr2[$k]."='".$val."', ";
$check.= $arr2[$k]."='".$val."' and ";
$sql3.= "'".$val."',";
}
else{
if ((strpos($name, 'int') !== false || strpos($name,'float') !== false) && (!empty($val) && !is_numeric ($val))){
goto error;
break;
}
}
}
}
else{
$sql2.=$arr2[$k]."="."NULL,";
$check.=$arr2[$k]."="."NULL and ";
$sql3.="NULL,";
}
$f++;
}
}
$k++;
}
$use = $_SESSION['user'];
$check = substr($check,0,-5);
$check.=";";
$sth = $conn->prepare("$check");
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
if(!$row){
$sql2 = substr($sql2, 0, -2);
$sql2.=" where ";
$i = 0;
$j=0;
$bool = 0;
if(isset($_SESSION['tab'])){
foreach ($arr as $name) {
if($arr2[$i] != "user"){
$id2 ='inp'.$j;
if(isset($_POST[$id2])){
$val = $_SESSION['tab'][$j];
if($id2 != ""){
$sql2.= $arr2[$i]."=";
if ((strpos($name, 'int') !== false || strpos($name,'float') !== false) && (!empty($val) && is_numeric ($val))){
$sql2.= $val." and ";
}
else{
if(strpos($name,'varchar') !== false){
$sql2.= "'".$val."' and ";
}
else{
if(empty($val)){
$sql2.= $arr2[$i]." IS NULL and ";
}
else{
if ((strpos($name, 'int') !== false || strpos($name,'float') !== false) && (!empty($val) && !is_numeric ($val))){
goto error;
break;
}
}
}
}
}
unset($_POST[$id2]);
$j++;
}
else{
$sql2.= $arr2[$i]." IS NULL and ";
}
}
$i++;
}
unset($_SESSION['tab']);
}
else{
echo "ici";
goto error;
$bool = -1;
}
if($bool != -1){
$sql2 = substr($sql2,0,-5);
$sql2.=";";
try{
$sth = $conn->prepare("$sql2");
$sth->execute();
}
catch(Exception $e){
echo $e;
}
$sql3 = substr($sql3,0,-1);
$sql3.=");";
try{
$sth = $conn->prepare("$sql3");
$sth->execute();
}
catch(Exception $e){
echo $e;
}
echo $sql2."<br/>".$sql3."<br/>";
unset($_POST['sub']);
//redirect('database.php', 303);
}
}
else{
echo $check;
goto error;
}
}
error :
if(isset($e))
echo $e;
this is the echo statement:
update recherche set date='erzerze', coordonnees='zeraezr', typeMilieu='Forêt', responsable='azerazer', observateurPreleveur='azerazer', projet='azerazer', observations='Flore', prelevements='Flore', experimentations='Identification', commentaires=' ', test='rtrt' where date='erzerze' and coordonnees='zeraezr' and typeMilieu='Forêt' and responsable='azerazer' and observateurPreleveur='azerazer' and projet='azerazer' and observations='Flore' and prelevements='Flore' and experimentations='Identification' and commentaires=' ' and test='';
