Mass update to SQL database via form

[uncaringuser]

Hey people, I am faced with a problem that I need guidance with in order to solve. I have a catering website that is connected to a mysql database that has a back end to do changes to the menu. I have created an update page that originally updated the price but I was asked for it to update all the fields with any changes being made on the page, sounds simple right? Only problem is, is that the data that is being displayed is pulled from the database and displayed as values in input boxes so that the user can make changes. Each input box has a nameid which is a unique number that is pulled from the database that is stored in a field for each entry.

 

I want it so that the user can change any input box data and click on the one button and it update the records to each correct field based on their own unique id.

 

Here is the code for the front end

 

<form action="chstarterprice.php" method="post">
  <br><br>
  <?php
  include("connection.php");


  $result= mysql_query("SELECT title, subtext, nameid, category, price FROM starters");

  while($row = mysql_fetch_array($result))
  {


  $nameid = $row['nameid'];
  $title = $row['title'];
  $subtext = $row['subtext'];
  $category = $row['category'];
  $price = $row['price'];


  echo"<table>";
  echo"<tr>";
  echo"<td width=\"70px\">";
  echo"<p class=\"form2\">";
  echo "<b>Title</b>";
  echo"</td>";
  echo"<td width=\"120px\">";
  echo"<p class=\"form2\">";
  echo "<b>Subtext</b>";
  echo"</td>";
  echo"<td width=\"80px\">";
  echo"<p class=\"form2\">";
  echo "<b>Category</b>";
  echo"</td>";
  echo"<td width=\"80px\">";
  echo"<p class=\"form2\">";
  echo "<b>Price</b>";
  echo"</td>";

  echo"</tr>";
  echo"<tr>";
  echo"<td width=\"120px\">";
  echo"<p class=\"form2\">";
  echo"<input type=\"text\" name=\"$nameid\" value=\"$title\">";
  echo"</td>";
  echo"<td width=\"80px\">";
  echo"<p class=\"form2\">";
  echo"<input type=\"text\" name=\"$nameid\" value=\"$subtext\">";
  echo"</td>";
  echo"</td>";
  echo"<td width=\"80px\">";
  echo"<p class=\"form2\">";
  echo"<input type=\"text\" name=\"$nameid\" value=\"$category\">";
  echo"</td>";
  echo"<td width=\"120px\">";
  echo"<p class=\"form2\">";
  echo"<input type=\"text\" name=\"$nameid\" value=\"$price\">";
  echo"</td>";
  echo"</tr>";
  echo"<tr>";
  echo"</tr>";
  echo"</table>";

  }

  mysql_close($con); ?>


  <br>
  <input name="Submit" type="submit" value="Change" /></p>
  </form>

 

Here is the backend script that processes the form data

 

<? ob_start(); ?>
<?php


include("connection.php");

$nameid = $_POST['nameid'];
$query = "update starters set title, subtext, nameid, category, price = ('$_POST[nameid]') where 'starters'.'nameid' = '$nameid' or die(mysql_error)";
if(mysql_query($query)){
header("location:change-prices.php");}
else{
header("location:change-prices.php");}
?>
<? ob_flush(); ?>

 

If you guys can point me in the right direction with the code to make this work I would be greatly appreciated, I am really sure that it has something to do with the backend script as opposed to the front end.

 

Please get back to me, thanks guys.

 

Mitch

[cyberRobot]

The form inputs need to have their own individual names. Otherwise, PHP isn't going to know the difference from one field to another. Maybe the following link will help:

http://www.tizag.com/phpT/forms.php

[uncaringuser]

The forms are generated from how much data is in the tables, so it would be impossible to provide a specified name to each of them, hence why I used a variable which was automatically generated from when the data was created. Is there a way I could automatically assign a name to the form inputs that is recognised by PHP thus resulting in a successful query?

[cyberRobot]

The ID could be added after the field name:

 

echo "<input type=\"text\" name=\"title$nameid\" value=\"$title\">";

 

 

Or you could use an array:

 

echo "<input type=\"text\" name=\"title[$nameid]\" value=\"$title\">";

 

More information about arrays in HTML forms can be found here:

http://www.thefuture...tml-form-inputs

[uncaringuser]

I have tried that and its still not working, im certain that the problem lies with the update statement in the back end script. Thank you for your help so far.

[Psycho]

OK, I think you you need to slow down a moment. Let's take it one step at a time.

 

