Mysql tips and tricks

User records table: How to get all ids of peoples who have matching year and month only

First thing you have to achieve is to

"get all date and month of 'dob' column"

This can be achieved by grouping rows based on date_month extracted using the format construct.

SELECT 
DATE_FORMAT(dob, '%m-%d') AS date_month 
FROM bday 
GROUP BY DATE_FORMAT(dob, '%m-%d');

As per your question, Next is

"peoples who have matching year and month only"

ie, group the above results with condition having more than one row. This can be achieved by adding the condition "HAVING count(id) > 1"

Now the query becomes

SELECT 
DATE_FORMAT(dob, '%m-%d') AS date_month 
FROM bday 
GROUP BY DATE_FORMAT(dob, '%m-%d') 
HAVING count(id) >1);

This query returns all date_month where more than one person's dob falls.

Now your ultimate aim is to get

" id of those peoples who have matching year and month only "

This can be achieved by wrapping this results in a sub query. ie, you have to fetch all ids with date and month falls in the set of results extracted using the previous query

SELECT 
id, DATE_FORMAT(dob, '%m-%d') AS date_month
FROM bday
WHERE 
DATE_FORMAT(dob, '%m-%d') IN 
(
    SELECT 
    DATE_FORMAT(dob, '%m-%d') AS date_month 
    FROM bday 
    GROUP BY DATE_FORMAT(dob, '%m-%d') 
    HAVING count(id) >1) ;
)

My table structure:

Content of table:

Query and result:

MySql "CONCAT" usage example

$sql = "SELECT
        email,
        username,
        CONCAT('https://mysiteurl/profile/',user_id) as profile_url
        FROM `engine4_user`
        LIMIT 5000
        into outfile '$path' FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' ";

mysql update a column with an int based on order of another field

Reference: http://stackoverflow.com/a/10485817/345721

SET @rownumber = 0;
update mytable set Moneyorder = (@rownumber:=@rownumber+1)
order by MoneyOrder asc

or to do it in a single query you can try

update mytable cross join (select @rownumber := 0) r set Moneyorder = (@rownumber := @rownumber + 1) order by MoneyOrder asc

MySql - Get first n results from each category

Click the following link to find a good article on how to get first n results from each category: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

PHP - Mysql: Get last error

mysql_error

PHP - Mysql: determine which database is selected?

function mysql_current_db() {
    $r = mysql_query("SELECT DATABASE()") or die(mysql_error());
    return mysql_result($r,0);
}