Illusion Posted May 17, 2012 Share Posted May 17, 2012 if you were clear about your requirements while starting a thread .....it would have saved lot of time for us. After so many replies , you are telling us that the query you had in the first step is what you required and now come up with a new requirement . Its waste of time Read the forums rules once again. Unless you are clear about your requirements and provide us sample output with dummy data , don't expect any reply. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1346382 Share on other sites More sharing options...
karimali831 Posted May 17, 2012 Author Share Posted May 17, 2012 if you were clear about your requirements while starting a thread .....it would have saved lot of time for us. After so many replies , you are telling us that the query you had in the first step is what you required and now come up with a new requirement . Its waste of time Read the forums rules once again. Unless you are clear about your requirements and provide us sample output with dummy data , don't expect any reply. Sorry sir, English not my first and I think you miss-understood something. there is two requests, one mikosiko gave the answer too but he did typo error a.clan1, b.clan1 AS 'versus', when it was suppose to be a.clan2, b.clan1 AS 'versus', my first requirement is working fine, my second was to insert values of the weeks and it is hard to explain that is why I made image. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1346413 Share on other sites More sharing options...
Illusion Posted May 18, 2012 Share Posted May 18, 2012 Algorithm for assigning week is something you have to implement outside mysql. And it depends on how many players are there in each group (MIN and MAX possible values) and number of players in each group are always equal or not ? A player plays only one match in a week is strictly true or it changes based on number of matches. you haven't provide any of these details .....and still expect understand or assume everything correctly. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1346561 Share on other sites More sharing options...
fenway Posted May 19, 2012 Share Posted May 19, 2012 Remove the extra parents. But, but, mother's day is this Sunday! Surely it's not the time to remove parents! :-P I sit corrected. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1346845 Share on other sites More sharing options...
karimali831 Posted May 30, 2012 Author Share Posted May 30, 2012 I just noticed something I can't believe I didn't spot, it generates 7 matches for clan1 which are all the matches but for clan2 is only generates 4 matches if you take a look at image in previous post. That is because clan2 player is not playing match with another clan2 player ... in that case you need to have another union can you show me how to add this other union please? Because the query it still duplicated same matches:- http://s17.postimage.org/mvueq1rql/prob4.png Code: safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) (SELECT a.$type, a.$type_opp, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan2 != b.clan2 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') UNION (SELECT a.$type, a.$type_opp, a.matchno, a.clan2, b.clan1 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan1 != b.clan1 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') ORDER BY clan1 DESC, versus"); Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1349896 Share on other sites More sharing options...
karimali831 Posted May 30, 2012 Author Share Posted May 30, 2012 Please ignore previous post!! It does not duplicate anything, clan1 rows are all generated (7 per team) but in clan2 only 4 matches each instead of 7: Mentioned in Illusion post I need ANOTHER UNION? http://s14.postimage.org/60uhbpe0f/prob5.png This is the code:- safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) (SELECT a.$type, a.$type_opp, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan2 != b.clan2 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') UNION (SELECT a.$type, a.$type_opp, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan1 < b.clan1 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') ORDER BY clan1 DESC, versus"); Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1349905 Share on other sites More sharing options...
karimali831 Posted May 30, 2012 Author Share Posted May 30, 2012 Tried using my brain this time... it seems ok! Need to make sure there is no problem, this is the long query:- safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) (SELECT a.$type, a.$type_opp, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan2 != b.clan2 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') UNION (SELECT a.$type, a.$type_opp, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan1 < b.clan1 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') UNION (SELECT a.$type, a.$type_opp, a.matchno, a.clan2, b.clan2 AS 'versus', a.comment, a.1on1 FROM ".PREFIX."cup_matches a JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' AND b.matchno = '$ID' AND a.clan2 < b.clan2 AND b.type = 'gs' WHERE a.matchno = '$ID' AND a.type = 'gs') ORDER BY clan1 DESC, versus"); Fine? Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1349906 Share on other sites More sharing options...
karimali831 Posted May 30, 2012 Author Share Posted May 30, 2012 Algorithm for assigning week is something you have to implement outside mysql. And it depends on how many players are there in each group (MIN and MAX possible values) and number of players in each group are always equal or not ? A player plays only one match in a week is strictly true or it changes based on number of matches. you haven't provide any of these details .....and still expect understand or assume everything correctly. Finally the query is ok now The second part of it is to split in weekly divisions.. Ok, -> each group will have even amount of teams -> 7 matches per team is for 7 weeks, each match per week -> league is not limited to max signups if league is for 16 teams there will only be 2 groups, a and b: min: 4 per group max: 8 per group -> group a which holds up to 8 teams -> group b which holds up to 8 teams -> all vs. all therefore is 16/2 * 7 weeks = 56 max matches (28 matches for group a and 28 for group b) if league is for 32 teams there will only be 4 groups, a, b, c and d: min: 4 per group max: 8 per group -> each group holds up to 8 teams each -> all vs. all therefore is 32/2 * 7 weeks = 112 matches (112 / 4 groups = 28 matches in each group) if league is for 64 teams there will be 8 groups (a-h): min: 4 per group max: 8 per group -> each group holds up to 8 teams each -> all vs. all therefore is 64/2 * 7 = 224 matches (224 / 8 groups = 28 matches in each group) if league is for 128 teams there will be 8 groups (a-h); min: 8 per group max: 16 per group -> each group holds up to 16 teams each -> all vs. all therefore is 128/2 * 7 = 448 matches (448 / 8 groups = 56 matches in each group) Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1349927 Share on other sites More sharing options...
Illusion Posted May 31, 2012 Share Posted May 31, 2012 You are totally lost. Its basic elementary mathematical problem. (i) If you have more than two groups then assuming that you have proper mechanism to group the players then your query needs to be executed n-1 ( n be the number of groups ) times with current logic . Assuming a,b,c,d as groups : a-b, a-c, a-d, b-c, b-d, c-d (ii) Assuming that you have a1,a2,a3 in group a and b1,b2,b3 in group your current query fetches following combinations only a1-b2 a1-b3 a2-b1 a2-b3 a3-b1 a3-b2 a1-a2 a1-a3 a2-a3 b1-b2 b1-b3 b2-b3 as you can see following combinations are ignored a1-b1 a2-b2 a3-b3 if you have n number of teams in each group then it will fetch n(n-1) + 2 (n-1) records only because o the condition a.clan2 != b.clan2. If you remove that condition you will get missed 3 combinations also assuming that those 3 are also valid for your requirement ... then in total you get n*n + 2 (n-1) so if you have 8 players in each group then total possible matches are 8*8 + 2 (8-1) = 78 Note that your current query fetches only 8*7 + 2(8-1)=70 As per your calculation given below its only 56 - which is wrong as per my understanding. if n^2 + 2(n-1) are total number of matches then each player plays 2n-1 matches .. which implies that all the matches will be played in 2n-1 weeks. Now you can order the results by clan1 and use simple math to assign week. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1350094 Share on other sites More sharing options...
karimali831 Posted May 31, 2012 Author Share Posted May 31, 2012 I'm no wizz at logics and mathematics but you are making it seem more complicated than it seems, the query fetches ALL combinations, there is two unions in the query now and if max is reached it will always have 28 matches per group. if 4 matches in each group (which will be for 8 players) 4 times 7 = 28 Where in this pic do you see a missed combination? Each player has 7 matches each. http://s13.postimage.org/i2apegtz9/solved.png The complicated theory now is assigning weeks.. Quote Link to comment https://forums.phpfreaks.com/topic/261956-need-help-getting-right-query/page/2/#findComment-1350139 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.