Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Exclamation Unanswered: loop stops inserting !

    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???

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    7
    Well this is sql in java
    I tried this also:

    Code:
    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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2008
    Posts
    7
    Even if i insert the connection in the loop
    it doesn't change anything ..

    Thanks for reply anyways
    Jonas

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  8. #8
    Join Date
    Jun 2008
    Posts
    7
    pff, i don't see why its not working
    and i don't understand what you mean

    Jonas

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mike is saying; can you provide examples of the full SQL statement, instead of the dynamic code you've used above.
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2008
    Posts
    7
    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

    }

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    if i understand u correct, i want this:
    Code:
    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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote 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.
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2008
    Posts
    7
    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
    Last edited by i'm jonas; 06-25-08 at 09:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •