karimali831

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?

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");

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");

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.

I fixed that, this is final scenario now: If it is possible to insert the week values whether insert() or update() that would be great, it does seem complicated! you see each match a team has there are 7 so the week values should be inserted as follow:- week 1 values are already inserted, each week there are 4 matches. http://s15.postimage.org/s4x5i0ard/success_final.png Code: for($i = 1; $i <= 8; $i++) { switch($i) { case 1: $var_alpha = $rows_a; $let_alpha = 'a'; break; case 2: $var_alpha = $rows_b; $let_alpha = 'b'; break; case 3: $var_alpha = $rows_c; $let_alpha = 'c'; break; case 4: $var_alpha = $rows_d; $let_alpha = 'd'; break; case 5: $var_alpha = $rows_e; $let_alpha = 'e'; break; case 6: $var_alpha = $rows_f; $let_alpha = 'f'; break; case 7: $var_alpha = $rows_g; $let_alpha = 'g'; break; case 8: $var_alpha = $rows_h; $let_alpha = 'h'; break; } if($var_alpha != $max_participants) { 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"); } }

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.

I see the problem now, there cannot be the same team in the same week more than once. Each team has 7 matches each, so it is 7 weeks. It doesn't have to be in the insert query I can use update after insert. I will re-think this later. http://s16.postimage.org/th6kv9m77/PROB3.png

Oops image disappear: http://s17.postimage.org/w358jzhe5/success.png

How can I go about doing this? (last request please :-\) This is the code, works fine now with inserting rows, thanks again the extra column I want to insert data to is "wk" and the values of it should be 1-7 as shown: for($i = 1; $i <= 8; $i++) { switch($i) { case 1: $var_alpha = $rows_a; $let_alpha = 'a'; break; case 2: $var_alpha = $rows_b; $let_alpha = 'b'; break; case 3: $var_alpha = $rows_c; $let_alpha = 'c'; break; case 4: $var_alpha = $rows_d; $let_alpha = 'd'; break; case 5: $var_alpha = $rows_e; $let_alpha = 'e'; break; case 6: $var_alpha = $rows_f; $let_alpha = 'f'; break; case 7: $var_alpha = $rows_g; $let_alpha = 'g'; break; case 8: $var_alpha = $rows_h; $let_alpha = 'h'; break; } if($var_alpha != $max_participants) { 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"); } }

Final question, am I able to insert values aswell? Or can it only be from SELECt?

Perfect!! Thanks soo much you made my day !!

Take a look at my pic above I circled in red where player 1 or player 2 is Karim. The rows showing where Karim is player 1 is right, there are 7 rows there. The rows showing where Karim is player 2 is wrong, there are 3 rows. Would you be able to help please?

Thanks that worked but there is problem:- It shouldn't be generating the rows in red as they already exist, everything above is fine:- 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 = 'a' 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 = 'a' 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"); These rows will already be inserted:- It's really close though

*bump*

The SELECT works no problem but using insert into - select says SQL syntax when I attempt to run the query.

Sorry I didn't get back sooner and thanks again for the help! I am trying to put this into INSERT INTO() 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 = 'a' 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 = 'a' 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)"); I get this error: error=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.cla' at line 1 query=INSERT INTO ws_bi2_cup_matches (cupID, ladID, matchno, clan1, clan2, comment, 1on1) (( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan2 != b.clan2 AND b.type = 'gs' WHERE a.matchno = '6' AND a.type = 'gs') UNION ( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan1 != b.clan1 AND b.type = 'gs' WHERE a.matchno = '6' AND a.type = 'gs') ORDER BY clan1 DESC, versus) I have been trying the last hour to figure what's wrong?

Thanks all, this join/cross query is very confusing for me, I tried the union in mikosiko post but it seems to selecting too much! Query: SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan2 != b.clan2 AND b.type = 'gs' UNION SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan1 != b.clan1 AND b.type = 'gs' WHERE a.matchno = '6' ORDER BY `clan1` ASC I noticed it selecting rows where matchno = 5 and not 6 as stated in query?

Hey, need some help please! Standard query 1: Query 2: 1) in clan1 they vs. all of clan 2, there is a row for every ID 2) but clan1 must vs. all of every other clan1 also does this make sense? so query 2 should be something like this: clan1 clan2 1 8 1 10 1 4 1 3 1 7 1 9 3 8 3 10 3 4 3 1 3 7 3 9 etc. anyone change query 2 for me to make it like this please?

