Not sure how can do this? INSERT INTO()

Hello!

I want to use insert rows into the table so that every user in clan1 matches clan2.

Take a look at the attatchment:

At the moment you see:

clan1:

2620

2617

2609

2629

clan2:

2627

2619

2612

2630

What I need is INSERT INTO() query that will insert multiple rows so that so that every user in clan1 is competiting against every user in clan2.

e.g for user 2620, add 3 rows.

new row 1 clan1: 2620

new row 1 clan2: 2619

new row 2 clan1: 2620

new row 2 clan2: 2612

new row 1 clan1: 2620

new row 1 clan2: 2630

as there are 4 users in clan1 in total, it should insert 12 rows?

Hope you understand.

Thanks for any help, really need this one.

[attachment deleted by admin]

Hi

If I understand correctly you have a pair of clients and you need every combination of members of one clan against members of the other clan?

If so it looks like you just need a cross join .Something like:-

SELECT a.ClanMember AS Clan1Member, b.ClanMember as Clan2Member
FROM Members a
CROSS JOIN Members b
WHERE a.Clan = 2620
AND b.Clan = 2619

All the best

Keith

Yes but I need to insert the rows.

Go to http://cupaddon.com/index.php?site=groups&laddID=1 and look under group b

I need the INSERT query to insert 12 rows so that :

test8 is also against test7, test4, test16

test5 is also against test13, test4, test16

test1 is also against test13, test7, test16

test15 is also against test4, test7, test13

Hi

The above is the basics, and should be useable to base an insert on.

I can do a bit more but I would need to table layouts and a few same bits of data to go on.

All the best

Keith

You can see the table structure in my first post - attatchment.

Can you base my structure on your select query then?

SELECT a.ClanMember AS Clan1Member, b.ClanMember as Clan2Member

FROM Members a

CROSS JOIN Members b

WHERE a.Clan = 2620

AND b.Clan = 2619

Then I can see what I can do and get back to you if I need further help.

Thanks again.

Hello!

 

 

I want to use insert rows into the table so that every user in clan1 matches clan2.

Take a look at the attatchment:

 

At the moment you see:

 

clan1:

 

2620

2617

2609

2629

 

clan2:

 

2627

2619

2612

2630

 

What I need is INSERT INTO() query that will insert multiple rows so that so that every user in clan1 is competiting against every user in clan2.

 

e.g for user 2620, add 3 rows.

 

new row 1 clan1: 2620

new row 1 clan2: 2619

 

new row 2 clan1: 2620

new row 2 clan2: 2612

 

new row 1 clan1: 2620

new row 1 clan2: 2630

 

as there are 4 users in clan1 in total, it should insert 12 rows?

 

Hope you understand.

 

Thanks for any help, really need this one.

something is not matching up between your statements and your examples... 

you said:

every user in clan1 is competiting against every user in clan2.

and your final example is letting out the first user of clan 2 (2627)...

don't follow... you want all the combinations after let out the first Id of clan 2? ..

if you want all the combinations this will do it

INSERT INTO table

  (SELECT a.fieldclan1Id, b.fieldclan2id

      FROM clan1table a, clan2table b)

INSERT INTO table
   (SELECT a.fieldclan1Id, b.fieldclan2id
       FROM clan1table a, clan2table b)

From table 1 and 2 ? My attatchment shows only one table.

Sorry, not sure why no one understands what I'm trying to do...

I will just manually do it then you should understand.

my bad... I didn't look your attachment.

your INSERT ... what table is supposed to use... the same or other?

Uses same table, now I have done it manually.

Surely this brings some understanding:

Don't relate the first attatchment to this one.

* Green is how it was.

* Red are the 12 rows I inserted manually, as you can see it matches every combination (clan1, clan2.)

[attachment deleted by admin]

Ok...  this select should have done the same

INSERT INTO table (clan1, clan2) (SELECT a.clan1, b.clan2
  from table a, table b
  WHERE a.clan2 != b.clan2)

Thanks again but query failed

