updating table using submit button

[adrianqx]

Hi i designed a website which displays a table containing name of product and number of products i have stored for the user ,below the table i have created a form which has the product name and next to it there is a input field for the user to enter the number of products he wants delivered to him ,i was wondering how will it be possible for PHP to subtract the figure entered in the form from the figure displayed in the table on the page when a user clicks on the submit button.

Please im pretty desparate 😕

[rincewind456]

If you mean to dynamically change the page then you can't do it with php without reloading the page from the server.
However it's just the sort of thing, that Javascript is designed for though.

[jkurrle]

Let me make sure I understand you correctly. You are storing inventory for your customers. You have an html <table> that shows each individual customer what inventory they have available. Below it, you have a form that the customer uses to "release" inventory to ship it. Once they click on the "Submit" button, you want the table to update and show the customer the correct inventory levels after they release inventory. Is this correct?

If so, the solution seems simple. Your <table> must be pulling data dynamically from a database table or a flat file database. When the user clicks "submit", the script that receives the data simply updates the database table or flat file database with the updated values, allocates the inventory for shipment, then returns back to the referring page.

If your pages are dynamic and are prone to being moved alot, I suggest you pass a hidden parameter in your form that identifies exactly where the page is. Here is an example of a hidden form input:

<?PHP $return=$_SERVER['SCRIPT_URI']; ?>
<input type="hidden" name="return" value="<?php echo $return; ?>" />

Your script pulls that variable down and knows where to exit back to, once the script finishes execution. Since the script will do a lot of processing before it can return back to the referring page, the header function won't work for you. You can use a simple html redirect though, like this:

echo "<meta http-equiv=\"refresh\" content=0;url=\"$return\">";

Bada bing, bada boom! You are redirected back to the referring page and it will pull up the newly updated values.

Does this make sense?

[adrianqx]

Yer the inventory table is dynamic(mysql) its called to the page by a php script ,ive done all that my problem is with the form below the table how exactly will a php script take the figure entered by the user in the text box and subtruct it from the figure in the table when the customer clicks on the submit .
take in mind im sort of a newbi when it comes to php so please is it possible to explain a step by step procedure

[jkurrle]

Ok, let me lay out an example and see if this fits you.

I have my table, listing all the inventory I'm holding for a customer, including counts of each item. My customer wants to release inventory, so how do I let him do this?

One design option is to build the form into the display table, as an extra column. I'd put my <form> tags outside my <table> tags and my table might have the following 4 columns:

Item Number
Item Description
Amount in Storage
Amount to be Released

"Amount to be Released" is a form <input> tag that allows the customer to specify how much he wants to release. You could use a "text" type to do this, but to reduce error checking, I'd use PHP to query the MySQL database, find how much the user has, then create a dropdown box ranging from zero to the amount the customer has in storage. After the table is finished, have a submit button taking you to the processing script.

What variables to pass? I'd say the item number as the variable name and the amount to release as the value. Then, I'd of course pass the customer identifier, so we know whose account to debit. I'd probably pass a return variable, like I discussed earlier, so I can move pages around or change page names with little modification.

The receiving script retrieves the passed parameters via $POST[''] or $GET[''], depending on the method you chose to pass information in in your form. It assigns variables to these passed parameters at the same time. Example: $Customer_ID=$_POST['cust_id'];

Next, you connect to your MySQL database, and retrieve the inventory and quantities the customer owns. Use a while() loop to process each returned value. In the while() loop, do the following:

• Extract the row
• If the $Inventory_ID isn't nul, then...
  • Subtract from the current Inventory quantity the amount that was posted from the form.
  • Update the MySQL table with the new amount

After looping through all the customer's inventory, then use your http-refresh to return back to the original form. All values should then be updated.

Does this help?

[adrianqx]

i realise this i want to reload the page from the server diplaying the new values
how do i get php to take the values entred in the form adn subtact from the values in the table
hope im clear

rincewind456 wrote:

