SOLVED: PHP Script to Update DB Row - Not Working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
will808
Forum Newbie
Posts: 8
Joined: Mon Apr 28, 2008 12:42 pm

SOLVED: PHP Script to Update DB Row - Not Working

Post by will808 »

Hi,

I took the script below from Spoono, and edited it slightly to how I wanted it. The script should do three things:

[1] Display a list of rows in the database, with a link on the unique ID.
[2] Populate the form fields with the row info when selected.
[3] Update the row with the new information from the form when submit is pressed.

1 and 2 are working fine, but the row is not being updated when I hit submit - by adding some echo statements in there, it seems that the hitting of the submit button is not even being recognised, but I could be wrong about that. Any comments would be really appreciated:

Code: Select all

 
<?php include 'connect.php'; ?>   
      <?
//connect to mysql
//then connect as user
     mysql_connect($host,$user,$pass);
   
   //select which database you want to edit
   mysql_select_db($db_name);
 
//If cmd has not been initialized
if(!isset($cmd))
{
  $result = mysql_query("select id, Forename, Surname, CurrentGrade, LicenseExpiry, Phone, Mobile, Email from members order by id");
   
   //print table headers
    $fields_num = mysql_num_fields($result);
 
echo "<h5>Table: {$table}</h5>";
echo "<div id=table><table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
   
}
 
echo "</tr>";
 
 
   
   //run the while loop that grabs all the Member Details
   while($r=mysql_fetch_array($result))
   {
   $id=$r["id"];//take out the id
   $Forename=$r["Forename"];//take out the forename
   $Surname=$r["Surname"];//take out the surname
   $CurrentGrade=$r['CurrentGrade'];
   $LicenseExpiry=$r['LicenseExpiry'];
   $Phone=$r['Phone'];
   $Mobile=$r['Mobile'];
   $Email=$r['Email'];
   
    //make the ID a link
      echo "<tr><td><a href='edit.php?cmd=edit&id=$id'>$id</td>";
      echo "<td>$Forename</td>";
      echo "<td>$Surname</td>";
      echo "<td>$CurrentGrade</td>";
      echo "<td>$LicenseExpiry</td>";
      echo "<td>$Phone</td>";
      echo "<td>$Mobile</td>";
      echo "<td>$Email</td>";
      echo "</tr>";
    }
    echo "</table></div>";
   
}
?>
 
<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
        $id = $_GET["id"];
      $sql = "SELECT * FROM $table WHERE id=$id";
      $result = mysql_query($sql);       
      $myrow = mysql_fetch_array($result);
      ?>
     <div style="clear: both;">&nbsp;</div>
     <input type="hidden" id="DPC_TODAY_TEXT" value="today">
<input type="hidden" id="DPC_BUTTON_TITLE" value="Open calendar...">
<input type="hidden" id="DPC_MONTH_NAMES" value="['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']">
<input type="hidden" id="DPC_DAY_NAMES" value="['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']">
 
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
   
      <fieldset> 
<legend>Personal & Contact Details</legend> 
<ol> 
<li> 
<label for="Forename">Forename: <em>required</em></label> 
<input id="Forename" name="Forename" class="text" type="text" VALUE="<?php echo $myrow["Forename"] ?>" /> 
</li> 
<li> 
<label for="Surname">Surname: <em>required</em></label> 
<input id="Surname" name="Surname" class="text" type="text" VALUE="<?php echo $myrow["Surname"] ?>" /> 
</li>
<li> 
<label for="DateOfBirth">Date of Birth:</label> 
<input id="DateOfBirth" name="DateOfBirth" class="text" type="text" datepicker="true" datepicker_format="YYYY-MM-DD" VALUE="<?php echo $myrow["DateOfBirth"] ?>" /> 
</li> 
<li> 
<label for="Address">Address:</label> 
<textarea id="Address" type="text" name="Address" datepicker="true"><?php echo $myrow["Address"] ?></textarea> 
</li>
<li> 
<label for="Postcode">Postcode:</label> 
<input id="Postcode" name="Postcode" class="text" type="text" VALUE="<?php echo $myrow["Postcode"] ?>" /> 
</li> 
<li> 
<label for="Phone">Telephone:</label> 
<input id="Phone" name="Phone" class="text" type="text" VALUE="<?php echo $myrow["Phone"] ?>" /> 
</li>
<li> 
<label for="Mobile">Mobile:</label> 
<input id="Mobile" name="Mobile" class="text" type="text" VALUE="<?php echo $myrow["Mobile"] ?>" />
</li>
<li> 
<label for="Email">Email:</label> 
<input id="Email" name="Email" class="text" type="text" VALUE="<?php echo $myrow["Email"] ?>" /> 
</li>
<li> 
<label for="Occupation">Occupation:</label> 
<input id="Occupation" name="Occupation" class="text" type="text" VALUE="<?php echo $myrow["Occupation"] ?>" /> 
</li> 
</ol> 
</fieldset> 
<fieldset> 
<legend>Karate Information</legend> 
<ol> 
<li> 
<label for="CurrentGrade">Current Grade:</label> 
<input id="CurrentGrade" name="CurrentGrade" class="text" 
type="text" VALUE="<?php echo $myrow["CurrentGrade"] ?>" /> 
</li> 
<li> 
<label for="DateAcheived">Date Acheived</label> 
<input id="DateAcheived" name="DateAcheived" class="text" 
type="text" datepicker="true" datepicker_format="YYYY-MM-DD" VALUE="<?php echo $myrow["DateAcheived"] ?>" /> 
</li> 
<li> 
<label for="LicenseNumber">License Number:</label> 
<input id="LicenseNumber" name="LicenseNumber" class="text" 
type="text" VALUE="<?php echo $myrow["LicenseNumber"] ?>" /> 
</li> 
<li> 
<label for="LicenseExpiry">License Expiry Date:</label> 
<input id="LicenseExpiry" name="LicenseExpiry" 
class="text textSmall" type="text" datepicker="true" datepicker_format="YYYY-MM-DD" VALUE="<?php echo $myrow["LicenseExpiry"] ?>" /> 
</li> 
</ol> 
</fieldset>
<fieldset> 
<legend>Other Information</legend> 
<ol> 
<li> 
<label for="MedicalProblem">Medical Information:</label> 
<textarea id="MedicalProblem" type="text" name="MedicalProblem"><?php echo $myrow["MedicalProblem"] ?></textarea> 
</li> 
<li> 
<label for="Medication">Medication Taken:</label> 
<textarea id="Medication" type="text" name="Medication"><?php echo $myrow["Medication"] ?></textarea> 
</li> 
<li> 
<label for="CriminalRecord">Criminal Record (details if applicable):</label> 
<textarea id="CriminalRecord" type="text" name="CriminalRecord"><?php echo $myrow["CriminalRecord"]?> </textarea> 
</li>   
</ol> 
</fieldset> 
<fieldset class="submit">
 <input type="hidden" name="cmd" value="edit">