Have I done something wrong?

        safe_query("INSERT INTO ".PREFIX."cup_matches (clan1, clan2) 
                  (SELECT a.clan1, b.clan2 FROM ".PREFIX."cup_matches a, ".PREFIX."cup_matches b 
                   WHERE a.clan2 != b.clan2 AND matchno='$ID' AND ($get_groups)");

well.. because you introduced 2 news AND to the original query ...

AND matchno = '$ID' 

would trigger a mysql error because it is ambiguous in the select... it should be

AND a.matchno = '$ID'

AND ($get_groups) 

No idea what it does therefore I can tell you that is will cause an error or not. In addition you didn't say if your other table's fields are required or not (NULL/NOT NULL) hence that could also trigger an error.

probably you are getting a mysql error... what it say?

mysql_query("INSERT INTO ".PREFIX."cup_matches (clan1, clan2) (SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '$ID'");

Does nothing. No errors but no INSERT

are you controlling the errors in your code?  ... if you don't and your query is failing nothing is going to tell you what is wrong.

at least you should have something like this (basic error control and stop the script)

mysql_query("INSERT INTO ".PREFIX."cup_matches (clan1, clan2) (SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '$ID'")
or die("Query Error : " .mysql_error());

why you don't post the rest of your relevant code ?... in that way we can help you better.

There is not much too it, it is simply this:

     if($staging && $dv['anzahl'] >= $ds['maxclan']+$ds['maxclan'])  

mysql_query("INSERT INTO ".PREFIX."cup_matches (clan1, clan2) (SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '$ID'")
or die("Query Error : " .mysql_error());

And the error doesn't tell you much:

Query 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 '' at line 3

Thanks again!

  ... you just know how to read the error 

This line:

WHERE a.clan2 != b.clan2 AND a.matchno = '$ID'")

  has something wrong... can you spot it?  (look the entire query ... compare with what I gave to you and you should be able to fix it....

Uhh for some reason I can't spot the error ..

I guess I'm use to the safe_query() and error control with a CMS. 

What about this?

WHERE a.clan2 != b.clan2 AND a.matchno = '$ID')")

Either the query is going crazy now.. or my internet is slow.

huhh?! generated 90000 rows!!

Now 503 Service Temporarily Unavailable

how many rows did you have originally in you table with matchno = '$ID' ?  remember that the query is making a cross join between all the rows in the table matching the conditions that you defined... if those conditions are incorrect the result is expected.... I suggest you to try just the select (without the INSERT) first and be sure that is is returning the rows/results that you want.

Result is expected? I only have 800-900 rows.

Ok, to test it:

$query = mysql_query("SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '1' AND ladID='b'");

while($ds=mysql_fetch_array($query)) {

echo 'c1 = '.$ds['clan1'].'<br>';
echo 'c2 = '.$ds['clan2'].'<br>';

}

Without 

AND ladID='b' 

there is just thousands of $ds['clan1'] and $ds['clan2']

With

AND ladID='b' 

there is nothing?

If you take a look at the table structure you can see rows that are "b" in column ladID are the only rows I want altered.

Thanks.

Oh how could I forget the

AND a.ladID='b'

But go to

http://cupaddon.com/index.php?site=groups&laddID=1

and look at the disaster it is doing.  :'(

Stop load immediately.

ok.. don't panic... everything can be fixed.

you see how a little piece of information that you miss yo tell could cause that anyone give you wrong alternatives?

the first thing that you have to do now is delete all the incorrect rows that the query produced in all of them the common characteristics is that the field matchno should be null (0 or NULL)... if you didn't have ANY rows before with matchno NULL it would clean up the mess.

 DELETE from PREFIX.cup_matches WHERE matchno IS NULL  // replace the proper PREFIX value.

  try this first... and we can resume to fix your INSERT after you clean the unwanted rows.

after you delete the unwanted rows test this query (I did and works fine)

$query = mysql_query("SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '1' AND b.ladID='b'");

before you try the query of my last post.... are you sure that you have 'b' in the column laid only for those records?... because if not the query is not correct either.

it will solve that problem

$query = mysql_query("SELECT a.clan1, b.clan2
  from ".PREFIX."cup_matches a, ".PREFIX."cup_matches b
  WHERE a.clan2 != b.clan2 AND a.matchno = '1' AND b.matchno = '1' AND b.ladID='b'");