使用PHP非空的输入将数据插入MySQL数据库(Insert data into MySQL database from inputs that are not empty with PHP)

 我有一个页面，用户可以更新或更改他的个人资料信息，例如他的位置或他的个人资料图片。 默认情况下，每个用户在其配置文件页面上获取占位符数据或信息，并且mysql数据库中的那些字段为空或者具有该用户ID的行不存在。 所以首先我要检查用户以前是否将他的个人资料信息输入到数据库中，如果数据存在，我将只使用非空的输入字段更新它，这部分有效。 但是，如果数据库或行中不存在数据，并且此用户ID不存在，那么我想插入数据，但只能输入非空的输入 。 
 
 但这是我得到的错误 
 
 
 
 带有消息'SQLSTATE [42000]的未捕获异常'PDOException'：语法错误或访问冲突：1064 SQL语法中有错误; ... 
 
 
 这是我的代码（ $userID是session $userID = $_SESSION['ID']; ） 
 
if(isset($_POST['updateInfo'])) {
$userDesc = filter_var($_POST['description_update'], FILTER_SANITIZE_STRING);
$userLocation = filter_var($_POST['location_update'], FILTER_SANITIZE_STRING);
$userBirthDate = filter_var($_POST['birthDate_update'], FILTER_SANITIZE_STRING);
$user_picture_tmp = $_FILES['user_picture']['tmp_name'];
$user_picture_name = $_FILES['user_picture']['name'];


$checkInfo = $con->prepare("SELECT * FROM user_info WHERE userid=:userID");
$checkInfo->execute(array(':userID' => $userID));
$data = $checkInfo->fetch(PDO::FETCH_ASSOC);    
$count_rows = $checkInfo->rowCount();

//Here i am checking if data exists in mysql and UPDATING it    
if($count_rows > 0) {   

    if(!empty($userDesc)) {
        $query = $con->prepare("UPDATE user_info SET description=:description WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':description'=> $userDesc));
    }   
    if(!empty($userLocation)) {
        $query = $con->prepare("UPDATE user_info SET location=:location WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':location'=> $userLocation));
    }
    if(!empty($userBirthDate)) {
        $query = $con->prepare("UPDATE user_info SET birthDate=:birthDate WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':birthDate'=> $userBirthDate));
    }
    if(!empty($user_picture_name)) {
        $query = $con->prepare("UPDATE user_info SET profile_pic=:profile_pic WHERE userid=:userID");
        move_uploaded_file($user_picture_tmp, 'user_pic/'.$user_picture_name);
        $query->execute(array(':userID' => $userID, ':profile_pic'=> $user_picture_name));

    }   
    //But if data doesn't exists i want to INSERT IT and here is error
}   else {

    if($userDesc AND $userLocation AND $userBirthDate AND $user_picture_name != '') {

        $query = $con->prepare("INSERT INTO user_info(userid) VALUES(:userID)");
        $query->execute(array(':userID' => $userID));

        if(!empty($userDesc)) {
            $query = $con->prepare("INSERT INTO user_info(description) VALUES(?) WHERE userid=?");
            $query->execute(array($userDesc, $userID));
        }   
        if(!empty($userLocation)) {
            $query = $con->prepare("INSERT INTO user_info(location) VALUES(?) WHERE userid=?");
            $query->execute(array($userLocation, $userID));
        }
        if(!empty($userBirthDate)) {
            $query = $con->prepare("INSERT INTO user_info(birthDate) VALUES(?) WHERE userid=?");
            $query->execute(array($userBirthDate, $userID));
        }   
        if(!empty($user_picture_name)) {
            $query = $con->prepare("INSERT INTO user_info(profile_pic) VALUES(?) WHERE userid=?");
            move_uploaded_file($user_picture_tmp, 'user_pic/'.$user_picture_name);
            $query->execute(array($user_picture_name, $userID));

        }
    } else {echo 'All fields are empty';} 

}                       
}
 
 所以我的问题是为什么我得到这个错误，我也是这样做错误的方式或是否有其他更好的方法来做到这一点。 这仅用于学习目的。 

I have page where user can update or change his profile information for example his location or his profile picture. By default every user get placeholder data or information on his profile page and those fields in mysql database are empty or row with that user id doesn't exists. So first i am checking if user previously entered his profile information into database and if data exist i will just update it with input fields that are not empty and this part works. But if data doesn't exists in database or row with this user id doesn't exist then i want to insert data but again only with inputs that are not empty. 
 
But this is error i am getting 
 
 
 
Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;...
 
 
This is my code ($userID is session $userID = $_SESSION['ID'];)
 
if(isset($_POST['updateInfo'])) {
$userDesc = filter_var($_POST['description_update'], FILTER_SANITIZE_STRING);
$userLocation = filter_var($_POST['location_update'], FILTER_SANITIZE_STRING);
$userBirthDate = filter_var($_POST['birthDate_update'], FILTER_SANITIZE_STRING);
$user_picture_tmp = $_FILES['user_picture']['tmp_name'];
$user_picture_name = $_FILES['user_picture']['name'];


$checkInfo = $con->prepare("SELECT * FROM user_info WHERE userid=:userID");
$checkInfo->execute(array(':userID' => $userID));
$data = $checkInfo->fetch(PDO::FETCH_ASSOC);    
$count_rows = $checkInfo->rowCount();

//Here i am checking if data exists in mysql and UPDATING it    
if($count_rows > 0) {   

    if(!empty($userDesc)) {
        $query = $con->prepare("UPDATE user_info SET description=:description WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':description'=> $userDesc));
    }   
    if(!empty($userLocation)) {
        $query = $con->prepare("UPDATE user_info SET location=:location WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':location'=> $userLocation));
    }
    if(!empty($userBirthDate)) {
        $query = $con->prepare("UPDATE user_info SET birthDate=:birthDate WHERE userid=:userID");
        $query->execute(array(':userID' => $userID, ':birthDate'=> $userBirthDate));
    }
    if(!empty($user_picture_name)) {
        $query = $con->prepare("UPDATE user_info SET profile_pic=:profile_pic WHERE userid=:userID");
        move_uploaded_file($user_picture_tmp, 'user_pic/'.$user_picture_name);
        $query->execute(array(':userID' => $userID, ':profile_pic'=> $user_picture_name));

    }   
    //But if data doesn't exists i want to INSERT IT and here is error
}   else {

    if($userDesc AND $userLocation AND $userBirthDate AND $user_picture_name != '') {

        $query = $con->prepare("INSERT INTO user_info(userid) VALUES(:userID)");
        $query->execute(array(':userID' => $userID));

        if(!empty($userDesc)) {
            $query = $con->prepare("INSERT INTO user_info(description) VALUES(?) WHERE userid=?");
            $query->execute(array($userDesc, $userID));
        }   
        if(!empty($userLocation)) {
            $query = $con->prepare("INSERT INTO user_info(location) VALUES(?) WHERE userid=?");
            $query->execute(array($userLocation, $userID));
        }
        if(!empty($userBirthDate)) {
            $query = $con->prepare("INSERT INTO user_info(birthDate) VALUES(?) WHERE userid=?");
            $query->execute(array($userBirthDate, $userID));
        }   
        if(!empty($user_picture_name)) {
            $query = $con->prepare("INSERT INTO user_info(profile_pic) VALUES(?) WHERE userid=?");
            move_uploaded_file($user_picture_tmp, 'user_pic/'.$user_picture_name);
            $query->execute(array($user_picture_name, $userID));

        }
    } else {echo 'All fields are empty';} 

}                       
}
 
So my question is why am i getting this error and also am i doing this the wrong way or is there some other better approach to do this. This is just for learning purposes. 

原文：https://stackoverflow.com/questions/33022398