Difference between bindParam and bindValue in PHP

PDOStatement::bindParam() Function

The PDOStatement::bindParam() function is an inbuilt function in PHP that is used to bind a parameter to the specified variable name. This function bound the variables, pass their value as input, and receives the output value, if any, of their associated parameter marker.
Syntax:

bool PDOStatement::bindParam
( $parameter, $variable, $data_type, $length, $driver_options )

Parameters: This function accepts five parameters as mentioned above and described below:

• $parameter: It is a parameter identifier that is used to prepare statements using name placeholders. It is the parameter name of the form : name.
• $variable: This parameter is used to hold the name of the variable to bind to the SQL statement parameter.
• $data_type: It is an explicit data type for the parameter using the PDO::PARAM_* constants.
• $length: This parameter is used to hold the length of the data type.
• $driver_options: This parameter holds the operation which needs to perform.

Return Value: This function returns True on success or false on failure.
Program: 
 

<?php

// setup PDO connection
$db = new PDO('mysql:host=localhost;dbname=geeks','root','');

// Get username
$username = 'geeksforgeeks';
    
$stmt = $db->prepare("SELECT * FROM users WHERE user = :username");

// Use bindParam function
$stmt->bindParam(':username', $username);

$username = 'g4g';
    
$stmt->execute();
?>

Note: The SQL statement will be executed using 'g4g' as the username because :username searches for $username upon execution, and the last known value of $username is 'g4g'.

PDOStatement::bindValue() Function

The PDOStatement::bindValue() function is an inbuilt function in PHP that is used to bind a value to a parameter. This function binds a value to the corresponding named or question mark placeholder in the SQL which is used to prepare the statement. 

Syntax:

bool PDOStatement::bindValue( $parameter, $value, $data_type )

Parameters: This function accepts three parameters as mentioned above and described below:

• $parameter: It is a parameter identifier that is used to prepare statements using name placeholders. It is the parameter name of the form:name.
• $value: This parameter is used to hold the value to bind the parameter.
• $data_type: It is an explicit data type for the parameter using the PDO::PARAM_* constants.

Return Value: This function returns True on success or False on failure.
Program:

<?php

// setup PDO connection
$db = new PDO('mysql:host=localhost;dbname=geeks','root','');

// Get username
$username = 'geeksforgeeks';
    
$stmt = $db->prepare("SELECT * FROM users WHERE user = :username");

// Use bindValue function
$stmt->bindValue(':username', $username);

$username = 'g4g';
    
$stmt->execute();
?>

Note: The SQL statement will be executed using 'geeksforgeeks' as the username because the literal value "geeksforgeeks" has been bound to username prior to the bindValue() function. Further changes to $username will not be reflected in the prepared statement.
Difference between bindParam() and bindValue():

1. bindParam(): 
   
   1. The bindParam() function binds a parameter to a named or question mark placeholder in an SQL statement.
   2. The bindParam () function is used to pass variable not value.
   3. bindParam() function is executed at  runtime.
   4. bindParam is a PHP inbuilt function.
   5. Parameters can be modified in bindParam().
   6. Its return value is of boolean types.
2. bindValue(): 
   
   1. The bindValue() function binds a value to a named or question mark in the SQL statement.
   2. The bindValue() function is used to pass both value and variable.
   3.  bindValue function is executed at compile time.
   4. bindValue() is an in built PHP function 
   5. Parameters cannot be modified in bindValue().
   6. Its return value is of boolean types.