php - Mysql function returns empty result

Solution:

1. set da = year-month; -> year and month are declared as varchars, however, in this context they would be interpreted as integers. If you wanted to create a string such as '2016-05', then use the concat() function:concat(year,'-',month). I have absolutely no idea why you declared da as bigint. It should be declared as varchar.

2. date_format(users.doj,'%Y-%m') <= 'da' -> this expression compares a string in year-month format with the string 'da'. Sql is not php, the variable names will not be expanded within a string. So, the expression should be:date_format(users.doj,'%Y-%m') <= da.

3. I also believe that you mixed up the order of the tables in the join. You should not really have a target for a user that does not exist.

4. If there is no such record that would satisfy your query, then the ifnull() function in the select statement will not even run, this is why you get an empty result in certain occasions. (Although how you tested your function is beyond me, given the issues above). Apparently, you expect to have a maximum of one record returned. In this case I suggest to use max() or min() function within the ifnull() because they guarantee to return a value:SELECT ifnull(max(user_target.monthly_target),0) as monthly_target ...

Solution:

I found a solution... I made a mistake in concat of year and month. That makes a issue. This is my corrected code.

DELIMITER $$
CREATE FUNCTION `monthtargetbyuser`(`userId` BIGINT,month int(11),year int(11)) RETURNS BIGINT

BEGIN 

DECLARE target,da varchar(50);
 set da =concat(year,'-',month);

        SET target = (SELECT ifnull(min(user_target.monthly_target),0) as monthly_target from user_target left join users on users.id=user_target.user_id where date_format(users.doj,'%Y-%m') <= da and user_target.year=year and year(users.doj)>0 and users.locked !=1 and users.id =userId ); 

   RETURN target;

END 
Copy code

Thanks a lot. @Shadow