Can someone help explain multiple insert?

butlins · 07-21-2011, 10:58 AM

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.

butlins · 07-21-2011, 12:12 PM

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.

XterM · 07-22-2011, 02:34 AM

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

butlins · 07-22-2011, 12:21 PM

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.

XterM · 07-22-2011, 03:35 PM

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

butlins · 07-22-2011, 03:42 PM

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!

XterM · 07-23-2011, 03:47 AM

you are welcome