Page 1 of 1

Code Request

Posted: Wed Jul 07, 2004 9:48 pm
by th3gh05t
Hi,

I need a php script that combs through everyday database in MySQL and it searches though every specified field for a value. i.e - word, number combination of the two.


Is this possible?

If yes, how fast can it be?


Thanks, th3gh05t

Posted: Wed Jul 07, 2004 11:26 pm
by Deemo
to my knowledge this can not be done without a list of all the databases. i would love to learn otherwise though :D

Posted: Wed Jul 07, 2004 11:33 pm
by litebearer
Perhaps this would help??

Code: Select all

<?php
  // listdb.php by detour@metalshell.com
  //
  // List all databases availible in mysql.
  //
  // http://www.metalshell.com/
  //


  $dbuser = "user";
  $dbpass = "pass";
  $dbhost = "localhost";

  echo('<html><head><title>Database List</title></head><body>');
  echo('<font face="arial" style="font-weight:bold">');

  // Connect to mysql
  $dbid = mysql_connect($dbhost, $dbuser, $dbpass);

  // Get the list.
  $db_list = mysql_list_dbs($dbid);

  echo('<H1>Availible Databases</H1>');
  // Process each row as an object.
  while($row = mysql_fetch_object($db_list)) &#123;
    // Print out the database name.
    echo $row->Database . "<br>";
  &#125;

  // Close connection.
  mysql_close($dbid);

  echo('</body></html>');
?>
followed by mysql_list_tables -- List tables in a MySQL database

which is followed by mysql_list_fields -- List MySQL table fields
Lite...

Posted: Thu Jul 08, 2004 1:10 am
by th3gh05t
Yes, but I also need something that searches.

I need something that searches through every DB for a value.

Like a word, or e-mail address or something..

Do you understand what I need?

Posted: Thu Jul 08, 2004 6:49 am
by kettle_drum
It would be pretty simple. First get a list of the databases - store them in an array. then start to loop through them all. Get a list of all the tables in the database. Then loop through each of the tables to get the column field names, and then search through these for the word/string your looking for.

Speed would depend on how big the database is and how many records it has to search through.

Posted: Thu Jul 08, 2004 9:22 am
by th3gh05t
Yea, I don't know how to do all that.

Posted: Thu Jul 08, 2004 9:25 am
by JayBird
moved to volunteer work

Posted: Thu Jul 08, 2004 9:28 am
by patrikG
th3gh05t wrote:Yea, I don't know how to do all that.
So, you're looking for someone to do the job for you or someone to point you to where to get the scripts?

Posted: Thu Jul 08, 2004 5:47 pm
by th3gh05t
Ok. Here is the code that I already have.

Can some improve on this?

Code: Select all

<?php
<?

	include "/htdocs/scripts/common.php";

//	$e = $argv[1];

	$br = 1;

	$b = ($br) ? "<br>" : "\n";

	if ($e)
	{
        $link = mysql_connect($DB_SERVER, $DB_LOGIN, $DB_PASSWORD);
        
        $d_query = "SELECT db_name FROM clients.data WHERE active='1'";
        $data = mysql_query($d_query, $link);
        while($show = mysql_fetch_array($data))
        {	
		if ($like == 'like')
		{
			$q = "SELECT user_id, user_email FROM ". $show['db_name'] .".user WHERE user_email LIKE '%". $e ."%'";
			$listed = mysql_query($q, $link);
		}
		else
		{
			$q = "SELECT user_id, user_email FROM ". $show['db_name'] .".user WHERE user_email='". $e ."'";
			$listed = mysql_query($q, $link);
		}

		$r = mysql_fetch_array($listed);
		
		if ($r)
		{
			echo $r['user_email']." - ".$show['db_name']."$b";
		}
        }
	}
	else
	{
?>
<html>
<body>
	<form action="<?= $PHP_SELF?>" method="get">
	<input type="text" name=e size="31" style="font-family: Verdana; color: #000000; border: 1px solid #666666">
<br>
	EXACT <input type="radio" name="like" value="exact">&nbsp;&nbsp;&nbsp;
	LIKE <input type="radio" name="like" value="like">
<br>
	<input type="submit" value="Check for email address" style="font-family: Verdana; color: #000000; 
background: #FFFFFF; border: 1px solid #666666">
	</form>
</body>
</html>

<?
	}
?>
?>

Posted: Fri Jul 09, 2004 7:37 am
by kettle_drum
Heres a rough little script that does what you what. You will probably want to pretty up the showing of the results, and maybe split up the search part into more methods to make it nicer. Enjoy.

Code: Select all

<?php

class Database_Search {
	
   var $db;													
   
   ##--------------
   ##Class constructor
   ##--------------
   function Database_Search($host, $name, $pass){
      $this->connect($host, $name, $pass);
      if(isset($_POST['submit'])){
		   $this->search();
      }else{
         $this->show_form();
      }
   }
   
   ##--------------
   ##Shows form
   ##--------------
   function show_form(){
      echo "
         <center>
            <h2>Database Search</h2>
            <br />
            <form method="POST" action="">
               <input type="text" name="search"> 
               <input type="submit" name="submit" value="Search">
            </form>
         </center>
      ";
   }
   
   ##--------------
   ##Controls the search process
   ##--------------
   function search(){
      $dbs = mysql_list_dbs($this->db);			//get list of dbs
      while($database = mysql_fetch_array($dbs)){		//rotate through each db
         
         mysql_select_db($database[0], $this->db);		//selects database to use
         
         $tables = mysql_list_tables($database[0], $this->db);	//get list of tables for current db
         while($table = mysql_fetch_array($tables)){				//rotate through each table

            $sql = "SELECT * FROM $table[0] WHERE";
            $fields = mysql_list_fields($database[0], $table[0], $this->db);	//gets list of fields	
            $columns = mysql_num_fields($fields);

            for ($i = 0; $i < $columns; $i++) {											//rotates through fields
               $sql .= " ".mysql_field_name($fields, $i)." LIKE '%$_POST[search]%' OR";	//creates the sql query
            }
            
            $sql = substr($sql, 0, (strlen($sql)-3));						//removes the last OR
            
            $result = mysql_query($sql, $this->db);						//queries the database
            while($row = mysql_fetch_array($result)){						//goes through result
               echo "<b>Database: $database[0] from Table: $table[0] -> </b>";
               print_r($row);
               echo "<br><br><br>";
            }
            
            
         }
         
      }   	
      
      
   }

   ##--------------
   ##Connects to database
   ##--------------
   function connect($host, $name, $pass){
   	$this->db = mysql_connect($host, $name, $pass);
   	if(!$this->db){
   		echo "Cannot connect to database.";
   		exit;
   	}
   }
 
}

$host = "localhost";
$name = "phpMyAdmin";
$pass = "thisismypassword";

//runs the class
$search = new Database_Search($host, $name, $pass);

?>

Posted: Fri Jul 16, 2004 7:17 pm
by th3gh05t
Wow!

Thanks so much!

How would I go about cleaning up the results?

Posted: Sat Jul 17, 2004 5:28 am
by kettle_drum
Just edit this bit:

Code: Select all

while($row = mysql_fetch_array($result)){                  //goes through result 
               echo "<b>Database: $database[0] from Table: $table[0] -> </b>"; 
               print_r($row); 
               echo "<br><br><br>"; 
            }
And have it echo in a table and only show certain fields etc.


If you want to only search in certain tables/databases then pass these values as an array instead of producing an array from the mySQL results.