datalogik

Logically anything.

MySQL Searching

This is a PHP script that I wrote to perform one function: locating data in a database without manually having to look through every table. This will let you enter a value and search every column in every table for this specific value.

How to Use

< ?php
  # Alter this to be the value you wish to search for.
  # This can contain the MySQL wildcard (%)
  $searchVal	= 'value';
 
  # Database Settings
  $dbServer 	= 'localhost';
  $dbUsername	= 'root';
  $dbPassword	= '';
  $dbName 	= 'dbname';
 
  # Establish the database connection
  $hdb = mysql_connect($dbServer, $dbUsername, $dbPassword);
  mysql_select_db($dbName, $hdb);
 
  # Execute query to show all tables in database
  $rs = mysql_query("SHOW TABLES FROM $dbName");
  while($tbls = mysql_fetch_array($rs)) {
    $tables[] = $tbls;
  }
 
  # Now we go through those tables and get the column names
  # and create a select query.
  for ($y = 0; isset($tables[$y]); $y++) {
    $tblName = $tables[$y][0];
 
    # Get column names and build field list
    $rs2 = mysql_query("DESCRIBE $tblName");
    while ($fields = mysql_fetch_array($rs2)) {
      $fieldList[] = $fields[0];
    }
 
    # Creating our select statement
    $sql = "SELECT * FROM $tblName WHERE ";
    for ($x = 0; isset($fieldList[$x]); $x++) {
      if ($x &gt; 0) { $sql .= " OR "; }
      $sql .= "`" . $fieldList[$x] . "` LIKE '%" . $searchVal . "%'";
    }
 
    # Run the newly formed query
    $rs3 = mysql_query($sql);
 
    # Go through the results and display the matches
    while ($fields = mysql_fetch_array($rs3)) {
      print "Table: " . $tblName . "\r\n";
      print_r($fields);
    }
    unset($fieldList);
  }
 
  # Cleanup
  mysql_free_result($rs);
  mysql_close($hdb);
?>

This was designed for execution via command line but should still display in a browser.

Search Columns

In the past, there have been situations where I will need to search a database for a specific column name. If you have a scenario come up where you can have 300+ table names with 30+ columns in each table, you obviously cannot manually search each one. Use the following query on the “mysql” database to search the table schema.

SELECT column_name, table_schema 
FROM information_schema.COLUMNS 
WHERE column_name LIKE '%value%' 
AND table_schema = 'dbname';

Obviously change “%value%” to the value you wish to search for (keeping % as it’s the MySQL wildcard) and then change the “dbname” to your database name.

Popularity: 2% [?]

Leave a Reply Cancel reply