Buscar una palabra dentro de una base de datos completa mysql

En ocasiones nos podemos ver con la circunstancia de tener que localizar una palabra concreta y ni siquiera sabemos la tabla ni la columna donde pueda estar.

Recientemente me he visto en la situación de localizar unos datos obsoletos de una antigua web y que era bastante complicado averiguar de donde venían.

Gracias a http://www.2mdc.com por esta gran utilidad. Aquí teneis el código:

<?php
ini_set('display_errors', 'On');
/***********************************************************************
* @name  AnyWhereInDB
* @author Nafis Ahmad 
* @abstract This project is to find out a part of string from anywhere in database
* @version 0.22  
* @package anywhereindb
*
*
*
*
*************************************************************************/
session_start(); 
// @abstract  We will keep the  authentication information in Session. 
// @todo  1. delete the line and 
// @todo  2.  chage the values of the variable 
// @uses  it will not show the login screen. 
/* delete this line if you want use same db connection info
 $_SESSION['server']='localhost';
 $_SESSION['dbuser']='dbuser';
 $_SESSION['pass']='dbpass';
 $_SESSION['dbname']='dbname';
 //**/
 
if(empty($_SESSION['server'])&& 
   empty($_SESSION['dbuser'])&& 
   empty($_SESSION['pass'])&& 
   empty($_SESSION['dbname']) 
   )