If you mean to dynamically change the page then you can't do it with php without reloading the page from the server.
However it's just the sort of thing, that Javascript is designed for though.

[rincewind456]

Then you would add something like this to the top of your php code

<?php

//if the submit button is pressed
if (isset($_POST['submit'])) {	//submit being the name of your submit button

//item_id being the unique identifier for the item.
$item_id = mysql_real_escape_string(trim($_POST['item_id']));
//QTY being the amount to be deducted
$qty = mysql_real_escape_string(trim($_POST['qty']));
// error checking to make sure the value entered is a integer
if (!is_numeric($qty)) {
	echo "Not a number please re-enter.";
}
//Update the database record
$sql = "UPDATE table_name SET qty=qty-$qty WHERE id ='$item_id";

$result = mysql_query($sql) or die(mysql_error());
//Print out number of updates, should only be 1
printf ("Updated records: %d\n", mysql_affected_rows());

}

// YOU WOULD HAVE YOUR CURRENT CODE FOR BUILDING YOUR PAGE HERE

this would run the update to the database and then when your current code runs it would take the new values from the databse to build the page.

Note: this is not tested and will need tweaking, but it should give you an idea.

[adrianqx]

this is what i have so far i try adding the code u give but i just keep getting errors can anyone go through this code and tell me what to add where pleease

<form action="formmail.php" method="post" name="stocks" id="stocks">
<input type="hidden" name="flex_pinmailer" value="flex_pinmailer">
<input type="hidden" name="freedom_pinmailer" value="freedom_pinmailer">
<input type="hidden" name="atm_rolls" value="atm_rolls">
<input type="hidden" name= "Recipient" value="[e-mail]">
<input type="hidden" name="bcc" value="[e-mail]">

            <table cellspacing="0" cellpadding="0">
              <!--DWLayoutTable-->
              <tr>
                <td height="30" valign="top"><p>Please use the forms below to check your availabe stocks and to order online. </p></td>
                <td width="3">&nbsp;</td>
              </tr>
              <tr>
                <td width="642" height="25">&nbsp;</td>
                <td></td>
              </tr>
            </table>
			<div align="center">
             <table width="442" border="0" background="../images/formbg.jpg">
              <tr>
                <td colspan="2"><div align="center"><strong>NIC  </strong> </div></td>
               </tr>
              <tr>
                <td height="44" colspan="2"><?php

$db="trend_db";
$link = mysql_connect("localhost","terry","password");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM nic_stocks" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows Items.<P>";
print "<table width=400 border=0>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=arial size=3/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
mysql_close($link);
?><div align="left"></div> <div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="center"><strong>STOCK ORDER FORM </strong></div></td>
</tr>
<tr>
<td width="140"><div align="left"><strong>ITEM</strong></div></td>
<td width="246"><div align="left"><strong>ENTER QUANTITY OF ITEM TO BE ORDERED </strong></div></td>
</tr>
<tr>
<td><div align="left">FLEX PINMAILER </div></td>
<td><div align="left">
<input name="flex pinmailer" type="text" size="15" maxlength="15">
</div></td>
</tr>
<tr>
<td><div align="left">FREEDOM PINMAILER </div></td>
<td><div align="left">
<input name="freedom pinmailer" type="text" size="15">
</div></td>
</tr>
<tr>
<td><div align="left">ATM ROLLS </div></td>
<td><div align="left">
<input name="atm rolls" type="text" size="15">
</div></td>
</tr>
<tr>
<td><div align="left"></div></td>
<td><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="center">
<input type="submit" name="Submit" value="Submit">
<input name="reset" type="submit" id="reset" value="Reset">
</div></td>
</tr>
</table>
</div>
</form> <p align="center">&nbsp;</p> </td>
</tr>

[rincewind456]

Your form input names are inconsistent, what are all the hidden name fields for?.
This should work as you want.

<?php
$db="trend_db";
$link = mysql_connect("localhost","terry","password");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

