Code Request

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

Post Reply
th3gh05t
Forum Newbie
Posts: 22
Joined: Tue Apr 20, 2004 11:52 pm

Code Request

Post 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
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

Post 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
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Post 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...
th3gh05t
Forum Newbie
Posts: 22
Joined: Tue Apr 20, 2004 11:52 pm

Post 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?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
th3gh05t
Forum Newbie
Posts: 22
Joined: Tue Apr 20, 2004 11:52 pm

Post by th3gh05t »

Yea, I don't know how to do all that.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

moved to volunteer work
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post 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?
th3gh05t
Forum Newbie
Posts: 22
Joined: Tue Apr 20, 2004 11:52 pm

Post 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>

<?
	}
?>
?>
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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);

?>
th3gh05t
Forum Newbie
Posts: 22
Joined: Tue Apr 20, 2004 11:52 pm

Post by th3gh05t »

Wow!

Thanks so much!

How would I go about cleaning up the results?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
Post Reply