PHP PDO Bind Parameters Redundancy

Question

I have this dynamic query that if the user doesn't upload a file it will only update the other fields, then if the user uploads a file, the query and fields to be updated are the same but this time it also updates the file - field. The code below actually works but I'm wondering if the bindParameters can be used once then just add the 'pdf_file' field instead of putting the same bindParamters on different queries.

$query = 'UPDATE `records` SET
            `title` = :title,
            `author`= :author,
            `subject` = :subject,
            `call_no`= :call_no,
            `year` = :year ';

 if(empty($pdf_file)){
        
        $query .='WHERE `record_id` = :id';
        $update=$pdo->prepare($query);
    
        $update->bindParam(':title',$title, PDO::PARAM_STR); 
        $update->bindParam(':author',$author, PDO::PARAM_STR);
        $update->bindParam(':subject',$subject, PDO::PARAM_STR); 
        $update->bindParam(':call_no',$call_no, PDO::PARAM_STR); 
        $update->bindParam(':year',$year, PDO::PARAM_STR); 
        $update->bindParam(':id',$id, PDO::PARAM_STR); 
    
        if($update->execute()){
            echo "nofile_ok";
        }
        $errorMsg = 'nofile_ok';
 }else if($ext == "pdf"){
      // Accept
 }else if($pdf_type == "application/pdf"){
     // Accept
  }else{
     echo "notpdf";
     $errorMsg = "Upload PDF Only.....Check your file extenson";
  }

  if(!isset($errorMsg)){
                    $query .= ',`record_file` = :file_pdf WHERE `record_id` = :id';
                    $update=$pdo->prepare($query);
                    $update->bindParam(':title',$title, PDO::PARAM_STR); 
                    $update->bindParam(':author',$author, PDO::PARAM_STR);
                    $update->bindParam(':subject',$subject, PDO::PARAM_STR); 
                    $update->bindParam(':call_no',$call_no, PDO::PARAM_STR); 
                    $update->bindParam(':year',$year, PDO::PARAM_STR); 
                    $update->bindParam(':id',$id, PDO::PARAM_STR); 
                    $update->bindParam(':file_pdf',$db_pdf, PDO::PARAM_STR);
                    
                    if($update->execute()){         
                        move_uploaded_file($temp,$pdf); //
                        echo "file_ok"; //pass ajax success message
                    }
                }

You can't reuse the same statement for different SQL code. But, if you're concerned about redundancy, you may want to pass all parameters at once in an array (you've chosen the most verbose syntax for no clear reason, since even integers are declared as strings). — Álvaro González
– Álvaro González, Commented Oct 10, 2020 at 17:06

Bill Karwin · Accepted Answer · 2020-10-10 18:41:51Z

I have no idea why so many developers assume you need to use bindParam() all over the place. It's documented and much more concise to use execute() with an array.

See the example #2 and #3 in the docs: https://www.php.net/manual/en/pdostatement.execute.php

You could define an associative array with your parameter => value pairs, and then if you want to add the :file_pdf parameter, just set one additional entry in the associative array.

$query = 'UPDATE `records` SET
            `title` = :title,
            `author`= :author,
            `subject` = :subject,
            `call_no`= :call_no,
            `year` = :year ';

$params = [ 
    'title' => $title,
    'author' => $author,
    'subject' => $subject,
    'call_no' => $call_no,
    'year' => $year,
    'id' => $id
];

if(empty($pdf_file)){
        
    $query .= 'WHERE `record_id` = :id';
    $update=$pdo->prepare($query);
    
    if($update->execute($params)){
        echo "nofile_ok";
    }   
    $errorMsg = 'nofile_ok';
}else if($ext == "pdf"){
    // Accept
}else if($pdf_type == "application/pdf"){
    // Accept
}else{
    echo "notpdf";
    $errorMsg = "Upload PDF Only.....Check your file extenson";
} 

if(!isset($errorMsg)){
    $query .= ',`record_file` = :file_pdf WHERE `record_id` = :id';
    $update=$pdo->prepare($query);

    $params['file_pdf'] = $db_pdf; // set one more parameter
    
    if($update->execute($params)){  
        move_uploaded_file($temp,$pdf); 
        echo "file_ok"; //pass ajax success message 
    }   
}

By the way, in old versions of the PDO library, it was necessary to use the : on the keys of the bound parameters, but it isn't anymore. You need the : sigil in the SQL query to signify a named parameter, but you don't need that sigil in the parameter keys when you pass them to the statement.

Collectives™ on Stack Overflow

PHP PDO Bind Parameters Redundancy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related