Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Can someone help explain multiple insert?

    I've got a problem sorting out an insert that cycles through multiple rows of a form, and then constructs and executes the insert. I've Googled 'mysql php insert multiple rows form', and while there's a quite a few articles, none of them seem particularly well commented or explained, so I'm hoping someone can shed light on my confusion.

    I have a database table called mailinglist which holds data about which people get invited to specific events for a client of mine. It has five columns - mailinglistID (the primary key) mailnglistnameID, locationID and clientID (foreign keys) and isCard (a boolean).

    I found some sample code at Object Mix, which I used as the basis of my attempt, after cleaning up the HTML.

    My form HTML is:
    Code:
    <!DOCTYPE html>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title>Untitled Document</title>
    </head>
    <body>
    <form action="insert1.php" method="post">
      <table>
        <caption>Test form to check multiple insert:</caption>
        <tr>
          <th>Mailing List Name ID</th>
          <th>Exhibition ID</th>
          <th>Client ID</th>
          <th>Send card</th>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[]" id="mailinglistnameID[]" /></td>
          <td><input type="text" name="locationID[]" id="locationID[]" /></td>
          <td><input type="text" name="clientID[]" id="clientID[]" /></td>
          <td><input type="checkbox" name="isCard[]" id="isCard[]" value="1" /></td>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[]" id="mailinglistnameID[]" /></td>
          <td><input type="text" name="locationID[]" id="locationID[]" /></td>
          <td><input type="text" name="clientID[]" id="clientID[]" /></td>
          <td><input type="checkbox" name="isCard[]" id="isCard[]" value="1" /></td>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[]" id="mailinglistnameID[]" /></td>
          <td><input type="text" name="locationID[]" id="locationID[]" /></td>
          <td><input type="text" name="clientID[]" id="clientID[]" /></td>
          <td><input type="checkbox" name="isCard[]" id="isCard[]" value="1" /></td>
        </tr>
      </table>
      <p>
        <input type="submit" name="Submit" id="Submit" value="Submit" />
      </p>
    </form>
    </body>
    </html>
    while the insert1.php ended up as:
    PHP Code:
    <?php
    $con 
    mysql_connect("server name","user","password");
    if (!
    $con)
    {
    die(
    'Could not connect: ' mysql_error());
    }

    mysql_select_db("schema name"$con);


    //Assign each array to a variable
    foreach($_POST['mailinglistnameID'] as $row=>$mailinglistnameID)
    {
    $mailinglistnameID=$mailinglistnameID;
    $locationID=$_POST['locationID'][$row];
    $clientID=$_POST['clientID'][$row];
    $isCard=$_POST['isCard'][$row];
    }


    //enter rows into database
    foreach($_POST['mailinglistnameID'] as $row=>$mailinglistnameID)
    {
    $mailinglistnameID=mysql_real_escape_string($mailinglistnameID);
    $locationID=mysql_real_escape_string($_POST['locationID'][$row]);
    $clientID=mysql_real_escape_string($_POST['clientID'][$row]);
    $isCard=($_POST['isCard'][$row]);
    }


    $query "INSERT INTO mailinglist (mailinglistnameID, locationID, clientID, isCard)
    VALUES ('.$mailinglistnameID.','.$locationID.','.$clientID.','.$isCard.')"
    ;


    if (!
    mysql_query($query,$con))
    {
    die(
    'Error: ' mysql_error());
    }
    echo 
    "$row record added";

    mysql_close($con)
    ?>
    However, running it only results in the last row of the form being inserted into the table, and the isCard boolean isn't inserted if the checkbox is ticked. Also, I'm a little concerned that, while I've read that giving the name of the field a '[]' suffix prepares PHP for inserting the contents into an array, that having multiple HTML elements with the same id invalidates the HTML, so I'm not sure that I'm doing it right…

    I've been looking at this since first thing yesterday, and I'm just going round in circles now. If anyone could point out where I'm going wrong, I'd be grateful.
    Last edited by butlins; 07-21-2011 at 12:06 PM.
    If anyone asks my boss, this counts as work, okay?

  • #2
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Okay - thought I'd try answering my own question about the repeated [] making the ids invalid and put numbers in there, which I can insert on my final from using a loop at run-time. So now my test form looks like this:
    Code:
    <!DOCTYPE html>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title>Untitled Document</title>
    </head>
    <body>
    <form action="insert1.php" method="post">
      <table>
        <caption>Test form to check multiple insert:</caption>
        <tr>
          <th>Mailing List Name ID</th>
          <th>Exhibition ID</th>
          <th>Client ID</th>
          <th>Send card</th>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[1]" id="mailinglistnameID[1]" /></td>
          <td><input type="text" name="locationID[1]" id="locationID[1]" /></td>
          <td><input type="text" name="clientID[1]" id="clientID[1]" /></td>
          <td><input type="checkbox" name="isCard[1]" id="isCard[1]" value="1" /></td>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[2]" id="mailinglistnameID[2]" /></td>
          <td><input type="text" name="locationID[2]" id="locationID[2]" /></td>
          <td><input type="text" name="clientID[2]" id="clientID[2]" /></td>
          <td><input type="checkbox" name="isCard[2]" id="isCard[2]" value="1" /></td>
        </tr>
        <tr>
          <td><input type="text" name="mailinglistnameID[3]" id="mailinglistnameID[3]" /></td>
          <td><input type="text" name="locationID[3]" id="locationID[3]" /></td>
          <td><input type="text" name="clientID[3]" id="clientID[3]" /></td>
          <td><input type="checkbox" name="isCard[3]" id="isCard[3]" value="1" /></td>
        </tr>
      </table>
      <p>
        <input type="submit" name="Submit" id="Submit" value="Submit" />
      </p>
    </form>
    </body>
    </html>
    And the isCard boolean is now being uploaded correctly, but it's still only inserting the final row, so there's got to be a problem in how I'm constructing my array.
    If anyone asks my boss, this counts as work, okay?

  • #3
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    use each array to save it. so, just get it one id to get all, and in each id, do insert.

    PHP Code:
    <?php
    $con 
    mysql_connect("server name","user","password");
    if (!
    $con)
    {
    die(
    'Could not connect: ' mysql_error());
    }

    mysql_select_db("schema name"$con);


    //Assign "each" array to a variable "and save it"
    foreach($_POST['mailinglistnameID'] as $row=>$mailinglistnameID)
    {
        
    $mailinglistnameID=mysql_real_escape_string($mailinglistnameID);
        
    $locationID=mysql_real_escape_string($_POST['locationID'][$row]);
        
    $clientID=mysql_real_escape_string($_POST['clientID'][$row]);
        
    $isCard=mysql_real_escape_string($_POST['isCard'][$row]);
        
        
    $query "INSERT INTO mailinglist (mailinglistnameID, locationID, clientID, isCard)
        VALUES ('.$mailinglistnameID.','.$locationID.','.$clientID.','.$isCard.')"
    ;

        if (!
    mysql_query($query,$con))
        {
        die(
    'Error: ' mysql_error());
        }
        echo 
    "$row record added";
    }

    mysql_close($con)
    ?>
    hope it helps

  • Users who have thanked XterM for this post:

    butlins (07-22-2011)

  • #4
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Thanks for being so helpful - that works perfectly. I'm trying to create a similar multiple update, using that as a template and have ended up with
    PHP Code:
    <?php 
    $con 
    mysql_connect("server name","user","password"); 
    if (!
    $con

    die(
    'Could not connect: ' mysql_error()); 


    mysql_select_db("schema name"$con); 


    //Assign "each" array to a variable "and save it" 
    foreach($_POST['mailinglistnameID'] as $row=>$mailinglistnameID

        
    $mailinglistnameID=mysql_real_escape_string($mailinglistnameID); 
        
    $locationID=mysql_real_escape_string($_POST['locationID'][$row]); 
        
    $clientID=mysql_real_escape_string($_POST['clientID'][$row]); 
        
    $isCard=mysql_real_escape_string($_POST['isCard'][$row]); 
         
        
    $query "UPDATE mailinglist SET locationID='$locationID[$row]' AND clientID='$clientID[$row]' AND isCard='$isCard[$row]' WHERE mailinglistnameID='$mailinglistnameID[$row]'";

        if (!
    mysql_query($query,$con)) 
        { 
        die(
    'Error: ' mysql_error()); 
        } 
        echo 
    "$row record updated"


    mysql_close($con
    ?>
    But, even though the page seems to be doing something after clicking the submit button, nothing gets updated. Again, any help you could give would be gratefully received.
    If anyone asks my boss, this counts as work, okay?

  • #5
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    You don't need to read array in update vars,
    PHP Code:
    locationID='$locationID[$row]' 
    cause $locationID is a variable, and it take from $_POST['locationID'][$row];

    so, you have get it variable values and just need to update, no need to read each array again.

    second problem, to update multipel field, separated it with comma, no used "AND".

    PHP Code:
    <?php 
    $con 
    mysql_connect("server name","user","password"); 
    if (!
    $con

    die(
    'Could not connect: ' mysql_error()); 


    mysql_select_db("schema name"$con); 


    //Assign "each" array to a variable "and save it" 
    foreach($_POST['mailinglistnameID'] as $row=>$mailinglistnameID

        
    ##here you get each variable from array
        
    $mailinglistnameID=mysql_real_escape_string($mailinglistnameID); 
        
    $locationID=mysql_real_escape_string($_POST['locationID'][$row]); 
        
    $clientID=mysql_real_escape_string($_POST['clientID'][$row]); 
        
    $isCard=mysql_real_escape_string($_POST['isCard'][$row]); 
         
        
    ##then update new values, separated each field by comma
        
    $query "UPDATE mailinglist SET 
                    locationID='$locationID', 
                    clientID='$clientID',
                    isCard='$isCard' 
                    WHERE mailinglistnameID='$mailinglistnameID'"
    ;

        if (!
    mysql_query($query,$con)) 
        { 
        die(
    'Error: ' mysql_error()); 
        } 
        echo 
    "$row record updated"


    mysql_close($con
    ?>
    hope it helps

  • Users who have thanked XterM for this post:

    butlins (07-22-2011)

  • #6
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Thanks - and thanks for the commenting in the code. I guess I've been spoiled having Dreamweaver write all the update and insert behaviours for me - I think it's probably time for me to buy a book and dig into the PHP properly!
    If anyone asks my boss, this counts as work, okay?

  • #7
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    you are welcome


  •  

    Tags for this Thread

    Posting Permissions

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