[RESOLVED] display form from database but how submit to new db?

DKY

Okay, I have looped through a database to show product listings that will have a qty box next to it. I guess my question is two-fold because I might be thinking of this wrong. I need to know how to submit dynamic data? I'm used to calling the input name and putting that in my update sql query but since I don't know what the input name is then I'm not sure how to do this. The other thing is how would I store the customers order for future reference (by the customer of course)?

My php is:

<?php
echo "<form name='form1' method='post' action='".$_SERVER["PHP_SELF"]."' enctype='multipart/form-data'>";
echo "<table width='100%' cellpadding='0' cellspacing='0' border='0' bordercolor='#000000'><tr>\n";
$CatCount = 0;
$CatQuery = "SELECT DISTINCT categoryname FROM $ProductsTable ORDER BY categoryname DESC";
$CatResult = mysql_query($CatQuery) or die(mysql_error().'<br />');
while ($Row = mysql_fetch_assoc($CatResult))
{
	$CategoryValue = $Row['categoryname'];
	if($CatCount % 2 == 0){echo"</TR><TR>\n";}
	echo "<td align=center valign=top><BR>";
	echo "<fieldset class='orderonline'>";
	echo "<legend>".$Row['categoryname']."</legend>";
	echo "<table width='100%' cellpadding='0' cellspacing='0' border='0' bordercolor='#000000'>";
	$ProdQuery = "SELECT * FROM $ProductsTable where categoryname = '$CategoryValue' ORDER BY productname DESC";
	$ProdResult = mysql_query($ProdQuery) or die(mysql_error().'<br />');
	while ($Row = mysql_fetch_assoc($ProdResult))
		{
			echo "<tr><td width='50' valign=top align=center><input class='orderonline' name='".$Row['productname']."' type='text'/></td>";
			echo "<td width='50' valign=top align=left>&nbsp;</td>";
			echo "<td valign=top align=left>".$Row['productname']."<BR>&nbsp;&nbsp;&nbsp;".$Row['uom']."</td></tr>";
	}
	echo "</table>";
	echo "</fieldset>";
	$CatCount = $CatCount+1;
}
if($CatCount % 2 == 0){echo"</TR><TR><TD colspan='2'>test</TD>\n";}
else{echo"<TD align=center>Comments</TD>\n";}
echo"</TR><TR><TD colspan='2'><fieldset class='submit'><button type='submit' id='submit-go' name='submit'>Submit</button></fieldset></TD>\n";
echo "</tr></table></form>";
?>

bradgrafelman

Why are you using the product name as the name of the text field? What type of data are they entering into the text field? Is this the quantity box? If so, I would change the name of the field to something like qty[$Row['productid']] (where $Row['productid'] is some unique identifier for the row, such as an AUTO_INCREMENT column). Then, when you process the POST'ed data, $_POST['qty'] will be an array where the keys are the product ID's and the values are the associated quantities that the customer entered.

As for storing the order, you could break it down into three tables: customers, orders, order_items. Table "customers" contains personal info about the customer (name, address, etc. etc.). Table "orders" contains an order id and any other metadata you might want to know about the order (datetime ordered, datetime shipped, shipping reference/tracking number, etc. etc.). Table "order_items" would contain an order id, product id, quantity, and any other information you want to store (e.g. price per item (which could change as you adjust prices), adjusted/discount amount, etc.).

Obviously the above DB schema is just some generic example I created on-the-fly... your specific needs might warrant additions or omissions from the basic structure.

igorek

double quotes. (html)

<table width="100%" cellpadding="0" cellspacing="0" border="0" bordercolor="#000000">
  <form method="post" action="<?php echo $_SERVER["PHP_SELF"]; ?>">
  <tr>
<?php
// where is $productsTable??

  $catCount   = 0;  

  $query      = "SELECT DISTINCT categoryname FROM '{$productsTable}' ORDER BY categoryname DESC";
  $result     = mysql_query($query) or die(mysql_error() . '<br />');
  while ($row = mysql_fetch_assoc($result)) {
    $categoryValue = $row['categoryname']; // the last result of loop saved
    // remainder ?
    if ($catCount % 2 == 0) { echo "</tr><tr>\n" ; }
?>
    <td align="center" valign="top">
      <br />
      <fieldset class="orderonline">
      <legend><?php echo $row['categoryname']; ?></legend>
<!-- table within table start -->
      <table width="100%" cellpadding="0" cellspacing="0" border="0" style="border-color:#000000;">
<?php
  $query  = "SELECT * FROM '{$productsTable}' WHERE categoryname = '{$categoryValue}' ORDER BY productname DESC";
  $result = mysql_query($query) or die(mysql_error().'<br />');
    while ($row = mysql_fetch_assoc($result)) {
?>
        <tr>
          <td width="50" valign="top" align="center">
            <input class="orderonline" name="<?php echo $row['productname']; ?>" type="text" />
          </td>
          <td width="50" valign="top" align="left"></td>  

          <td valign="top" align="left"><?php echo $row['productname'] . '<br />   ' .$row['uom']; ?></td>
        </tr>
<?php  } // close the while loop #2 ?>
      </table>
<!-- table within table end -->      

      </fieldset>
<?php
    $catCount = $catCount++;
} // close while loop #1
  if ($catCount % 2 == 0) {
    echo '</tr><tr><td colspan="2">test</tr>\n';
  } else { echo '<td align="center">Comments</td>\n'; }
?>   

  </tr>
  <tr>
    <td colspan="2">
    <fieldset class="submit">
        <input type="submit" id="submit-go" name="submit" value="Submit" />
      </fieldset>
    </td>
  </tr>
