Adam Green
Twitter API Consultant
adam@140dev.com
781-879-2960
@140dev

Simple PHP/MySQL database library source code: db_lib.php

by Adam Green on May 15, 2012

in Database Cache,Twitter Database Programming

There seems to be a good amount of interest in the new set of tutorials I’ve started writing, and most of the code I produce interacts with a MySQL database, so I’m going to post the code for my standard database library here. This makes it easy for me to link to this post multiple times, rather than include the source of this library in multiple posts. This is a simplified version of the library included in the 140dev Framework.

The login info for this library is kept in a separate script called db_config.php. For the sample code shown on this blog this configuration file will be kept in the same directory as the db_lib.php script. Security minded programmers will probably want to keep this in a different location on their server, preferably outside the web accessible directories.

db_config.php

1
2
3
4
5
6
7

<?php
// db_config.php
$db_host = 'localhost';
$db_user = 'ENTER USER NAME HERE';
$db_password = 'ENTER USER PASSWORD HERE'; 
$db_name = 'ENTER DATABASE NAME HERE'; 
?>

The actual library code is written as a PHP class. This allows the code to open a MySQL connection once, and then keep it open for the entire time the script using the library is running. The library contains simple functions for preparing data for insertion, running any SQL query, checking to see if a value already exists in a table, and table insertion and update functions.

db_lib.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

<?php
// db_lib.php

class db
{
  public $dbh;

  // Create a database connection for use by all functions in this class
  function __construct() {

    require_once('db_config.php');
    
    if($this->dbh = mysqli_connect($db_host, 
      $db_user, $db_password, $db_name)) { 
	} else {
	  exit('Unable to connect to DB');
    }
	// Set every possible option to utf-8
    mysqli_query($this->dbh, 'SET NAMES "utf8"');
    mysqli_query($this->dbh, 'SET CHARACTER SET "utf8"');
    mysqli_query($this->dbh, 'SET character_set_results = "utf8",' .
        'character_set_client = "utf8", character_set_connection = "utf8",' .
        'character_set_database = "utf8", character_set_server = "utf8"');
  }
  
  // Create a standard data format for insertion of PHP dates into MySQL
  public function date($php_date) {
    return date('Y-m-d H:i:s', strtotime($php_date));	
  }
  
  // All text added to the DB should be cleaned with mysqli_real_escape_string
  // to block attempted SQL insertion exploits
  public function escape($str) {
    return mysqli_real_escape_string($this->dbh,$str);
  }
    
  // Test to see if a specific field value is already in the DB
  // Return false if no, true if yes
  public function in_table($table,$where) {
    $query = 'SELECT * FROM ' . $table . 
      ' WHERE ' . $where;
    $result = mysqli_query($this->dbh,$query);
    return mysqli_num_rows($result) > 0;
  }

  // Perform a generic select and return a pointer to the result
  public function select($query) {
    $result = mysqli_query( $this->dbh, $query );
    return $result;
  }
    
  // Add a row to any table
  public function insert($table,$field_values) {
    $query = 'INSERT INTO ' . $table . ' SET ' . $field_values;
    mysqli_query($this->dbh,$query);
  }
  
  // Update any row that matches a WHERE clause
  public function update($table,$field_values,$where) {
    $query = 'UPDATE ' . $table . ' SET ' . $field_values . 
      ' WHERE ' . $where;
    mysqli_query($this->dbh,$query);
  } 
 
}  
?>

There will be practical examples of using this library throughout the tutorials coming up in the blog. Just to show the simplest example possible, here is a script that makes a database connection and then runs a “SHOW TABLES” MySQL query.

db_lib_demo.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php 
// db_lib_demo.php

// Create a database connection
require_once('db_lib.php');
$oDB = new db;

// Run a MySQL query
$query = "SHOW TABLES";
$result = $oDB->select($query);

// Retrieve the first row of results as an array
$row = mysqli_fetch_assoc($result);
print_r($row);

?>

This script can be run from the command line of an SSH or Telnet client, however you normally connect to your server.

# php db_lib_demo.php

1
2
3
4

 Array
(
    [Tables_in_140dev_tutorials] => rss_feed
)

Previous post:

Next post: