Finding a term in mysql database

one idea is to run a number of loops and query the db.

this mysql_search.php script tells you which table in a db has the match for the searched term. run the script in commandline, ie

<?php

// Config
$server = "localhost";
$username = "root";
$passwd = "";
$db = "moodle";
$searchTerm = "water_pics";
$log = "mysql_search.log";
// --- END OF CONFIGURATION -- //
mysql_connect($server, $username, $passwd);
mysql_select_db("$db");
$r = mysql_query("show tables");

while ($table = mysql_fetch_array($r, MYSQL_ASSOC)) {
  $r1 = mysql_query("show columns from ".$table['Tables_in_'.$db]);
  while ($col = mysql_fetch_array($r1, MYSQL_ASSOC)) {
    $r2 = mysql_query("Select * from ".$table['Tables_in_'.$db]." where ". $col['Field']." like '%$searchTerm%'");
    while ($match = mysql_fetch_array($r2)) {
        $str = "table(".$table['Tables_in_'.$db].") - First_column_id ($match[0]) - column_matched (". $col['Field'].")\n";
        echo $str;
        $fp = fopen($log, 'a+');
        fwrite($fp, $str);
        fclose($fp);
    }
  }
}

Like it.? Share it:

Tags: MYSQL

Comments are closed.

Related Articles

Allow DB synchronisation in PHPMyAdmin: phpmyadmin has a useful feature to allow the ability to synchronise dbs from different servers. This is useful if you want to sync your production db to you dev db for example. In the phpmyadmin config.inc.php in your dev machine, add the following then go to the phpmyadmin dashboard and click on the synchronize option. … Continue reading
Retrieve Lost Password in MYSQL: Sometime, we forget the root password for mysql. It happened to me once in my company. The previous developer left and didn’t document where to look for passwords. Well, it is possible for the root user of the system to reset it anyway… First shutdown mysql, /etc/init.d/mysql stop then mysqld –skip-grant-tables –u root mysql -u … Continue reading
Removing Foreign Key Constraints In MYSQL: Some Mysql db is using innoDB which implements foreign key contraints. If you can’t drop or alter a table, check that it doesnt have foreign key contraints. Well, innoDB can be an angel or devil… In mysql command line, check existence of foreign keys show create table demographic_type CREATE TABLE `demographic_type` ( `id` int(10) unsigned … Continue reading
export custom mysql query to csv: Sometimes it is useful to export custom query and then import it as a csv into another table mysql -uroot -p your_db -e “select_query INTO OUTFILE ‘/tmp/query.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘ LINES TERMINATED BY ‘\n’” Like it.? Share it:Tweet
Drop all mysql tables without permission to delete and create new database: In mysql, the simplest way to flush all tables is to delete the db and create a new one. However, one might not have permission to do that. So we need a script to list all tables and delete it. Like it.? Share it:Tweet
Dropping or updating uniq, foreign key in mysql tables: If you need to drop an index or uniq column from mysql, in the command line, first find out the FK or UNIQ key, “show create table table_name”; now, you will see the sql for creating a table, copy and paste the key, eg “drop index FK_40123345 on table_name;” To double check if this is correct, … Continue reading
Quick Mysql Tips: mysql dump limited data mysqldump -uroot -pxx dbname dbtable –where=”true order by transactionid desc limit 1″ Logging slow queries To enable the slow query log, start mysqld with the –log-slow-queries[=file_name] option. Use the mysqldumpslow command to summarize the queries that appear in the log. For example: mysqldumpslow /path/to/your/mysql-slow-queries.log -t 10 shows you top 10 performance … Continue reading
Quick script to sort mysql database/tables based on table size: Mysql can show the table status with the row and size for each table but doesn’t do sorting. We can however store each row in an array and sort the array. I am too lazy to write a script for it. 2 x for loops does the trick. This script is from adamyoung.net. Just run … Continue reading