Forums
This topic is locked
Update multiple records
Posted 29 Sep 2001 00:04:19
1
has voted
29 Sep 2001 00:04:19 Keith Slater posted:
Hey I was wondering what would be the easiest way to update multiple records at the same time. I got a page that displays all the records and Im wanted to make it so they can update more than one record at once... This is the code I got right now:<?php
// Copyright (c) Interakt Online 2001
// www.interakt.ro/
require("./../adodb/adodb.inc.php"
;require("./../Connections/mosby.php"
;?><?php
// *** Restrict Access To Page: Grant or deny access to this page
$KT_authorizedUsers=" admin,user";
$KT_authFailedURL="fail.php";
$KT_grantAccess=0;
session_start();
if (isset($HTTP_SESSION_VARS["KT_Username"])) {
if (false || !(isset($HTTP_SESSION_VARS["KT_UserAuthorization"])) || $HTTP_SESSION_VARS["KT_UserAuthorization"]=="" || strpos($KT_authorizedUsers, $HTTP_SESSION_VARS["KT_UserAuthorization"])) {
$KT_grantAccess = 1;
}
}
if (!$KT_grantAccess) {
$KT_qsChar = "?";
if (strpos($KT_authFailedURL, "?"
) $KT_qsChar = "&";$KT_referrer = $PHP_SELF;
if (strlen($QUERY_STRING) > 0) $KT_referrer .= "?" . $QUERY_STRING;
$KT_authFailedURL = $KT_authFailedURL . $KT_qsChar . "accessdenied=" . urlencode($KT_referrer);
header("Location: $KT_authFailedURL"
;exit;
}
?><?php
// *** Edit Operations: declare Tables
$MM_editAction = $PHP_SELF;
if ($QUERY_STRING) {
$MM_editAction = $MM_editAction . "?" . $QUERY_STRING;
}
$MM_abortEdit = 0;
$MM_editQuery = "";
?><?php
// *** Update Record: set variables
if (isset($MM_update) && (isset($MM_recordId))) {
// $MM_editConnection = $MM_mosby_STRING;
$MM_editTable = "awards";
$MM_editColumn = "sequ";
$MM_recordId = "" . $MM_recordId . "";
$MM_editRedirectUrl = "award.php";
$MM_fieldsStr = "seq|value|award|value";
$MM_columnsStr = "sequ|none,none,NULL|award|',none,''";
// create the $MM_fields and $MM_columns arrays
$MM_fields = Explode("|", $MM_fieldsStr);
$MM_columns = Explode("|", $MM_columnsStr);
// set the form values
for ($i=0; $i+1 < sizeof($MM_fields); ($i=$i+2)) {
$MM_fields[$i+1] = $$MM_fields[$i];
}
// append the query string to the redirect URL
if ($MM_editRedirectUrl && $QUERY_STRING && (strlen($QUERY_STRING) > 0)) {
$MM_editRedirectUrl .= ((strpos($MM_editRedirectUrl, '?') == false)?"?":"&"
. $QUERY_STRING;}
}
?><?php
// *** Update Record: construct a sql update statement and execute it
if (isset($MM_update) && (isset($MM_recordId))) {
// create the sql update statement
$MM_editQuery = "update " . $MM_editTable . " set ";
for ( $i=0; $i+1 < sizeof($MM_fields); ($i=$i+2))
{
$formVal = $MM_fields[$i+1];
$MM_typesArray = Explode(",", $MM_columns[$i+1]);
$delim = ($MM_typesArray[0] != "none"
? $MM_typesArray[0] : "";$altVal = ($MM_typesArray[1] != "none"
? $MM_typesArray[1] : "";$emptyVal = ($MM_typesArray[2] != "none"
? $MM_typesArray[2] : "";if ($formVal == "" || !isset($formVal))
{
$formVal = $emptyVal;
}
else
{
if ($altVal != ""
{
$formVal = $altVal;
}
else if ($delim == "'"
{ // do not escape quotes in PHP4
$formVal = "'" .$formVal . "'";
}
else
{
$formVal = $delim . $formVal . $delim;
}
}
if ($i != 0)
{
$MM_editQuery = $MM_editQuery . ", " . $MM_columns[$i] . " = " . $formVal;
}
else
{
$MM_editQuery = $MM_editQuery . $MM_columns[$i] . " = " . $formVal;
}
}
$MM_editQuery = $MM_editQuery . " where " . $MM_editColumn . " = " . $MM_recordId;
if ($MM_abortEdit != 1)
{
// execute the insert
$queryrs = $mosby->Execute($MM_editQuery) or DIE($mosby->ErrorMsg());
if ($MM_editRedirectUrl)
{
header ("Location: $MM_editRedirectUrl"
;}
}
}
?><?php
$Recordset1=$mosby->Execute("SELECT * FROM awards"
or DIE($mosby->ErrorMsg());$Recordset1_numRows=0;
$Recordset1__totalRows=$Recordset1->RecordCount();
?><?php
$Repeat1__numRows = -1;
$Repeat1__index= 0;
$Recordset1_numRows = $Recordset1_numRows + $Repeat1__numRows;
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0">
<div align="center">
<p>Awards</p>
<p> </p>
<form method="POST" action="<?php echo $MM_editAction?>" name="form1">
<table border=0 cellpadding=2 cellspacing=0 align="center">
<?php while (($Repeat1__numRows-- != 0) && (!$Recordset1->EOF))
{
?>
<tr valign="baseline">
<td nowrap align="right">Seq:</td>
<td>
<input type="text" name="seq" value="<?php echo $Recordset1->Fields("sequ"
?>" size="3"></td>
<td width="20"> </td>
<td>Award:</td>
<td>
<input type="text" name="award" value="<?php echo $Recordset1->Fields("award"
?>" size="45"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right"> </td>
<td colspan="4">
</td>
</tr>
<?php
$Repeat1__index++;
$Recordset1->MoveNext();
}
?>
<tr valign="baseline">
<td nowrap align="right"> </td>
<td colspan="4">
<input type="submit" value="Update Record" name="submit">
</td>
</tr>
</table>
<input type="hidden" name="MM_update" value="true">
<input type="hidden" name="MM_recordId" value="<?php echo $Recordset1->Fields("sequ"
?>"></form>
<p> </p>
<p>Add an Award</p>
<form name="form2" method="post" action="">
<input type="text" name="textfield" size="3">
<input type="text" name="textfield2">
<br>
<input type="submit" name="Submit" value="Submit">
<br>
</form>
<p> </p>
</div>
</body>
</html>
<?php
$Recordset1->Close();
?>
Thanks a lot for any help
Keith Slater
Replies
Replied 01 Oct 2001 08:55:27
01 Oct 2001 08:55:27 Tim Green replied:
Keith. The easiest way to implement this at the moment is to use a customised SQL statement, and other custom code on the page.
Unfortunately though, yours wasn't really a question, more a request for the code to be written for you.
If you have a specific issue while developing this section of code then I will be happy to help. Unfortunately I do not have the time to take your source code and make it work for you for free.
It isn't really fair to post a standard generated page in the hope that the code will be created for you. Please do not do this again.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/01/2001 08:57:50
Edited by - rawveg on 10/01/2001 08:59:42
Unfortunately though, yours wasn't really a question, more a request for the code to be written for you.
If you have a specific issue while developing this section of code then I will be happy to help. Unfortunately I do not have the time to take your source code and make it work for you for free.
It isn't really fair to post a standard generated page in the hope that the code will be created for you. Please do not do this again.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/01/2001 08:57:50
Edited by - rawveg on 10/01/2001 08:59:42
Replied 01 Oct 2001 15:36:17
01 Oct 2001 15:36:17 Keith Slater replied:
Sorry about the way that I worded that, What would be the easiest way to start to do multiple updates?? Would it be generally the same thing as deleting multiple records??
Thanks
Keith Slater
Thanks
Keith Slater
Replied 02 Oct 2001 16:54:38
02 Oct 2001 16:54:38 Tim Green replied:
Absolutely, you could adapt that system to handle multiple updates quite easily.
Of course you would have to ensure that you update the SQL, but it should give you a good base to build from.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Of course you would have to ensure that you update the SQL, but it should give you a good base to build from.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Replied 03 Oct 2001 15:32:26
03 Oct 2001 15:32:26 A H replied:
I've been trying to adapt the multiple delete code to my own multiple update but im not finding it so easy <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle> I've got the delete one working well with my database but I'm not really sure where I'm going with the update version. I don't need check boxes and I think thats throwing me off. I just need one value(a quantity for a shopping cart - text box or list) to be updated. Can anyone help me out...please...Cheers.
Replied 05 Oct 2001 15:36:52
05 Oct 2001 15:36:52 Tim Green replied:
The easiest way to run a multiple update for multiple tables is to code it specifically.
You can do this by doing something like this :-
<?php
$theSQL = "UPDATE tablename SET columnname = '" . $newvalue . "', columnname = '" . $newvalue . "' WHERE ID = '". $keyvariable . "'";
$theRS = $connection->Execute($theSQL);
$theRS->close();
?>
All you need to do is repeat that code block for each table you want to update. Replacing all Column Names, Variable Names and Connection names as appropriate.
Hope this puts you on the right track.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/05/2001 15:39:23
You can do this by doing something like this :-
<?php
$theSQL = "UPDATE tablename SET columnname = '" . $newvalue . "', columnname = '" . $newvalue . "' WHERE ID = '". $keyvariable . "'";
$theRS = $connection->Execute($theSQL);
$theRS->close();
?>
All you need to do is repeat that code block for each table you want to update. Replacing all Column Names, Variable Names and Connection names as appropriate.
Hope this puts you on the right track.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/05/2001 15:39:23
Replied 06 Oct 2001 02:29:29
06 Oct 2001 02:29:29 A H replied:
Hi Tim,
Thanks for your reply. I think this is pretty close now. I've modified your multidelete and the code above to a one page multi update - just one table with one field to update but multiple records.
It's now updating only the last record - not any others. I've spent a few hours trying things but can't seem to get it to happen. Can you check it out please <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>?
The update bit:
<?php<br>
//If variable $formSeen has been set then process form parameters<br>
if (isset($HTTP_POST_VARS["formSeen"])) {</p>
<p> $theSQL = "UPDATE user_track SET sel_item_qty = '" . $quantity .
"', sel_item_qty = '" . $quantity . "' WHERE cart_id = '".
$cart_id . "'";<br>
$theRS = $dish->Execute($theSQL);<br>
$theRS->close();</p>
<p>}<br>
?>
Thanks for your reply. I think this is pretty close now. I've modified your multidelete and the code above to a one page multi update - just one table with one field to update but multiple records.
It's now updating only the last record - not any others. I've spent a few hours trying things but can't seem to get it to happen. Can you check it out please <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>?
The update bit:
<?php<br>
//If variable $formSeen has been set then process form parameters<br>
if (isset($HTTP_POST_VARS["formSeen"])) {</p>
<p> $theSQL = "UPDATE user_track SET sel_item_qty = '" . $quantity .
"', sel_item_qty = '" . $quantity . "' WHERE cart_id = '".
$cart_id . "'";<br>
$theRS = $dish->Execute($theSQL);<br>
$theRS->close();</p>
<p>}<br>
?>
Replied 07 Oct 2001 01:36:30
07 Oct 2001 01:36:30 Tim Green replied:
OK, I take it $cart_id contains only a single value ? It needs to be an array. This way you can just loop through the number of instances in the array using :-
for ($k=0; $k < count($cart_id); $k++) {
// your update code here
}
To actually get the code, use a little bit of javascript to push all the values into a hidden form field (like in my multi-delete example). And you should be quids in.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
for ($k=0; $k < count($cart_id); $k++) {
// your update code here
}
To actually get the code, use a little bit of javascript to push all the values into a hidden form field (like in my multi-delete example). And you should be quids in.
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Replied 08 Oct 2001 02:26:51
08 Oct 2001 02:26:51 A H replied:
Thanks Tim,
I'm still floundering around. I've now got a more comprehensive layout and format of what it all looks like and needs to do.
cart.php
- displays the contents of the user_track table based on the $PHPSESSID.
- has a list that contains the quantity from user_track and other vales that will update the quantity column in the user_track table.
- repeat region for all records that match the session id.
- i've assigned the list field the name of 'quantity<cart_id>'.
once i submit the form to update.php i want the field 'quantity<cart_id>' to update the appropriate column and record (defined by cart_id) in the user_track table.
I bought the book PHP Essentials by Julie C. Meloni (great book for Ultradev users because she puts all the PHP code above and in the HTML so you can work WYSIWYG in Ultradev) and have been modifying the cart in that. The problem is that this cart has no way of updating the quantity of items ordered by the user. - I'm in to far to go back now...ahhhhhh
I tried to create the array with your existing javascript but i'm just staring blankly at it on screen - not really sure what I'm looking for.
I'm not really sure where to go from here.
Please help me....please...
Edited by - hyperbuzzy on 10/08/2001 04:56:39
I'm still floundering around. I've now got a more comprehensive layout and format of what it all looks like and needs to do.
cart.php
- displays the contents of the user_track table based on the $PHPSESSID.
- has a list that contains the quantity from user_track and other vales that will update the quantity column in the user_track table.
- repeat region for all records that match the session id.
- i've assigned the list field the name of 'quantity<cart_id>'.
once i submit the form to update.php i want the field 'quantity<cart_id>' to update the appropriate column and record (defined by cart_id) in the user_track table.
I bought the book PHP Essentials by Julie C. Meloni (great book for Ultradev users because she puts all the PHP code above and in the HTML so you can work WYSIWYG in Ultradev) and have been modifying the cart in that. The problem is that this cart has no way of updating the quantity of items ordered by the user. - I'm in to far to go back now...ahhhhhh
I tried to create the array with your existing javascript but i'm just staring blankly at it on screen - not really sure what I'm looking for.
I'm not really sure where to go from here.
Please help me....please...
Edited by - hyperbuzzy on 10/08/2001 04:56:39
Replied 08 Oct 2001 05:12:07
08 Oct 2001 05:12:07 A H replied:
My update page - the cart one i thought was too long to post.
update.php
<pre id=code><font face=courier size=2 id=code>
<?
session_start();
$totalprice = $quantity * $price;
$connection = mysql_connect("localhost", "changed", "changed" )
or die ("Couldn't connect to server." );
$db = mysql_select_db("dishdash", $connection)
or die ("Couldn't select database." );
//$sql = "UPDATE user_track SET quantity = '$quantity', total_price =
'$totalprice' WHERE cart_id = $cart_id";
//$sql_result = mysql_query($sql,$connection)
// or die ("Couldn't update record!" );
for ($k=0; $k < count($cart_id); $k++) {
// your update code here
$theSQL = "UPDATE user_track SET quantity = '" . $quantity . "',
quantity = '" . $quantity . "' WHERE cart_id = '". $cart_id .
"'";
$sql_result = mysql_query($theSQL,$connection)
or die ("Couldn't update record!" );
//$theRS = $dishdash->Execute($theSQL);
//$theRS->close();
}
//}
?><?php
Header("Location: cart.php" );
?>
</font id=code></pre id=code>
Edited by - hyperbuzzy on 10/08/2001 05:33:06
update.php
<pre id=code><font face=courier size=2 id=code>
<?
session_start();
$totalprice = $quantity * $price;
$connection = mysql_connect("localhost", "changed", "changed" )
or die ("Couldn't connect to server." );
$db = mysql_select_db("dishdash", $connection)
or die ("Couldn't select database." );
//$sql = "UPDATE user_track SET quantity = '$quantity', total_price =
'$totalprice' WHERE cart_id = $cart_id";
//$sql_result = mysql_query($sql,$connection)
// or die ("Couldn't update record!" );
for ($k=0; $k < count($cart_id); $k++) {
// your update code here
$theSQL = "UPDATE user_track SET quantity = '" . $quantity . "',
quantity = '" . $quantity . "' WHERE cart_id = '". $cart_id .
"'";
$sql_result = mysql_query($theSQL,$connection)
or die ("Couldn't update record!" );
//$theRS = $dishdash->Execute($theSQL);
//$theRS->close();
}
//}
?><?php
Header("Location: cart.php" );
?>
</font id=code></pre id=code>
Edited by - hyperbuzzy on 10/08/2001 05:33:06
Replied 08 Oct 2001 16:39:48
08 Oct 2001 16:39:48 Tim Green replied:
Hyperbuzzy,
The code to create the array has already been given. CLUE: It's JavaScript.
BIGGER CLUE: The code is in the thread called "using check box too delete records ( PHPAKT)"
Essentially you just grab all the values you want from the various form fields and stuff them into a hidden text field, and separate the values with commas.
This gives you a text field with say:-
1,2,3,4,5 as the value.
Of course all you need to do is then create the array by splitting the form field value by the comma :-
$idArray = split(",",$formfield);
and then you can just loop through each element in $idArray as you go.
Alternatively you can always wait for IntelliCART, which will be requiring BETA testers soon.... but perhaps that's a little too late for you....
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/08/2001 16:41:57
The code to create the array has already been given. CLUE: It's JavaScript.
BIGGER CLUE: The code is in the thread called "using check box too delete records ( PHPAKT)"
Essentially you just grab all the values you want from the various form fields and stuff them into a hidden text field, and separate the values with commas.
This gives you a text field with say:-
1,2,3,4,5 as the value.
Of course all you need to do is then create the array by splitting the form field value by the comma :-
$idArray = split(",",$formfield);
and then you can just loop through each element in $idArray as you go.
Alternatively you can always wait for IntelliCART, which will be requiring BETA testers soon.... but perhaps that's a little too late for you....
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Edited by - rawveg on 10/08/2001 16:41:57
Replied 06 Feb 2002 17:57:42
06 Feb 2002 17:57:42 David Fee replied:
I have a similar problem. I'm trying to insert records into multiple tables. When I have only one insert statement, it works fine. The page loads, user enters info, user presses submit button and data is inserted into table. When I add the second insert statement to add a record to a different table, the sql code is executed when the page loads and I get an error message that says the insert statement failed. (It failed because there was no data to insert.) My question is why does the page load fine with only one insert statement and fail when there's two insert statements. It seems to try to run the second insert statement while the page loads. Here's the code:
<?php
// Connect to database named "ABC" with user "postgres".
$db=pg_connect("dbname=ABC user=postgres" or die("Couldn't connect.";
//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
//First Insert Statement. Data is taken from an array
$table_name = "items";
for ( $i=0; $i<count($code); $i++ ) {
$sql = "INSERT INTO $table_name
( code, item, amount, itemnumber )
VALUES
( '" . $code[$i] . "' , '" . $desc[$i] . "' , '" . $amount[$i] . "' , '" . $num[$i] . "');";
$result = pg_exec($db, $sql) or die("Couldn't execute query.";
}
//Second Insert Statement. data is taken from textboxes on form
$table_name2 = "invoiceinfo";
$sql2 = "INSERT INTO $table_name2
( invoicenumber, invoicedate, invoiceduedate, invoicepaydate, invlineitemnumber, vendorcode )
VALUES
( '$txtinvnum', '$txtinvdate', '$txtduedate', '$txtpaydate', '$txttransnum', '$hiddenFieldVendorCode');";
$result2 = pg_exec($db, $sql2) or die("Couldn't execute query.";
pg_close($db)
?>
Thanks,
Dave
<?php
// Connect to database named "ABC" with user "postgres".
$db=pg_connect("dbname=ABC user=postgres"
or die("Couldn't connect.";//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
//First Insert Statement. Data is taken from an array
$table_name = "items";
for ( $i=0; $i<count($code); $i++ ) {
$sql = "INSERT INTO $table_name
( code, item, amount, itemnumber )
VALUES
( '" . $code[$i] . "' , '" . $desc[$i] . "' , '" . $amount[$i] . "' , '" . $num[$i] . "');";
$result = pg_exec($db, $sql) or die("Couldn't execute query."
;}
//Second Insert Statement. data is taken from textboxes on form
$table_name2 = "invoiceinfo";
$sql2 = "INSERT INTO $table_name2
( invoicenumber, invoicedate, invoiceduedate, invoicepaydate, invlineitemnumber, vendorcode )
VALUES
( '$txtinvnum', '$txtinvdate', '$txtduedate', '$txtpaydate', '$txttransnum', '$hiddenFieldVendorCode');";
$result2 = pg_exec($db, $sql2) or die("Couldn't execute query."
;pg_close($db)
?>
Thanks,
Dave
Replied 07 Feb 2002 00:08:03
07 Feb 2002 00:08:03 Tim Green replied:
Dave,
This is made trickier, because you're not using the UltraDev system for inserts, and therefore not using ADODB methods.
Anyway, this aside. The problem that you are having is that you are trying to enter data into the database from a form that hasn't yet submitted.
Now, I'm presuming the first insert happens because you are submitting information to this page.
That done, what you then need to do is segregate the two inserts. Yes, they can both be in the page, but in the current way your process is being handled, grouping them like this will always produce an error of some form.
Try something like this:-
<?php
if (!isset($formSeen)) {
// This execute when the page loads the first time
// Connect to database named "ABC" with user "postgres".
$db=pg_connect("dbname=ABC user=postgres" or die("Couldn't connect.";
//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
//First Insert Statement. Data is taken from an array
$table_name = "items";
for ( $i=0; $i<count($code); $i++ ) {
$sql = "INSERT INTO $table_name ( code, item, amount, itemnumber ) VALUES ( '" . $code[$i] . "' , '" . $desc[$i] . "' , '" . $amount[$i] . "' , '" . $num[$i] . "');";
$result = pg_exec($db, $sql) or die("Couldn't execute query.";
}
pg_close($db)
} else {
//Second Insert Statement. data is taken from textboxes on form
// Only executes when the form has been loaded.
$db=pg_connect("dbname=ABC user=postgres" or die("Couldn't connect.";
//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
$table_name2 = "invoiceinfo";
$sql2 = "INSERT INTO $table_name2 ( invoicenumber, invoicedate, invoiceduedate, invoicepaydate, invlineitemnumber, vendorcode ) VALUES ( '$txtinvnum', '$txtinvdate', '$txtduedate', '$txtpaydate', '$txttransnum', '$hiddenFieldVendorCode');";
$result2 = pg_exec($db, $sql2) or die("Couldn't execute query.";
pg_close($db)
?>
and then inside your form insert a hidden field called 'formSeen' and set it's value to true.
Now, when the page loads the first insert will happen, and when the form is submitted, the second insert will occur.
I hope I have understood your problem, and that this helps to resolve it.
All the best
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
This is made trickier, because you're not using the UltraDev system for inserts, and therefore not using ADODB methods.
Anyway, this aside. The problem that you are having is that you are trying to enter data into the database from a form that hasn't yet submitted.
Now, I'm presuming the first insert happens because you are submitting information to this page.
That done, what you then need to do is segregate the two inserts. Yes, they can both be in the page, but in the current way your process is being handled, grouping them like this will always produce an error of some form.
Try something like this:-
<?php
if (!isset($formSeen)) {
// This execute when the page loads the first time
// Connect to database named "ABC" with user "postgres".
$db=pg_connect("dbname=ABC user=postgres"
or die("Couldn't connect.";//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
//First Insert Statement. Data is taken from an array
$table_name = "items";
for ( $i=0; $i<count($code); $i++ ) {
$sql = "INSERT INTO $table_name ( code, item, amount, itemnumber ) VALUES ( '" . $code[$i] . "' , '" . $desc[$i] . "' , '" . $amount[$i] . "' , '" . $num[$i] . "');";
$result = pg_exec($db, $sql) or die("Couldn't execute query."
;}
pg_close($db)
} else {
//Second Insert Statement. data is taken from textboxes on form
// Only executes when the form has been loaded.
$db=pg_connect("dbname=ABC user=postgres"
or die("Couldn't connect.";//see if our connection was successful
if ( !$db ) { printf( "message" ); exit; }
$table_name2 = "invoiceinfo";
$sql2 = "INSERT INTO $table_name2 ( invoicenumber, invoicedate, invoiceduedate, invoicepaydate, invlineitemnumber, vendorcode ) VALUES ( '$txtinvnum', '$txtinvdate', '$txtduedate', '$txtpaydate', '$txttransnum', '$hiddenFieldVendorCode');";
$result2 = pg_exec($db, $sql2) or die("Couldn't execute query."
;pg_close($db)
?>
and then inside your form insert a hidden field called 'formSeen' and set it's value to true.
Now, when the page loads the first insert will happen, and when the form is submitted, the second insert will occur.
I hope I have understood your problem, and that this helps to resolve it.
All the best
Tim Green
Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Replied 07 Feb 2002 00:21:21
07 Feb 2002 00:21:21 David Fee replied:
Tim,
The way I understand your suggestion is that one insert runs when the page is loaded and the other runs when the form is submitted. I want both inserts to run when the form is submitted. Also, can UD insert data into more than one table. I couldn't get it to do that. I have some javascript on my page that adds additional rows to the table as the enduser needs them. Therefore, I need an insert statement inside a loop to repeat itself as many times as there are rows. Can UD do that? I would like to keep the ADODB functionality.
Thanks,
I appreciate your help.
Dave
The way I understand your suggestion is that one insert runs when the page is loaded and the other runs when the form is submitted. I want both inserts to run when the form is submitted. Also, can UD insert data into more than one table. I couldn't get it to do that. I have some javascript on my page that adds additional rows to the table as the enduser needs them. Therefore, I need an insert statement inside a loop to repeat itself as many times as there are rows. Can UD do that? I would like to keep the ADODB functionality.
Thanks,
I appreciate your help.
Dave