Forums

PHP

This topic is locked

Multiple record insert from one form

Bruce McCulloch

Posted 14 Jun 2007 03:21:40

has voted

Vote

14 Jun 2007 03:21:40 Bruce McCulloch posted:

I have a form with a select box allowing for multiple client id selection. I need to know how to loop the insert statement to insert all records into a mysql table from one form. Here is the code I'm using:

[CODE]
<?php require_once('../Connections/dbconn.php'); ?>
<?php
if (!function_exists("GetSQLValueString"

) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = ""

{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string"

? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != ""

? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != ""

? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != ""

? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != ""

? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != ""

? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1"

) {
$insertSQL = sprintf("INSERT INTO history (clientid, `date`, status, notes, advisor, id) VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['clientid'], "int"

,
GetSQLValueString($_POST['date'], "date"

,
GetSQLValueString($_POST['status'], "text"

,
GetSQLValueString($_POST['notes'], "text"

,
GetSQLValueString($_POST['advisor'], "text"

,
GetSQLValueString($_POST['id'], "int"

);

mysql_select_db($database_dbconn, $dbconn);
$Result1 = mysql_query($insertSQL, $dbconn) or die(mysql_error());

$insertGoTo = "historylist.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

$clientid = $_GET['clientid'];
$maxRows_clientinfo = 1;
$pageNum_clientinfo = 0;
if (isset($_GET['pageNum_clientinfo'])) {
$pageNum_clientinfo = $_GET['pageNum_clientinfo'];
}
$startRow_clientinfo = $pageNum_clientinfo * $maxRows_clientinfo;

$colname_clientinfo = "-1";
if (isset($_GET['clientid'])) {
$colname_clientinfo = $_GET['clientid'];
}
mysql_select_db($database_dbconn, $dbconn);
$query_clientinfo = sprintf("SELECT * FROM clients Inner Join accounts ON clients.clientid = accounts.clientid Inner Join history ON clients.clientid = history.clientid WHERE clients.clientid = %s", GetSQLValueString($colname_clientinfo, "int"

);
$query_limit_clientinfo = sprintf("%s LIMIT %d, %d", $query_clientinfo, $startRow_clientinfo, $maxRows_clientinfo);
$clientinfo = mysql_query($query_limit_clientinfo, $dbconn) or die(mysql_error());
$row_clientinfo = mysql_fetch_assoc($clientinfo);

if (isset($_GET['totalRows_clientinfo'])) {
$totalRows_clientinfo = $_GET['totalRows_clientinfo'];
} else {
$all_clientinfo = mysql_query($query_clientinfo);
$totalRows_clientinfo = mysql_num_rows($all_clientinfo);
}
$totalPages_clientinfo = ceil($totalRows_clientinfo/$maxRows_clientinfo)-1;

mysql_select_db($database_dbconn, $dbconn);
$query_history = "SELECT * FROM history";
$history = mysql_query($query_history, $dbconn) or die(mysql_error());
$row_history = mysql_fetch_assoc($history);
$totalRows_history = mysql_num_rows($history);

mysql_select_db($database_dbconn, $dbconn);
$query_clients = "SELECT DISTINCT clientid FROM clients ORDER BY clientid ASC";
$clients = mysql_query($query_clients, $dbconn) or die(mysql_error());
$row_clients = mysql_fetch_assoc($clients);
$totalRows_clients = mysql_num_rows($clients);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Client Info</title>
<link href="ew.css" rel="stylesheet" type="text/css" />
<link href="ruddyduck.css" rel="stylesheet" type="text/css" />
</head>

<body>

<table width="100%" border="0" cellspacing="5" cellpadding="10">
<tr>
<td><span class="phpmaker"><strong>Ruddy Duck</strong></span>
<p><a href="historylist.php">Back to History</a></p>
<p>User ID - <?php echo $row_clientinfo['clientid']; ?></p>
<p>Add New Item to Multiple Clients</p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table>
<tr valign="baseline">
<td align="right" nowrap="nowrap" class="ewTableHeader">Clientid:</td>
<td class="ewTableAltRow"><select name="clientid" size="10" multiple="multiple">
<?php
do {
?>
<option value="<?php echo $row_clients['clientid']?>" ><?php echo $row_clients['clientid']?></option>
<?php
} while ($row_clients = mysql_fetch_assoc($clients));
?>
</select> </td>
</tr>

<tr valign="baseline">
<td align="right" nowrap="nowrap" class="ewTableHeader">Status:</td>
<td class="ewTableAltRow"><input type="text" name="status" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td align="right" valign="top" nowrap="nowrap" class="ewTableHeader">Notes:</td>
<td class="ewTableAltRow"><textarea name="notes" cols="50" rows="5"></textarea> </td>
</tr>
<tr valign="baseline">
<td align="right" nowrap="nowrap" class="ewTableHeader">Advisor:</td>
<td class="ewTableAltRow"><input type="text" name="advisor" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td colspan="2" align="center" nowrap="nowrap"><?php $date = date("Y-m-d H:i:s"

; ?>
<input type="hidden" name="date" value="<?php echo $date ?>" />
<input type="hidden" name="id" value="" />
<input type="hidden" name="MM_insert" value="form1" />
<input type="submit" class="ewTableRow" value="Insert Items" /></td>
</tr>
</table>
</form>
<p> </p> </td>
</tr>
</table>
</body>
</html>
<?php
mysql_free_result($clientinfo);

mysql_free_result($history);

mysql_free_result($clients);
?>
[End Code]

Replies

Steve Fuller

Replied 09 Jul 2007 21:22:05

09 Jul 2007 21:22:05 Steve Fuller replied:

Hey! I don't know if this helps, but it might get you thinking! This is how I recently did a multiple add to cart option list insert keyed by item number from another table.

This form...
[CODE]
form:
<form action="AddToCart.php" method="POST" name="AddToCart">
<input name="CurrentCartItemID" type="hidden" value="<?php echo $_SESSION['itemID']; ?>" />

repeat region in DW
<?php do { ?>
<tr bgcolor="<?php
if($SSAdv_m1%$SSAdv_change_every1==0 && $SSAdv_m1>0){
$SSAdv_k1++;
}
print $SSAdv_colors1[$SSAdv_k1%count($SSAdv_colors1)];
$SSAdv_m1++;
?>">
<td width="499"><p class="songlist"><?php echo $row_addsongs1[ItemName]; ?></p></td>
<td width="158" align="right"><p class="songlist">
<input type="checkbox" name="AddToCompList<?php echo ++$i; ?>" value="<?php echo $row_addsongs1['ItemID']; ?>" />
Add this song!</p></td>
</tr>
<?php } while ($row_addsongs1 = mysql_fetch_assoc($addsongs1)); ?>

[End Code]

executes this script....
[CODE]
<?

$CartItemID = $_POST['CartItemID'];
$AddToCompList1 = $_POST['AddToCompList1'];
$AddToCompList2 = $_POST['AddToCompList2'];
$AddToCompList3 = $_POST['AddToCompList3'];
$AddToCompList4 = $_POST['AddToCompList4'];
$AddToCompList5 = $_POST['AddToCompList5'];
$AddToCompList6 = $_POST['AddToCompList6'];
$AddToCompList7 = $_POST['AddToCompList7'];
$AddToCompList8 = $_POST['AddToCompList8'];
$AddToCompList9 = $_POST['AddToCompList9'];
$AddToCompList10 = $_POST['AddToCompList10'];
$AddToCompList11 = $_POST['AddToCompList11'];
$AddToCompList12 = $_POST['AddToCompList12'];
$AddToCompList13 = $_POST['AddToCompList13'];
$AddToCompList14 = $_POST['AddToCompList14'];
$AddToCompList15 = $_POST['AddToCompList15'];
$AddToCompList16 = $_POST['AddToCompList16'];
$AddToCompList17 = $_POST['AddToCompList17'];
$AddToCompList18 = $_POST['AddToCompList18'];
$AddToCompList19 = $_POST['AddToCompList19'];
$AddToCompList20 = $_POST['AddToCompList20'];

$buildtosuit_str = "INSERT INTO ERS_orderoptions (itemID, optionID)\n";
$buildtosuit_str .= " VALUES \n";

if (isset($AddToCompList1)) {
$buildtosuit_str .= "($CartItemID, $AddToCompList1)\n";
}
if (isset($AddToCompList2)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList2)\n";
}
if (isset($AddToCompList3)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList3)\n";
}
if (isset($AddToCompList4)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList4)\n";
}
if (isset($AddToCompList5)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList5)\n";
}
if (isset($AddToCompList6)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList6)\n";
}
if (isset($AddToCompList7)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList7)\n";
}
if (isset($AddToCompList8)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList8)\n";
}
if (isset($AddToCompList9)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList9)\n";
}
if (isset($AddToCompList10)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList10)\n";
}
if (isset($AddToCompList11)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList11)\n";
}
if (isset($AddToCompList12)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList12)\n";
}
if (isset($AddToCompList13)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList13)\n";
}
if (isset($AddToCompList14)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList14)\n";
}
if (isset($AddToCompList15)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList15)\n";
}
if (isset($AddToCompList16)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList16)\n";
}
if (isset($AddToCompList17)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList17)\n";
}
if (isset($AddToCompList18)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList18)\n";
}
if (isset($AddToCompList19)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList19)\n";
}
if (isset($AddToCompList20)) {
$buildtosuit_str .= ", ($CartItemID, $AddToCompList20)\n";
}

