PageRenderTime 49ms CodeModel.GetById 26ms RepoModel.GetById 1ms app.codeStats 0ms

/web/pierc_db.php

https://github.com/Aldarone/pierc
PHP | 254 lines | 191 code | 47 blank | 16 comment | 35 complexity | 9a3e652ec67d712655d3b1d12877fc51 MD5 | raw file
  1. <?php
  2. class db_class
  3. {
  4. protected $_conn;
  5. public function __construct( $server, $port, $database, $user, $password, $timezone)
  6. {
  7. if ($port) { $port = ":".$port; }
  8. $this->_conn = mysql_connect( $server.$port, $user, $password );
  9. if (!$this->_conn){ die ("Could not connect: " + mysql_error() ); }
  10. mysql_select_db( $database, $this->_conn );
  11. $this->timezone = $timezone;
  12. }
  13. public function __destruct( )
  14. {
  15. mysql_close( $this->_conn );
  16. }
  17. }
  18. class pierc_db extends db_class
  19. {
  20. protected function hashinate( $result )
  21. {
  22. $lines = array();
  23. $counter = 0;
  24. while( $row = mysql_fetch_assoc($result) )
  25. {
  26. if( isset( $row['time'] ) )
  27. {
  28. date_default_timezone_set('UTC');
  29. $dt = date_create( $row['time']);
  30. $dt->setTimezone( new DateTimeZone($this->timezone));
  31. $row['time'] = $dt->format("Y-m-d H:i:s");
  32. }
  33. $lines[$counter] = $row;
  34. $counter++;
  35. }
  36. return $lines;
  37. }
  38. public function get_last_n_lines( $channel, $n )
  39. {
  40. $channel = mysql_real_escape_string( $channel );
  41. $n = (int)$n;
  42. $query = "
  43. SELECT id, channel, name, time, message, type, hidden FROM main WHERE channel = '$channel' ORDER BY id DESC LIMIT $n;";
  44. $results = mysql_query( $query, $this->_conn);
  45. if (!$results){ print mysql_error(); return false; }
  46. if( mysql_num_rows($results) == 0 ) { return false; }
  47. return array_reverse($this->hashinate($results));
  48. }
  49. public function get_before( $channel, $id, $n )
  50. {
  51. $channel = mysql_real_escape_string( $channel );
  52. $n = (int)$n;
  53. $id = (int)$id;
  54. $query = "
  55. SELECT id, channel, name, time, message, type, hidden FROM main WHERE channel = '$channel' AND id < $id ORDER BY id DESC LIMIT $n;";
  56. $results = mysql_query( $query, $this->_conn);
  57. if (!$results){ print mysql_error(); return false; }
  58. if( mysql_num_rows($results) == 0 ) { return false; }
  59. return $this->hashinate($results);
  60. }
  61. public function get_after( $channel, $id, $n )
  62. {
  63. $channel = mysql_real_escape_string( $channel );
  64. $n = (int)$n;
  65. $id = (int)$id;
  66. $query = "
  67. SELECT id, channel, name, time, message, type, hidden FROM main WHERE channel = '$channel' AND id > $id ORDER BY time ASC, id DESC LIMIT $n;";
  68. $results = mysql_query( $query, $this->_conn);
  69. if (!$results){ print mysql_error(); return false; }
  70. if( mysql_num_rows($results) == 0 ) { return false; }
  71. return $this->hashinate($results);
  72. }
  73. public function get_lines_between_now_and_id( $channel, $id)
  74. {
  75. $channel = mysql_real_escape_string( $channel );
  76. $id = (int)$id;
  77. $query = "
  78. SELECT id, channel, name, time, message, type, hidden FROM main WHERE channel = '$channel' AND id > $id ORDER BY id DESC LIMIT 500";
  79. $results = mysql_query( $query, $this->_conn);
  80. if (!$results){ print mysql_error(); return false; }
  81. if( mysql_num_rows($results) == 0 ) { return false; }
  82. return array_reverse($this->hashinate($results));
  83. }
  84. // Returns the number of records in 'channel' with an ID below $id
  85. public function get_count( $channel, $id)
  86. {
  87. $channel = mysql_real_escape_string( $channel );
  88. $id = (int)$id;
  89. $query = "
  90. SELECT COUNT(*) as count FROM main
  91. WHERE channel = '$channel'
  92. AND id < $id;";
  93. $results = mysql_query( $query, $this->_conn);
  94. if (!$results){ print mysql_error(); return false; }
  95. if( mysql_num_rows($results) == 0 ) { return false; }
  96. $res = $this->hashinate($results);
  97. $count = $res[0]["count"];
  98. if ( $count < 0 )
  99. {
  100. return 0;
  101. }
  102. return $count;
  103. }
  104. public function get_context( $channel, $id, $n)
  105. {
  106. // Let's imagine that we have 800,000 records, divided
  107. // between two different channels, #hurf and #durf.
  108. // we want to select the $n (50) records surrounding
  109. // id-678809 in #durf. So, first we count the number
  110. // of records in # durf that are below id-678809.
  111. //
  112. // Remember: OFFSET is the number of records that MySQL
  113. // will skip when you do a SELECT statement -
  114. // So "SELECT * FROM main LIMIT 50 OFFSET 150 will select
  115. // rows 150-200.
  116. //
  117. // If we used the $count as an $offset, we'd have a conversation
  118. // _starting_ with id-678809 - but we want to capture the
  119. // conversation _surrounding_ id-678809, so we subtract
  120. // $n (50)/2, or 25.
  121. $channel = mysql_real_escape_string($channel);
  122. $id = (int)$id;
  123. $n = (int)$n;
  124. $count = $this->get_count( $channel, $id );
  125. $offset = $count - (int)($n/2);
  126. if( $offset < 0)
  127. {
  128. $offset = 0;
  129. }
  130. $query = "
  131. SELECT *
  132. FROM (SELECT * FROM main
  133. WHERE channel = '$channel'
  134. LIMIT $n OFFSET $offset) channel_table
  135. ORDER BY id DESC ;
  136. ";
  137. $results = mysql_query( $query, $this->_conn);
  138. if (!$results){ print mysql_error(); return false; }
  139. if( mysql_num_rows($results) == 0 ) { return false; }
  140. return array_reverse($this->hashinate($results));
  141. }
  142. public function get_search_results( $channel, $search, $n, $offset=0 )
  143. {
  144. $search = mysql_real_escape_string($search);
  145. $channel = mysql_real_escape_string($channel);
  146. $n = (int) $n;
  147. $offset = (int) $offset;
  148. $searchquery = " WHERE channel = '$channel' ";
  149. $searcharray = split("[ (%20)(%25)(%2520)|]", $search);
  150. foreach($searcharray as $searchterm )
  151. {
  152. $searchquery .= "AND (message LIKE '%".mysql_real_escape_string($searchterm)."%' OR name LIKE '%".mysql_real_escape_string($searchterm)."%' ) ";
  153. }
  154. $n = (int)$n;
  155. $query = "
  156. SELECT id, channel, name, time, message, type, hidden
  157. FROM main
  158. $searchquery ORDER BY id DESC LIMIT $n OFFSET $offset;";
  159. $results = mysql_query( $query, $this->_conn);
  160. if (!$results){ print mysql_error(); return false; }
  161. if( mysql_num_rows($results) == 0 ) { return false; }
  162. $results = array_reverse($this->hashinate($results));
  163. return $results;
  164. }
  165. public function get_tag( $channel, $tag, $n )
  166. {
  167. $tag = mysql_real_escape_string($tag);
  168. $channel = mysql_real_escape_string($channel);
  169. $n = (int)$n;
  170. $query = "
  171. SELECT id, channel, name, time, message, type, hidden
  172. FROM main
  173. WHERE message LIKE '".$tag.":%' ORDER BY id DESC LIMIT $n;";
  174. $results = mysql_query( $query, $this->_conn);
  175. if (!$results){ print mysql_error(); return false; }
  176. if( mysql_num_rows($results) == 0 ) { return false; }
  177. return array_reverse($this->hashinate($results));
  178. }
  179. public function get_lastseen( $channel, $user )
  180. {
  181. $user = mysql_real_escape_string($user);
  182. $channel = mysql_real_escape_string($channel);
  183. $query = "
  184. SELECT time
  185. FROM main
  186. WHERE name = '".$user."' ORDER BY id DESC LIMIT 1;";
  187. $results = mysql_query( $query, $this->_conn);
  188. if (!$results){ print mysql_error(); return false; }
  189. if( mysql_num_rows($results) == 0 ) { return false; }
  190. return $this->hashinate($results);
  191. }
  192. public function get_user( $channel, $user, $n )
  193. {
  194. $user = mysql_real_escape_string($user);
  195. $channel = mysql_real_escape_string($channel);
  196. $n = (int) $n;
  197. $query = "
  198. SELECT id, channel, name, time, message, type
  199. FROM main
  200. WHERE name = '".$user."' ORDER BY id DESC LIMIT ".$n.";";
  201. $results = mysql_query( $query, $this->_conn);
  202. if (!$results){ print mysql_error(); return false; }
  203. if( mysql_num_rows($results) == 0 ) { return false; }
  204. return $this->hashinate($results);
  205. }
  206. }
  207. ?>