getting different values from query

[shaddf]

Ihave this sql:

CREATE PROCEDURE sp_get_ourclubresults_details(IN inteamId INT,IN inseasonId INT,IN incategoryId int,IN inCompId INT,IN inVenueId varchar(1))
READS SQL DATA
BEGIN

  IF NOT ISNULL(inCompId) THEN

      select * from  

  	tbl_matches 

     where 
 	(HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
 	and LeagueSeasonID = inseasonId 
 	and  Team_catId=incategoryId
 	and  SeasonID = inseasonId
	and  CompetitionID = inCompId
	order by mDate desc;
  ELSEIF NOT ISNULL(inVenueId) THEN
      select * from  

  	tbl_matches 
     where 
 	(HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
 	and LeagueSeasonID = inseasonId 
 	and  Team_catId=incategoryId
 	and  SeasonID = inseasonId
	and  venue = inVenueId
    order by mDate desc;
  ELSEIF (NOT ISNULL(inVenueId) AND NOT ISNULL(inCompId)) THEN

      select * from  

  	tbl_matches 

     where 
 	(HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
 	and LeagueSeasonID = inseasonId 
 	and  Team_catId=incategoryId
 	and  SeasonID = inseasonId
	and  CompetitionID = inCompId
	and  venue = inVenueId
    order by mDate desc;

  ELSE

      select * from  

  	tbl_matches 
     where 
 	(HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
 	and LeagueSeasonID = inseasonId 
 	and  Team_catId=incategoryId
 	and  SeasonID = inseasonId
    order by mDate desc;
  END IF;

END $$

but when i supply :

call sp_get_ourclubresults_details('6','12','2','7','H');

iget

+-----------+-----------+------+-----------+------------+------------+---------------+-------+
| HomeScore | AwayScore | g_id | mo_th     | h_name     | a_name     | CompetitionID | venue |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+
|         1 |         0 |   15 | May  2017 | CRYSTAL SA | Sebfwa FC  |             7 | A     |
|         1 |         1 |   11 | May  2017 | Sebfwa FC  | PARK YARD  |             7 | H     |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+

and when i supply:

call sp_get_ourclubresults_details('6','12','2','7','');

  i get this too

+-----------+-----------+------+-----------+------------+------------+---------------+-------+
| HomeScore | AwayScore | g_id | mo_th     | h_name     | a_name     | CompetitionID | venue |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+
|         1 |         0 |   15 | May  2017 | CRYSTAL SA | Sebfwa FC  |             7 | A     |
|         1 |         1 |   11 | May  2017 | Sebfwa FC  | PARK YARD  |             7 | H     |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+
2 rows in set (0.00 sec)

how can i solve this?

[Psycho]

Not sure what problem you are trying to solve. The results are exactly as they should be based on the logic. I have no idea what you expect to happen to offer a solution.

 

You have four conditions in your procedure as follows

IF NOT ISNULL(inCompId) THEN
  >> Condition 1
ELSEIF NOT ISNULL(inVenueId) THEN
  >> Condition 2
ELSEIF (NOT ISNULL(inVenueId) AND NOT ISNULL(inCompId)) THEN
  >> Condition 3
ELSE
  >> Condition 4
END IF;

In both procedure calls you pass a value for inCompId which determines the result of Condition 1. Since both calls have a value for that parameter - both will use the logic in the first condition. Additionally, both calls pass the exact same values for all parameters except the last one (inVenueId). That value is not used in the Condition 1 part of the query.

 

select * from
tbl_matches 
where 
  (HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
   and LeagueSeasonID = inseasonId 
   and  Team_catId=incategoryId
   and  SeasonID = inseasonId
   and  CompetitionID = inCompId
order by mDate desc;

Therefore, both scenarios are using the same condition and the same values - resulting in the same output. If that is not the correct result, you need to provide what you expect to happen.

Edited July 11, 2017 by Psycho

[shaddf]

Not sure what problem you are trying to solve. The results are exactly as they should be based on the logic. I have no idea what you expect to happen to offer a solution.

 

You have four conditions in your procedure as follows

IF NOT ISNULL(inCompId) THEN
  >> Condition 1
ELSEIF NOT ISNULL(inVenueId) THEN
  >> Condition 2
ELSEIF (NOT ISNULL(inVenueId) AND NOT ISNULL(inCompId)) THEN
  >> Condition 3
ELSE
  >> Condition 4
END IF;

In both procedure calls you pass a value for inCompId which determines the result of Condition 1. Since both calls have a value for that parameter - both will use the logic in the first condition. Additionally, both calls pass the exact same values for all parameters except the last one (inVenueId). That value is not used in the Condition 1 part of the query.

select * from
tbl_matches 
where 
  (HomeTeamID=inteamId  OR AwayTeamID=inteamId) and mDate<=date(now()) 
   and LeagueSeasonID = inseasonId 
   and  Team_catId=incategoryId
   and  SeasonID = inseasonId
   and  CompetitionID = inCompId
order by mDate desc;

Therefore, both scenarios are using the same condition and the same values - resulting in the same output. If that is not the correct result, you need to provide what you expect to happen.

i expect condition 3

call sp_get_ourclubresults_details('6','12','2','7','H');

 

to produce this

 

+-----------+-----------+------+-----------+------------+------------+---------------+-------+
| HomeScore | AwayScore | g_id | mo_th     | h_name     | a_name     | CompetitionID | venue |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+
|         1 |         1 |   11 | May  2017 | Sebfwa FC  | PARK YARD  |             7 | H     |
+-----------+-----------+------+-----------+------------+------------+---------------+-------+

[Jacques1]

The whole logic is messed up. As soon as you supply a competition ID, the first condition is fulfilled, so the function stops there. Additional parameters aren't even checked, you always get the first query. You need the opposite order: First a check if all parameters are present, then a check if at least the competion ID is present etc.

 

Additionally, you don't seem to understand the difference between the empty string and NULL. An empty string is still a string. It's a real value, not NULL. So even if you fix the function itself, all of your function calls will trigger the same condition.

 

I would throw away the function. It increases the complexity (read: number of bugs), you aren't taking advantage of the function concept (instead of reusing the common parts of the query, you've duplicated them four times), and it's just not very useful for such a trivial query. Stored procedures should generally be avoided. They're difficult to manage, difficult to debug, difficult to modify. Use plain old PHP functions. There's also the MVC pattern as a more modern way of handling complex data.

[Psycho]

The whole logic is messed up. As soon as you supply a competition ID, the first condition is fulfilled, so the function stops there.

 

@shaddf: As I stated in more elaborate detail and as Jacques1 has echoed in a succinct sentence, the results are correct per your input. In both cases the first condition is TRUE because you pass a value for inCompId in both calls. I missed the whole problem with the fact that you are doing a NULL check but passing empty strings.