22 August 2013

PHP Database Class MySQLi

Almost in every project I need a database class behind my BLL classes. I keep my SQL queries in BLL classes and use the database class to run the queries. This is kind of engine used by all classes in common. I will explain how to implement and how to use step by step in comments code.

Note that this class does not use the deprecated MySQL library, it uses MySQLi library. Here is the complete PHP Database Class source code;

First php is the configuration file for use in database class.
I call it dbconfig.php
<?php
define("DB_SERVER", "localhost");
define("DB_USER", "db_username");
define("DB_PASS", "db_user_password");
define("DB_NAME", "db_scheme_name");
?>
I think it is better to keep configuration data in a seperate php file, because if change the database name, username or password, i just change the dbconfig.php file.

MySQLi Database Class
<?php

// My database Class called myDBC
class myDBC {

// our mysqli object instance
public $mysqli = null;

// Class constructor override
public function __construct() {
 
include_once "dbconfig.php";        
       
$this->mysqli = 
   new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

if ($this->mysqli->connect_errno) {
    echo "Error MySQLi: ("&nbsp. $this->mysqli->connect_errno 
    . ") " . $this->mysqli->connect_error;
    exit();
 }
   $this->mysqli->set_charset("utf8"); 
}

// Class deconstructor override
public function __destruct() {
   $this->CloseDB();
 }

// runs a sql query
    public function runQuery($qry) {
        $result = $this->mysqli->query($qry);
        return $result;
    }

// runs multiple sql queres
    public function runMultipleQueries($qry) {
        $result = $this->mysqli->multi_query($qry);
        return $result;
    }

// Close database connection
    public function CloseDB() {
        $this->mysqli->close();
    }

// Escape the string get ready to insert or update
    public function clearText($text) {
        $text = trim($text);
        return $this->mysqli->real_escape_string($text);
    }

// Get the last insert id 
    public function lastInsertID() {
        return $this->mysqli->insert_id;
    }

// Gets the total count and returns integer
public function totalCount($fieldname, $tablename, $where = "") 
{
$q = "SELECT count(".$fieldname.") FROM " 
. $tablename . " " . $where;
        
$result = $this->mysqli->query($q);
$count = 0;
if ($result) {
    while ($row = mysqli_fetch_array($result)) {
    $count = $row[0];
   }
  }
  return $count;
}

}

?>
Other Descriptions
Because I always use UTF-8 encoding I set my database engine connection to utf8 too.
   $this->mysqli->set_charset("utf8");

Actually you dont need to close the connection, as the connection closes it self after the query executions completted. But sometimes I run lots of queries in same session and I just close it.
public function __destruct() {
   $this->CloseDB();
 }

Usage Examples


SQL Insert Example
include "mydbclass.php";

$mydb = new myDBC();

$firstname = $mydb->clearText($firstname);
$lastname  = $mydb->clearText($lastname);

$sql = "INSERT INTO users (usr_firstname,usr_lastname) 
VALUES ('$firstname','$lastname'); ";

$mydb->runQuery($sql);


SQL SELECT Example
include "mydbclass.php";

$mydb = new myDBC();

$id = $mydb->clearText($id);
$sql = "SELECT * FROM users WHERE usr_id='$id' LIMIT 1;";

$result = $mydb->runQuery($sql);

if(isset($result)) 
{
  $row = mysqli_fetch_array($result);
  $firstname = $row['usr_firstname']; 
  $lastname = $row['usr_lastname']; 
}


18 August 2013

PHP Get Server OS Name

The operating system name where the php running on can be read by several ways;
echo PHP_OS;

// output: CentOS

But if we want to get more detailed information about the OS, we may use php_uname
 
echo php_uname('s');

// output: Linux 2.6.18-348.6.1.el5 x86_64

php_uname gives information including; OS name, OS version, if it is 32bit or 64bit.

17 August 2013

PHP Check If Form Submitted

There are several ways to check whether a form submitted or not.

if(isset($_POST))
{
// do work
}

if (!empty($_POST))
{
// do work
}
 

But it is much better to check the server parameter. There are pretty much information stored in $_SERVER.
if ($_SERVER["REQUEST_METHOD"] == "POST") {
{
// now we are pretty sure for the post action 
}
 
 


Request method is just more rock solid way to check is a form submitted or not.

15 August 2013

PHP Get Referrer Url

in Php $_SERVER array consists of various data in it. Such as;
  • SERVER_NAME
  • REQUEST_METHOD
  • QUERY_STRING
  • HTTP_REFERER
Where the  HTTP_REFERER is the page address which the user agent referred.
Note that it is not guaranteed that this referrer set.
If the visitor comes from the bookmark, it will not be set.

So if it is set, we can get the url of the referrer by ;

$url="";
if(isset($_SERVER["HTTP_REFERER"]))
    $url = $_SERVER["HTTP_REFERER"];


Why we need this referrer url ?
Well I use this referrer url to redirect user to a certain page after a certain job done.
For example, log in user, than redirect to user to his/her profile page.

Example;

$url="";
if(isset($_SERVER["HTTP_REFERER"]))
    $url = $_SERVER["HTTP_REFERER"];

// ... do some job here ...

Header("Location: ".$url);
exit();

This may helpful when a user tries to access a log in required content, after log in successful he/she will be redirected to the previous page and continue whatever he/she will do on that page.

Note that, it is not always a good idea to trust this referrer url, referrer url should be stored in SESSION or cookie will be more secure.

PHP Get Page Parameter Safe $_GET

Most of us have to use the $_GET in PHP to get the desired content id and print the contents on page. But we cannot trust the incoming paremeter directly from the url.
We should check the parameter and validate it.

If it is set
Check if the parameter is set or not
if ( isset($_GET['id']) )
{
 // ok it is set.
}

Now I also check if it is empty or not;

If it is not empty

if ( isset($_GET['id']) && !empty($_GET['id']) )
{
 // ok it is set and not empty
}

If you know the parameter must be integer, than lets check
If it is a valid number. 

if ( isset($_GET['id']) && !empty($_GET['id']) && is_numeric($_GET['id']) )
{
 // ok it is set, not empty, and a number, get it;
$id = trim($_GET['id']);
}

Finally we get our parameter safely in PHP.

PHP Get Current URL

I need to get the current url for some purposes, such as;
  • Check the url if it is used to be. This is required to prevent dublicated content from different urls in your website.
  • Generate social bookmark and like data-href links
It is easy to get the current url in PHP;

<?php 

echo "http://".$_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"];

?>


This will give you the exact url of the current page.