Multiple Records insert from Database

Hi,

I am working on a page that needs to insert multiple records when the user checks them.

Explanation:

The user can add more users to his/her account. Then when he goes to the inquiry page, he can choose all the users he want, that he has in the accout:

User 1........ check
User 2........ not check
User 3.......check

In this example, I will need to add User1 Id to the database as well as User 3

I already have a recordset and a repeat region action that works fine

the code for the checkbox is the following

<input name="addUser[]" type="checkbox" id="addUser[]" value="<?php echo $row_rsAuthUsers['USER_ID']; ?>"

This works great, when looking at the source I see each user Id.

The problem is that only the last user checked get insrted in the database.

Thanks for any help.

try somethign along the lines of:
[note, this is off the top of my head and may contain minor errors]

That didn;t worked.

To explain better:

First of all, I added a hidden field next to each checkbox to store the users id.

I need to add all the users that are checked.

I have two other fields that will have the same information for all users, and one that will have their ID.

Codes:

Form:

<table width="100%" border="1" cellspacing="0" cellpadding="3">
<tr class="tdSubTitles">
<td width="295">User</td>
<td width="232"> <p>Type</p></td>
<td width="217">Add</td>
</tr>
<?php do { ?>
<tr class="tdContentLast">
<td><?php echo $row_rsAuthUsers['FNAME']; ?> <?php echo $row_rsAuthUsers['LNAME']; ?></td>
<td>
<?php $type = $row_rsAuthUsers['TYPE'];?>
<?php if ($type == "3")
echo "Witness" ?>
<?php if ($type == "4")
echo "Editor" ?>
</td>
<td><input name="addUser[]" type="checkbox" id="addUser[]" value="Yes">
<input name="authId" type="hidden" id="authId" value="<?php echo $row_rsAuthUsers['USER_ID']; ?>"> </td>
</tr>
<?php } while ($row_rsAuthUsers = mysql_fetch_assoc($rsAuthUsers)); ?>
</table>

PHP:

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "INQUIRY")) {
for($i=1; $<=sizeof($addUser[]); ; $i++){
$insertSQL = sprintf("INSERT INTO authUser (INQUIRY_ID, COMP_ID, USER_ID) VALUES (%s, %s, %s)",
GetSQLValueString($HTTP_POST_VARS['INQUIRY_ID'], "text"),
GetSQLValueString($HTTP_POST_VARS['COMP_ID'], "text"),
GetSQLValueString($HTTP_POST_VARS['authId'], "text"));

mysql_select_db($database_inquiries, $inquiries);
$Result1 = mysql_query($insertSQL, $inquiries) or die(mysql_error());
}
}



Thanks again for any help in this matter

For the form, couldn't you do something like this?


Then simply handle it in the php as follows:
[loosely based on my previous example, and your code]


Where's INQUIRY_ID and COMP_ID coming from? I don't see that in your form anywhere... similarly, what does GetSQLValueString() do?

Inquiry_id and comp_id are in the table, just not posted here. They get their values the same way as the user id does, only it is the same value for all of them

I triewd the script, It lloks like it is set to work but it is not working, now:

Do I have to have my php code in a separte page instead of the same page as the form?

I don't think i properly understand your question...

The form code (the <input> box) would change on the page you have the form... the other code I posted (the foreach) would be in the page that the form is posted to...

MadCowDzz, Thanks for your help.

I am going to paste the complete finished code so you can look at it and identify my problem.

Form Page:

<form action="usersForInquiry.php" method="post" name="addSuplier">
<table width="700" border="0" cellpadding="3">
<tr>
<td class="titles">Add Suppliers to this Inquiry: <?php echo $row_rsInquiry['PROJECT_NAME']; ?> Inquiry#: <?php echo $row_rsInquiry['INQUIRY_ID']; ?></td>
</tr>
<tr>
<td><a href="#" onClick="MM_openBrWindow('addSuppliersPop.php?USER_ID=<?php echo $row_rsUSer['USER_ID']; ?>&COMP_ID=<?php echo $row_rsUSer['COMP_ID']; ?>','main','width=500,height=400')">Add
More Suppliers</a></td>
</tr>
<tr>
<td class="tableBorderGen"><table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td width="145" class="tdSubTitles">Company Name</td>
<td width="150" class="tdSubTitles">Main Activity</td>
<td width="221" class="tdSubTitles">Contact Name</td>
<td width="115" class="tdSubTitles">Email</td>
<td width="242" class="tdSubTitles">Add<a href="#" class="link2" onClick="MM_openBrWindow('what.php?myId=activeSupplier','wh at','scrollbars=yes,width=300,height=400')"></a></td>
</tr>
<?php do { ?>
<tr>
<td class="tdContent"><?php echo $row_rsSupplier['COMP_NAME']; ?></td>
<td class="tdContent">&nbsp;<?php echo $row_rsSupplier['ACTIVITY']; ?> </td>
<td class="tdContent"><?php echo $row_rsSupplier['FNAME']; ?> <?php echo $row_rsSupplier['LNAME']; ?></td>
<td class="tdContent"><?php echo $row_rsSupplier['EMAIL']; ?></td>
<td class="tdContent">
<input name="addSupplier[]" type="checkbox" id="addSupplier[]" value="<?php echo $row_rsSupplier['USER_ID']?>">
<input name="INQUIRY_ID" type="hidden" id="INQUIRY_ID" value="<?php echo $HTTP_GET_VARS['INQUIRY_ID']?>">
<input name="USER_ID2" type="hidden" id="COMP_ID" value="<?php echo $HTTP_GET_VARS['COMP_ID']?>"></td>
</tr>
<?php } while ($row_rsSupplier = mysql_fetch_assoc($rsSupplier)); ?>
</table></td>
</tr>
<tr>
<td><hr></td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="ADD SUPPLIERS" class="btn"></td>
</tr>
</table>
</form>



usersForInquiry.php:

<?php
mysql_select_db($database_inquiries, $inquiries);
foreach ($HTTP_GET_VARS['addSupplier'] AS $value) {
$sql = "INSERT INTO supplier (INQUIRY_ID, COMP_ID, USER_ID) VALUES (".$HTTP_GET_VARS['INQUIRY_ID'].", ".$HTTP_GET_VARS['COMP_ID'].", ".$HTTP_GET_VARS['addSupplier'].")";
mysql_query ($sql) or die(mysql_error());
}

?>

Thanks again for your help. I know we are close to making it happen

What does happen exactly?
Are you getting an error? (if so, is it php, or mysql related?)

[i've lost track]

I don't get an error, but the data is not inserted in the table.

The form is about to work, the only problem I am getting now is this:

Unknown column '2cqarhf6p56n8vew' in 'field list'

that number is the first useer ID. If I uncheck the firtst user, I'll get the same error but with the Second User Id and so on.


I also believe that I need a statment in case none of the users is checked, or when only one is checked.

Please help me finish this, Thanks

Hello,
I really know nothing about php or mysql. Only remember stuff that I have read on the forums and tutorials but, from reading your post about inserting multiple records into a field or column, Wouldn't you do this as an array or with using ENUM or SET in the database?

I am only posting because nothing here mentions how your db is setup or if you are using arrays to either add to or select from the addUser field.

I'm confused but maybe this will give you an idea or something.
Chris

Here is a link to a solution with a simular issue. Hope this helps.
http://www.webmasterworld.com/forum88/2565.htm