Jump to content

Mass update to SQL database via form


uncaringuser

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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);
   }
}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.