import GeoIP csv to MySQL DB provided by MaxMind

you can download GeoIP csv database from http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip

unzip  and extract the csv file just upload it your web server

$ips=explode("\n",file_get_contents('GeoIPCountryWhois.csv'));
foreach($ips as $ip)
{
$ip=str_replace('"',"'",$ip); //this variable cantains sample string '1.0.0.0','1.0.0.255','16777216','16777471','AU','Australia'   which means you can use this string directly in mysql insert query

//perform your mysql insert query here

}


that is all :))

group by,order by sum value together

Table Structure and Data

employer_id    salary

111              1000
112              2000
113              1000
111              1000
112              5000
113              2000
111              1000
113              1000


this is just an example for understanding purpose..


so here we have 3 employers,employe(111) got salary 3 times,employe(112) got salary 2times and employe(113) got salary 3 times

so we are going to sort the list by who got maximum money

SQL Query


SELECT sum( salary ) AS amt , employ_id FROM TABLE GROUP BY employer_id ORDER BY amt DESC

 
SAMPLE OUTPUT

amt     employer_id
7000   112
4000   113
3000   111
No matter howmany times the employe got salary just we are listing out based on highest earner..

reset mysql root password

ps -ef | grep mysql      - checks if mysql/mysqld is one of the running processes. 

pkill mysqld             - kills the daemon, if it is running.

mysqld_safe --skip-grant-tables & - Run MySQL safe daemon with skipping grant tables

mysql -u root mysql - Login to MySQL as root with no password

UPDATE user SET password=PASSWORD("your-new-passowrd") WHERE user="root"; 

FLUSH PRIVILEGES;

Done 

unique combination of multiple columns in mysql

alter table tablename add unique index(column1,column2,column3);

[mysql ] perform order by column and rand() in single query

SELECT * FROM
(
    SELECT * FROM table1
    ORDER BY nos desc limit 0,1) 

          )  T1 ORDER BY RAND()

usage: 

if the column nos is same in multipe rows then fetch a random row..

sort by occurence of anyvalue in a column

SELECT * , COUNT( ref ) AS occurances FROM table GROUP BY ref ORDER BY occurances DESC