PHP Dynamic Form HELP!

[ezb]

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

// connect to the server
   @mysql_connect($host, $username, $password) or die ("Server is Down");
   @mysql_select_db($database) or die ("Database Error");
   $query="SELECT * FROM itemtbl";
$result=mysql_query($query);

$num =mysql_numrows($result);

mysql_close();

?>
<table>
<tr><td>ID</td><td>Session</td><td>Item</td><td>Date</td><td>Qty</td></tr>
<tr><td><form id="orderform" name="orderform" method="post" action=" "><input name="id" type="text" id="id" size="10"></td><td><input name="name" type="text" id="name" size="20"></td><td><select name="product" size="1">
<?php
$i=0;
while ($i < $num){
$id=mysql_result($result,$i,"itemid");
$info=mysql_result($result,$i,"info");
$price=mysql_result($result,$i,"price");
$qty=mysql_result($result,$i,"itemqty");

echo "<option value=$id> $info<option>";

$i++;
}
?>
</select> 

</td><td><input name="date" type="text" id="date" size="20"></td><td><select name="qty" size="1">
  <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> 

</td></tr>
<tr><td></td><td></td><td><input type="submit" name="submit" value="Submit"></td><td></td><td><input type="reset" name="submit2" value="Reset"></form></td></tr>

</table>

Cheers for any help

[JeniF]

Name
Product
Quanity
Price
Date

<?php
do {
?>
<?php echo $row_rs_name['name']?>
<?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);
}
?>


<?php
do {
?>
<?php echo $row_rs_Product['product']?>
<?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);
}
?>

12345
$
"/>


   

 

[ezb]

Thanks alot for that, but looks really confusing, could you talk me through it, may understand it better, if you dont mind

[JeniF]

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;


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
<strong>// this is calling a file containing the connection string to your db:</strong>
require_once('Connections/phpForum.php');
<strong>//since I didn't index the tables. I set the error checking to the following</strong>
error_reporting(E_ALL ^ E_NOTICE);
<strong>//start retrieving that value types</strong>
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;
}
<strong>// set the form action</strong>
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
/////////////////////////////////////////////////
<strong>//this is done for your comparison values as you mentioned</strong>
<strong>//if you don't have enough items in stock, you cannot order the item</strong>
//Declare Variables
$qty= $_GET['qty'];
$purchaseqty = $_POST['purchaseqty'];
/////////////////////////////////////////////////////////////////////////////
<strong>//select the database to show the quanity in a dymanic dropdown list</strong>
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);
<strong>//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</strong>
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");
}
<strong>///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</strong>
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;
}
}
<strong>////////////////////////////////////</strong>
<strong>//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</strong>
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());

<strong>//after inserting the information, determine where you are redirecting the page to, here I just put in somepage.php</strong>
$insertGoTo = "somepage.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
<strong>//the following are recordsets that provide dynamic content for your dropdowns</strong>
<strong>///this is for the product table</strong>
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);
<strong>//this is for the name table</strong>
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);
<strong>//this is for the itemtbl</strong>
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" " <a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a> ">
<html xmlns=" <a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a> ">
<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" />
&nbsp;&nbsp;&nbsp;
<input name="Reset" type="reset" id="Reset" value="Reset" />
</div></td>
</tr>
</table>
<p>&nbsp;</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.

[ezb]

thanks alot, I shall give this ago as soon as I can and let you know the outcome, cheers for your help

[Yashua]

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.