Searching a keyword in a MySql table can be easily implemented, but it has always been a cumbersome task to search a keyword in the whole database.
Moreover it is has been observed as a common need for many web application, particularly for advanced search like functionality.
Search a Keyword in Whole Database in PHP
Taking it into consideration, PHP has introduced a new class, named as “searchEntireDB”.
Just include this file into your code to make the searching of some word or a number in the whole database.
Here is the code for class “searchEntireDB” saved as searchEntireDB.php.
searchEntireDB.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 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 110 111 112 113 114 |
<?php class searchEntireDB { protected $_query; protected $_sql; private $_userName; private $_passWord; private $_dbName; protected $_tableName; protected $_connection; protected $_columnName; protected $_targetText; public $_finalResult; public $_reports; public function __construct($DB, $userName, $passWord, $columnName, $targetText) { $this->_dbName = $DB; $this->_userName = $userName; $this->_passWord = $passWord; $this->_columnName = $columnName; $this->_targetText = $targetText; try { $this->_connection = new PDO('mysql: host = localhost; dbname = ' . $this->_dbName, $this->_userName, $this->_passWord); $this->_connection -> exec("SET SESSION collation_connection = 'utf8_general_ci';"); $this->_connection -> exec("SET CHARACTER SET 'utf8';"); $this->_connection -> exec("set names utf8"); }//try catch( Exception $e ) { die('Connection error...'); }//catch searchEntireDB::searchDB(); }// __construct() public function searchDB() { $this->_sql = 'SHOW TABLES FROM ' . $this->_dbName; $this->_query = $this->_connection->query($this->_sql); $this->_tableName = $this->_query->fetchAll(); $tableNumber = count($this->_tableName); for ( $i = 0; $i <= $tableNumber - 1; $i++ ) { $sql2 = "SELECT * FROM " . $this->_dbName . "." . $this->_tableName[$i][0]; $query2 = $this->_connection->query($sql2); $fetch = $query2->fetchAll(); $tempDBName = $this->_tableName[$i][0]; if ( empty($fetch) ) { $this->_reports['emptyDBs'][] = $tempDBName; }// if this DB is Empty else { $thisArrayIndexes = count($fetch); foreach ( $fetch as $key=>$value ) { if ( is_array($value) ) { foreach ( $value as $column=>$fieldValue ) { if ( $this->_columnName != NULL ) { $pos = strpos($fieldValue,$this->_targetText); if ( $column == $this->_columnName && $pos !== false ) { $this->_reports['result'][$tempDBName][$column] = $fieldValue; $this->_finalResult[] = array('DBName'=>$tempDBName, 'ColumnFoundMatch'=>$column, 'searchedFor'=>$fieldValue); }//Last layer }//if ( $this->_columnName != NULL else { if ( strpos($fieldValue,$this->_targetText) !== false ) { if ( is_int($column) ) { continue; }//prevent including integer keys in report and result else { $this->_reports['result'][$tempDBName][$column] = $fieldValue; $this->_finalResult[] = array('DBName'=>$tempDBName, 'ColumnFoundMatch'=>$column, 'searchedFor'=>$fieldValue); } }//Last layer when ColumnName is Null }//IF $this->_columnName ( is )--> NULL }//foreach ( $value as $column=>$fieldValue }//if ( is_array($value }//oreach ( $fetch as $key=>$value $this->_reports['notEmpty'][] = $tempDBName; }//else }//for return $this->_reports; return $this->_finalResult; }//searchDB() }//class searchEntireDb |
And here is the example code, showing the use of the above class .
|
1 2 3 4 5 6 |
<?php require_once 'searchEntireDB.php'; $obj = new searchEntireDB('Database name','username','password',NULL,'keyword'); echo "<pre>"; print_r($obj); ?> |
It is important to mention here, how you will see the results.
Here is a snapshot of the search results, that will help you to understand the output.
1 -> result = name of the array containing the matching results.If result array is missing, it indicates that no match found for given keyword.
2 -> users = name of the table containing the matched records
3, 4 -> identifier,name = columns of the users table, containing the matched values
5, 6 -> JohnC, John C. = the matched values
Also with his useful class, you can also find the empty and non empty tables in the searched database, resulted in form of emptyDBs and notEmpty array respectively.
