MySql/Magento database scanner PHP script

Database_Scanner.php

<?php

/*
* scan Magento or regular MySql database for a search string
* usage: $ php searchEntireDatabase.php
*/

define('TESTING', true);
define('SKIP_TABLES', false);

$database = 'your_db_name';
$tablesToSkip = array(
'sample_table_one',
'sample_table_two',
);

//testing
if (TESTING) {
	$config = (object)array(
	'host'		=>'localhost',
	'username' 	=> 'user',
	'password' 	=> 'pw',
	'database'  => $database
	);

	$searchWord = 'yourKeywordHere';

} else {
	require_once("../app/Mage.php");
	$app = Mage::app('default');

	// Grab our database config info
	$config = Mage::getConfig()->getResourceConnectionConfig("default_setup");
  
	$searchWord = 'yourKeywordHere';
}

$diagnostics = array();

//  Connect to MySQL 
$mysqli = new mysqli($config->host, $config->username, $config->password, $database);

if ($mysqli->connect_errno) {

    printf("Connect failed: %s\n", $mysqli->connect_error);

    exit();

}



function strip_whitespace($tablesToSkip) {

	$new = array();

	foreach($tablesToSkip as $skip) {

		$new[] = trim($skip);

	}

	return $new;

}



function pretty($var) {

	echo '<pre>';

	print_r($var);

	echo '</pre>';

}



function getDbTables() {

	global $mysqli,$database,$tablesToSkip;

	if (SKIP_TABLES) {

		$tablesToSkip = strip_whitespace($tablesToSkip);

	}

	$myTables = array();

	$tables =  $mysqli->query("SHOW TABLES FROM $database");

	while($table = $tables->fetch_array()) {

		if (SKIP_TABLES) {

			if (!in_array($table[0], $tablesToSkip)) {

				$myTables[] = $table[0];

			}

		} else {

			$myTables[] = $table[0];

		}   

	}

	return $myTables;

}



function getColumns($table) {

	global $mysqli;

	$myColumns = array();

	$columns =  $mysqli->query("SHOW COLUMNS FROM $table");

	while($column = $columns->fetch_array()) { 

    	$myColumns[] = $column[0];   

	}

	return $myColumns;

}



function filterText($rawres,$searchWord) {

	$likelyIndexKey = '';

	$keys = array_keys($rawres);

	if (!empty($keys)) {

		$likelyIndexKey = array( $keys[0], $rawres[$keys[0]] );

	}



	$founds = array();

	foreach($rawres as $rkey => $res) {

		preg_match_all('/(?:\S+\s*){0,5}\S*'.$searchWord.'\S*(?:\s*\S+){0,5}/',$res,$matches);

		$res =  implode(" , ",$matches[0]);

		if (strpos($res, $searchWord) !== false) {

		    $founds[] = " [Column " .  $rkey ."] \n [TEXT] ". $res ." [TEXT] \n Likely index ( $likelyIndexKey[0] = $likelyIndexKey[1] )\n";

		}

	}

	return implode(" ",$founds);



}



function searchEachColumn($table,$columns,$searchWord) {

	global $mysqli,$diagnostics;

	echo "Searching Table $table ...\n";

	$diagnostics[] = array('tables',$table);

	$results = array();

	if (count($columns) > 0) {

		foreach($columns as $column) {

			echo "	Searching Column $column ...\n";

			$diagnostics[] = array('columns',$column);

			// /$column

			$result = $mysqli->query("SELECT * FROM $table WHERE $column LIKE '%$searchWord%' ");

			if ($result->num_rows > 0) {

				while($res = $result->fetch_assoc()) { 

			    	$results[] =  " Keyword: $searchWord\n [ table: $table ]\[ column: $column ] \n" . filterText($res,$searchWord) . "\n\r";  

				}

			}

		}

	}

	return $results;

}



function tableLook($table,$searchWord) {

	$columns = getColumns($table);

	return searchEachColumn($table,$columns,$searchWord);

}

function display($results) {

	$html = array();

	if (count($results) > 0) {

		foreach($results as $res) {

			if (count($res) > 0) {

				foreach($res as $rz) {

					$html[] = $rz;

				}

			}

		}

		return $html;

	}

}



$tables = getDbTables();



if (count($tables) > 0) {

	

	$results = array();

	foreach($tables as $table) {

		$results[] = tableLook($table,$searchWord);

	}

	$html = display($results);



	echo "###### Database Searcher 1.0 #######\n\r";

	if (count($html) > 0 ) {

		echo implode(" -------------------------------- \n", $html);

	} else {

		echo " --------- No Results ----------- \n";

	}

	echo "------------------------------------------- \n";

	//var_dump($diagnostics);

	// echo "Diagnostics {tables: ".count($diagnostics['table'])."}\n";

	// echo "Diagnostics {columns: ".count($diagnostics['column'])."}\n";

}