shaddf Posted July 11, 2017 Share Posted July 11, 2017 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? Quote Link to comment https://forums.phpfreaks.com/topic/304299-getting-different-values-from-query/ Share on other sites More sharing options...
Psycho Posted July 11, 2017 Share Posted July 11, 2017 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/304299-getting-different-values-from-query/#findComment-1548219 Share on other sites More sharing options...
shaddf Posted July 12, 2017 Author Share Posted July 12, 2017 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 | +-----------+-----------+------+-----------+------------+------------+---------------+-------+ Quote Link to comment https://forums.phpfreaks.com/topic/304299-getting-different-values-from-query/#findComment-1548253 Share on other sites More sharing options...
Jacques1 Posted July 12, 2017 Share Posted July 12, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/304299-getting-different-values-from-query/#findComment-1548254 Share on other sites More sharing options...
Psycho Posted July 13, 2017 Share Posted July 13, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/304299-getting-different-values-from-query/#findComment-1548288 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.