This is not my website just testing a file on it.

Hi! Please take a look at on both Firefox and Chrome: http://painstreetnation.com/index.php?site=standings&ladderID=30&start=61 On Chrome the td height is fixed where as Firefox the td height stretches because of the bars on the right? How can I get the td height to be fixed like how Chrome displays it? This is the tr codes:- echo '<tr bgcolor="'.$bg_color_dd.'" onMouseOver="this.style.backgroundColor=\''.$hover_color.'\'" onMouseOut="this.style.backgroundColor=\''.$bg_color_dd.'\'"> <td style="height:27px; width:7%;">'.$position.''.$rank.'.</td> <td style="height:27px; width:30%;"><a href="'.getname3($teamID,$ds['ladID'],$ac=0,$var="ladder").'" name="hover_dt_'.$rank.'" style="text-decoration:none; cursor:help" onmouseover="showWMTT(\'hover_dt_'.$rank.'\')" onmouseout="hideWMTT()">'.(ladderis1on1($ds['ladID']) ? getusercountry3($teamID,$ac=0) : '<img src="images/flags/'.getclancountry($teamID,$img=0).'.gif">').' <strong>'.getname2($teamID,$ds['ladID'],$ac=0,$var="ladder").'</strong></a>'.$extras.$extras1.$extras2.$extras3.$extras4.$extras5.$extras6.$extras7.$extras8.'</td> <td style="height:27px;" align="center"><strong>'.$ds['tp'].'</td></strong> <td style="height:27px;" align="center"><strong>'.$last_points.'</td></strong> <td style="height:27px;" align="center"><strong><font color="'.$wincolor.'">'.$won.'</font></strong></td> <td style="height:27px;" align="center"><strong><font color="'.$drawcolor.'">'.$draw.'</font></strong></td> <td style="height:27px;" align="center"><strong><font color="'.$loosecolor.'">'.$lost.'</font></strong></td> <td style="height:27px;" align="center"><strong><font color="'.$wincolor.'">'.getstreak($teamID,$laddID).'</font></strong></td> <td style="height:27px;" align="center">'.$rating.'</td> <td style="height:27px;" align="center">'.$fight.'</td> </tr>'; $rank++; } if($are_participants) { echo '<tr> <td style="height:27px;" bgcolor="'.$pagebg.'" align="center" colspan="10">'.$button_prev.$button_next.'</td> </tr> </table>'; } Thanks all

Thanks alot, I'll keep in mind what you said

Hey can someone use their intelligence and teach me how to change the below if statements to a loop? if($h_rows) { $rl_rows = 8; } elseif($g_rows) { $rl_rows = 7; } elseif($f_rows) { $rl_rows = 6; } elseif($e_rows) { $rl_rows = 5; } elseif($d_rows) { $rl_rows = 4; } elseif($c_rows) { $rl_rows = 3; } elseif($b_rows) { $rl_rows = 2; } elseif($a_rows) { $rl_rows = 1; } Thanks

So you cannot use insert select and insert value in one query? I would appreciate someone can help me with this as it is urgent, please

Need help with insert(), I am using the below insert query with values from SELECT but want to add data not from the select also, so instead of ...clan2, comment, 1on1 I would like to put: ...clan2, comment, 1on1, date and value of date: time() do I need join query someone show me how to please? 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, a.comment, a.1on1 FROM ".PREFIX."cup_matches a, ".PREFIX."cup_matches b WHERE a.clan2 != b.clan2 AND a.matchno = '$ID' AND b.matchno = '$ID' AND a.$type='h' AND b.$type='h')");

perfect exackly what I wanted, amazing thank you very much.