<input type="submit"  name="submit" value="submit" /> 
</fieldset>
   
      </form>
   
   <? }
 
  ?>
   
<?
   if ($_POST["$submit"])
   {
   $Forename = $_POST["Forename"];
   $Surname = $_POST["Surname"];
   $CurrentGrade = $_POST['CurrentGrade'];
   $DateAcheived = $_POST['DateAcheived'];
   $LicenseNumber = $_POST['LicenseNumber'];
   $LicenseExpiry = $_POST['LicenseExpiry'];
   $DateOfBirth = $_POST['DateOfBirth'];
   $MedicalProblem = $_POST['MedicalProblem'];
   $Medication = $_POST['Medication'];
   $CriminalRecord = $_POST['CriminalRecord'];
   $Address = $_POST['Address'];
   $Postcode = $_POST['Postcode'];
   $Phone = $_POST['Phone'];
   $Mobile = $_POST['Mobile'];
   $Email = $_POST['Email'];
   $Occupation = $_POST['Occupation'];
 
     $sql = "UPDATE $table SET Forename='$Forename',Surname='$Surname',CurrentGrade='$CurrentGrade',DateAcheived='$DateAcheived',LicenseNumber='$LicenseNumber',LicenseExpiry='$LicenseExpiry',DateOfBirth='$DateOfBirth',MedicalProblem='$MedicalProblem',Medication='$Medication',CriminalRecord='$CriminalRecord',Address='$Address',Postcode='$Postcode',Phone='$Phone',Mobile='$Mobile',Email='$Mobile',Occupation='$Occupation' WHERE id=$id";
 
      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
 
   }
}
?>
 
Sorry for the lengthy code, but I think some of the problem might be in the form, so wanted to post it all. Thanks for any help.
Last edited by will808 on Sun Jan 04, 2009 3:08 am, edited 1 time in total.
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: PHP Script to Update DB Row - Not Working

Post by sergio-pro »

Hi

if ($_POST["$submit"])

should be

if ($_POST["submit"])
will808
Forum Newbie
Posts: 8
Joined: Mon Apr 28, 2008 12:42 pm

Re: PHP Script to Update DB Row - Not Working

Post by will808 »

Hi Sergio,

Thanks very much for finding that error - should have seen that :oops:

There's another problem now where the last row of the database is being updated rather than the one I want to update - I expect that the unique ID isn't being passed correctly to the query, so this causes the database to update the last row.

Does anyone know what I've done wrong this time?
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: PHP Script to Update DB Row - Not Working

Post by sergio-pro »

At the top of the script you do the following:

Code: Select all

 
#   $result = mysql_query("select id, Forename, Surname, CurrentGrade, LicenseExpiry, Phone, Mobile, Email from members order by id");
...
#    while($r=mysql_fetch_array($result))
#    {
#    $id=$r["id"];//take out the id
 
Thats why the variable $id holds the id of the last user in the result set.
And you don't change the value of the variable later (in form submit context).

Hope this helps
will808
Forum Newbie
Posts: 8
Joined: Mon Apr 28, 2008 12:42 pm

Re: PHP Script to Update DB Row - Not Working

Post by will808 »

OK, I see the problem, thanks.

But I use the ID as a link to select the row to edit, so I still need to use the loop to output the data at the top of the script.

Is there any simple way to make sure that the right ID is passed to the query? Apologies if I'm missing something obvious - I've never done PHP before.
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: PHP Script to Update DB Row - Not Working

Post by sergio-pro »

The loop is ok.

One thing is the ID of the user you fetched from database, to put it to a list.
The other thing is the ID of the user, that came from edit-form to be updated in the database.
You must know that id, to correctly update user.

You've set it to a hidden field, but did not read before updating database.\

I think this should help:

if ($_POST["$submit"])
{
$id=$_POST["id"];
$Forename = $_POST["Forename"];
will808
Forum Newbie
Posts: 8
Joined: Mon Apr 28, 2008 12:42 pm

Re: PHP Script to Update DB Row - Not Working

Post by will808 »

Hi Sergio,

That's fixed it - many thanks for your help!
Post Reply