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

Free Source Code – Twitter Database Server: db_lib.php

140dev Twitter Framework
Twitter Database Server
Download
Install
Code Architecture
MySQL Database Schema
Phirehose Library
140dev_config.php
db_config.php
db_lib.php
db_test.php
get_tweets.php
monitor_tweets.php
parse_tweets.php
Twitter Display

All of the code in the 140dev Twitter framework uses this script to read from and write to the MySQL database. It writes any errors to error_log.txt in the db directory. You should check this log file regularly, since Twitter frequently changes its data structures, causing failures on database inserts. The log file is also a great debugging aid when you are writing new code for this framework.

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

<?php
/**
* db_lib.php
* General purpose database library for use by all 140dev code
* 
* Latest copy of this code: http://140dev.com/free-twitter-api-source-code-library/
* @author Adam Green <140dev@gmail.com>
* @license GNU Public License
* @version BETA 0.30
*/
class db
{
  public $dbh;
  public $error;
  public $error_msg;    
  
  // 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)) { 
            
      // 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"');
    } else {
      // Log an error if the connection fails
      $this->error = true;
      $this->error_msg = 'Unable to connect to DB';
      $this->log_error('__construct','attempted connection to ' . $db_name);
    }
        
    date_default_timezone_set(TIME_ZONE);
  }
  
  // Call this after each DB request to test for and log errors
  // Supply the calling function name and query, so they can be logged
  private function error_test($function,$query) {
    
    // Record the last error state in the object, 
    // so code using objects of this class can read it
    if ($this->error_msg = mysqli_error($this->dbh)) {
        $this->log_error($function,$query);
        $this->error = true;
    } else {
        $this->error = false;
    }
    return $this->error;
  }
    
  // Write any errors into a text log
  // Include the date, calling script, function called, and query
  private function log_error($function,$query) {
    $fp = fopen('error_log.txt','a');
    fwrite($fp, date(DATE_RFC822) . ' | ' . 
      $_SERVER["SCRIPT_NAME"] . ' -> ' . $function . 
      ' | ' . $this->error_msg . ' | ' . $query . "\n");
    fclose($fp); 
  }
    
  // 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);
    $this->error_test('in_table',$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 );
    $this->error_test("select",$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);
    $this->error_test('insert',$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);
   $this->error_test('update',$query);
  }  
}  
?>

streaming_framework