CODEWITHSUNDEEP
phpmysqlfunctionpdorepeat
Openstar63
Openstar63

Reputation: 159

How to avoid repeating similar sections of MySQL query code in PHP?

To avoid repeating very similar sections of update/insert MySQL query code throughout my PHP, I've created these two functions. Is there a better way?.. or is this approach valid and secure? It's a low traffic application.

funcConnectToDatabase("localhost","MyDatabase");

funcUpdatePHPVariableInMySQL( "TableName","RequiredColumnName","RequiredColumnValue","UpdateColumnName","UpdateColumnValue");

function funcConnectToDatabase($thisHost,$thisDatabaseName)
{
  global $pdo, $username, $password;
  require_once($_SERVER['DOCUMENT_ROOT'] . '/../sql.php'); //includes credentials from outside root
  $pdo = new PDO("mysql:host=$thisHost;dbname=$thisDatabaseName;charset=gbk",$username,$password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
};

function funcUpdatePHPVariableInMySQL($thisTableName,$thisRequiredColumnName,$thisRequiredColumnValue,$thisUpdateColumnName,$thisUpdateColumnValue)
{
  global $pdo;
  $sqlquery = $pdo->prepare("SELECT * FROM $thisTableName WHERE $thisRequiredColumnName = :$thisRequiredColumnName");
  $sqlquery->bindParam(":$thisRequiredColumnName",$thisRequiredColumnValue);
  $sqlquery->execute();
  $rows = $sqlquery->fetchAll(PDO::FETCH_ASSOC);  
  if (count($rows) > 0) {
    $sqlquery = $pdo->prepare("UPDATE $thisTableName SET $thisUpdateColumnName='$thisUpdateColumnValue' WHERE $thisRequiredColumnName = :$thisRequiredColumnValue");
    $sqlquery->bindParam(":$thisRequiredColumnValue",$thisRequiredColumnValue);
    $sqlquery->execute();
  }
  else
  {
    $sqlquery = $pdo->prepare("INSERT INTO $thisTableName ($thisUpdateColumnName) VALUES (:$thisUpdateColumnValue)");
    $sqlquery->bindParam(":$thisUpdateColumnValue",$thisUpdateColumnValue);
    $sqlquery->execute();
  };
};

Thank you, Mark

Upvotes: 0

Views: 232

Answers (1)

Tommys
Tommys

Reputation: 121

This is not best practice to write code like this regardless whether the app is low volume or not. You are declaring same pdo object over and over again everytime the function is called which is not efficient. You should look to abstract the code to look at OOP concepts. For example create the PDO object once and then return an instance of it when connecting to DB. 

funcUpdatePHPVariableInMySQL

You are better of using something like a switch statement such as:

switch (n) {
    case update:
update DB;
        break;
    case insert:
insert into DB;       
 break;
    case delete:
delete from DB;
        break;
    ...
    default:
 xyz;
}

above looks too much hardcoding to fit the application needs. That's just my two cents.

Upvotes: 1

Related Questions