Code Request
Moderator: General Moderators
Code Request
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
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
-
litebearer
- Forum Contributor
- Posts: 194
- Joined: Sat Mar 27, 2004 5:54 am
Perhaps this would help??
followed by mysql_list_tables -- List tables in a MySQL database
which is followed by mysql_list_fields -- List MySQL table fields
Lite...
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)) {
// Print out the database name.
echo $row->Database . "<br>";
}
// Close connection.
mysql_close($dbid);
echo('</body></html>');
?>which is followed by mysql_list_fields -- List MySQL table fields
Lite...
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
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.
Speed would depend on how big the database is and how many records it has to search through.
Ok. Here is the code that I already have.
Can some improve on this?
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">
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
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);
?>-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
Just edit this bit:
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.
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>";
}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.