1

I am making an ordering form and all of the products' data are stored in a MySQL database. There is a menu page with 10 items, each item has its own drop-down list for quantity (qty).

  • I am using PHP to generate HTML form elements (eg. input textfields) and display items.

  • Database has been redesigned: Table1= User_Orders, Table2= Product_Data

  • All code to display product information and to connect to MySQL, is working correctly

My display code:

form action="process.php" method="POST" name="menu"
//PHP
$system = 'SELECT * FROM products ORDER BY id ASC';
if(!$result2=mysql_query($system)){
die('Error encountered. MySQL said: '.mysql_error());
}
while ($rows2 = mysql_fetch_array($result2)) 
{
  $id=$rows2['id'];
  $gitem=$rows2['item'];
  $gdesc=$rows2['description'];

  $menu='<input name="qty1" type="text" class="textfield" id="qty1" value="'. $gitem .'" size="25"/>
          <textarea name="desc1" cols="10" rows="3" class="textfield" id="desc1" style="width: 222px; height: 51px;">'.$gdesc .'</textarea>';
echo $menu; }
//END PHP, restart HTML
</form  >

My Submit Code

//PHP
$submit=$_POST['submit'];
$sitem=$_POST['qty1'];
$sdesc=$_POST['desc1'];
$sql = "UPDATE products SET item='$sitem' ,description='$sdesc' , WHERE `id`='".mysql_escape_string($id)."'";

if($submit) //submit button is pressed
{
mysql_query($sql);
}

Problem: When I submit the form, only the newest/lastest row is updated (the one with the highest ID). The other fields are unaffected.

My idea to why it is happening: I notice the textfields all share the same name's. This is because of the PHP generated HTML.

Question: How do I make each textfield have its own unique name using generated PHP? (eg. qty1, qty2).

My Research I thought about using an array: qty[]

Something like this: How to get multiple selected values of select box in php?

http://www.shotdev.com/php/php-form/php-input-multiple-textbox/comment-page-1/#comment-42091

Please help me, I am stuck.

Lee

3
  • Well, for one, you need to pass the id from mysql into the form, probably in a hidden input field, so that you can properly identify which id in the DB to update. Ooo, or, you can do name="qty[' . $id . ']" and name="desc[' . $id . ']" in the output, so that when submitted the array keys of the fields in the arrays are the DB id. Commented Dec 26, 2011 at 6:10
  • If I pull the ID like this (my preferred way), how do I submit each new name? qt1, qty2, qty3... etc. Commented Dec 26, 2011 at 6:32
  • See my answer. It would be somewhat annoying to check through a bunch of different names, would be doable doing something like a for loop checking keys for $_POST['qty' . $i] or whatever, but it's better to put it into an array to start with, especially considering that it's possible to delete an item in the DB and have a gap there, so you'd have to go for($i = 0; $i < infinityorsomereallybignumberormaxidintheDBatleast; $i++) in order to get them all. Commented Dec 26, 2011 at 6:49

3 Answers 3

1

Either you can use name[] and get the parameters as an array in php

while ($rows2 = mysql_fetch_array($result2)) 
{
   $id=$rows2['id'];
   $gitem=$rows2['item'];
   $gdesc=$rows2['description'];

   $menu='<input name="qty[]" type="text" class="textfield" id="qty1" value="'. $gitem .'" size="25"/> <textarea name="desc[]" cols="10" rows="3" class="textfield" id="desc1" style="width: 222px; height: 51px;">'.$gdesc .'</textarea>';
   echo $menu; 

}

Or you can append a count to name.

$count = 1;
while ($rows2 = mysql_fetch_array($result2)) 
{
   $id=$rows2['id'];
   $gitem=$rows2['item'];
   $gdesc=$rows2['description'];

   $menu='<input name="qty' . $count . '" type="text" class="textfield" id="qty1" value="'. $gitem .'" size="25"/> <textarea name="desc' . $count . '" cols="10" rows="3" class="textfield" id="desc1" style="width: 222px; height: 51px;">'.$gdesc .'</textarea>';
   echo $menu;
   $count++; 
}
Sign up to request clarification or add additional context in comments.

7 Comments

This won't help because the id attribute of the elements never change.
@Tim id attribute doesn't matter, it submits on the name attribute. In fact, it's superfluous to even include it in his code to begin with.
@Tim and if he's using it for styling, then it should be a class= and not id=.
@Phoenix Ah yes, you're right! Of course, it's still not recommended to duplicate IDs...
@Phoenix ID is a perfectly acceptable if you don't ever plan on having more than one element use the styling.
|
0

Ok, first off, you're not passing the item id into the form so it knows what item to actually update.

Let me see what I can do here:

while ($rows2 = mysql_fetch_array($result2)) 
{
  $id=$rows2['id'];
  $gitem=$rows2['item'];
  $gdesc=$rows2['description'];

  $menu='<input name="qty[' . $id . ']" type="text" class="textfield" id="qty1" value="'. $gitem .'" size="25"/>
          <textarea name="desc[' . $id . ']" cols="10" rows="3" class="textfield" id="desc1" style="width: 222px; height: 51px;">'.$gdesc .'</textarea>';
  echo $menu; 
}

