How to find a column or filed name of a table from an entire database?

If you are working on a BIG project with lots of tables in the database, then it will be a good trick query for you to find out a column name of any table from that database.

 

My scenario was a bit different, I had to start work in the middle of a project and there was no one to guide/help me regarding  anything. And you know, about the deadline… really messed up. So, I didn’t hopeless and as usual did some google and prepare this query.

 

“SELECT TABLE_NAME, COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE column_name LIKE ‘your_column_name’ “

 

As you can see it search information schema for the column name you are finding after the LIKE clause. And this query saved me and my ass 😉

 

I am writing this article for my future reference. Hope it will also help you and save your ass too 😉 as like mine. If this article save your time then you can comment and share this article to save others too. [sharing is caring]

 

 

[Note: May be you can find this query on other sites. As I said I did some google and make for me. Sorry forgot those site otherwise I put those on ref. section like my other post.]

Joomla! 3 installation freezes at creating database table

After long time I try to install Joomla and it is Joomla 3.3.0 and face a problem as like you. All things are ok but its taking too much time during database table creation. And here is the solution

To solve this go to

Your joomla folder\installation\sql\mysql and open Joomla.sql file find the term

“ENGINE=InnoDB”

and replace ALL with

“ENGINE=MyIsam”

MyIsam is more supported and compatible with Joomla.

Do also the same with (sample_data.sql) file, if it’s quickstart setup.

PHP code to execute MySQL script

There are advanced and comfortable tools to manage your MySQL databases (i.e.  server side: phpMyAdmin, client side:  SQLyog).

But sometimes you can not use those tools and need the ability to execute some SQL script on server by your own hands, easy and fast way. What to do in such situation? Use this PHP code to execute all SQL commands from SQL script text file, one by one:

$sqlFileToExecute = 'path/to/sql/script.sql';

$hostname = DB_HOST;
$db_name = DB_NAME;
$db_user = DB_USER;
$db_password = DB_PASSWORD;

$link = mysql_connect($hostname, $db_user, $db_password);
if (!$link) {
  die ("MySQL Connection error");
}

mysql_select_db($db_name, $link) or die ("Wrong MySQL Database");

$fp = file($sqlFileToExecute, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$query = '';
foreach ($fp as $line) {
  if ($line != '' && strpos($line, '--') === false) {
  $query .= $line;
    if (substr($query, -1) == ';') {
      mysql_query($query);
      $query = '';
    }
  }
}