Posted on Leave a comment

Fix SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

MySQL 5.7.5+ changed the GROUP BY behavior. So in this and future versions GROUP BY would not work in same way as it used to be before.

So to make GROUP BY feature working like before follow these details:

Note: First of all to follow these steps you must need to have root access to your server.

If you want to disable error sql_mode=only_full_group_by  permanently then do those steps:

  1. sudo nano /etc/mysql/my.cnf
    
  2. Add this to the end of the file
    [mysqld]
    sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    
  3. sudo service mysql restart  to restart MySQL

This will disable ONLY_FULL_GROUP_BY for ALL users

Posted on Leave a comment

How to setup Google reCaptcha Version 2.0?

Screenshot_4

To setup Google reCaptcha oepn url https://www.google.com/recaptcha/admin and fill form as shown in the screenshot below:

After registration open newly created reCaptcha site and read the guide provided by google as shown below:

Screenshot_3

Now paste <script src=’https://www.google.com/recaptcha/api.js’></script>  in <head> or anywhere before use of captcha.

And then paste <div class=”g-recaptcha” data-sitekey=”6LcPQggTAAAAAAw-SQm8KTEW-N1CBuzBz_gW71UT”></div> to the place where you want to show captcha in form and obviously you will have to use tag with your own site key from google reCaptcha site.

Continue reading How to setup Google reCaptcha Version 2.0?

Posted on Leave a comment

PHP: How to search and replace string from complete database using PHP

If you want to search and replace string from entire database (from all tables of any database) using php then use following code.

<?php
// Find and replace facility for complete MySQL database
//
// Written by Mark Jackson @ MJDIGITAL
// Can be used by anyone - but give me a nod if you do!
// http://www.mjdigital.co.uk/blog

// SEARCH FOR
$search        = '**__FIND_THIS__**';

// REPLACE WITH
$replace    = '**__REPLACE_WITH_THIS__**'; // (used if queryType below is set to 'replace')

// DB Details
$hostname = "__DB_HOST__";
$database = "__DB_DATABASE__";
$username = "__DB_USER__";
$password = "__DB_PASSWORD__";

// Query Type: 'search' or 'replace'
$queryType = 'replace';

// show errors (.ini file dependant) - true/false
$showErrors = true;

//////////////////////////////////////////////////////
//
//        DO NOT EDIT BELOW
//
//////////////////////////////////////////////////////

if($showErrors) {
    error_reporting(E_ALL);
    ini_set('error_reporting', E_ALL);
    ini_set('display_errors',1);
}

// Create connectio to DB
$MJCONN = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database,$MJCONN);

// Get list of tables
$table_sql = 'SHOW TABLES';
$table_q = mysql_query($table_sql,$MJCONN) or die("Cannot Query DB: ".mysql_error());
$tables_r = mysql_fetch_assoc($table_q);
$tables = array();

do{
    $tables[] = $tables_r['Tables_in_'.strtolower($database)];
}while($tables_r = mysql_fetch_assoc($table_q));

// create array to hold required SQL
$use_sql = array();

$rowHeading = ($queryType=='replace') ? 
        'Replacing \''.$search.'\' with \''.$replace.'\' in \''.$database."'\n\nSTATUS    |    ROWS AFFECTED    |    TABLE/FIELD    (+ERROR)\n"
      : 'Searching for \''.$search.'\' in \''.$database."'\n\nSTATUS    |    ROWS CONTAINING    |    TABLE/FIELD    (+ERROR)\n";

$output = $rowHeading;

$summary = '';

// LOOP THROUGH EACH TABLE
foreach($tables as $table) {
    // GET A LIST OF FIELDS
    $field_sql = 'SHOW FIELDS FROM '.$table;
    $field_q = mysql_query($field_sql,$MJCONN);
    $field_r = mysql_fetch_assoc($field_q);

    // compile + run SQL
    do {
        $field = $field_r['Field'];
        $type = $field_r['Type'];

        switch(true) {
            // set which column types can be replaced/searched
            case stristr(strtolower($type),'char'): $typeOK = true; break;
            case stristr(strtolower($type),'text'): $typeOK = true; break;
            case stristr(strtolower($type),'blob'): $typeOK = true; break;
            case stristr(strtolower($field_r['Key']),'pri'): $typeOK = false; break; // do not replace on primary keys
            default: $typeOK = false; break;
        }

        if($typeOK) { // Field type is OK ro replacement
            // create unique handle for update_sql array
            $handle = $table.'_'.$field;
            if($queryType=='replace') {
                $sql[$handle]['sql'] = 'UPDATE '.$table.' SET '.$field.' = REPLACE('.$field.',\''.$search.'\',\''.$replace.'\')';
            } else {
                $sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE '.$field.' REGEXP(\''.$search.'\')';
            }

            // execute SQL
            $error = false;
            $query = @mysql_query($sql[$handle]['sql'],$MJCONN) or $error = mysql_error();
            $row_count = @mysql_affected_rows() or $row_count = 0;

            // store the output (just in case)
            $sql[$handle]['result'] = $query;
            $sql[$handle]['affected'] = $row_count;
            $sql[$handle]['error'] = $error;

            // Write out Results into $output
            $output .= ($query) ? 'OK        ' : '--        ';
            $output .= ($row_count>0) ? '<strong>'.$row_count.'</strong>            ' : '<span style="color:#CCC">'.$row_count.'</span>            ';
            $fieldName = '`'.$table.'`.`'.$field.'`';
            $output .= $fieldName;
            $erTab = str_repeat(' ', (60-strlen($fieldName)) );
            $output .= ($error) ? $erTab.'(ERROR: '.$error.')' : '';

            $output .= "\n";
        }
    }while($field_r = mysql_fetch_assoc($field_q));
}

// write the output out to the page
echo '<pre>';
echo $output."\n";
echo '<pre>';
?>

Just use your database credentials and it will work like charm.

Credits: Mark Jackson