How to update mysql data in php properly

Question

I'm trying to have an html form which updates mysql data. Now , I have this code(which is also a form action) and I'm trying to also use this as a form for my update. Because I will need the data that this form would show, so that it will be easier for the users to update only what they wish to update.

this is the form that will search the data :

  <form name="form1" method="post" action="new.php">
 <td>

  <table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
  <tr>
 <td colspan="16" style="background:#9ACD32; color:white; border:white 1px solid; text-align:  center"><strong><font size="3">ADMISSION INFORMATION SHEET</strong></td>
 </tr>
 <tr>

    <td width="54"><font size="3">Hospital #</td>

   <td width="3">:</td>

<td width="168"><input name="hnum" type="text" id="hospnum"></td>


    <td width="41"><font size="3">Room #</td>
   <td width="3">:</td>
   <td width="168"><input name="rnum" type="text" id="rnum"></td>


 <td width="67"><font size="3">Admission Date</td>
    <td width="3">:</td>
   <td width="168"><input name="ad8" type="text" id="ad8"></td>

    <td width="67"><font size="3">Admission Time</td>
     <td width="3">:</td>
  <td width="168"><input name="adtym" type="text" id="adtym"></td>
   </tr>
    <tr>


     <td><font size="3">Last Name</td>
<td>:</td>
<td><input name="lname" type="text" id="lname"></td>
<td><font size="3">First Name</td>
<td>:</td>
<td><input name="fname" type="text" id="fname"></td>
<td><font size="3">Middle Name</td>
<td>:</td>
<td><input name="mname" type="text" id="mname"></td>
</tr>


<tr>
<td><font size="3">Civil Status</td>
<td>:</td>
<td><input name="cs" type="text" id="cs"></td>
<td><font size="3">Age</td>
<td>:</td>
<td><input name="age" type="text" id="age"></td>
<td><font size="3">Birthday</td>
<td>:</td>
<td><input name="bday" type="text" id="bday"></td>

</tr>

<tr>
<td><font size="3">Address</td>
<td>:</td>
<td><input name="ad" type="text" id="ad"></td>
<td><font size="3">Telephone #</td>
<td>:</td>
<td><input name="telnum" type="text" id="telnum"></td>

<td width="23"><font size="3">Sex</td>
<td width="3">:</td>
<td width="174"><input name="sex" type="text" id="sex"></td>
</tr>

<tr>
<td><font size="3">Pls. Check</td>
<td>:</td>
<td><input name="stats1" type="checkbox" id="SSS" value="SSS">SSS</td>
<td><font size="3"></td>
<td>:</td>
<td><input name="stats2" type="checkbox" id="nonmed" value="NonMedicare">Non Medicare</td>

<td><font size="3"></td>
<td>:</td>
<td><input name="stats3" type="checkbox" id="sh" value="stockholder">Stockholder</td>
</tr>


<tr>
<td><font size="3"></td>
<td></td>
<td><input name="stats4" type="checkbox" id="gsis" value="GSIS">GSIS</td>
<td><font size="3"></td>
<td></td>
<td><input name="stats5" type="checkbox" id="senior" value="seniorcitizen">Senior-Citizen</td>


<tr>
<td><font size="3"></td>
<td></td>
<td><input name="stats6" type="checkbox" id="dep" value="dependent">Dependent</td>
<td><font size="3"></td>
<td></td>
<td><input name="stats7" type="checkbox" id="emp" value="employee">Employee</td>
<td><font size="3"></td>
<td></td>
<td><input name="stats8" type="text" id="" value="">Others</td>

</tr>

<tr>
<td><font size="3">Admitting/Attending Nurse</td>
<td>:</td>
<td><input name="nurse" type="text" id="nurse"></td>

</tr>






<tr>

<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Search"></td>

</form>



</tr>
</table>
</td>
</form>
</tr>
</table>

This is new.php( will display the corresponding data based on the firstname inputted. And will also try to serve as a form for the update process.

    <?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Hospital", $con);

mysql_query("
    UPDATE t2
    SET HOSPNUM='" . mysql_real_escape_string($_POST['hnum']) . "',
        ROOMNUM='" . mysql_real_escape_string($_POST['rnum']) . "',
        LASTNAME='" . mysql_real_escape_string($_POST['lname']) . "',
        FIRSTNAME='" . mysql_real_escape_string($_POST['fname']) . "',
        MIDNAME='" . mysql_real_escape_string($_POST['mname']) . "',
        CSTAT='" . mysql_real_escape_string($_POST['cs']) . "',
        AGE='" . mysql_real_escape_string($_POST['age']) . "',
        BDAY='" . mysql_real_escape_string($_POST['bday']) . "',
        ADDRESS='" . mysql_real_escape_string($_POST['ad']) . "',
        STAT='" . mysql_real_escape_string($_POST['stats1']) . "',
        STAT2='" . mysql_real_escape_string($_POST['stats2']) . "',
        STAT3='" . mysql_real_escape_string($_POST['stats3']) . "',
        STAT4='" . mysql_real_escape_string($_POST['stats4']) . "',
        STAT5='" . mysql_real_escape_string($_POST['stats5']) . "',
        STAT6='" . mysql_real_escape_string($_POST['stats6']) . "',
        STAT7='" . mysql_real_escape_string($_POST['stats7']) . "',
        STAT8='" . mysql_real_escape_string($_POST['stats8']) . "',
        NURSE='" . mysql_real_escape_string($_POST['nurse']) . "',
        TELNUM=''" . mysql_real_escape_string($_POST['telnum']) . "'
    WHERE FNAME=''" . mysql_real_escape_string($_POST['fname']) . "'
");

mysql_close($con);
?>

Please help, I'm just a beginner I want to learn

Answer 1

You are trying to do multiple things, make your approach a little simple

Create a form that asks for user's first name

then post this name to a php script which then fetches the details based on the username

and then finally display the page that updates user record.

search for the user firstname
like this:

if(isset($_POST['search']))
{
    $con = mysql_connect("localhost","root","");
      if (!$con)
       {
         die('Could not connect: ' . mysql_error());
      }
    mysql_select_db("Hospital", $con);
    $result = mysql_query("SELECT * FROM t2 WHERE FIRSTNAME='".$_POST['fname']."');

    $data = mysql_fetch_assoc($result);
}

FORM FOR DISPLAYING THE RECORDS GOES HERE

then post this form to a new script (update.php) which updates user's records. Let me know if you are facing problems.

Answer 2

Why have you got two apostrophes before the values for TELNUM and FNAME in the SQL? That will make the values zero length strings followed by rubbish which will invalidate the query.

Answer 3

Your forms are correct. Few things are missing.

First your update query update records according to user first name which is wrong as (there might be more than one records whose first name are same), so use user primary key.(For Example UserID).

Another important thing is before updating check that Is the request is GET or POST.