/** * Tests the get_custom_forms method * * @test */ public function testGetCustomForms() { // Database instance for the test $db = new Database(); // The record count should be the same since get_custom_forms() has no predicates $this->assertEquals($db->count_records('form'), customforms::get_custom_forms()->count()); }
public static function get_visitor_count($start, $end, $unique = false) { $db = new Database(); if (!$unique) { if ($start) { $count = $db->where(array('when_log >' => $start, 'when_log <' => $end))->count_records('statistics'); } else { $count = $db->count_records('statistics'); } return $count; } else { if ($start) { $count = $db->select('COUNT(DISTINCT ip) as count')->where(array('when_log >' => $start, 'when_log <' => $end))->from('statistics')->get()->result_array(); } else { $count = $db->select('COUNT(DISTINCT ip) as count')->from('statistics')->get()->result_array(); } return $count[0]->count; } }
/** * Demonstrates the features of the Database library. * * Table Structure: * CREATE TABLE `pages` ( * `id` mediumint( 9 ) NOT NULL AUTO_INCREMENT , * `page_name` varchar( 100 ) NOT NULL , * `title` varchar( 255 ) NOT NULL , * `content` longtext NOT NULL , * `menu` tinyint( 1 ) NOT NULL default '0', * `filename` varchar( 255 ) NOT NULL , * `order` mediumint( 9 ) NOT NULL , * `date` int( 11 ) NOT NULL , * `child_of` mediumint( 9 ) NOT NULL default '0', * PRIMARY KEY ( `id` ) , * UNIQUE KEY `filename` ( `filename` ) * ) ENGINE = MYISAM DEFAULT CHARSET = utf8 PACK_KEYS =0; * */ function database() { $db = new Database(); $table = 'pages'; echo 'Does the ' . $table . ' table exist? '; if ($db->table_exists($table)) { echo '<p>YES! Lets do some work =)</p>'; $query = $db->select('DISTINCT pages.*')->from($table)->get(); echo $db->last_query(); echo '<h3>Iterate through the result:</h3>'; foreach ($query as $item) { echo '<p>' . $item->title . '</p>'; } echo '<h3>Numrows using count(): ' . count($query) . '</h3>'; echo 'Table Listing:<pre>' . print_r($db->list_tables(), TRUE) . '</pre>'; echo '<h3>Try Query Binding with objects:</h3>'; $sql = 'SELECT * FROM ' . $table . ' WHERE id = ?'; $query = $db->query($sql, array(1)); echo '<p>' . $db->last_query() . '</p>'; $query->result(TRUE); foreach ($query as $item) { echo '<pre>' . print_r($item, true) . '</pre>'; } echo '<h3>Try Query Binding with arrays (returns both associative and numeric because I pass MYSQL_BOTH to result():</h3>'; $sql = 'SELECT * FROM ' . $table . ' WHERE id = ?'; $query = $db->query($sql, array(1)); echo '<p>' . $db->last_query() . '</p>'; $query->result(FALSE, MYSQL_BOTH); foreach ($query as $item) { echo '<pre>' . print_r($item, true) . '</pre>'; } echo '<h3>Look, we can also manually advance the result pointer!</h3>'; $query = $db->select('title')->from($table)->get(); echo 'First:<pre>' . print_r($query->current(), true) . '</pre><br />'; $query->next(); echo 'Second:<pre>' . print_r($query->current(), true) . '</pre><br />'; $query->next(); echo 'Third:<pre>' . print_r($query->current(), true) . '</pre>'; echo '<h3>And we can reset it to the beginning:</h3>'; $query->rewind(); echo 'Rewound:<pre>' . print_r($query->current(), true) . '</pre>'; echo '<p>Number of rows using count_records(): ' . $db->count_records('pages') . '</p>'; } else { echo 'NO! The ' . $table . ' table doesn\'t exist, so we can\'t continue =( '; } echo "<br/><br/>\n"; echo 'done in {execution_time} seconds'; }
/** * Returns true if a spatial reference system is recognisable as a notation * or EPSG code. */ public static function is_valid_system($system) { $db = new Database(); if (is_numeric($system)) { $found = $db->count_records('spatial_ref_sys', array('auth_srid' => $system)); } else { $found = array_key_exists(strtolower($system), self::system_metadata()); } return $found > 0; }
/** * Build a search query with relevancy * Stop word control included */ public function index($page = 1) { $this->template->content = new View('search'); $search_query = ""; $keyword_string = ""; $where_string = ""; $plus = ""; $or = ""; $search_info = ""; $html = ""; $pagination = ""; // Stop words that we won't search for // Add words as needed!! $stop_words = array('the', 'and', 'a', 'to', 'of', 'in', 'i', 'is', 'that', 'it', 'on', 'you', 'this', 'for', 'but', 'with', 'are', 'have', 'be', 'at', 'or', 'as', 'was', 'so', 'if', 'out', 'not' ); if ($_GET) { /** * NOTES: 15/10/2010 - Emmanuel Kala <*****@*****.**> * * The search string undergoes a 3-phase sanitization process. This is not optimal * but it works for now. The Kohana provided XSS cleaning mechanism does not expel * content contained in between HTML tags this the "bruteforce" input sanitization. * * However, XSS is attempted using Javascript tags, Kohana's routing mechanism strips * the "<script>" tags from the URL variables and passes inline text as part of the URL * variable - This has to be fixed */ // Phase 1 - Fetch the search string and perform initial sanitization $keyword_raw = (isset($_GET['k']))? mysql_real_escape_string($_GET['k']) : ""; // Phase 2 - Strip the search string of any HTML and PHP tags that may be present for additional safety $keyword_raw = strip_tags($keyword_raw); // Phase 3 - Apply Kohana's XSS cleaning mechanism $keyword_raw = $this->input->xss_clean($keyword_raw); } else { $keyword_raw = ""; } $keywords = explode(' ', $keyword_raw); if (is_array($keywords) && !empty($keywords)) { $match = "MATCH(incident_title,incident_description) AGAINST(\"*D+1:2,2:1 $keyword_raw\" IN BOOLEAN MODE)"; $keyword_string = $match; $where_string = $match.' AND incident_active = 1'; $search_query = "SELECT *, (".$keyword_string.") AS relevance FROM ".$this->table_prefix."incident". " WHERE (".$where_string.") ORDER BY relevance DESC LIMIT "; } if (!empty($search_query)) { // Pagination $slave_config = Kohana::config('database.slave'); $db = new Database($slave_config); $pagination = new Pagination(array( 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $db->count_records('incident',$where_string) )); $query = $db->query($search_query . $pagination->sql_offset . ",". (int)Kohana::config('settings.items_per_page')); // Results Bar if ($pagination->total_items != 0) { $search_info .= "<div class=\"search_info\">"; $search_info .= Kohana::lang('ui_admin.showing_results').' '. ( $pagination->sql_offset + 1 ).' '.Kohana::lang('ui_admin.to').' '. ( (int) Kohana::config('settings.items_per_page') + $pagination->sql_offset ) .' '.Kohana::lang('ui_admin.of').' '. $pagination->total_items .' '.Kohana::lang('ui_admin.searching_for').'<strong>'. $keyword_raw . "</strong>"; $search_info .= "</div>"; } else { $search_info .= "<div class=\"search_info\">0 ".Kohana::lang('ui_admin.results')."</div>"; $html .= "<div class=\"search_result\">"; $html .= "<h3>".Kohana::lang('ui_admin.your_search_for')."<strong> ".$keyword_raw."</strong> ".Kohana::lang('ui_admin.match_no_documents')."</h3>"; $html .= "</div>"; $pagination = ""; } foreach ($query as $search) { $incident_id = $search->id; $incident_title = $search->incident_title; $highlight_title = ""; $incident_title_arr = explode(' ', $incident_title); foreach($incident_title_arr as $value) { if (in_array(strtolower($value),$keywords) && !in_array(strtolower($value),$stop_words)) { $highlight_title .= "<span class=\"search_highlight\">" . $value . "</span> "; } else { $highlight_title .= $value . " "; } } $incident_description = $search->incident_description; // Remove any markup, otherwise trimming below will mess things up $incident_description = strip_tags($incident_description); // Trim to 180 characters without cutting words if ((strlen($incident_description) > 180) && (strlen($incident_description) > 1)) { $whitespaceposition = strpos($incident_description," ",175)-1; $incident_description = substr($incident_description, 0, $whitespaceposition); } $highlight_description = ""; $incident_description_arr = explode(' ', $incident_description); foreach($incident_description_arr as $value) { if (in_array(strtolower($value),$keywords) && !in_array(strtolower($value),$stop_words)) { $highlight_description .= "<span class=\"search_highlight\">" . $value . "</span> "; } else { $highlight_description .= $value . " "; } } $incident_date = date('D M j Y g:i:s a', strtotime($search->incident_date)); $html .= "<div class=\"search_result\">"; $html .= "<h3><a href=\"" . url::base() . "reports/view/" . $incident_id . "\">" . $highlight_title . "</a></h3>"; $html .= $highlight_description . " ..."; $html .= "<div class=\"search_date\">" . $incident_date . " | ".Kohana::lang('ui_admin.relevance').": <strong>+" . $search->relevance . "</strong></div>"; $html .= "</div>"; } } else { // Results Bar $search_info .= "<div class=\"search_info\">0 ".Kohana::lang('ui_admin.results')."</div>"; $html .= "<div class=\"search_result\">"; $html .= "<h3>".Kohana::lang('ui_admin.your_search_for')."<strong>".$keyword_raw."</strong> ".Kohana::lang('ui_admin.match_no_documents')."</h3>"; $html .= "</div>"; } $html .= $pagination; $this->template->content->search_info = $search_info; $this->template->content->search_results = $html; // Rebuild Header Block $this->template->header->header_block = $this->themes->header_block(); }
public function index() { $apiurl = "http://tasukeai.heroku.com/all.xml"; #$apiurl = "http://localhost/message.xml"; $messages = simplexml_load_file($apiurl); foreach ($messages as $message) { $title = ""; $lat = ""; $active = 1; $long = ""; $matches = array(); if (strcmp($message->title["nil"], "true") != 0) { $title = (string) $message->title; } else { if (preg_match("/\\s*\\[ボランティア名称\\]\\s*\n([^\n]+)\n/", $message->body, $matches)) { $title = $matches[1]; } else { if (preg_match("/\\s*\\[主催\\]\\s*([^\n]+)\n/", $message->body, $matches)) { $title = $matches[1]; } else { if (preg_match("/\\s*\\[タイトル\\]\\s*([^\n]+)\n/", $message->body, $matches)) { $title = $matches[1]; } else { $title = "無題"; $active = 0; } } } } if (strcmp($message->latitude["nil"], "true") != 0 && strcmp($message->longitude["nil"], "true") != 0) { $lat = (double) $message->latitude; $long = (double) $message->longitude; } else { if (preg_match("/\\s*\\[緯度経度\\]\\s*\n([^,]+),([^\n]+)/", $message->body, $matches)) { $lat = $matches[1]; $long = $matches[2]; } } $link = $this->input->xss_clean($message->link); $where_string = "media_link = '" . $link . "'"; $db = new Database(); $count = $db->count_records('media', $where_string); if ($count > 0) { if (strcmp($message->{"valid-f"}, "false") == 0) { $search_query = "SELECT incident_id FROM media" . " WHERE (" . $where_string . ")"; $query = $db->query($search_query); ORM::factory('Incident')->where('id', $query[0]->incident_id)->delete_all(); ORM::factory('Media')->where('incident_id', $query[0]->incident_id)->delete_all(); } continue; } if (strcmp($message->{"valid-f"}, "true") != 0) { continue; } $incident = new Incident_Model(); // STEP 1: SAVE LOCATION if (isset($lat) && isset($long)) { $location = new Location_Model(""); $location->location_name = (string) $message->address; $location->latitude = $lat; $location->longitude = $long; $location->location_date = date("Y-m-d H:i:s", time()); $location->save(); $incident->location_id = $location->id; } $incident->incident_title = $title; $incident->incident_description = (string) $message->body; $incident->incident_date = date("Y-m-d H:i:s", strtotime($message->{"created-at"})); $incident->incident_dateadd = date("Y-m-d H:i:s", time()); $incident->incident_mode = 1; $incident->incident_active = $active; $incident->incident_verified = 1; $incident->incident_source = 3; $incident->incident_information = 1; //Save $incident->save(); $news = new Media_Model(); $news->incident_id = $incident->id; if (isset($location)) { $news->location_id = $location->id; } $news->media_type = 4; // News $news->media_link = $link; $news->media_date = date("Y-m-d H:i:s", strtotime($message->{"created-at"})); $news->save(); $incident_category = new Incident_Category_Model(); $incident_category->incident_id = $incident->id; if (strcmp($message->target, "2") == 0) { $incident_category->category_id = 9; //救援物資 } else { $incident_category->category_id = 13; //求む } $incident_category->save(); } $this->template->content = new View('tasukeaiimport/main'); }
/** * Validates that a value is unique across a table column, NULLs are ignored. * When checking a new record, just count all records in DB. When Updating, count all * records excluding the one we are updating. * * @param string column Value * @param array table name, table column, id of current record * @return boolean */ public static function unique($column_value, $args) { $db = new Database(); if ($args[2] == '') { $number_of_records = $db->count_records($args[0], array($args[1] => $column_value, 'deleted' => 'f')); } else { $number_of_records = $db->count_records($args[0], array($args[1] => $column_value, 'id !=' => $args[2], 'deleted' => 'f')); } return $number_of_records == 0; }