I have a table that pulls records from mysql database. I am having trouble trying to make it possible to make changes to the input fields and submit the changes inside the one form. Can this be done?
Here is my dynamic table code.
[code]
| Order No. | Order Date | Required Date | Code | CUSTOMER |
Part |
Description |
Qty | Freight | Supplier |
|---|
Thanks for any help or suggestions.
Hi cracker,
It can be done.
The first thing you will need to do is to setup an actual form for your form elements. If you are using html5, this would be something like:[php][/php]
While this should work with other doctypes, HTML5 makes the action attribute optional. Using HTML4, the action attribute is required - in addition, it cannot be empty, so you would need to set the current page as the action in these cases. Many on here and elsewhere will tell you to use a blank action, but this is not correct. It will likely work; however, it is not valid markup and should be avoided. In HTML4 it would look like this:[php][/php]
You will need a submit button:[php][/php]
And you will need to add a form end tag as well. This is simply:[php][/php]
Next, you will need to assign a type and name for each of your inputs. Since you are going to have multiple rows, you will need to add the array indicator to the name. For example, your first input might be:[php]<input type=“text” name=“ordernum[]” value="<?php echo $row_Orders['OrderNumber']; ?>[/php]
The above example will not quite work for your scenario because a blank entry in one of the inputs will throw everything off in your arrays. You will need to specifically index each iteration in order to guarantee that the fields will all be correct after posting. I will demonstrate this below.
Depending on how many rows you will be displaying, you may run into a problem with too large a post on submitting your form. If this happens, you will need to consider different options; let me know and I’ll try to help you find the right one.
You are using a lot of depreciated elements. In addition, there is no reason to use both a table and divs for your data presentation; you should pick one or the other. Many people will tell you that you should never use tables, but this is a valid place for one and I would go ahead and use the table element here. You should also be using css for most of the styles. I will also demonstrate this below.
For testing purposes, here is a mockup to try: on submission, this will display all the contents of your $_POST. This is merely to help determine that everything is being sent on submit and to help you identify each element. The example is presented in html5. One final note: You will need to include your database connect code, it is left out of this example.[php]
Orders Review .ac { text-align: center; } .al { text-align: left; } .ordnum { width: 20px; } <?php if(isset($_POST['submit'])) { // INSERT YOUR PROCESSING CODE HERE echo '';
print_r($_POST);
}
?>
| Order No. | Order Date | Required Date | Code | CUSTOMER | Part | Description | Qty | Freight | Supplier |
|---|---|---|---|---|---|---|---|---|---|
While I believe the code above is correct, I did not test it with actual data, as this would have required setting up a test database and inserting several lines. Hopefully it will give you a good start. Once you look it over let me know any questions you have or problems you encounter.
Best,
jay
It was too late to edit my previous post, but the more I look at it, the less I think a table is the right way to go here. It works fine, but it is really pushing the limit of what tables are meant for. I would probably recode it without the table in hindsight.
Thanks jay for sharing your wisdom. I have been creating this with the help of Dreamweaver and I don’t completely follow the coding it creates. I have my table inside the form tags
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">The action points to this (well I think it points to this at least), which I don’t understand. Can you explain what it means?
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
…and can I change the action to point to a page of my choice after user hits the submit button?
<form id="form1" name="form1" method="POST" action="EditOrder.php">Regards.
cracker
jay, Can you explain what this code does?
[php]<?php
if(isset($_POST[‘submit’]))
{
// INSERT YOUR PROCESSING CODE HERE
echo ‘
’;
print_r($_POST);
}
?>[/php]
Also the comment INSERT YOUR PROCESSING CODE HERE, do I write an UPDATE mysql statement here? Where should I insert the snippet?
You are absolutely correct that you can change the page that receives the form information through the action attribute - your example should work perfectly.
What is happening in the code that you posted is:[php]$editFormAction = $_SERVER[‘PHP_SELF’]; // Sets $editFormAction = this page’s path and name
if(isset($_SERVER[‘QUERY_STRING’])) {
$editFormAction .= “?” . htmlentities($_SERVER[‘QUERY_STRING’]); // Concatenate any additional elements from the query string (such as $_GET variables) to the $editFormAction value[/php]
Prior to html5, the action attribute was required and could not be empty. Many people would disregard this rule since most popular browsers would handle an empty action by posting back to the same page, as desired. This is a bad practice, but very common and what you see in your code is a more “correct” way to do it. Note that HTML5 now makes the action attribute optional, but it still cannot be empty if used.
The section of the code that I posted checks to see if the “submit” button was pressed on the form. You would then include the code that you want to execute when the form is submitted in this section. This would include checking the user’s data, looking for anything that was changed, and updating the table or presenting a changes confirmation screen. It is important that you verify that the submit button was pressed before trying to process the form.
The other two lines are only meant to show you everything that was submitted with the form. This is merely to help you understand how you will need to access your $_POST data. It will also enable you to ensure that everything in the form is working as you expected. You will want to remove this as soon as you are comfortable that you understand what the array looks like and that you are able to access each element.
I see the array is working nicely with the print_r($_POST) after I make some changes to the order. Just need to figure out the UPDATE statement now to update the database. It’s midnight here so, I’m off for some shut eye.
Thanks for the help. Back tomorrow.
Sounds good, feel free to let me know if you have any questions with the update code. I would suggest that you find a way to identify only the things that were changed (how you implement this would depend a lot on who is using the form and how much you trust them). Once you have identified the changed fields, I would present a confirmation layer where the changes that will be made are displayed. After the changes are approved, you would then execute the updates.
To identify changes, there are several different options. I would probably redo the query that the original form was built off of, effectively getting the original values. I would step through each value comparing it to the one returned from the form. If it matches, move on; if it doesn’t, add it to an array of changed fields, etc.
jay
jay,
I have written some code that does the multiple update, however, when I go back and re-open the update page, not all the records are showing.
Basically, I have a search.php and an update.php. The search.php has a table with the current orders. The first column allows you to view the details of the order by opening an new page… orderDetail.php. The last column has a link to the orderEdit.php. Here you can make change to the order details and submit the changes. So far so good. Changes can be made and mysql database is updated… I can go back into the orderDetail.php, see the changes…however…if I go to the orderEdit.php for a particular record again, maybe there was a mistake or something needs to be changed again, not all the records are listed. That I find very strange. Not sure if there is something to do with the counter, or the query? Not even sure which piece of code snippet to show.
This code is a table with the orders and the two links to the CustomerOrderDetail.php and CustomerOrderEdit.php
<?php do { ?>
<tr>
<td align="center"><a href="CustomerOrderDetails.php?recordID=<?php echo $row_Orders['OrderNumber']; ?>"><?php echo $row_Orders['OrderNumber']; ?></a></td>
<td><?php echo $row_Orders['Customer']; ?></td>
<td align="center"><?php echo $row_Orders['OrderDate']; ?></td>
<td align="center"><?php echo $row_Orders['RequiredDate']; ?></td>
<td align="center"><?php echo $row_Orders['Freight']; ?></td>
<td align="center"><a href="DeleteOrder.php?recordID=<?php echo $row_Orders['OrderNumber']; ?>">Delete</a></td>
<td align="center"><a href="CustomerOrderEdit.php?recordID=<?php echo $row_Orders['OrderNumber']; ?>">Edit</a></td>
</tr>
<?php } while ($row_Orders = mysql_fetch_assoc($Orders)); ?>
This snippet is from the CustomerOrderEdit.php
[code]echo ‘’;
echo ’
';
while ($OrderResult = mysql_fetch_array($Orders)) {
echo ‘
echo ‘’;
echo “”;
echo “”;[/code]
If you have any suggestions, where I am going wrong or where to look for the error, that would great.
cracker
I think we need to isolate whether the issue is with the update or with the orderEdit.php file. Can you verify that the information is being correctly updated in the table? You may need to use phpmyadmin (if you have access to it). If it is not being updated correctly, could you post the query that you are using for the update, as well as a list of the tables columns and types.
If the update is taking place correctly, could you post the code for the orderEdit.php (or is it the code that we put together in the earlier post?)
When you say that some of the records are not showing up, is it always the same records?
Records are being updated in the database. All good in the phpmyadmin. I’ve done a few tests and have noticed that the first record is not showing in the CustomerEditOrder.php. The CustomerOrderDetails.php is fine, all working as expected.
This is the query
[php]$query_Orders = sprintf(“SELECT * FROM tbl_OrderDetails WHERE OrderNumber = %s AND tbl_OrderDetails.ClientID = %s”, GetSQLValueString($colname_Orders, “text”),GetSQLValueString($ID_Orders, “int”));
$Orders = mysql_query($query_Orders, $mydatabase) or die(mysql_error());
$row_Orders = mysql_fetch_assoc($Orders);
$totalRows_Orders = mysql_num_rows($Orders);[/php]
This is from the CustomerOrderEdit.php
[php]$i=0;
echo ‘
| Order No. | ’;Order Date | ’;Required Date | ’;Code | ’;Customer | ’;Part | ’;Description | ’;Qty | ’;Freight | ’;Supplier | ’;
|---|---|---|---|---|---|---|---|---|---|
| <input type=‘text’ size=‘10’ name=‘OrderNumber[$i]’ value=’{$OrderResult[‘OrderNumber’]}’ /> | ”;<input type=‘text’ size=‘10’ name=‘OrderDate[$i]’ value=’{$OrderResult[‘OrderDate’]}’ /> | ”;<input type=‘text’ size=‘10’ name=‘RequiredDate[$i]’ value=’{$OrderResult[‘RequiredDate’]}’ /> | ”;<input type=‘text’ size=‘20’ name=‘Code[$i]’ value=’{$OrderResult[‘Code’]}’ /> | ”;<input type=‘text’ size=‘20’ name=‘Customer[$i]’ value=’{$OrderResult[‘Customer’]}’ /> | ”;<input type=‘text’ size=‘20’ name=‘PartCode[$i]’ value=’{$OrderResult[‘PartCode’]}’ /> | ”;<input type=‘text’ size=‘20’ name=‘PartDescription[$i]’ value=’{$OrderResult[‘PartDescription’]}’ /> | ”;<input type=‘text’ size=‘10’ name=‘PartQty[$i]’ value=’{$OrderResult[‘PartQty’]}’ /> | ”;<input type=‘text’ size=‘20’ name=‘Freight[$i]’ value=’{$OrderResult[‘Freight’]}’ /> | ”;<input type=‘text’ size=‘10’ name=‘Supplier[$i]’ value=’{$OrderResult[‘Supplier’]}’ /> | ”;
I’m thinking counter problems somewhere maybe?
Thanks for the help.
jay, I made a change from while loop to a do while. That seemed to fixed the problem. More testing and I will let you know if I am able to move onto the next stage.
Appreciate your time.
cracker
Jay,
I have had a minor problem with my update query. Not sure what happened but I don’t think I changed anything however, I now get an error message and the query does not update the database. Can you shed some light on the problem?
This is the code I use to update the records.
[php]$size = count($_POST[‘OrderNumber’]);
$i = 0;
while ($i < $size) {
$OrderNumber= $_POST[‘OrderNumber’][$i];
$OrderDate= $_POST[‘OrderDate’][$i];
$RequiredDate= $_POST[‘RequiredDate’][$i];
$fk_TrophyCode= $_POST[‘fk_TrophyCode’][$i];
$Customer= $_POST[‘Customer’][$i];
$PartCode= $_POST[‘PartCode’][$i];
$PartDescription= $_POST[‘PartDescription’][$i];
$PartQty= $_POST[‘PartQty’][$i];
$Freight= $_POST[‘Freight’][$i];
$Supplier= $_POST[‘Supplier’][$i];
$OrderDetailID = $_POST[‘OrderDetailID’][$i];
$query = "UPDATE tbl_OrderDetails SET
OrderNumber = '$OrderNumber',
OrderDate = '$OrderDate',
RequiredDate = '$RequiredDate',
fk_TrophyCode = '$fk_TrophyCode',
Customer = '$Customer',
PartCode = '$PartCode',
PartDescription = '$PartDescription',
PartQty = '$PartQty',
Freight = '$Freight',
Supplier = '$Supplier'
WHERE OrderDetailID = '$OrderDetailID'";
mysql_query($query) or die ("Error in update query: $query");
++$i;
}[/php]
The odd thing is that it works on my local site but not on my remote site. All else is okay. I can create a new order, view saved orders, delete but not update.
Regards.
cracker
Jay, I got it to work. I added the database name to the database table name in the query and it fixed the problem.