Build your own CMS with PHP and MySQL from scratch step by step Part-2

Show all tutorials in this series till now

Go back to previous tutorial

updated on: 14 JULY, 2015

In this part we will write one of two library classes: MysqlDatabase and Route. MysqlDatabase handles the mysql database queries and Route handles the routing.

MysqlDatabase class:

Location of mysql database file

Create a file mysql_database.php in folder todolist/libraries/core/database. In this file first we will check our security variable. The we will write a class named MysqlDatabase. This class has following functions:


  1. Set configuration for a connection
  2. Connection to a database
  3. Disconnect from database
  4. Quote data before putting in database. It is used to make sensitive data secure before putting in database. We will use it for email addresses. For other data, we will use other method.
  5. execute a prepare statement for insertion and deletion in database.
  6. Select a single row from database and return it
  7. Select a set of rows from database and return them.

Note: I am using only prepare statements for all database queries. Functions query() and prepare() do same work but in different manner. query() embeds variables in it directly and executes statements for database but prepare() first binds variables to the sql query. In other words, we make blocks in sql statements and prepare assign variables to those blocks and executes later. Now if a user fills data something like this: username = 'username' and password = 'something' OR A = A. The query function will authorize the user for all values for password because it is taking three parameters username, password and A ( A = A returns true and depresses password check). But prepare function will notice that we want only two parameters and will assign value 'something' OR A = A to password instead of creating one more parameter for A.

prepare function is also fast in performance than query function. So, I will highly recommend you to use prepare function over query as much as possible. prepare has one more advantage that it sends sql statement only once and after that it sends only variables. It is useful when you have to perform same action multiple times. It will reduce your bandwidth and also give you a high speed.

Security check:

We want that only root index.php file can open this file, so we will add this code before defining our class:

  1. <?php
  2. // security check
  3. if(!isset($security_check))
  4. {
  5. echo "This is restricted file";
  6. exit();
  7. }

It wil check if there is a variable with name $security_check or not. We are defining this variable only in index.php. If anyone access this file directly then $security_check will be absent and file will be closed.

Now define MysqlDatabase class:

  1. /**
  2. * class for mysql database
  3. */
  4. class MysqlDatabase
  5. {
  6. // defining secrete variables
  7. private $db_name= DATABASE_NAME;
  8. private $db_user= DATABASE_USER;
  9. private $db_pass= DATABASE_PASS;
  10. private $db_server= DATABASE_SERVER;
  11. // protected PDO connection
  12. protected $con;

Here I have defined four private and one protected variable. Private variables are our server config data and protected variable is our database connection.

Set private variables:

If we have multiple databases then we need to change the value of private variables. So, we are writing a function for it.

  1. /**
  2. * setting database configuration
  3. */
  4. function set_config($server,$db,$user,$pass)
  5. {
  6. $this->db_server=$server;
  7. $this->db_name=$db;
  8. $this->db_user=$user;
  9. $this->db_pass=$pass;
  10. }

Connect to mysql database:

Now we have to connect our $con variable with database. I am using PDO for it.

  1. /**
  2. * connecting with a mysql database
  3. */
  4. private function connect()
  5. {
  6. $info = 'mysql:host='.$this->db_server.';dbname='.$this->db_name;
  7. try{
  8. $this->con = new PDO($info, $this->db_user, $this->db_pass);//data server connection
  9. }
  10. catch(PDOException $Exception )
  11. {
  12. header('HTTP/1.1 500 Database Error');
  13. exit;
  14. }
  15. if (!$this->con)
  16. {
  17. die('Could not connect: ' . mysql_error());
  18. }
  19. }

In connect function we are opening a connection with mysql server using PDO. This function is private because I will call this function from this class only. I prefer PDO over mysql_connect and mysqli because of its overall performance. Mysqli is more fast than PDO, but it has a limited functions and specially only limited upto mysql. It is always good to put PDO statement in try catch block so that it can not display default errors which may contain your private data.

Disconnect from database:

Now we need one more function which will disconnect the connection. This function is also private.

  1. /**
  2. * disconnecting database connection
  3. */
  4. private function disconnect()
  5. {
  6. $this->con = null;
  7. }

Why do we need to disconnect database manually? It is because it will close the database connection just after using it. There will not be any vain connection. It will rise the performance by reducing the load of database server.

Quote data before placing in database:

Placing sensitive data in database is insecure. quote() function puts the quote around the parameter assign to it. It is helpful in reducing risks in sql injection.

  1. /**
  2. * quoting string
  3. * @param string $args
  4. * @return sting $args
  5. */
  6. function quote($arg)
  7. {
  8. $this->connect();
  9. $arg = $this->con->quote($arg);
  10. $this->disconnect();
  11. return $arg;
  12. }

Here we are first connection to database in line 8. In line 9, we are quoting the data. In line 10, connection is closed and in line 11, quoted data is returned.

Execute a prepare statement:

  1. /**
  2. * prepare statement for single fetch
  3. * @param string $sql
  4. * @param array $args
  5. * @return query $q
  6. */
  7. function prepare($sql,$args)
  8. {
  9. $this->connect();
  10. $q =$this->con->prepare($sql);
  11. $q ->execute($args);
  12. $this->disconnect();
  13. return $q;
  14. }

prepare() function takes two parameters. First is a SQL query and another is an array of values for binding with this query. This function is only for one way query like insertion and deletion in database. Just like quote() function, we are opening the database connection then preparing a database query. Then binding the values to query. After it we are disconnecting the database connection and finally we return the query. Why do we return query? Because after execution we want to verify if database transaction is successful or failed. If database transaction is failed then $q will be null.

Load data from database:

We will load data from database just like prepare() function. But only change will be that we will write one more line which will fetch data from database. Mysql has two functions fetch() and fetchAll() which fetch data from database. fetch() fetches only single row and fetchAll() can fetch multiple rows.

  1. /**
  2. * Return array of results
  3. * @param string $sql
  4. * @param array $args
  5. * @return query array $rows
  6. */
  7. function load_result($sql,$args)
  8. {
  9. $this->connect();
  10. $q = $this->con->prepare($sql);
  11. $q->execute($args);
  12. $rows = $q->fetchAll();
  13. $this->disconnect();
  14. return $rows;
  15. }
  16. /**
  17. * Return single result
  18. * @param string $sql
  19. * @param array $args
  20. * @return query $row
  21. */
  22. function load_single_result($sql,$args)
  23. {
  24. $this->connect();
  25. $q = $this->con->prepare($sql);
  26. $q->execute($args);
  27. $row = $q->fetch();
  28. $this->disconnect();
  29. return $row;
  30. }
  31. }
  32. ?>