//if the submit button is pressed
if (isset($_POST['Submit'])) {    //submit being the name of your submit button

//item_id being the unique identifier for the item.
$flex_Pinmailer = mysql_real_escape_string(trim($_POST["flex_Pinmailer"]));
$freedom_Pinmailer = mysql_real_escape_string(trim($_POST["freedom_Pinmailer"]	));
$atm_rolls = mysql_real_escape_string(trim($_POST["atm_rolls"]));
// error checking to make sure the value entered is a integer
//Change messages to suit
if (!is_numeric($flex_Pinmailer)) {
	echo "Not a number please re-enter.";
}
	if (!is_numeric($freedom_Pinmailer)) {
	echo "Not a number please re-enter.";
}
	if (!is_numeric($atm_rolls)) {
	echo "Not a number please re-enter.";
}
//Update the database record
$sql = 'UPDATE nic_stocks
 SET flex_Pinmailer=flex_Pinmailer - '.$flex_Pinmailer.',
 freedom_Pinmailer = freedom_Pinmailer - '.$freedom_Pinmailer.',
 atm_rolls = atm_rolls - '.$atm_rolls;

$result = mysql_query($sql) or die(mysql_error());

}

$result = mysql_query( "SELECT * FROM nic_stocks" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
$table = "There are $num_rows Items.<P>";
$table .= "<table width=400 border=0>\n";
while ($get_info = mysql_fetch_row($result)){
$table .= "<tr>\n";
foreach ($get_info as $field)
$table .= "\t<td><font face=arial size=3/>$field</font></td>\n";
$table .= "</tr>\n";
}
$table .= "</table>\n";
mysql_close($link);
?>


<form action="" method="post" name="stocks" id="stocks">
<input type="hidden" name= "Recipient" value="[e-mail]">
<input type="hidden" name="bcc" value="[e-mail]">

<table cellspacing="0" cellpadding="0">
<!--DWLayoutTable-->
<tr>
<td height="30" valign="top"><p>Please use the forms below to check your availabe stocks and to order online. </p></td>
<td width="3">&nbsp;</td>
</tr>
<tr>
<td width="642" height="25">&nbsp;</td>
<td></td>
</tr>
</table>
<div align="center">
<table width="442" border="0" background="../images/formbg.jpg">
<tr>
<td colspan="2"><div align="center"><strong>NIC </strong> </div></td>
</tr>
<tr>
<td height="44" colspan="2"><?php
print $table
?><div align="left"></div> <div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="center"><strong>STOCK ORDER FORM </strong></div></td>
</tr>
<tr>
<td width="140"><div align="left"><strong>ITEM</strong></div></td>
<td width="246"><div align="left"><strong>ENTER QUANTITY OF ITEM TO BE ORDERED </strong></div></td>
</tr>
<tr>
<td><div align="left">FLEX PINMAILER </div></td>
<td><div align="left">
<input name="flex_Pinmailer" type="text" size="15" maxlength="15">
</div></td>
</tr>
<tr>
<td><div align="left">FREEDOM PINMAILER </div></td>
<td><div align="left">
<input name="freedom_Pinmailer" type="text" size="15">
</div></td>
</tr>
<tr>
<td><div align="left">ATM ROLLS </div></td>
<td><div align="left">
<input name="atm_rolls" type="text" size="15">
</div></td>
</tr>
<tr>
<td><div align="left"></div></td>
<td><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="center">
<input type="submit" name="Submit" value="Submit">
<input name="reset" type="submit" id="reset" value="Reset">
</div></td>
</tr>
</table>
</div>
</form> <p align="center">&nbsp;</p> </td>
</tr>

[adrianqx]

i pasted the code and did all the adjustments but i get the following error-

"Unknown column 'flex_pinmailer' in 'field list'"

the flex_pinmailer is under the column item_name in nic_stocks table in the database. what could be the problem?
the hidden values in the form were meant for the form precessesing script

[thorpe]

the flex_pinmailer is under the column item_name in nic_stocks table in the database. what could be the problem?

Can we see your database structure? Do you have a field called flex_pinmailer? Is it spelt correctly?

[rincewind456]

Are you sure it's not called flex_Pinmailer, as I noticed in your code that you had used different cases for the same name

[adrianqx]

heres the table i have ,i was wondering with the code ugave me will it in anyway clash with the submit script for the form im using jacks frommail script it's a free form processor code i got from the net "www.dtheatre.com/scripts/"

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2

-- http://www.phpmyadmin.net

-- Host: localhost
-- Generation Time: Mar 21, 2006 at 09:10 AM
-- Server version: 4.0.25

-- PHP Version: 4.3.11

-- Database: trend_db

---

--

-- Table structure for table nic_stocks

CREATE TABLE nic_stocks (
item_no char(4) NOT NULL default '',
item_name char(15) NOT NULL default '',
quantity int(11) NOT NULL default '0',
PRIMARY KEY (item_no)
) TYPE=MyISAM;

--

-- Dumping data for table nic_stocks

INSERT INTO nic_stocks VALUES ('aa1', 'flex_pinmailer', 8000);
INSERT INTO nic_stocks VALUES ('aa2', 'atm_rolls', 5000);
INSERT INTO nic_stocks VALUES ('aa3', 'freedom_mailers', 8000);

[rincewind456]

Replace this bit of code

 //Update the database record 
    $sql = 'UPDATE nic_stocks 
     SET flex_Pinmailer=flex_Pinmailer - '.$flex_Pinmailer.', 
     freedom_Pinmailer = freedom_Pinmailer - '.$freedom_Pinmailer.', 
     atm_rolls = atm_rolls - '.$atm_rolls; 

$result = mysql_query($sql) or die(mysql_error());

with this

	//Update the database record

if ($flex_Pinmailer>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$flex_Pinmailer." WHERE item_no ='aa1'";
	$result = mysql_query($sql) or die(mysql_error());
}

if ($atm_rolls>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$atm_rolls." WHERE item_no ='aa2'";
	$result = mysql_query($sql) or die(mysql_error());
}
if ($freedom_Pinmailer>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$freedom_Pinmailer." WHERE item_no ='aa3'";
	$result = mysql_query($sql) or die(mysql_error());
}

and it should work.

[adrianqx]

HI thanks Man

the code worked it deducted and refreshed the page with the new values problem is it does this only when i remove the "action= formmail.php" from the from action "formmail.php " is the srcript i use to send the contents to a specified email address.is there anyway around this? sorry 4 being such a bother

[Blulagoon]

Take a look at the PHP mail() function.

You could add this to your php code right after you update the database, such that an email is only sent if the db has been updated.

[adrianqx]

can adding such a code 2 my existing code send the form entries and can i add a bcc?

<?php
$to = '[e-mail]';
$subject = 'nic form';
$message = '$flex_pinmailer,$freedom_pinmailer';
$headers = 'From: [e-mail]' . "\r\n" .
'Reply-To: [e-mail]' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
?>

[Houdini]

Just add the bcc: to the $headers

<?php
$to = '[e-mail]';
$subject = 'nic form';
$message = '$flex_pinmailer,$freedom_pinmailer';
$headers = 'From: [e-mail]' . "\r\n" .
'Bcc: [e-mail]' . "\r\n" .
'Reply-To: [e-mail]' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
?>

[rincewind456]

This

<?php 
$db="trend_db";
$link = mysql_connect("localhost","terry","password");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

//if the submit button is pressed
if (isset($_POST['Submit'])) {    //submit being the name of your submit button


$flex_Pinmailer = mysql_real_escape_string(trim($_POST["flex_Pinmailer"]));
$freedom_Pinmailer = mysql_real_escape_string(trim($_POST["freedom_Pinmailer"]    ));
$atm_rolls = mysql_real_escape_string(trim($_POST["atm_rolls"]));
// error checking to make sure the value entered is a integer
//Change messages to suit
if (!is_numeric($flex_Pinmailer)) {
	echo "Not a number please re-enter.";
}
if (!is_numeric($freedom_Pinmailer)) {
	echo "Not a number please re-enter.";
}
if (!is_numeric($atm_rolls)) {
	echo "Not a number please re-enter.";
}
//Update the database record

if ($flex_Pinmailer>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$flex_Pinmailer." WHERE item_no ='aa1'"; 
	$result = mysql_query($sql) or die(mysql_error());
}

if ($atm_rolls>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$atm_rolls." WHERE item_no ='aa2'"; 
	$result = mysql_query($sql) or die(mysql_error());
}
if ($freedom_Pinmailer>0) {
	$sql = "UPDATE nic_stocks
 SET quantity=quantity - ".$freedom_Pinmailer." WHERE item_no ='aa3'"; 
	$result = mysql_query($sql) or die(mysql_error());
}

$to = '[e-mail]';
$subject = 'nic form';
$message = $flex_Pinmailer.', '.$freedom_Pinmailer;
$headers = 'From: [e-mail]' . "\r\n" .
'Bcc: [e-mail]' . "\r\n" .
'Reply-To: [e-mail]' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);

}





