Three PHP database queries to manage accounts I have a database connection PHP file which has a lot of queries to the database. Here are 3 of the functions that I am using:

public function deleteAccount($token)
{
    $sql1 =
        "DELETE FROM $this-&gt;table_user
         WHERE $this-&gt;key_token = ?;";

    $stmt1 = $this-&gt;connection-&gt;prepare($sql1);
    $stmt1-&gt;bind_param("s", $token);
    $stmt1-&gt;execute();
    $result = $stmt1-&gt;affected_rows;
    $stmt1-&gt;close();
    return ($result &gt;= 1);
}

public function getAccountDetails($token)
{
    $sql1 =
        "SELECT $this-&gt;key_name, $this-&gt;key_username, $this-&gt;key_email
         FROM $this-&gt;table_user
         WHERE $this-&gt;key_token = ?;";
    $stmt1 = $this-&gt;connection-&gt;prepare($sql1);
    $stmt1-&gt;bind_param("s", $token);
    $stmt1-&gt;execute();
    $stmt1-&gt;bind_result($name, $username, $email);
    $stmt1-&gt;fetch();
    $stmt1-&gt;close();
    return
        array(
            "name" =&gt; $name,
            "username" =&gt; $username,
            "email" =&gt; $email
        );
}

public function resetPassword($password, $hash, $email)
{
    $sql1 =
        "UPDATE $this-&gt;table_user
         SET $this-&gt;key_hash = ?
         WHERE $this-&gt;key_email = ? AND $this-&gt;key_passwordhash = ?;";

    $stmt1 = $this-&gt;connection-&gt;prepare($sql1);
    $passhash = password_hash($password, $this-&gt;algorithm);
    $stmt1-&gt;bind_param("sss", $passhash, $email, $hash);
    $stmt1-&gt;execute();
    $result = ($stmt1-&gt;affected_rows == 1);
    $stmt1-&gt;close();
    return $result;
}


To me this seems like a lot of code duplication and I find myself doing a lot of copying and pasting which is a big sign that it is very duplicated but I am struggling to see how I can refactor this code so that for every sql query I perform it does not require so many lines of code.
Common functionality refactoring


public function deleteAccount($token)
{
    $sql1 =
        "DELETE FROM $this-&gt;table_user
         WHERE $this-&gt;key_token = ?;";

    $stmt1 = $this-&gt;connection-&gt;prepare($sql1);
    $stmt1-&gt;bind_param("s", $token);
    $stmt1-&gt;execute();
    $result = $stmt1-&gt;affected_rows;
    $stmt1-&gt;close();
    return ($result &gt;= 1);
}



You could change this to something like 

private function modify($sql, array $parameters = [])
{
    $stmt = $this-&gt;connection-&gt;prepare($sql);

    if (!empty($parameters))
    {
        // ensure $parameters are passed by reference
        $parameters_by_reference = [];
        for ($i = 0, $n = count($parameters); $i &lt; $n; $i++)
        {
            $parameters_by_reference[] =&amp; $parameters[$i];
        }

        call_user_func_array([$stmt, bind_param], $parameters_by_reference);
    }

    $stmt-&gt;execute();
    $result = $stmt-&gt;affected_rows;
    $stmt-&gt;close();

    return $result;
}

public function deleteAccount($token)
{
    $sql = &lt;&lt;&lt;EOSQL
DELETE FROM $this-&gt;table_user
  WHERE $this-&gt;key_token = ?
EOSQL;

    return ($this-&gt;modify($sql, ['s', $token]) &gt;= 1);
}


Now it only takes two or three statements to set up the SQL query.  The repeated part is all inside the modify function.  Note that modify can handle any of INSERT, UPDATE, or DELETE.  

I prefer the heredoc as being better for multiline strings.  

I don't like numbered variables, particularly if there's only one of them.  

I prefer to use single quotes for PHP strings that do not have any variable interpolation in them.  

You do not need to pass a ; when executing a single statement programmatically.  

Be careful about $this-&gt;table_user and $this-&gt;key_token.  These should NOT contain any user input.  Otherwise, this would be subject to SQL injection.  

You can then change 


public function resetPassword($password, $hash, $email)
{
    $sql1 =
        "UPDATE $this-&gt;table_user
         SET $this-&gt;key_hash = ?
         WHERE $this-&gt;key_email = ? AND $this-&gt;key_passwordhash = ?;";

    $stmt1 = $this-&gt;connection-&gt;prepare($sql1);
    $passhash = password_hash($password, $this-&gt;algorithm);
    $stmt1-&gt;bind_param("sss", $passhash, $email, $hash);
    $stmt1-&gt;execute();
    $result = ($stmt1-&gt;affected_rows == 1);
    $stmt1-&gt;close();
    return $result;
}



to 

public function resetPassword($password, $hash, $email)
{
    $sql = &lt;&lt;&lt;EOSQL
UPDATE $this-&gt;table_user
  SET $this-&gt;key_hash = ?
  WHERE $this-&gt;key_email = ? AND $this-&gt;key_passwordhash = ?
EOSQL;

    $passhash = password_hash($password, $this-&gt;algorithm);

    return ($this-&gt;modify($sql, 'sss', $passhash, $email, $hash) == 1);
}


PDO

Note that if you switch to PDO, then modify could be simpler:  

private function modify($sql, array $parameters = [])
{
    $stmt = $this-&gt;connection-&gt;prepare($sql);

    foreach ($parameters as $parameter =&gt; $value)
    {
        $stmt-&gt;bindParam($parameter, $value);
    }

    $stmt-&gt;execute();

    return $stmt-&gt;rowCount();
}


And you would call it like 

    return ($this-&gt;modify($sql, [1 =&gt; $passhash, 2 =&gt; $email, 3 =&gt; $hash]) == 1);
    return ($this-&gt;modify($sql, [1 =&gt; $token]) &gt;= 1);


Or you could use named parameters, but that requires modifying the SQL string as well.