load_single_result() returns only single row and load_result() returns multiple rows. What will happen if there is no row in database corresponding to query? Then these functions will return null (empty) value. And finally we are closing our class. Here I have closed PHP tag (?>). You can ignore it.

Final mysql_database.php:

The finally our class is:

  1. <?php
  2. /**
  3. * @author Harish Kumar
  4. * @copyright Find All Together
  5. * @link http://www.findalltogeher.com
  6. * @version 1.0
  7. * This is core file which manupulate database instructions
  8. * It is using PDO connection
  9. * MAll functions are using prepare statements
  10. */
  11. // security check
  12. if(!isset($security_check))
  13. {
  14. echo "This is restricted file";
  15. exit();
  16. }
  17. /**
  18. * class for mysql database
  19. */
  20. class MysqlDatabase
  21. {
  22. // defining secrete variables
  23. private $db_name= DATABASE_NAME;
  24. private $db_user= DATABASE_USER;
  25. private $db_pass= DATABASE_PASS;
  26. private $db_server= DATABASE_SERVER;
  27. // protected PDO connection
  28. protected $con;
  29. /**
  30. * setting database configuration
  31. */
  32. function set_config($server,$db,$user,$pass)
  33. {
  34. $this->db_server=$server;
  35. $this->db_name=$db;
  36. $this->db_user=$user;
  37. $this->db_pass=$pass;
  38. }
  39. /**
  40. * connecting with a mysql database
  41. */
  42. private function connect()
  43. {
  44. $info = 'mysql:host='.$this->db_server.';dbname='.$this->db_name;
  45. try{
  46. $this->con = new PDO($info, $this->db_user, $this->db_pass);//data server connection
  47. }
  48. catch(PDOException $Exception )
  49. {
  50. header('HTTP/1.1 500 Database Error');
  51. exit;
  52. }
  53. if (!$this->con)
  54. {
  55. die('Could not connect: ' . mysql_error());
  56. }
  57. }
  58. /**
  59. * disconnecting database connection
  60. */
  61. private function disconnect()
  62. {
  63. $this->con = null;
  64. }
  65. /**
  66. * quoting string
  67. * @param string $args
  68. * @return sting $args
  69. */
  70. function quote($arg)
  71. {
  72. $this->connect();
  73. $arg = $this->con->quote($arg);
  74. $this->disconnect();
  75. return $arg;
  76. }
  77. /**
  78. * prepare statement for single fetch
  79. * @param string $sql
  80. * @param array $args
  81. * @return query $q
  82. */
  83. function prepare($sql,$args)
  84. {
  85. $this->connect();
  86. $q =$this->con->prepare($sql);
  87. $q ->execute($args);
  88. $this->disconnect();
  89. return $q;
  90. }
  91. /**
  92. * Return array of results
  93. * @param string $sql
  94. * @param array $args
  95. * @return query array $rows
  96. */
  97. function load_result($sql,$args)
  98. {
  99. $this->connect();
  100. $q = $this->con->prepare($sql);
  101. $q->execute($args);
  102. $rows = $q->fetchAll();
  103. $this->disconnect();
  104. return $rows;
  105. }
  106. /**
  107. * Return single result
  108. * @param string $sql
  109. * @param array $args
  110. * @return query $row
  111. */
  112. function load_single_result($sql,$args)
  113. {
  114. $this->connect();
  115. $q = $this->con->prepare($sql);
  116. $q->execute($args);
  117. $row = $q->fetch();
  118. $this->disconnect();
  119. return $row;
  120. }
  121. }
  122. ?>

NOTE:

  • It is best practice to check if class is defined already or not. I am not using it here but you must use it in your professional projects.
  • I have used mysql here but you can also make similar functions in any sql language. Important thing is that rest of your program will not affect by switching language. You have to just switch the name of your file. It is important when you are using more than one type of database in single project or want to change your database type after some time (or any time). For example, if you want to connect with postgresql then you have to write functions with same names and functional activities in postgresql and then you have to change your file name. Where? We will see it later when we use this file in Base.php.

In next tutorial, we will make another library file. Next part



About Harish Kumar

Harish is an interested person in the field of web development and blogging. He works for the need of young web developers in learning various languages, latest technologies and other essential tips and tricks. If you need some help or you have some suggestion then you email him at harish@findalltogether.com without any hesitation. You can also suggest/demand for articles of your own choice.

Related Articles

In this part of free tutorial series, I am telling you about syntax, variables, data types, comments, constants and o...
Introduction to PHP
In this part of free step by step tutorial series I will teach you about for loop, while loop and do while loop of PH...
loops in PHP
In this part of free step by step tutorial series I will teach you about if, if else, nested and switch statements of...
Conditional statements in php

Login or Sign up to leave comment.