This php script will help you to do almost all database interactions and operations with your website.

With this script your php will get connected to your database then you can execute any MySQL statements by calling db_execute_mysql() with your MySQL statment as a parameter.

You can get any data from your database based on your prepared MySQL statment using db_get_row() and db_get_multiple_rows() functions. we will discuss more about each functions later, but now let’s see the program

<?php

/**
 * This method is used to connect 
   this web app to database.
 * It returns an object  of PDO.
 * The object holds database connection.
 */
function db_db_connection() {
   /*
    * ::DATABASE configuration START::
    * Fill the following database details
      based your database configuration. 
    * If your database doesn't 
      have password leave $db_password empty. 
    */
   $db_name = "lab";
   $db_user_name = "root";
   $db_password = "root";
   $server_name = "localhost";
   /* ::DATABASE configuration END:: */

   try {
      $connection = new PDO("mysql:host=$server_name;dbname=$db_name", $db_user_name, $db_password);
      $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      return $connection;
   } catch (PDOException $e) {
      echo "Database Connection Error : " . $e->getMessage() . "<br>";
   }
}




/**
 * This method is used to Execute mySQL Commands.
 * It accepts mySQL command as a parameter.
 * Then it Executes the command.
 */
function db_execute_mysql($mysql_cmd) {
   try {
      db_db_connection()->exec($mysql_cmd);
   } catch (PDOException $e) {
      echo "MYSQL Execution Error : " . $e->getMessage() . "<br>";
   }
}




/**
 * This method is used to extract data of 
   selected single row from a database table.
 * It returns one dimensional associative
   array of that row.
 * It accepts  mysql command as a parameter.
 */
function db_get_row($mysql_cmnd) {
   try {
      $prepared_statment = db_db_connection()->prepare($mysql_cmnd);
      $prepared_statment->execute(array());
      $fetched_array = $prepared_statment->fetch();
      return $fetched_array;
   } catch (PDOException $e) {
      echo "Data Extraction Error : " . $e->getMessage() . "<br>";
   }
}




/**
 * This method is used to extract data of
   selected multiple rows form a database table.
 * It returns two dimensional associative 
   array of these rows.
 * It accepts  mysql command as a parameter.
 */
function db_get_multiple_rows($mysql_cmnd) {
   $prepared_statment = db_db_connection()->prepare($mysql_cmnd);
   $prepared_statment->execute(array());
   $a = 0;
   while ($fetched_array = $prepared_statment->fetch()) {
      $data_array[$a] = $fetched_array;
      $a++;
   }
   return $data_array;
}



Copy and save it as “include.php” in your website project root folder then you should include it in other files of your project that need to interact with database.

Now let see each methods purpose and functionality.

db_db_connection ()

Function used to connect your website application to database server. It returns PDO object that holds connection parameters to database. it is not necessary to call this function in other parts of your php code, because you can use the other three functions for almost all interactions to database. this function is called three times at three different functions, which are db_get_multiple_rows(), db_get_row() and db_execute_mysql(). because with out database connection these functions will not work.

In db_db_connection() there are four database configuration variables that hold your database configuration. so you must configure your database name, username, password and server name based on your database configuration. if there is any error connection to database this function prints error massage start by Database connection error.

db_execute_mysql($mysql_cmd)

This function executes any MySQL command passed to it with parameter. it accepts MySQL command as a parameter.

So if there is any MySQL command we want to execute, we can execute by calling this function with database prepared statment as a parameter.

db_get_row($mysql_cmd)

This function is used to extract (get) selected data or record from database. it accepts prepared database statment as a parameter then it returns an associative array that holds the details of the selected record.

NB. Use this function only if your selected record is one.

db_get_multiple_rows($mysql_cmd)

This function accepts prepared database statment to select multiple records from database then it returns those records and their respective details to two dimensional associative array.

We can use this function to get multiple records from database based on our database prepared statment.

To understand how this php script functions work let’s see an example that uses this php script as database interaction and operation module.

Here is the php code for our example

<?php

require './include.php';

//Deleting users table if it exists.
$delete_users_table = "DROP TABLE IF EXISTS `users`;";
db_execute_mysql($delete_users_table);


// Creating users table 
$create_users_table = "CREATE TABLE users(id INT(6) AUTO_INCREMENT, full_name VARCHAR(60), username VARCHAR(60), password VARCHAR(60), phone_number VARCHAR(60), PRIMARY KEY(id))";
db_execute_mysql($create_users_table);


// Inserting user into users table.
$register_user1 = "INSERT INTO users(full_name, username, password, phone_number) VALUES('Thomas Androsen', 'thomas128', '3#&sjbvWX3', '+976464323332')";
db_execute_mysql($register_user1);


// Inserting user into users table.
$register_user2 = "INSERT INTO users(full_name, username, password, phone_number) VALUES('Jhon Patrik', 'jhon75', '%exg8496HDr', '+948167963245')";
db_execute_mysql($register_user2);


// MySQL command to select single user with certain criteria. 
$select_user = "SELECT* FROM users WHERE id=2";
// Getting one dimensional associative array that holds user details.
$User = db_get_row($select_user);

// Displaying user details 
echo '<br>== Single user ==';
echo '<br>full name: ' . $User["full_name"];
echo '<br>username: ' . $User["username"];
echo '<br>phone number: ' . $User["phone_number"];


// MySQL command to select all users.
$select_users = "SELECT* FROM users";

// Getting two dimensional associative array that holds all users and their details.
$Users = db_get_multiple_rows($select_users);

// Displaying all users using while loop.
echo '<br><br><br>== All Users ==';
$i = 0;
while (!empty($Users[$i]["id"])) {
   echo '<br>full name: ' . $Users[$i]["full_name"];
   echo '<br>username: ' . $Users[$i]["username"];
   echo '<br>phone number: ' . $Users[$i]["phone_number"];
   echo '<br>';
   $i++;
}

As you see this file includes the “include.php” at the beginning of it’s php script. then it uses all functions to connect with database, to execute MySQL and to get data from the database.

First it created database table called users consists of full name, username, password and phone number. Then it inserts two users to the table. we used db_execute_mysql() function to execute these operations. here are the registered users as you see below

After users are registered to the table, It selected one record and multiple records from that table using db_get_row() and db_get_multiple_rows() functions respectively.

Finally the selected informations are displayed as you can see below

Conclusion

Instead of writing all steps to interact with database by calling php built-in PDO or MYSQL function so many time at different parts of your project using this php script and its functions can be better way to make your project easier and simple to develop and use. if you have any question or get error just comment below and we will help as soon as possible.

PHP Program To Connect With Database, Execute MySQL And Get Data From Database