This should return 2 arrays when submitted, qty and desc, with the keys of each entry equal to the id from the DB.

Then when checking the submission:

if($_POST['submit']) //Wanna check this first off, checks whether or not form has been submitted, don't want to do anything at all concerning processing the submission if the form hasn't been sumbitted, probably better to do if(isset($_POST['submit'])) rather than checking directly. 
{
 $qty = $_POST['qty'];  //These two variable declarations assign the two form field arrays into easier to type/identify variable names, might want a little additional error checking to at least make sure that these are arrays with is_array() before going into the foreach loop.
 $desc = $_POST['desc'];

 //Loop through each entry from the form, UPDATE entries in database that correspond to array keys
 foreach($qty as $key => $value)  //Set up a loop on the $qty array from the form as array $key and $value and iterate through each entry in the array, the array keys should be the same item id from the DB that corresponds to both qty and desc value entries
 {
    $sitem = mysql_real_escape_string($value);  //Escape $qty[$key] ($value) textfield input from form, put it in an easy to type variable.  Note also, mysql_real_escape_string requires an active mysql connection to have been previously established elsewhere.  mysql_escape_string() which you were using is depreciated, mysql_real_escape_string() is better.

    $sdesc = mysql_real_escape_string($desc[$key]);  //Escape $desc[$key] textarea input from form, put it in an easy to type variable.  Since the keys should match, you can reach outside the foreach into $desc for it.

    $id = mysql_real_escape_string($key);  //Escape $key (id) from form, in case of malicious live html editing, might be best to cast to (int) instead like $id = (int)$key since id should always be an int.

    $sql = "UPDATE `products` SET `item` = '$sitem', `description` = '$sdesc' WHERE `id` = $id LIMIT 1";  //Construct SQL query from escaped variables.  Backticks around field and table names are pretty standard formal syntax.  LIMIT 1 speeds up the query and reduces db server load because it will stop when it finds a matching WHERE condition rather than continuing to look for more, and there should only be a single matching id field, so no reason to continue to look for more.

    mysql_query($sql);  //Execute Query
 }
}

Oh, here's the code for doing it with PDO for extra security:

if($_POST['submit']) //Wanna check this first off
{
 $qty = $_POST['qty'];
 $desc = $_POST['desc'];

 $dsn="mysql:dbname=whateveryourdbisnamed;host=localhost";  //Of course change values to appropriate ones

 $dbh = new PDO($dsn,"mysqlusername","mysqlpassword");  //Connect to DB.  Might want some error checking to make sure it connected.

 foreach($qty as $key => $value)
 {
    $sql = "UPDATE `products` SET `item` = :item, `description` = :desc WHERE `id` = :id LIMIT 1";

    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(":item",$value,PDO::PARAM_INT); //Note:  Not sure if item is a number of not.  If a string of any length, change it to next line
    //$stmt->bindParam(":item",$value,PDO::PARAM_STR,128);  //Note, change last parameter to set max length of string
    $stmt->bindParam(":desc",$desc[$key],PDO::PARAM_STR,256);  //Change last parameter to set max length of desc, or remove if no max length
    $stmt->bindParam(":id",$key,PDO::PARAM_INT);

    $stmt->execute();  //Execute query
 }
}

14 Comments

Hello, do I need to also pass the Item ID to the textfield's name and id properties?
@Lee Names yes, ids no. It's already in the code above. In fact, you can remove the id=whatever from the output code. Well, unless you're using it for jquery or whatever, in which case you'll want id="qty' . $id . '" to pass the proper id numbers to work with. Oh, and if you don't pass the id in the desc name, then it won't know which desc goes with what qty.
Thanks for your quick reply, I'm using the first 2 code boxes at the moment. I can't any to update at the moment. $sql = "UPDATE products SET item='$sitem' , description='$sdesc' , = '$dateproc' WHERE id='".mysql_escape_string($id)."' LIMIT 1"
@Lee What's that extra ,='$dateproc'? That's not valid query syntax, need a field name there. Oh, also, don't put single quotes around ints like the id, and if item is an int in the DB as well.
@Lee Also, make sure qty and desc are properly populated on the submit by adding print_r($qty); and print_r($desc); below the lines that set them in the submission code. You can remove them after you verify that they are properly populated with data from the form. Also, if you are using the submission code above, then I've already done mysql_real_escape_string on the id, and it doesn't need to be done again.
|
0

Try...

$i = 0;
while ($rows2 = mysql_fetch_array($result2)) 
{
  ++$i;
  $id=$rows2['id'];
  $gitem=$rows2['item'];
  $gdesc=$rows2['description'];

  $menu='<input name="qty' . $i . '" type="text" class="textfield" id="qty' . $i . '" value="'. $gitem .'" size="25"/>
      <textarea name="desc' . $i . '" cols="10" rows="3" class="textfield" id="desc' . $i . '" style="width: 222px; height: 51px;">'.$gdesc .'</textarea>';
  echo $menu;
}

1 Comment

Thanks, for the alternate method. I prefer grabbing the ID from MySQL instead, as I need to do more stuff with it :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.