PHP PDO dynamic update query to MYSQL

Question

I have a form with an image upload and text inputs. it keeps replacing the profile_picture field with NULL. Therefore, I'm trying to create a dynamic update query, where if one value is empty it's excluded from the query altogether.

Any help is appreciated.

IMAGE UPLOAD:

if (!empty($_FILES['profile_picture']) && $_FILES['profile_picture']['error'] == UPLOAD_ERR_OK) {

    // Rename the uploaded file
    $uploadName = $_FILES['profile_picture']['name'];
    $tmp_name = $_FILES['profile_picture']['tmp_file'];
    $ext = strtolower(substr($uploadName, strripos($uploadName, '.')+1));
    $filename = round(microtime(true)).mt_rand().'.'.$ext;

if (move_uploaded_file($_FILES['profile_picture']['tmp_name'],'../profile_picutres/'. $filename)) {

}

}

UPDATE QUERY:

$stmt = $dbh->prepare("UPDATE 001_user_table_as SET profile_picture=:profile_picture, first_name=:first_name, last_name=:last_name, phone_number=:phone_number, nationality=:nationality, years_experience=:years_experience, data=:data WHERE id=:id");

 $stmt->bindParam(':profile_picture', $filename);
 $stmt->bindParam(':first_name', $first_name);
 $stmt->bindParam(':last_name', $last_name);
 $stmt->bindParam(':phone_number', $phone_number);
 $stmt->bindParam(':nationality', $nationality);
 $stmt->bindParam(':years_experience', $years_experience);
 $stmt->bindParam(':data', $cv_data);
 $stmt->bindParam(':id', $user_id);

if($stmt->execute()){
$response["message"] = 'success';   
}else{
$response["message"] = 'error'; 
$errors++;
} 

Answer 1

Below is the solution, where an input is empty, it'll use the existing data in that field and will accept not only $_POST variables, but all variables.

// the list of allowed field names
$allowed = ["profile_picture","first_name","last_name", "phone_number", "nationality", "years_experience", "data" ];

// initialize an array with values:
$params = [];

// initialize a string with `fieldname` = :placeholder pairs
$setStr = "";

// loop over source data array
foreach ($allowed as $key)
{
    if (!empty([$key]) || $key != "" || $key != NULL)
    {

        if($GLOBALS[$key] != NULL){

        $setStr .= "`$key` = :$key ,";
        $params[$key] = $GLOBALS[$key];

        }else{

        $setStr .= "`$key` = $key ,";

        }

    }else{



    }
}
$setStr = rtrim($setStr, ",");

$params['id'] = $_SESSION['user_id'];

$dbh->prepare("UPDATE 001_user_table_as SET $setStr WHERE id = :id")->execute($params);

Answer 2

Rather than relying on a global variable, you could use a function to generate the SQL string that depends only on a table name, allowed columns and columns provided. This allows you to react to any source of request (forms, raw body, ...).

<?php

function getPreparedUpdateSql(string $table, array $allowedColumns, array $columns): string
{
    $set = [];

    foreach ($columns as $column) {
        if (!in_array($column, $allowedColumns)) {
            continue;
        }

        $set[] = "$column = :$column";
    }

    $set = implode(", ", $set);

    return "UPDATE $table SET $set WHERE id = :id";
}

And here is an example usage of that function anywhere you need it.

<?php

$connection = new PDO("mysql:dbname=dbname;host=127.0.0.1", "user", "pass");

$jsonRequestBody = json_decode(file_get_contents("php://input"), true);
// ["firstname" => "firstname", "lastname" => "lastname"]

$entityId = 1;

$table = "users";

$allowedColumns = ["firstname", "lastname", "email", "role"];

$columns = array_keys($jsonRequestBody);
// ["firstname", "lastname"]

$sql = getPreparedUpdateSql($table, $allowedColumns, $columns);
// UPDATE users SET firstname = :firstname, lastname = :lastname WHERE id = :id

$query = $connection->prepare($sql);

$query->execute([...$jsonRequestBody, "id" => $entityId]);

If you want to use it on traditional forms, you can simply change the columns variable to this.

<?php

$columns = array_keys($_POST);

Do not forget to check for thrown exceptions!

Answer 3

try to uninstall your current nodeJs.and install the LTS version of node again.

Answer 4

Refer this answer. The error you are getting mentions that Python cannot be found. The steps in the above linked answer describes how you can resolve this by deleting and reinstalling node_modules.