Implement global functions to connect to a database and execute queries in PHP

Question

I am in the learning phase of PHP. Could anyone please help me to write several functions:

• ConDB($host, $user, $pwd) to connect to MySQL database via PDO.

• RunSQL($sql) to execute statements (like CREATE, INSERT, UPDATE, DELETE). This function will use the ConDB() function to connect to the database and execute the $sql then close the connection automatically.

• QuerySQL($sql) to query the database. It should return an array of recordsets that we can use further to display as a table on a page.

The RunSQL() and QuerySQL() functions should be available on any .php pages.

If something better can be done to achieve this will be a great help for me. Thanks.

For example I want to be able to write this:

$sql = "CREATE TABLE Persons(Person_ID int Auto_Incrememt PrimaryKey, Name varchar(255))";
RunSQL($sql);
$sql = "SELECT * FROM Persons ORDER BY Person_ID ASC";
$result = QuerySQL($sql)

Then with a while loop, we can fetch the data into a table. I hope the idea is clear to you.

• Nigel Ren: SO is more about you trying something and then asking specific questions when you have problems. There are plenty of examples/tutorials out there showing these things so do a bit of research and come back if you have a problem.
• Raj Shekhar Singh: I deeply appreciate your comment. I did try few tutorials on youtube and also searched on stackoverflow but could not draw the code according to my need thus I asked here so that if someone can understand my idea and explain me how to do it. Stackoverflow has always helped me.

Answer 1

Nigel Ren's comment is correct but since I had the same problem a couple of years ago here is the link I used at that time. It also has usage examples. Roll your own PDO class

Below you see my Database class that I created based on that example and some constants I use in that class as well as one simple usage example.

define("DB_USER", "yourUser");
define("DB_PASS", "yourPassword");
define("DB_HOST", "localhost");
define("DB_NAME", "yourDBName");
define("DB_CHARSET", "utf8mb4");
//create new database handler
$dbh = new Database();
//sample query
$dbh->query('SELECT var FROM table WHERE user_id = :id');
$dbh->bind(':id', $id );
$result = $dbh->resultsetAssoc(); // a two dimensional array is returned
foreach ($result as $values) {
 //do something with $values["var"]
}
class Database
{
 private $host = DB_HOST;
 private $dbname = DB_NAME;
 private $charset = DB_CHARSET;
 private $user = DB_USER;
 private $pass = DB_PASS; 
 private $dbh;
 private $error;
 private $stmt;
 public function __construct() {
 // Set DSN
 $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname . ';charset=' . $this->charset;
 // Set options
 $options = array(
 PDO::ATTR_PERSISTENT => true,
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 );
 // Create a new PDO instanace
 try{
 $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
 }
 catch(PDOException $e) { 
 $this->error = $e->getMessage(); 
 file_put_contents('PDOErrors.txt', $this->error, FILE_APPEND);
 die ("Database Connection Error");
 }
 } 
 public function query($query) {
 $this->stmt = $this->dbh->prepare($query); 
 }
 public function bind($param, $value, $type = null){
 //determine type as input for bindValue
 if (is_null($type)) {
 switch (true) {
 case is_int($value):
 $type = PDO::PARAM_INT;
 break;
 case is_bool($value):
 $type = PDO::PARAM_BOOL;
 break;
 case is_null($value):
 $type = PDO::PARAM_NULL;
 break;
 default:
 $type = PDO::PARAM_STR;
 }
 }
 // PDO method bindValue (PDOStatement::bindValue)
 $this->stmt->bindValue($param, $value, $type);
 }
 public function execute(){
 return $this->stmt->execute();
 }
// This is returned by resultset():
// Multidimemnsional array with column labeled values
// and numbered values
// Array
//(
// [0] => Array
// (
// [name] => pear
// [0] => pear
// [colour] => green
// [1] => green
// )
//
// [1] => Array
// (
// [name] => watermelon
// [0] => watermelon
// [colour] => pink
// [1] => pink
// )
//
//)
 public function resultset(){
 $this->execute();
 return $this->stmt->fetchAll();
 }
// This is returned by resultsetAssoc():
// Multidimemnsional array with column labeled values
// and NO numbered values
// Array
//(
// [0] => Array
// (
// [name] => pear
// [colour] => green
// )
//
// [1] => Array
// (
// [name] => watermelon
// [colour] => pink
// )
//
//)
 public function resultsetAssoc(){
 $this->execute();
 return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
 }
// With single():
// The array has only one dimension!!!
 public function single(){
 $this->execute();
 return $this->stmt->fetch();
 }
// With singleAssoc():
// The array has only one dimension!!!
 public function singleAssoc(){
 $this->execute();
 return $this->stmt->fetch(PDO::FETCH_ASSOC);
 }
 public function rowCount(){
 return $this->stmt->rowCount();
 }
 public function lastInsertId(){
 return $this->dbh->lastInsertId();
 }
 public function beginTransaction(){
 return $this->dbh->beginTransaction();
 }
 public function endTransaction(){
 return $this->dbh->commit();
 }
 public function inTransaction(){
 return $this->dbh->inTransaction();
 }
 public function cancelTransaction(){
 return $this->dbh->rollBack();
 }
 public function debugDumpParams(){
 return $this->stmt->debugDumpParams();
 }
// array is returned: [0] SQLSTATE, [1] some useless error code [2] error message
 public function errorInfo() {
 return $this->stmt->errorInfo();
 }
}

• Raj Shekhar Singh: Thanks a lot sir I will use this in my project and if something goes wrong I will share the code. Once again thanks a ton.
• rf1234: There are so many posts on the use of PDO... just google your issue whenever you run into a specific problem. If you like my answer please accept it.