Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

17 April 2013

import GeoIP csv to MySQL DB provided by MaxMind

you can download GeoIP csv database from

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

foreach($ips as $ip)
$ip=str_replace('"',"'",$ip); //this variable cantains sample string '','','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 :))

24 October 2012

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


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..

7 June 2012

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"; 

25 March 2012

14 March 2012

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

    SELECT * FROM table1
    ORDER BY nos desc limit 0,1) 
          )  T1 ORDER BY RAND()
if the column nos is same in multipe rows then fetch a random row..

28 January 2012

sort by occurence of anyvalue in a column

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