</form>
</table>

bradgrafelman

@: Er... why'd you put single quotes around the table name? That'll definitely cause a SQL query error.

DKY

@
Strictly curiousity but what's the difference between using productid and productname? I figured it was a unique value so it wouldn't be an issue would ID work more efficiently than name?

Also curious that once I have all this information in an array how would I pull it out to do an sql insert statement?

So the three tables could look like (just making sure I understand):

Customers
--Name
--Address
--Email
--CustomerID

Orders
--OrderID
--CustomerID

OrderItems
--OrderID
--DatetimeOrdered
--DatetimeShipped
--TrackingInfo

@
I don't know what you're showing me in your example above, I could very easily be missing something.

bradgrafelman

DKY wrote:
Strictly curiousity but what's the difference between using productid and productname? I figured it was a unique value so it wouldn't be an issue would ID work more efficiently than name?

I don't have any empirical evidence to support this (and I'm far too lazy to actually fire up my SQL server and get that evidence :p), but I would think that searching through a key listing of integers would indeed be more efficient that strings.

Plus, it reduces the length of the string you'd need to pass around... e.g. "482" versus "Heavy-duty Industrial Strength Pseudoaluminum Polybicarbonate Thingamabob w/ Tritanium-reinforced Backplating." (By the way, can I place an order for 3 of those?)

Unless you ship partial orders, I would think that this:

--DatetimeOrdered
--DatetimeShipped
--TrackingInfo

would be better suited for the "Orders" table, so that it's not duplicated for each item in the order. Again, that's something you'd have to decide since I don't know as much about your specific needs/plans as you do.

DKY

LOL! That makes sense, I forgot that we carry those psuedoaluminum bicarbonate soda things...

So when I get my code together using ID I still run into the same problem as my first post. How do I pull this data up to use it in an insert statement?

I'm used to doing something like this (an example from another project so don't get this code confused with this project):

    	$CategoryName = addslashes($_POST['category']);
    	$ProductName = addslashes($_POST['product']);
    	$CatProd = addslashes($_POST['category']).addslashes($_POST['product']);
    	$UOM = addslashes($_POST['uom']);
    	$CategoryName_query = "INSERT INTO $ProductsTable
		(TimeStamp,categoryname,productname,uom,catprod)
		VALUES
		('$timenow','$CategoryName','$ProductName','$UOM','$CatProd')";

		if (!($CategoryName_result      = mysql_query($CategoryName_query)))
			{
				$mcrmessage = mysql_error();
				echo "$mcrmessage<br>";
				die();
			}

This would be an instance where I know the name of the fields that I'm inserting (like the first one, I'm calling $_POST['category'). How would I loop through the array to figure out which ID's to insert?

bradgrafelman

If you're still using that code elsewhere... note that [man]addslashes/man should never be used to prepare data for a MySQL query; that's what [man]mysql_real_escape_string/man is used for.

If you submit the elements where $_POST['qty'] is an array, then you'd simply do something like:

foreach($_POST['qty'] as $prod_id => $quantity) {
    // code here
}

DKY

Okay, I'm definitely missing something so, I changed this

echo "<tr><td width='50' valign=top align=center><input class='orderonline' name='".$Row['productname']."' type='text'/></td>";

to this

echo "<tr><td width='50' valign=top align=center><input class='orderonline' name='".qty[$Row['ID']]."' type='text'/></td>";

and I'm getting this

Parse error: syntax error, unexpected '[', expecting ',' or ';'

anoopmail

i think u missed the $ in qty, doesn't that be $qty?

DKY

I don't know, I was looking this up on google and found an example that showed me that this would work (and it does):

name='qty[]'

but this doesn't work

name='qty[".$Row['ID']."]'

DKY

You know what, my error is somewhere else and I can't figure it out. I pasted in the same code and I'm getting an error so I'll have to look into this a little more.

bradgrafelman

qty isn't a variable; it (and the brackets) should appear literally in the string, e.g.:

echo "<tr><td width='50' valign=top align=center><input class='orderonline' name='qty[" . $Row['ID'] . "]' type='text'/></td>";

DKY

Okay, I was missing a } (of course)

I have the suggested code now

echo "<tr><td width='50' valign=top align=center><input class='orderonline' name='qty[".$Row['ID']."]' type='text'/></td>";

and up top I put

if (((isset($_POST["submit"])) && ($_POST["submit"] == "Submit")))
{
    foreach($_POST['qty'] as $ID => $QTY)
        {
            if($QTY<>"")
            {

    	}
    }
}

but I can't wrap my brain around the steps, do I:
- insert a new row with an new OrderID
- pull the OrderID back in
- loop through the array and do an update for each value in the array (so I could end up doing like 50 update queries)? That doesn't sound right. What am I missing?

igorek

brad, i was rushing i guess and didn't see that i was looking for a table name instead of data to be inserted.

DKY,
as for the code I posted was a clearer version of your code compare. + which browser are you using I dont think any of your html will be interpreted correctly.

bradgrafelman

igorek;10953202 wrote:
I dont think any of your html will be interpreted correctly.

What's wrong with his HTML?

DKY wrote:
but I can't wrap my brain around the steps, do I:
- insert a new row with an new OrderID
- pull the OrderID back in
- loop through the array and do an update for each value in the array (so I could end up doing like 50 update queries)? That doesn't sound right. What am I missing?

Well... I guess it depends on what you're trying to do with this code. Is this an order form, or are they editing a previous order? What does your DB schema/structure look like?

igorek

my mistake I guess when I learned html and was tought to use double quotes and had a few errors during interpretation when i used single quotes. learn something new everyday.