You have a list of records that you want to update on a single form. Each record has (at least) 5 fields. So, you need to supply five input fields for each record. And, most important, you must have a way to logically associate those fields together so that when you POST the form you know which ones go together. In your code above you are giving the fields the exact same name. That will not work since only the last field will be sent in the post data. Each field MUST have a unique name. But, you can use arrays as your name. That is the key.

 

In this case I would name each field according to the value it contains, i.e. the price field will be named price (for all of the price fields). But, you make it an array and use the record's unique ID as the index of the array. So, for example

<input type="text" name="price[$rec_id]" value="$price">

 

Then, when the form is posted you can associate each "group" of fields using the record ID.

[uncaringuser]

I have followed your example and changed the code for the front end, however I do not know how to take the array and update my database using an array as data in the back end. Would you be able to (based on your example) come up with an example update statement so I can get a better understanding?

[cyberRobot]

One problem with the back-end code is it's only processing one set of fields. You'll need to add some type of loop to go through the form input. It isn't too much different than what you have for the front end.

[uncaringuser]

I agree, a for each loop would be the best approach I feel however how would I apply the for-loop syntax to the backend script based on the front end variables I have now created?

[Psycho]

OK, here is some sample code to get you started. Your original HTML markup that was being created was very bloated. If you want all the TDs in a table to use a specific class, there is no need to create a span tag around the contents in each cell with a class name. You can do it with a single line in the style sheet. Also, use the table header tag (<th>) instead of using additional markup (i.e. the bold tag). So, I took the liberty of removing a lot of the cruft.

 

Below are two sample scripts. One to create the form with the input fields created with array names and a second script to process the form.

 

Form script

<?php

include("connection.php");
$query = "SELECT title, subtext, nameid, category, price FROM starters";
$result= mysql_query($query);

$formFields = '';
while($row = mysql_fetch_array($result))
{
   //Make values safe to use in HTML form
   $id       = intval($row['nameid']);
   $title    = htmlspecialchars($row['title'], ENT_QUOTES);
   $subtext  = htmlspecialchars($row['subtext'], ENT_QUOTES);
   $category = htmlspecialchars($row['category'], ENT_QUOTES);
   $price    = htmlspecialchars($row['price'], ENT_QUOTES);

   $formFields .= "  <tr>\n";
   $formFields .= "    <td><input type=\"text\" name=\"title[$id]\" value=\"$title\"></td>\n";
   $formFields .= "    <td><input type=\"text\" name=\"subtext[$id]\" value=\"$subtext\"></td>\n";
   $formFields .= "    <td><input type=\"text\" name=\"category[$id]\" value=\"$category\"></td>\n";
   $formFields .= "    <td><input type=\"text\" name=\"price[$id]\" value=\"$price\"></td>\n";
   $formFields .= "  </tr>\n";
}

mysql_close($con);

?>

<form action="chstarterprice.php" method="post">
<br><br>
<table>";
 <tr>
   <th width="70px">Title</th>
   <th width="120px">Subtext</th>
   <th width="80px">Category</th>
   <th width="80px">Price</th>
 </tr>
 <?php echo $formFields; ?>
</table>
<input name="Submit" type="submit" value="Change" /></p>
</form>

 

Processing script:

if(isset($_POST['title']))
{
   foreach($_POST['title'] as $id => $title)
   {
       $id = intval($id);
       $title = trim($title);
       $subtext  = isset($_POST['title'][$id]) ? trim($_POST['title'][$id]) : '';
       $category = isset($_POST['category'][$id]) ? trim($_POST['category'][$id]) : '';
       $price    = isset($_POST['price'][$id]) ? trim($_POST['price'][$id]) : '';

       //Insert code to validate the data.
       //Are certain values required or must be of a certain type?
       //  e.g. price would be expected to be a number

       //Preepare values for use in query
       $titleSQL    = mysql_real_escape_string($title);
       $subtextSQL  = mysql_real_escape_string($subtext);
       $categorySQL = mysql_real_escape_string($category);
       $priceSQL    = floatval($price);

       //Create an run update query
       //NOTE: Running queiries in loops is very inefficient
       //if this is only for a relatively small number of records (e.g. 50)
       //this should be OK.
       $query = "UPDATE starters
                 SET title = '$titleSQL',
                     subtext = '$subtextSQL',
                     category = '$categorySQL',
                     price = '$priceSQL'
                 WHERE nameid = $id";
       $result = mysql_query($query);
   }
}

[uncaringuser]

Psycho thank you so much for the example script, after a few modifications it proved to be exactly what I needed. Thanks again for the help and support. Consider this matter SOLVED