Problem saving changes with UPDATE with form data.

[HurricaneDigital]

Hi there,

 

I've been trying to figure out why my update script wont commit changes to the database. I'm building a simple call tracking web app for our technicians. Currently the view, add, and delete functions of the app work fine. The trouble I'm running into is when I want to update a record. I want the fields of the form to be auto filled from the current record. On the view page you enter the work order number and hit an update button. This is the update page:

 

   1.
      <?php
   2.
      $username="xxxxxxx";
   3.
      $password="xxxxxxx";
   4.
      $database="xxxxxxx";
   5.
      $con=mysql_connect(localhost,$username,$password);
   6.
      if (!$con)
   7.
      {
   8.
      die('Could not connect: ' . mysql_error());
   9.
      }
  10.
      mysql_select_db($database, $con);
  11.
       
  12.
      $work_order=$_GET['work_order'];
  13.
      $query="SELECT * FROM call_tracker WHERE work_order='$work_order'";
  14.
      $result=mysql_query($query);
  15.
      $num=mysql_numrows($result);
  16.
      $i=0;
  17.
       
  18.
      while ($i < $num) {
  19.
      $location=mysql_result($result,$i,"location");
  20.
      $customer_name=mysql_result($result,$i,"customer_name");
  21.
      $contact_number=mysql_result($result,$i,"contact_number");
  22.
      $date_last_called=mysql_result($result,$i,"date_last_called");
  23.
      $call_frequency=mysql_result($result,$i,"call_frequency");
  24.
      $call_today=mysql_result($result,$i,"call_today");
  25.
      $notes=mysql_result($result,$i,"notes");
  26.
      ++$i;
  27.
      }
  28.
      ?>
  29.
      <table border="0">
  30.
      <form action="update.php" method="get">
  31.
      <tr><td>Work Order:</td><td><? echo $work_order; ?></td></tr>
  32.
      <tr><td>Location: </td><td><input type="text" name="location" value="<? echo $location; ?>"></td></tr>
  33.
      <tr><td>Customer Name: </td><td><input type="text" name="customer_name" value="<? echo $customer_name; ?>"></td></tr>
  34.
      <tr><td>Contact Number: </td><td><input type="text" name="contact_number" value="<? echo $contact_number; ?>"></td></tr>
  35.
      <tr><td>Date Last Called: </td><td><input type="text" name="date_last_called" value="<? echo $date_last_called; ?>"></td></tr>
  36.
      <tr><td>Call Frequency: </td><td><input type="text" name="call_frequency" value="<? echo $call_frequency; ?>"></td></tr>
  37.
      <tr><td>Call Today: </td><td><input type="text" name="call_today" value="<? echo $call_today; ?>"></td></tr>
  38.
      <tr><td>Notes: </td><td><input type="text" name="notes" value="<? echo $notes; ?>"></td></tr>
  39.
      <tr><td></br><input type="button" onclick="window.location.href='view.php'" value="View Call Tracker"></input></td><td align="right"></br><input type="Submit" value="Update"></td></tr>
  40.
      </form>
  41.
      </table>

 

Once the fields are modified it gets sent onto this script to write the changes to the database:

 

   1.
      <?php
   2.
      $location=$_GET['location'];
   3.
      $work_order=$_GET['work_order'];
   4.
      $customer_name=$_GET['customer_name'];
   5.
      $contact_number=$_GET['contact_number'];
   6.
      $date_last_called=$_GET['date_last_called'];
   7.
      $call_frequency=$_GET['call_frequency'];
   8.
      $call_today=$_GET['call_today'];
   9.
      $notes=$_GET['notes'];
  10.
       
  11.
      $username="xxxxxxx";
  12.
      $password="xxxxxxx";
  13.
      $database="xxxxxxx";
  14.
      $con=mysql_connect(localhost,$username,$password);
  15.
      if (!$con)
  16.
      {
  17.
      die('Could not connect: ' . mysql_error());
  18.
      }
  19.
      mysql_select_db("$database", $con);
  20.
       
  21.
      $query="UPDATE call_tracker SET location='$location', customer_name='$customer_name', contact_number='$contact_number', date_last_called='$date_last_called', call_frequency='$call_frequency', call_today='$call_today', notes='$notes' WHERE work_order='$work_order'";
  22.
      mysql_query($query);
  23.
       
  24.
      echo "Record Updated";
  25.
      ?>
  26.
      <br>
  27.
      <form>
  28.
      <input type="button" onclick="window.location.href='view.php'" value="View Call Tracker"></input>
  29.
      </form>

 

I'm very new to PHP and SQL, but I've been using several different tutorials and I have a simpler version of this that just uses 2 fields and it works properly, I just can't seem to figure out why this doesn't work. Any help would be greatly appreciated.

[Psycho]

The problem is pretty simple really. Workign backwards:

 

Your query has the following WHERE clause

 WHERE work_order='$work_order'

 

On the page you define $work_order as

$work_order=$_GET['work_order'];

 

So far, so good. But, on the form page you only use the work order number for displaying on the page:

<tr><td>Work Order:</td><td><?php echo $work_order; ?></td></tr>

 

No where on that page is there a field called 'work_order' to pass the value to the processing page. You can fix this by simply creating a hidden field as follows

<inout name="work_order" type="hidden" value="<?php echo $work_order; ?>" />

[HurricaneDigital]

WOW! Thank you so much. I figured it was an issue passing the data on but I didn't think about using the hidden field. That would explain why my simplified 2 field one worked because it was passing on the work order since I kept it as an editable field. But now I can just hide the actual value that will be passed on and display the work order just as a reference. Thanks for the help!