 |  |  |  |  |  |  |  |
PHP Dynamic Form HELP!
Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
I have two slight problems I simply cant find an answer to anywhere, the first, only small.
I have my database printing out items from a database into a select box, this is giving me blank options even though there are none in the database, I remember having this problem before but cant remember how to get around it?
Secondly on the same form I would like another drop down field to change according to what is selected e.g. customer selects product, amount availible is stored in the database so I would like a qty drop down that will display from 1 upto the amount availible.
This is the form in question
And this is the code
Cheers for any help
I have my database printing out items from a database into a select box, this is giving me blank options even though there are none in the database, I remember having this problem before but cant remember how to get around it?
Secondly on the same form I would like another drop down field to change according to what is selected e.g. customer selects product, amount availible is stored in the database so I would like a qty drop down that will display from 1 upto the amount availible.
This is the form in question
And this is the code
PHP Syntax (Toggle Plain Text)
•
•
Join Date: Aug 2007
Posts: 52
Reputation: 
Solved Threads: 5
Junior Poster in Training
OK, there are several things wrong with your code, but because it's would be to lengthy to explain the errors, I spent some time creating a code that will work. You will need to create a few tables to generate dynamic content first:
# phpMyAdmin MySQL-Dump
# version 2.5.1
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Sep 07, 2007 at 09:23 PM
# Server version: 4.0.13
# PHP Version: 4.3.6
# Database : `forumtest`
# --------------------------------------------------------
#
# Table structure for table `itemtbl`
#
# Creation: Sep 07, 2007 at 09:18 PM
# Last update: Sep 07, 2007 at 09:18 PM
#
CREATE TABLE `itemtbl` (
`id` int(11) NOT NULL auto_increment,
`price` varchar(255) NOT NULL default '',
`purchaseqty` int(11) NOT NULL default '0',
`product` varchar(255) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
#
# Dumping data for table `itemtbl`
#
INSERT INTO `itemtbl` VALUES (1, '12345', 5, 'product1', '2007-09-07', 'name1');
# --------------------------------------------------------
#
# Table structure for table `name`
#
# Creation: Sep 07, 2007 at 05:55 PM
# Last update: Sep 07, 2007 at 05:56 PM
#
CREATE TABLE `name` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `name`
#
INSERT INTO `name` VALUES (1, 'name1');
INSERT INTO `name` VALUES (2, 'name2');
INSERT INTO `name` VALUES (3, 'name3');
INSERT INTO `name` VALUES (4, 'name4');
INSERT INTO `name` VALUES (5, 'name5');
# --------------------------------------------------------
#
# Table structure for table `product`
#
# Creation: Sep 07, 2007 at 05:41 PM
# Last update: Sep 07, 2007 at 05:52 PM
#
CREATE TABLE `product` (
`id` int(11) NOT NULL auto_increment,
`product` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `product`
#
INSERT INTO `product` VALUES (1, 'product1');
INSERT INTO `product` VALUES (2, 'product2');
INSERT INTO `product` VALUES (3, 'product3');
INSERT INTO `product` VALUES (4, 'product4');
INSERT INTO `product` VALUES (5, 'product5');
# --------------------------------------------------------
#
# Table structure for table `qty`
#
# Creation: Sep 07, 2007 at 05:51 PM
# Last update: Sep 07, 2007 at 08:58 PM
#
CREATE TABLE `qty` (
`qty` int(11) NOT NULL default '0',
`usedqty` int(11) NOT NULL default '0'
) TYPE=MyISAM;
#
# Dumping data for table `qty`
#
INSERT INTO `qty` VALUES (5, 5);
Then I created a page called forumForm.php
Here is the page code:
<?php require_once('Connections/phpForum.php'); ?>
<?php
error_reporting(E_ALL ^ E_NOTICE);
ob_start();
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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']);
}
/////////////////////////////////////////////////
//Declare Variables
$qty= $_GET['qty'];
$purchaseqty = $_POST['purchaseqty'];
/////////////////////////////////////////////////////////////////////////////
mysql_select_db($database_phpForum, $phpForum);
$query_rs_qty = "SELECT * FROM qty";
$rs_qty = mysql_query($query_rs_qty, $phpForum) or die(mysql_error());
$row_rs_qty = mysql_fetch_assoc($rs_qty);
$totalRows_rs_qty = mysql_num_rows($rs_qty);
if($_POST["Submit"])
{
if (($row_rs_qty['usedqty']) < ($row_rs_qty['qty']))
{
$inventory = "UPDATE qty SET usedqty = usedqty + $purchaseqty ";
$inventory_result = mysql_query($inventory,$phpForum)
or die ("Couldn't update inventory");
}
if (($row_rs_qty['usedqty']) >= ($row_rs_qty['qty']))
{
echo "<b><i><font color =\"red\">We do not have enough inventory to perform this action! </font></i></b><br/>";
echo "<input type='button' name='Back' value='Back' onclick=\"self.location='forumForm.php'\" />";
exit;
}
}
if (((isset($_POST["MM_insert"])) && ($_POST["MM_insert"]) && (($row_rs_qty['usedqty']) < ($row_rs_qty['qty'])) == "orderform")) {
$insertSQL = sprintf("INSERT INTO itemtbl (price, purchaseqty, product, `date`, name) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($_POST['price'], "text"),
GetSQLValueString($_POST['purchaseqty'], "int"),
GetSQLValueString($_POST['product'], "text"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['name'], "text"));
mysql_select_db($database_phpForum, $phpForum);
$Result1 = mysql_query($insertSQL, $phpForum) or die(mysql_error());
$insertGoTo = "#";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
mysql_select_db($database_phpForum, $phpForum);
$query_rs_Product = "SELECT * FROM product";
$rs_Product = mysql_query($query_rs_Product, $phpForum) or die(mysql_error());
$row_rs_Product = mysql_fetch_assoc($rs_Product);
$totalRows_rs_Product = mysql_num_rows($rs_Product);
mysql_select_db($database_phpForum, $phpForum);
$query_rs_name = "SELECT * FROM name";
$rs_name = mysql_query($query_rs_name, $phpForum) or die(mysql_error());
$row_rs_name = mysql_fetch_assoc($rs_name);
$totalRows_rs_name = mysql_num_rows($rs_name);
mysql_select_db($database_phpForum, $phpForum);
$query_rs_itemtbl = "SELECT * FROM itemtbl";
$rs_itemtbl = mysql_query($query_rs_itemtbl, $phpForum) or die(mysql_error());
$row_rs_itemtbl = mysql_fetch_assoc($rs_itemtbl);
$totalRows_rs_itemtbl = mysql_num_rows($rs_itemtbl);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form id="orderform" name="orderform" method="POST" action="<?php echo $editFormAction; ?>">
<table width="606" border="1" bordercolor="#000066">
<tr>
<td width="102">Name</td>
<td width="68">Product</td>
<td width="87">Quanity</td>
<td width="156">Price</td>
<td width="159">Date</td>
</tr>
<tr>
<td><select name="name" id="name">
<option value=""></option>
<?php
do {
?>
<option value="<?php echo $row_rs_name['name']?>"><?php echo $row_rs_name['name']?></option>
<?php
} while ($row_rs_name = mysql_fetch_assoc($rs_name));
$rows = mysql_num_rows($rs_name);
if($rows > 0) {
mysql_data_seek($rs_name, 0);
$row_rs_name = mysql_fetch_assoc($rs_name);
}
?>
</select></td>
<td><select name="product" id="product">
<option value=""></option>
<?php
do {
?>
<option value="<?php echo $row_rs_Product['product']?>"><?php echo $row_rs_Product['product']?></option>
<?php
} while ($row_rs_Product = mysql_fetch_assoc($rs_Product));
$rows = mysql_num_rows($rs_Product);
if($rows > 0) {
mysql_data_seek($rs_Product, 0);
$row_rs_Product = mysql_fetch_assoc($rs_Product);
}
?>
</select></td>
<td><select name="purchaseqty" id="purchaseqty">
<option value=""></option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<input name="qty" type="hidden" id="qty" value="<?php echo $row_rs_qty['qty']; ?>" />
<input name="usedqty" type="hidden" id="usedqty" value="<?php echo $row_rs_qty['usedqty']; ?>"/></td>
<td>$<input name="price" type="text" id="price" /></td>
<td> <input name="date" type="text" id="date" value="<? echo date("Y-m-d "); ?>"/></td>
</tr>
<tr>
<td colspan="5"><div align="center">
<input type="submit" name="Submit" value="Submit" />
<input name="Reset" type="reset" id="Reset" value="Reset" />
</div></td>
</tr>
</table>
<p> </p>
<input type="hidden" name="MM_insert" value="orderform">
</form>
</body>
</html>
<?php
mysql_free_result($rs_Product);
mysql_free_result($rs_name);
mysql_free_result($rs_itemtbl);
mysql_free_result($rs_qty);
ob_flush();
?>
You will need to create your connection string for your information in the file called at the top of the page
(<?php require_once('Connections/phpForum.php'); ?>)
This is what mine looks like:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_phpForum = "localhost";
$database_phpForum = "forumtest";
$username_phpForum = "your_user_name";
$password_phpForum = "your_password";
$phpForum = mysql_pconnect($hostname_phpForum, $username_phpForum, $password_phpForum) or trigger_error(mysql_error(),E_USER_ERROR);
?>
I have this working fine on my localhost.
Hope this helps you out or at least gets you headed in the correct direction!
# phpMyAdmin MySQL-Dump
# version 2.5.1
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Sep 07, 2007 at 09:23 PM
# Server version: 4.0.13
# PHP Version: 4.3.6
# Database : `forumtest`
# --------------------------------------------------------
#
# Table structure for table `itemtbl`
#
# Creation: Sep 07, 2007 at 09:18 PM
# Last update: Sep 07, 2007 at 09:18 PM
#
CREATE TABLE `itemtbl` (
`id` int(11) NOT NULL auto_increment,
`price` varchar(255) NOT NULL default '',
`purchaseqty` int(11) NOT NULL default '0',
`product` varchar(255) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
#
# Dumping data for table `itemtbl`
#
INSERT INTO `itemtbl` VALUES (1, '12345', 5, 'product1', '2007-09-07', 'name1');
# --------------------------------------------------------
#
# Table structure for table `name`
#
# Creation: Sep 07, 2007 at 05:55 PM
# Last update: Sep 07, 2007 at 05:56 PM
#
CREATE TABLE `name` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `name`
#
INSERT INTO `name` VALUES (1, 'name1');
INSERT INTO `name` VALUES (2, 'name2');
INSERT INTO `name` VALUES (3, 'name3');
INSERT INTO `name` VALUES (4, 'name4');
INSERT INTO `name` VALUES (5, 'name5');
# --------------------------------------------------------
#
# Table structure for table `product`
#
# Creation: Sep 07, 2007 at 05:41 PM
# Last update: Sep 07, 2007 at 05:52 PM
#
CREATE TABLE `product` (
`id` int(11) NOT NULL auto_increment,
`product` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `product`
#
INSERT INTO `product` VALUES (1, 'product1');
INSERT INTO `product` VALUES (2, 'product2');
INSERT INTO `product` VALUES (3, 'product3');
INSERT INTO `product` VALUES (4, 'product4');
INSERT INTO `product` VALUES (5, 'product5');
# --------------------------------------------------------
#
# Table structure for table `qty`
#
# Creation: Sep 07, 2007 at 05:51 PM
# Last update: Sep 07, 2007 at 08:58 PM
#
CREATE TABLE `qty` (
`qty` int(11) NOT NULL default '0',
`usedqty` int(11) NOT NULL default '0'
) TYPE=MyISAM;
#
# Dumping data for table `qty`
#
INSERT INTO `qty` VALUES (5, 5);
Then I created a page called forumForm.php
Here is the page code:
<?php require_once('Connections/phpForum.php'); ?>
<?php
error_reporting(E_ALL ^ E_NOTICE);
ob_start();
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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']);
}
/////////////////////////////////////////////////
//Declare Variables
$qty= $_GET['qty'];
$purchaseqty = $_POST['purchaseqty'];
/////////////////////////////////////////////////////////////////////////////
mysql_select_db($database_phpForum, $phpForum);
$query_rs_qty = "SELECT * FROM qty";
$rs_qty = mysql_query($query_rs_qty, $phpForum) or die(mysql_error());
$row_rs_qty = mysql_fetch_assoc($rs_qty);
$totalRows_rs_qty = mysql_num_rows($rs_qty);
if($_POST["Submit"])
{
if (($row_rs_qty['usedqty']) < ($row_rs_qty['qty']))
{
$inventory = "UPDATE qty SET usedqty = usedqty + $purchaseqty ";
$inventory_result = mysql_query($inventory,$phpForum)
or die ("Couldn't update inventory");
}
if (($row_rs_qty['usedqty']) >= ($row_rs_qty['qty']))
{
echo "<b><i><font color =\"red\">We do not have enough inventory to perform this action! </font></i></b><br/>";
echo "<input type='button' name='Back' value='Back' onclick=\"self.location='forumForm.php'\" />";
exit;
}
}
if (((isset($_POST["MM_insert"])) && ($_POST["MM_insert"]) && (($row_rs_qty['usedqty']) < ($row_rs_qty['qty'])) == "orderform")) {
$insertSQL = sprintf("INSERT INTO itemtbl (price, purchaseqty, product, `date`, name) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($_POST['price'], "text"),
GetSQLValueString($_POST['purchaseqty'], "int"),
GetSQLValueString($_POST['product'], "text"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['name'], "text"));
mysql_select_db($database_phpForum, $phpForum);
$Result1 = mysql_query($insertSQL, $phpForum) or die(mysql_error());
$insertGoTo = "#";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
mysql_select_db($database_phpForum, $phpForum);
$query_rs_Product = "SELECT * FROM product";
$rs_Product = mysql_query($query_rs_Product, $phpForum) or die(mysql_error());
$row_rs_Product = mysql_fetch_assoc($rs_Product);
$totalRows_rs_Product = mysql_num_rows($rs_Product);
mysql_select_db($database_phpForum, $phpForum);
$query_rs_name = "SELECT * FROM name";
$rs_name = mysql_query($query_rs_name, $phpForum) or die(mysql_error());
$row_rs_name = mysql_fetch_assoc($rs_name);
$totalRows_rs_name = mysql_num_rows($rs_name);
mysql_select_db($database_phpForum, $phpForum);
$query_rs_itemtbl = "SELECT * FROM itemtbl";
$rs_itemtbl = mysql_query($query_rs_itemtbl, $phpForum) or die(mysql_error());
$row_rs_itemtbl = mysql_fetch_assoc($rs_itemtbl);
$totalRows_rs_itemtbl = mysql_num_rows($rs_itemtbl);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form id="orderform" name="orderform" method="POST" action="<?php echo $editFormAction; ?>">
<table width="606" border="1" bordercolor="#000066">
<tr>
<td width="102">Name</td>
<td width="68">Product</td>
<td width="87">Quanity</td>
<td width="156">Price</td>
<td width="159">Date</td>
</tr>
<tr>
<td><select name="name" id="name">
<option value=""></option>
<?php
do {
?>
<option value="<?php echo $row_rs_name['name']?>"><?php echo $row_rs_name['name']?></option>
<?php
} while ($row_rs_name = mysql_fetch_assoc($rs_name));
$rows = mysql_num_rows($rs_name);
if($rows > 0) {
mysql_data_seek($rs_name, 0);
$row_rs_name = mysql_fetch_assoc($rs_name);
}
?>
</select></td>
<td><select name="product" id="product">
<option value=""></option>
<?php
do {
?>
<option value="<?php echo $row_rs_Product['product']?>"><?php echo $row_rs_Product['product']?></option>
<?php
} while ($row_rs_Product = mysql_fetch_assoc($rs_Product));
$rows = mysql_num_rows($rs_Product);
if($rows > 0) {
mysql_data_seek($rs_Product, 0);
$row_rs_Product = mysql_fetch_assoc($rs_Product);
}
?>
</select></td>
<td><select name="purchaseqty" id="purchaseqty">
<option value=""></option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<input name="qty" type="hidden" id="qty" value="<?php echo $row_rs_qty['qty']; ?>" />
<input name="usedqty" type="hidden" id="usedqty" value="<?php echo $row_rs_qty['usedqty']; ?>"/></td>
<td>$<input name="price" type="text" id="price" /></td>
<td> <input name="date" type="text" id="date" value="<? echo date("Y-m-d "); ?>"/></td>
</tr>
<tr>
<td colspan="5"><div align="center">
<input type="submit" name="Submit" value="Submit" />
<input name="Reset" type="reset" id="Reset" value="Reset" />
</div></td>
</tr>
</table>
<p> </p>
<input type="hidden" name="MM_insert" value="orderform">
</form>
</body>
</html>
<?php
mysql_free_result($rs_Product);
mysql_free_result($rs_name);
mysql_free_result($rs_itemtbl);
mysql_free_result($rs_qty);
ob_flush();
?>
You will need to create your connection string for your information in the file called at the top of the page
(<?php require_once('Connections/phpForum.php'); ?>)
This is what mine looks like:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_phpForum = "localhost";
$database_phpForum = "forumtest";
$username_phpForum = "your_user_name";
$password_phpForum = "your_password";
$phpForum = mysql_pconnect($hostname_phpForum, $username_phpForum, $password_phpForum) or trigger_error(mysql_error(),E_USER_ERROR);
?>
I have this working fine on my localhost.
Hope this helps you out or at least gets you headed in the correct direction!
Last edited by JeniF; Sep 7th, 2007 at 10:24 pm. Reason: typo
I keep hitting "escape", but I'm still here!!!!
:}
:}
Thanks alot for that, but looks really confusing, could you talk me through it, may understand it better, if you dont mind
•
•
Join Date: Aug 2007
Posts: 52
Reputation:
Solved Threads: 5
Junior Poster in Training
Ok a walk through for you:
I created tables based on what your post contained, and inserted some values that I could use to test out the code on. The following are the tables and the "dummy" info;
Then I created a page called forumForm.php
Here is the page code:
starting the form, naming the form, setting the action
the next 2 select boxes are dymanically produced using the recordsets created above
this select box has no dynamically produced values
the 2 hidden fields below are generated from the record sets above and allow the calculations on quanity available
in the date field below, php date function is used to display the current date
You will need to create your connection string for your information in the file called at the top of the page
(<?php require_once('Connections/phpForum.php'); ?>)
This is what mine looks like:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_phpForum = "localhost";
$database_phpForum = "forumtest";
$username_phpForum = "your_user_name";
$password_phpForum = "your_password";
$phpForum = mysql_pconnect($hostname_phpForum, $username_phpForum, $password_phpForum) or trigger_error(mysql_error(),E_USER_ERROR);
?>
as I stated, this code is functional and working on my local host.
I created tables based on what your post contained, and inserted some values that I could use to test out the code on. The following are the tables and the "dummy" info;
CREATE TABLE `itemtbl` (
`id` int(11) NOT NULL auto_increment,
`price` varchar(255) NOT NULL default '',
`purchaseqty` int(11) NOT NULL default '0',
`product` varchar(255) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
#
# Dumping data for table `itemtbl`
#
INSERT INTO `itemtbl` VALUES (1, '12345', 5, 'product1', '2007-09-07', 'name1');
# --------------------------------------------------------
#
# Table structure for table `name`
#
# Creation: Sep 07, 2007 at 05:55 PM
# Last update: Sep 07, 2007 at 05:56 PM
#
CREATE TABLE `name` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `name`
#
INSERT INTO `name` VALUES (1, 'name1');
INSERT INTO `name` VALUES (2, 'name2');
INSERT INTO `name` VALUES (3, 'name3');
INSERT INTO `name` VALUES (4, 'name4');
INSERT INTO `name` VALUES (5, 'name5');
# --------------------------------------------------------
#
# Table structure for table `product`
#
# Creation: Sep 07, 2007 at 05:41 PM
# Last update: Sep 07, 2007 at 05:52 PM
#
CREATE TABLE `product` (
`id` int(11) NOT NULL auto_increment,
`product` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
#
# Dumping data for table `product`
#
INSERT INTO `product` VALUES (1, 'product1');
INSERT INTO `product` VALUES (2, 'product2');
INSERT INTO `product` VALUES (3, 'product3');
INSERT INTO `product` VALUES (4, 'product4');
INSERT INTO `product` VALUES (5, 'product5');
# --------------------------------------------------------
#
# Table structure for table `qty`
#
# Creation: Sep 07, 2007 at 05:51 PM
# Last update: Sep 07, 2007 at 08:58 PM
#
CREATE TABLE `qty` (
`qty` int(11) NOT NULL default '0',
`usedqty` int(11) NOT NULL default '0'
) TYPE=MyISAM;
#
# Dumping data for table `qty`
#
INSERT INTO `qty` VALUES (5, 5);
Then I created a page called forumForm.php
Here is the page code:
<?php
// this is calling a file containing the connection string to your db:
require_once('Connections/phpForum.php');
//since I didn't index the tables. I set the error checking to the following
error_reporting(E_ALL ^ E_NOTICE);
//start retrieving that value types
ob_start();
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}
// set the form action
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
/////////////////////////////////////////////////
//this is done for your comparison values as you mentioned
//if you don't have enough items in stock, you cannot order the item
//Declare Variables
$qty= $_GET['qty'];
$purchaseqty = $_POST['purchaseqty'];
/////////////////////////////////////////////////////////////////////////////
//select the database to show the quanity in a dymanic dropdown list
mysql_select_db($database_phpForum, $phpForum);
$query_rs_qty = "SELECT * FROM qty";
$rs_qty = mysql_query($query_rs_qty, $phpForum) or die(mysql_error());
$row_rs_qty = mysql_fetch_assoc($rs_qty);
$totalRows_rs_qty = mysql_num_rows($rs_qty);
//now, if the form is submitted, perform the operations to determine if the customer can order a product as long as the used (or already ordered quanity) is less than the quanity that you have available and update the quanity table to the new values
if($_POST["Submit"])
{
if (($row_rs_qty['usedqty']) < ($row_rs_qty['qty']))
{
$inventory = "UPDATE qty SET usedqty = usedqty + $purchaseqty ";
$inventory_result = mysql_query($inventory,$phpForum)
or die ("Couldn't update inventory");
}
///here we determine if the quanity available is graer than or equal to the the quanity of product you have in stock, alert the customer, and give them a back button
if (($row_rs_qty['usedqty']) >= ($row_rs_qty['qty']))
{
echo "<b><i><font color =\"red\">We do not have enough inventory to perform this action! </font></i></b><br/>";
echo "<input type='button' name='Back' value='Back' onclick=\"self.location='forumForm.php'\" />";
exit;
}
}
////////////////////////////////////
//the following statements are for the insert statement, reading it across, it says: If you hit the submit button on the order form and the quanity you are ordering is available and has passed the above cirteria, insert into your itemtbl the customers information
if (((isset($_POST["MM_insert"])) && ($_POST["MM_insert"]) && (($row_rs_qty['usedqty']) < ($row_rs_qty['qty'])) == "orderform")) {
$insertSQL = sprintf("INSERT INTO itemtbl (price, purchaseqty, product, `date`, name) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($_POST['price'], "text"),
GetSQLValueString($_POST['purchaseqty'], "int"),
GetSQLValueString($_POST['product'], "text"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['name'], "text"));
mysql_select_db($database_phpForum, $phpForum);
$Result1 = mysql_query($insertSQL, $phpForum) or die(mysql_error());
//after inserting the information, determine where you are redirecting the page to, here I just put in somepage.php
$insertGoTo = "somepage.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
//the following are recordsets that provide dynamic content for your dropdowns
///this is for the product table
mysql_select_db($database_phpForum, $phpForum);
$query_rs_Product = "SELECT * FROM product";
$rs_Product = mysql_query($query_rs_Product, $phpForum) or die(mysql_error());
$row_rs_Product = mysql_fetch_assoc($rs_Product);
$totalRows_rs_Product = mysql_num_rows($rs_Product);
//this is for the name table
mysql_select_db($database_phpForum, $phpForum);
$query_rs_name = "SELECT * FROM name";
$rs_name = mysql_query($query_rs_name, $phpForum) or die(mysql_error());
$row_rs_name = mysql_fetch_assoc($rs_name);
$totalRows_rs_name = mysql_num_rows($rs_name);
//this is for the itemtbl
mysql_select_db($database_phpForum, $phpForum);
$query_rs_itemtbl = "SELECT * FROM itemtbl";
$rs_itemtbl = mysql_query($query_rs_itemtbl, $phpForum) or die(mysql_error());
$row_rs_itemtbl = mysql_fetch_assoc($rs_itemtbl);
$totalRows_rs_itemtbl = mysql_num_rows($rs_itemtbl);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
starting the form, naming the form, setting the action
<form id="orderform" name="orderform" method="POST" action="<?php echo $editFormAction; ?>">
<table width="606" border="1" bordercolor="#000066">
<tr>
<td width="102">Name</td>
<td width="68">Product</td>
<td width="87">Quanity</td>
<td width="156">Price</td>
<td width="159">Date</td>
</tr>
<tr>
<td>
the next 2 select boxes are dymanically produced using the recordsets created above
<select name="name" id="name">
<option value=""></option>
<?php
do {
?>
<option value="<?php echo $row_rs_name['name']?>"><?php echo $row_rs_name['name']?></option>
<?php
} while ($row_rs_name = mysql_fetch_assoc($rs_name));
$rows = mysql_num_rows($rs_name);
if($rows > 0) {
mysql_data_seek($rs_name, 0);
$row_rs_name = mysql_fetch_assoc($rs_name);
}
?>
</select></td>
<td><select name="product" id="product">
<option value=""><--SELECT--></option>
<?php
do {
?>
<option value="<?php echo $row_rs_Product['product']?>"><?php echo $row_rs_Product['product']?></option>
<?php
} while ($row_rs_Product = mysql_fetch_assoc($rs_Product));
$rows = mysql_num_rows($rs_Product);
if($rows > 0) {
mysql_data_seek($rs_Product, 0);
$row_rs_Product = mysql_fetch_assoc($rs_Product);
}
?>
this select box has no dynamically produced values
</select></td>
<td><select name="purchaseqty" id="purchaseqty">
<option value=""><--Select a Qty--></option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
the 2 hidden fields below are generated from the record sets above and allow the calculations on quanity available
<input name="qty" type="hidden" id="qty" value="<?php echo $row_rs_qty['qty']; ?>" />
<input name="usedqty" type="hidden" id="usedqty" value="<?php echo $row_rs_qty['usedqty']; ?>"/></td>
in the date field below, php date function is used to display the current date
<td>$<input name="price" type="text" id="price" /></td>
<td> <input name="date" type="text" id="date" value="<? echo date("Y-m-d "); ?>"/></td>
</tr>
<tr>
<td colspan="5"><div align="center">
<input type="submit" name="Submit" value="Submit" />
<input name="Reset" type="reset" id="Reset" value="Reset" />
</div></td>
</tr>
</table>
<p> </p>
<input type="hidden" name="MM_insert" value="orderform">
</form>
</body>
</html>
<?php
mysql_free_result($rs_Product);
mysql_free_result($rs_name);
mysql_free_result($rs_itemtbl);
mysql_free_result($rs_qty);
ob_flush();
?>
You will need to create your connection string for your information in the file called at the top of the page
(<?php require_once('Connections/phpForum.php'); ?>)
This is what mine looks like:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_phpForum = "localhost";
$database_phpForum = "forumtest";
$username_phpForum = "your_user_name";
$password_phpForum = "your_password";
$phpForum = mysql_pconnect($hostname_phpForum, $username_phpForum, $password_phpForum) or trigger_error(mysql_error(),E_USER_ERROR);
?>
as I stated, this code is functional and working on my local host.
I keep hitting "escape", but I'm still here!!!!
:}
:}
thanks alot, I shall give this ago as soon as I can and let you know the outcome, cheers for your help
Hi, I was very impressed with Jeni's coding. I am working on something very similar but kind of stuck. The code you have listed here is so similar to what I'd need, that is why I was hoping if you could kindly help me with my need. Thanks so much!!! Yashua.
I wish to create an inventory database with the following:
1. When an item is checkmarked and the submit button is pressed that item is deducted from the mySQL Inventory portion.
By default only 1 is deducted per item, but if they add to the Quantity they wish to order then the amount listed in the "quantity" would be deducted from mySQL.
2. I would love to have a link for the admin to be able to add to the stock.
3. I wish to have this form dynamically show how much there is in stock.
4. I wish to have an ALERT email sent to my email when the stock reaches the minimum default of 2 or less per item and that default can be changed for all items in the form.
Example:
When an item is checkmarked it will be ordered and deducted from stock.
If the item is NOT checkmarked, obviously nothing will be ordered and nothing will be deducted.
------------------------------------------------------------------------------------------------
client Function in the Form: Admin Function in the Form:
Chicken [x] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Milk [ ] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Orange [ ] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Apple [x] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
[SUBMIT]
Add Additional Inventory Items to Database
--------------------------------------------------------------------------------------------------------
Is there a quick way of doing this from the code you already have?
I would really appreciate your help. Thanks a million.
I wish to create an inventory database with the following:
1. When an item is checkmarked and the submit button is pressed that item is deducted from the mySQL Inventory portion.
By default only 1 is deducted per item, but if they add to the Quantity they wish to order then the amount listed in the "quantity" would be deducted from mySQL.
2. I would love to have a link for the admin to be able to add to the stock.
3. I wish to have this form dynamically show how much there is in stock.
4. I wish to have an ALERT email sent to my email when the stock reaches the minimum default of 2 or less per item and that default can be changed for all items in the form.
Example:
When an item is checkmarked it will be ordered and deducted from stock.
If the item is NOT checkmarked, obviously nothing will be ordered and nothing will be deducted.
------------------------------------------------------------------------------------------------
client Function in the Form: Admin Function in the Form:
Chicken [x] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Milk [ ] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Orange [ ] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
Apple [x] Quantity [1] In Stock [5] Admin Add to Stock Alert when [ 2] or less.
[SUBMIT]
Add Additional Inventory Items to Database
--------------------------------------------------------------------------------------------------------
Is there a quick way of doing this from the code you already have?
I would really appreciate your help. Thanks a million.
|
Similar Threads
- Setting up a form in PHP (PHP)
- dynamic form filling (PHP)
- PHP Quote form (PHP)
- Storing dynamic form values in Arrays for display & insert (PHP)
- E-mail form data quick and easily with PHP (PHP)
- php login form (PHP)
- php mail form - need to redirect to new page (PHP)
- Need help in design and javascript code for Dynamic form (JavaScript / DHTML / AJAX)
- dreamweaver, PHP and submit form query (PHP)
Other Threads in the PHP Forum
- Previous Thread: Mysql to Excel worksheet, need code
- Next Thread: javascript with php
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest PHP Posts
- Today's Posts
- Unanswered Threads
# 5.2.10 access action address apache api array auto autoincrement broken cakephp checkbox class classes clean clients cms code cron curl database date dehasher destroy directory dissertation domain dropdown dynamic echo$_get[x]changingitintovariable... email encode error errorlog fairness fatalerror file folder form function functions href htaccess html image include indentedsubcategory ip javascript joomla legislation limit link load local login mail masterthesis memberships menu methods multiple multipletables mysql mysqlquery newsletters oop open passwords paypal pdf persist php popup provider query radio random script search secure server sessions simple sockets source space spam sql system table tutorial upload url user variable voteup web youtube