$dblink=MYSQL_CONNECT('database_server', 'username', 'password') OR DIE("Unable to connect to database"

;
@mysql_select_db("database"

or die( "Unable to select database"

;
mysql_query( $buildtosuit_str, $dblink );
mysql_close($dblink);

header("Location: www.domain.com/cart.php");
exit;
?>
[End Code]

Just a thought. If statements for each of my incremented form elements...
Good luck with it!

Reply to this topic

Latest Extensions

App Connect Summernote File Upload Upload different files in Summernote Editor
App Connect Summernote Simple, yet powerful and modern WYSIWYG Editor
Bootstrap 4 Dynamic Table Generator 2 Automatically generate dynamic data tables
Bootstrap 4 Dynamic Form Generator 2 Auto generate perfect Bootstrap 4 dynamic forms in seconds
App Connect Bootstrap 4 Dynamic Paging Generator Auto generate awesome dynamic Bootstrap 4 paging in seconds
App Connect Event Calendar The most powerful full-sized event calendar for Dreamweaver
DMXzone App Connect Next generation client side framework for data-driven web sites and apps
DMXzone Database Custom Query Create advanced, complex custom SQL queries with ease

Latest Videos

Using App Connect Autocomplete Learn how to add autocomplete functionality on your page, using the App Connect Autocomplete
Creating Custom SQL Queries Learn how to create custom SQL Queries with DMXzone Database Custom Query
Restricting Access to Your Pages Learn how to restrict access to your pages, using DMXzone Security Provider 2
Adding Log Out Functionality with DMXzone Security Provider 2 Learn how to add log out functionality to your page with DMXzone Security Provider 2
Creating a Log In Page with DMXzone Security Provider 2 Learn how to create a log in page with DMXzone Security Provider 2
Setting Up Site Security Settings Learn how to setup your site security, using DMXzone Security Provider 2
Generating an Update Record Form Learn how to create an update record form, using the Bootstrap 4 Dynamic Form Generator 2
Generating an Insert Record Form Learn how to create an insert record form, using the Bootstrap 4 Dynamic Form Generator 2