$result = mysql_query( "SELECT * FROM nic_stocks" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
$table = "There are $num_rows Items.<P>";
$table .= "<table width=400 border=0>\n";
while ($get_info = mysql_fetch_row($result)){
	$table .= "<tr>\n";
	foreach ($get_info as $field)
	$table .= "\t<td><font face=arial size=3/>$field</font></td>\n";
	$table .= "</tr>\n";
}
$table .= "</table>\n";
mysql_close($link);
?> 


<form action="" method="post" name="stocks" id="stocks"> 
<input type="hidden" name= "Recipient" value="[e-mail]"> 
<input type="hidden" name="bcc" value="[e-mail]"> 

<table cellspacing="0" cellpadding="0"> 
<!--DWLayoutTable--> 
<tr> 
<td height="30" valign="top"><p>Please use the forms below to check your availabe stocks and to order online. </p></td> 
<td width="3">&nbsp;</td> 
</tr> 
<tr> 
<td width="642" height="25">&nbsp;</td> 
<td></td> 
</tr> 
</table> 
<div align="center"> 
<table width="442" border="0" background="../images/formbg.jpg"> 
<tr> 
<td colspan="2"><div align="center"><strong>NIC </strong> </div></td> 
</tr> 
<tr> 
<td height="44" colspan="2"><?php 
print $table
?><div align="left"></div> <div align="left"></div></td> 
</tr> 
<tr> 
<td colspan="2"><div align="center"><strong>STOCK ORDER FORM </strong></div></td> 
</tr> 
<tr> 
<td width="140"><div align="left"><strong>ITEM</strong></div></td> 
<td width="246"><div align="left"><strong>ENTER QUANTITY OF ITEM TO BE ORDERED </strong></div></td> 
</tr> 
<tr> 
<td><div align="left">FLEX PINMAILER </div></td> 
<td><div align="left"> 
<input name="flex_Pinmailer" type="text" size="15" maxlength="15"> 
</div></td> 
</tr> 
<tr> 
<td><div align="left">FREEDOM PINMAILER </div></td> 
<td><div align="left"> 
<input name="freedom_Pinmailer" type="text" size="15"> 
</div></td> 
</tr> 
<tr> 
<td><div align="left">ATM ROLLS </div></td> 
<td><div align="left"> 
<input name="atm_rolls" type="text" size="15"> 
</div></td> 
</tr> 
<tr> 
<td><div align="left"></div></td> 
<td><div align="left"></div></td> 
</tr> 
<tr> 
<td colspan="2"><div align="center"> 
<input type="submit" name="Submit" value="Submit"> 
<input name="reset" type="submit" id="reset" value="Reset"> 
</div></td> 
</tr> 
</table> 
</div> 
</form> <p align="center">&nbsp;</p> </td> 
</tr>

Will send an email when you hit the submit button on the page, as well as updating the database.

[adrianqx]

thanks guys the code works great ur real life savers!!!!