SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    for loop in mysql query

    Hi,
    How can i loop a mysql query ? I have a loop like below, But it's value is inserting only once.

    Code:
    for ($x=0; $x<$ParticiDetails; $x++){
    
    $sql="INSERT INTO table (column1,column2,column3,column4) VALUES ('$_POST[field1]','$_POST[field2]','$_POST[field3]','$_POST[field4]')";
    }

  2. #2
    SitePoint Addict Smolf3d's Avatar
    Join Date
    Aug 2004
    Location
    russia
    Posts
    226
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    see Control Structures examples :-)

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    You would have to execute the query inside the loop; what your loop does is overwrite the $sql variable over and over, and when you get to the end, you execute it - the last instance of the loop.

    Also, never do that.

    (Seriously.)

    Instead of looping the query, make the query insert multiple records at the same time.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    148
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    Something like this would work:

    Code:
    <?php
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
    
    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    
    for ($i = 0; $i < 10; $i++) {
      $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
      $mysqli->query($query);
      printf ("New Record has id %d.\n", $mysqli->insert_id);
    }
    
    /* close connection */
    $mysqli->close();
    ?>
    Like StarLion said, you need to execute the query (it's what the $mysqli->query($query) line does).

    Your code is just looping like this :

    Code:
    For 10 times loop and 
      set the variable $SQL to "insert INTO table (...) values (...)";
    End of loop
    
    Execute the $SQL query on database;
    See, at the last line, it will execute only use the last value of the SQL variable and execute it on the database.

    Again, like StarLion said, it's way more efficient to use only one query to insert multiple rows with MySQL. From the MySQL doc:
    INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
    Example:
    Code:
    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
    That way, you only "talk" to the database once, it's really more efficient than looping and inserting row by row.

    A warning:
    Be careful when you "craft" a SQL query right from parameters that you didn't validate. Your code, as I see it now, is probably vulnerable to SQL injections. I wrote about injections on my blog:
    http://www.mogosselin.com/injection-...w-to-fix-them/

    Here's how to use prepared statements to prevent SQL injections:
    http://mattbango.com/notebook/code/p...hp-and-mysqli/

    Also, make sure that you're using mysqli_* methods or PDO, NOT mysql_* methods. Read this article on sitepoint for more details: http://www.sitepoint.com/forums/show...cedural-mysqli

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks xMog for the help ,

    I have the fields is based on user selected, so how can i write this query according to that ?

    Some times it will be like INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);

    or INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6); etc... It depends on user selection, So how can i write the query ?

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Inserting the same (4 values) X number of times with one query is not hard to do. Here's a sample. Remove test data of course.
    PHP Code:
    <?php
    /////////////////////////
    // for testing. Remove //
    $ParticiDetails 3;
    $_POST['field1'] = "field1";
    $_POST['field2'] = "field2";
    $_POST['field3'] = "field3";
    $_POST['field4'] = "field4";
    /////////////////////////

    //Any validation should be done here
    $field1 trim($_POST['field1']);
    $field2 trim($_POST['field2']);
    $field3 trim($_POST['field3']);
    $field4 trim($_POST['field4']);

    $values = array();
    for (
    $x=0$x<$ParticiDetails$x++){
        
    $values[] = "('$field1','$field2','$field3','$field4')";
    }

    $sql "INSERT INTO table (column1,column2,column3,column4) VALUES"
    $sql .= implode(",",$values);
    echo 
    "$sql";
    ?>
    The result when echoing $sql
    INSERT INTO table (column1,column2,column3,column4) VALUES('field1','field2','field3','field4'),('field1','field2','field3','field4'),('field1','field2','field3','field4'),('field1','field2','field3','field4'),('field1','field2','field3','field4'),('field1','field2','field3','field4')

  7. #7
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    If however you are looping through your form fields so each would be an array it would be more like this.
    PHP Code:
    <?php
    /////////
    //If you are looping form fields building array for each name like
    // <input type="text" name="field1[]" />
    // the output would look like this
    $_POST['field1'] = array("F1_Value1","F1_Value5","F1_Value9","F1_Value13");
    $_POST['field2'] = array("F2_Value2","F2_Value6","F2_Value10","F2_Value14");
    $_POST['field3'] = array("F3_Value3","F3_Value7","F3_Value11","F3_Value15");
    $_POST['field4'] = array("F4_Value4","F4_Value8","F4_Value12","F4_Value16");


    $values = array();
    foreach(
    $_POST['field1'] as $k => $v){ 
        
    $field1 trim($_POST['field1'][$k]);
        
    $field2 trim($_POST['field2'][$k]);
        
    $field3 trim($_POST['field3'][$k]);
        
    $field4 trim($_POST['field4'][$k]);
        
    $values[] = "('$field1','$field2','$field3','$field4')";


    $sql "INSERT INTO table (column1,column2,column3,column4) VALUES"
    $sql .= implode(",",$values);
    echo 
    "$sql";
    ?>
    Output
    INSERT INTO table (column1,column2,column3,column4) VALUES('F1_Value1','F2_Value2','F3_Value3','F4_Value4'),('F1_Value5','F2_Value6','F3_Value7','F4_Value8'),('F1_Value9','F2_Value10','F3_Value11','F4_Value12'),('F1_Value13','F2_Value14','F3_Value15','F4_Value16')


Tags for this Thread

Bookmarks

Posting Permissions

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