Database controler for MySQL

When start a dynamical web from zero and pretends no use a framework, is good idea think how will are the interaction between the server web and the database management system.

Always you have the option to open and close the connections to bd when you need between the code, but my experience tell me that is more good that centralize all operations between the server and the DBMS in only one function or class because, if you need make a change, you not need modify all the files. Only need change one.

In most of projects that I participate, the formulates is Apache + MySQL Server and one of my proposals is that I do detailed more below.

The first part of the file, define the constants that indicate the parameters that use the system for connect to MySQL Server.

1
2
3
4
5
6
<?php
  define('DB_USER','User database');
  define('DB_PASS','Password database');
  define('DB_NAME','Name of the database');
  define('DB_HOST','IP where the database is hosted');
?>

The function CleanSQL clean the values to be send. This function is useful for clean values collected for forms, for avoid SQL Injection attacks.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function CleanSQL( $value_to_clean, $use_trim = 1, $use_urldecode = 1 ){
  $value_to_clean = strip_tags( $value_to_clean );
 
  $conn = @mysql_connect( DB_HOST, DB_USER, DB_PASS );
 
  if( get_magic_quotes_gpc() ) $value_to_clean = stripslashes( $value_to_clean );
    $value_to_clean = mysql_real_escape_string( $value_to_clean, $conn );
 
    @mysql_close();
 
    if ( $use_urldecode ){
      $value_to_clean = urldecode( $value_to_clean );
    }
    if ( $use_trim ){
      $value_to_clean = trim( $value_to_clean );
    }
    return $value_to_clean;
}

So here are the 2 functions that manage the connection to db. ExecuteSQL and InsertSQL, run the sentences SQL that to be send. The difference between they is that the function ExecuteSQL return only true (1) or false (0) if the sentence sql are execute correctly and the function InsertSQL return false (0) if an error are occurred when execute sql sentence or, in case of success, return the id of the row inserted in the mysql.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
/*
* Execute a sentence SQL in a DBMS configured
* Attention: $sql can not be null
*
* Return result of sentence in resource format.
* More info in http://php.net/manual/en/function.mysql-query.php
* If any error occurred to connect to BD, return -1;
*/
function ExecuteSQL( $sql ){
  $result_sql = -1;
  $conn = @mysql_connect( DB_HOST, DB_USER,DB_PASS );
  if ( ! $conn ){
    $result_sql = -1;
  } else {
    if ( ! @mysql_select_db( DB_NAME ) ){
      $result_sql = -1;
    } else {
      $result_sql = mysql_query( $sql );
    }
  }
  @mysql_close();
  return $result_sql;
}
 
/*
* Execute a sentence SQL insert in a DBMS configured
* Attention: $sql can not be null
*
* Return the id of the sentence inserted
* If any error occurred in insert sentence, return 0;
* If any error occurred to connect to BD, return -1;
*/
function InsertSQL( $sql ){
 
  $id = 0;
  $conn = @mysql_connect( DB_HOST, DB_USER,DB_PASS );
  if ( ! $conn ){
    $id = -1;
  } else {
    if ( ! @mysql_select_db( DB_NAME ) ){
      $id = -1;
    } else {
      $result_sql = mysql_query($sql);
      if ( $result_sql ){
        $id = mysql_insert_id();
      }
    }
  }
  @mysql_close();
  return $id;
}
?>

You must be mind that, this two functions not check if the sentence sql is valid or content errors. Is important before use this functions, check if the sql sentences is valid and clean parameters using CleanSQL function to prevent errors.

So continuous, I detail a example to how do you must use this functions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
include('controler-bd.php'); //Is the sum of all code described before.
 
$mobile = CleanSql( $_REQUEST["mobile"] );
 
$sql = "SELECT * FROM user WHERE phone = '$mobile' LIMIT 1";
$resUser = ExecuteSQL($sql);
 
if ( $resUser != 0 && ! mysql_num_rows( $resUser ) ){
  $sql = "INSERT INTO user (phone) VALUES ('$mobile')";
  $idres = InsertSQL( $sql );
  if($idres != 0) {
    echo "Insertion of sql is finished correctly with id $idres";
  } else {
    echo 'Error to execute sql insert.';
  }
} else {
  echo 'Error to execute sql sentence.';
}
?>

Well, that’s all. It is not a great function but it help to start little and simple projects that no required for big developments.

Download controler-bd.php

Leave a Reply

Your email address will not be published. Required fields are marked *