// @abstract this is to check the  session is not avilaable 
{
	if(!empty($_POST['server']) && !empty($_POST['dbuser']) && !empty($_POST['dbname']))
	// @abstract this is to check the  session information else it will show the login prompt
	{
		echo "vacio";
		$_SESSION['server'] = $_POST['server'];
		$_SESSION['dbuser']= $_POST['dbuser'];
		$_SESSION['pass']	= $_POST['pass'];
		$_SESSION['dbname']= $_POST['dbname'];
		header("Refresh:0;url=http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']);
	}
	else
	// @abstract  it will show the login prompt
	{
			//  @link  html_head will printed here!! 
			html_header(); 
 
			if(!empty($_REQUEST['error_message']))
			// @uses Error in DB connection  
			{
				echo '<span style="color:red;">'.$_REQUEST['error_message'].'</span>';
			} 
			?>
 
			<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="POST">  
				<table>
					<tbody>
						<tr>
							<td><label for="server">Server Name </label></td>
							<td><input type="text" name="server" value="localhost"/></td>
						</tr>
						<tr>
							<td><label for="dbuser">User Name </label></td>
							<td><input type="text" name="dbuser" /></td>
						</tr>
						<tr>
							<td><label for="pass">Password </label></td>
							<td><input type="password" name="pass" /></td>
						</tr>
						<tr>
							<td><label for="dbname">Database Name </label></td>
							<td><input type="text" name="dbname" /></td>
						</tr>
						<tr>
							<td><input type="submit" value="Login to your Database" /></td>
						</tr>
					</tbody>
				</table>
			</form>
 
<?php
	}// @endof Else  !empty($_POST )
} // @endof Else  !!empty($_SESSION )
else
// @abstract  the session has the login information 
{
	if(!empty($_REQUEST['logout']))
	// @name  Logout module   
	// @abstract    distroy session and page reload. 
	{
		session_destroy();
		header("Refresh:0;url=http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']);
	}
 
	$server = $_SESSION['server'];
	$dbuser = $_SESSION['dbuser'];
	$dbpass = $_SESSION['pass'];
	$dbname = $_SESSION['dbname'];
 
	// @name Databse Connection 
	// @abstract  connected with database. and without showing any error ... 
	$link = @mysql_connect($server, $dbuser, $dbpass);
	if (!$link) {  session_destroy(); header("Refresh:0;url=http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'].'?error_message=Username OR password Missmatch');}
	if(!@mysql_select_db($dbname, $link)){ session_destroy(); header("Refresh:0;url=http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'].'?error_message=Database Not found');};
	///@endof Databse Connection  
 
	html_header();	 //  @link  html_head will printed here!! 
 
	// @abstract  Show the html search Form !!
	?>
 
	<div style="position:absolute; right:100px; width:100px;"><a href="<?php echo $_SERVER['PHP_SELF']; ?>?logout=out">Disconnect/Change Database</a></div>
	<div>
		<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="POST">  
			<label for="search_text"> Search on Database '<?php echo $dbname ?>'</label><br />
			<input type="text" name="search_text" <?php if(!empty($_POST['search_text'])) echo 'value="'.$_POST['search_text'].'"';  ?> />
			<input type="submit" value="Search" />
		</form>
	</div>
	<?php 
	//endof html search form
 
	if(!empty($_POST['search_text']))
	 // @abstract for each Search Text we seach in the database
	{	
		$search_text = mysql_real_escape_string($_POST['search_text']);
		$result_in_tables = 0;
 
		echo '<a href="javascript:hide_all()">Collapse All Result</a> 
			 <a href="javascript:show_all()">Expand All Result</a>';
		echo "<h4>Results for: <i>". $search_text.'</i></h4>';
 
		// @abstract  table count in the database
		$sql= 'show tables';
		$res = mysql_query($sql);
		//@abstract  get all table information in row tables
		$tables = fetch_array($res);
 
 
                //$tables = array(array('album'));
		//endof table count
 
 
 
	   for($i=0;$i<sizeof($tables);$i++)
	   // @abstract  for each table of the db seaching text
	   {
			//@abstract querry bliding of each table
			$sql = 'select count(*) from '.$tables[$i]['Tables_in_'.$dbname];
			$res = mysql_query($sql);
 
			if(mysql_num_rows($res)>0)
			//@abstract Buliding search Querry, search
			{
				//@abstract taking the table data type information
				$sql = 'desc '.$tables[$i]['Tables_in_'.$dbname]; 
				$res = mysql_query($sql);
				$collum = fetch_array($res);
 
				$search_sql = 'select * from '.$tables[$i]['Tables_in_'.$dbname].' where ';
				$no_varchar_field = 0;
 
				for($j=0;$j<sizeof($collum);$j++)
				// @abstract only finding each row information
				{
						## we are searching all the fields in this table
						
						//if(substr($collum[$j]['Type'],0,7)=='varchar'|| substr($collum[$j]['Type'],0,7)=='text')
						// @abstractonly type selection part of query buliding
						// @todo seach all field in the data base put a 1 in if(1)
						// @example if(1) 
						//{
							//echo $collum[$j]->Field .'<br />';
							if($no_varchar_field!=0){$search_sql .= ' or ' ;}
							$search_sql .= '`'.$collum[$j]['Field'] .'` like \'%'.$search_text.'%\' ';			
							$no_varchar_field++;
						//} // endof type selection part of query bulidingtype selection part
 
				}//@endof for |buliding search query
 
 
				if($no_varchar_field>0)
				// @abstract only main searching part showing the data
				{
					$res = mysql_query($search_sql);
					$search_result = fetch_array($res);
					if(sizeof($search_result))
					// @abstract found search data showing it! 
					{
						$result_in_tables++;
 
						echo '<div class="table_name">&nbsp;&nbsp; Table :<input type="text" style="width:400px;" value="' . $tables[$i]['Tables_in_'.$dbname].'"/> &nbsp;&nbsp;</div> 
							  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.
							'<span class="number_result"> Total Results for <i>"'.$search_text .'"</i>: '.mysql_affected_rows().'</span>
							<br/>
							<div class="link_wrapper"><a href="javascript:toggle(\''.$tables[$i]['Tables_in_'.$dbname].'_sql'.'\')">SQL</a></div>
							<div id="'.$tables[$i]['Tables_in_'.$dbname].'_sql" class="sql keys"><i>'.$search_sql.'</i	></div>
                            <form method="post" action="anywhereindbDelete.php" target="_blank" >
                                <div class="eliminar">
                                    <button type="submit" name="query" value="'.str_replace("select *", "delete ", $search_sql).'">Borrar</button>
                                    <!--<a href="anywhereindbDelete.php?query='.str_replace("select *", "delete ", $search_sql).'" target="_blank">Borrar</a>-->
                                </div>
                                <input type="hidden" name="server" value="'.$_SESSION['server'].'"/>
                                <input type="hidden" name="dbuser" value="'.$_SESSION['dbuser'].'"/>
                                <input type="hidden" name="pass" value="'.$_SESSION['pass'].'"/>
                                <input type="hidden" name="dbname" value="'.$_SESSION['dbname'].'"/>
                            </form>
							<div class="link_wrapper"><a href="javascript:toggle(\''.$tables[$i]['Tables_in_'.$dbname].'_wrapper'.'\')">Result</a></div>
							<script language="JavaScript">
								table_id.push("'.$tables[$i]['Tables_in_'.$dbname].'_wrapper");
							</script>
							<div class="wrapper" id="'.$tables[$i]['Tables_in_'.$dbname].'_wrapper">';
 
						table_arrange($search_result);
						echo '</div><br/><br/>';
					}// @endof showing found search  
 
				}//@endof  main searching 
			}//@endof  querry building and searching 
 
	   }
 
	   if(!$result_in_tables)
	   // @abstract if result is not found
	   {
			echo '<p style="color:red;">Sorry, <i>'.
					$search_text.
					'</i> is not found in this Database ('.$dbname.') !</p>';
	   }
	mysql_close($link); 
	}
}// @endof Else  
// @abstract common  footter
?>
<br/>
<br/>
<span  class="me">"AnyWhereInDB" is a Open Source Project, developed by <a href="http://twitter.com/happy56">Nafis Ahmad</a>. 
<br /> 
<a href="http://code.google.com/p/anywhereindb">http://code.google.com/p/anywhereindb </a>
</span>
</body>
</html>
<?php
//@endof common fotter
//*********************
//* PHP functions 
//*********************
function fetch_array($res)
// @method    fetch_array
// @abstract taking the mySQL $resource id and fetch and return the result array
// @param   string| MySQL resouser 
// @return  array  
{
	   $data = array();	
	while ($row = mysql_fetch_assoc($res)) 
	{
		$data[] = $row;
	}
	return $data;
} //@endof  function fetch_array
function table_arrange($array)
// @method  table_arrange
// @abstract taking the mySQL the result array and return html Table in a string. showing the search content in a diffrent css class.
// @param  array 
// @post_data  search_text
// @return  string | html table 
{
 
	$table_data = ''; // @abstract  returning table
 
	$max =0; // @abstract  max lenth of a row
 
	$max_i =0; // @abstract  number of the row which is maximum max lenth of a row
 
	$search_text = $_POST["search_text"];
 
	for($i=0;$i<sizeof($array);$i++)
	{
		//@abstract table row 
		$table_data .= '<tr class='.(($i&1)?'"odd_row"':'"even_row"') .' >';
		//
		$j=0;
 
		foreach($array[$i] as $key => $data) 
		{
 
			//@abstract a class around the search text 
			$data = preg_replace("|($search_text)|Ui" , "<pre class=\"search_text\"><b>$1</b>

” , htmlspecialchars($data));

$table_data .= ‘

‘. $data .’  

‘;

$j++;
}

if($max<$j) { $max = $j; $max_i = $i; } $table_data .= '

‘.”\n”;
}
$table_data .= ‘

‘;
unset($data);
// @endof html table

//@abstract populating the table head

// @varname $data_a
//@abstract taking the highest sized array and printing the key name.
$data_a = $array[$max_i];

$table_head = ‘

‘;
foreach($data_a as $key => $value)
{
$table_head .= ‘

‘. $key.’

‘;
}

$table_head .= ‘

‘.”\n”;
//@endof populating the table head

// @abstract printing the table data
echo ‘

‘.$table_head.$table_data;
}//@endof function table_arrange
function html_header()
// @method html_header
// @abstract showing the html header of the instance.
// @result prints the html header
{
?>




Any where In DB || AnyWhereInDB.php


AnyWhereInDB.php

2 comments
  1. Hola. quiero decir te que estas haciendo un gran labor y te agradezco mor ello. Estoy terminando de desarrollar una pagina a partir de un famoso tema OpenDoor 1.4. Es muy completo y cuando empece, no tenia ni idea de como funcionaba WP. Sin embargo llevo una semana y la pagina esta casi lista. El problema es que me he encontado con una dificultad. La búsqueda interna predeterminada por el tema, solo funciona en la pagina search reults, al realizar la búsqueda desde cualquier otra pagina, sea inicio o sobre nosotros, me lleva a la misma pagina. Cual crees que puede ser el problema?

    Gracias de antemano

Add Comment

Required fields are marked *. Your email address will not be published.