[i'm jonas]

for some reason my loop stops when i = 1 :s


Statement s7 = MySql.connection.createStatement();
for(int i = 0; i < 50 ; i++) {
String test = "INSERT ignore INTO `testtable` (`name`,`Item"+i+"`,`Amount"+i+"`) values ('"+getUsername()+"','"+getItem(i)+"','"+getAmount (i)+"')";
s7.executeUpdate(test);
}

table: item1 amount1 item2 amount2 item3 amount3 ...
Can anyone help me please???

[mike_bike_kite]

It would be helpful if you gave a definition of your table but ...

If you print out the sql before running it you'll see that your inserting into fields name,Item1,Amount1 on the first iteration. On the next iteration you're inserting into fields name,Item2,Amount2 etc. Should your field names be changing each time?

Best bet is to write out the SQL you want to produce. Then get your program to generate this SQL for each loop. At the moment you're trying to do it all at once and confusing yourself.

Mike

[georgev]

What language are we using here, I'm guessing C#.NET? This is probably better suited in that topic as oppsed to the mysql one. Post back here and someone will move the thread.

P.S. if you're fields are called Item1, Item2, Item3, etc then you have a fatal flaw in your database design.

[i'm jonas]

Well this is sql in java
I tried this also:

Statement s7 = MySql.connection.createStatement(); 
	for(int i = 0; i < 50 ; i++) { //values ('"+p.getUsername()+"','"+getBankID(i,p)+"','"+getBankAmount(i,p)+"')";
	String insertBank = "INSERT ignore INTO `bank` (`playerName`,`bankItem"+i+"`,`bankItemAmount"+i+"`) values ('"+p.getUsername()+"',5,4)";
	s7.executeUpdate(insertBank);						
	}

This is the result:
name item1 amount1 item2 amount2
test 5 4 NULL NULL ...
the name is primary key
the item and amount is allowed to be NULL

Anyone know what the problem is?
I'm not sure about the 'ignore' maybe thats the problem?

thnx in advance

Jonas

[georgev]

I think the problem (other than the shocking table design) is in the fact that you're creating a single statement outside of the loop and binding it's value within the loop.

Moved to JAVA topic

[i'm jonas]

Even if i insert the connection in the loop
it doesn't change anything ..

Thanks for reply anyways
Jonas

[mike_bike_kite]

I'll repeat :-

Best bet is to write out the SQL you want to produce. Check it works. Then get your program to generate this SQL for each loop. Just print out the sql first and then once you're happy with the SQL then you try to run it. At the moment you're trying to do it all at once and confusing yourself.

Mike

[i'm jonas]

pff, i don't see why its not working
and i don't understand what you mean

Jonas

[georgev]

Mike is saying; can you provide examples of the full SQL statement, instead of the dynamic code you've used above.

[i'm jonas]

if i understand u correct, i want this:

INSERT INTO bank (`playerName`,`bankItem0`,`bankItemAmount0`,`bankI tem1`,`bankItemAmount1` ... ) values ('"+p.getUsername()+"',getItem(0),getAmount(0),get Item(1),getAmount(1) ...)";
s7.executeUpdate(insertBank);

And i need that to go from 0 - 50
si i tought it would be possible with a loop, so i don't need to type that 50 times.. , but it doesn't

Hope you can help me

Thanks for reading
Jonas

}

[mike_bike_kite]

if i understand u correct, i want this:

INSERT INTO bank (`playerName`,`bankItem0`,`bankItemAmount0`,`bankItem1`,
    `bankItemAmount1` ... ) values ('"+p.getUsername()+"',getItem(0),
    getAmount(0),get Item(1),getAmount(1) ...)";
s7.executeUpdate(insertBank); 

Nope, u dont understand. I wanted to see what SQL you were hoping to generate but what you’ve done is just cut out the code from your original program and reposted it. I’m afraid your code hasn’t improved by itself in the meantime. Does your table (bank) have fields called bankItem1, bankItem2 … bankItem50 ? If it doesn’t then your SQL is wrong where it lists the fields to insert into.

Have you tried printing out the insertBank variable (the variable that holds your SQL) rather than executing it. Have you tried running this SQL through MySQL by hand – just to see if it works? If it doesn’t then it’s hardly worth writing a program to generate this SQL.

Why don’t you :

• Print out the SQL rather than run it – that way you’ll see what’s being passed to MySQL and maybe you’ll see the issues.
• If you can’t see the issues then simply take one of the insert statements and try running it through MySQL yourself. Keep changing the code around until you can get it to run.
• Now go back to your original code and alter it so it produces the improved
  SQL.

I personally prefer using PHP rather than Java with MySQL – there are also lots of good books available that easily explain how the SQL language works, how to use MySQL and how to work with PHP.

Mike

[georgev]

Originally Posted by mike_bike_kite

Does your table (bank) have fields called bankItem1, bankItem2 … bankItem50 ? If it doesn’t then your SQL is wrong where it lists the fields to insert into.

And if it does - you need to redesign your table.

[i'm jonas]

it does have this:
playerName
bankItem0
bankItemAmount0
bankItem1
bankItemAmount0
...
bankItem49
bankItemAmount49

When i print out the 'i', it prints it out 50 time
So idk what's wrong, i do some more research and do what u said

Thanks;

Jonas