Updating a record field using a form.

I am really struggling here. I have a page on our charity website where a nominated user can update a members record using a straightforward form. The code I am using works but the user has to input all the fields whether or not they need updating. So for example if the firleds were FirstName, Surname ,Role, Address and email and I wanted to change the members email address then the way I am doing it at present all the fields would need to be entered or only the email address would apprear in the record. The update statement and code I using at this stage is:

[code]$id = $_POST[‘member_id’];
$field2 = $_POST[‘FirstName’];
$field3 = $_POST[‘Surname’];
$field4 = $_POST[‘Role’];
$field5 = $_POST[‘Address1’];
$field6 = $_POST[‘Address2’];
$field7 = $_POST[‘Address3’];
$field8 = $_POST[‘Address4’];
$field9 = $_POST[‘PostCode’];
$field10 = $_POST[‘Telephone’];
$field11 = $_POST[‘email’];

if($id){

mysql_query("UPDATE tblMembers SET FirstName='$field2',Surname='$field3',Role='$field4',Address1='$field5',Address2='$field6',Address3='$field7',Address4='$field8',PostCode='$field9',Telephone='$field10',email='$field11' WHERE eventid ='$id'");

}else{}
[/code]

The form I am using is shown in this code:

[code]

<td width="35%"></td>

<tr>

  <td>Member No</td>
  <td><input name="member_id" type="text" id="member_id" /></td>
</tr>
    <tr>
	<td>First Name</td>

  <td><input name="FirstName" type="text" id="FirstName" /></td>
</tr>
    <tr>
	<td>Surname</td>
 
  <td><input name="Surname" type="text" id="Surname" /></td>
      <tr>
	  <td>Role</td>

  <td><input name="Role" type="text" id="Role" /></td>
      <tr>
	  <td>Address</td>
 
  <td><input name="Address1" type="text" id="Address1" /></td>
</tr>
  <td></td>
  <td><input name="Address2" type="text" id="Address2" /></td>
</tr>
  <td><input name="PostCode" type="text" id="PostCode" /></td>
</tr>
<td>Telephone</td>

  <td><input name="Telephone" type="text" id="Telephone" /></td>
</tr>
<td>email</td>

  <td><input name="email" type="text" id="email" /></td>
</tr>
</tr>
</tr>
<tr>
  <td>&nbsp;</td>
  <td><input type="submit" name="Submit" value="Submit" /></td>
</tr>
Post Code
[/code]

My MySQL Database is called: “eachfrie_Events” and the relevant table is called “tblMembers”

Ideally what I would like is a combo box which shows both the member_id and Surname where the user can click on the required member, the form populate with all the fields for that record and when the user changes one of the fields it updates the table without the need to change each field in the record. Can anyone please show me how I can achieve this?

When displaying a form you need to read record for this user from the database and populate form fields. You can modify your form like this (add php code):
[php]<?php
$id = $_REQUEST[‘id’];
if($id){
$r = mysql_query(“SELECT * FROM tblMembers WHERE eventid =’$id’”);
if(mysql_num_rows($r)){
$f = mysql_fetch_array($r);
$member_id = $f[“eventid”];
$FirstName = $f[“FirstName”];
$Surname = $f[“Surname”];
}
}
?>

<td width="35%"></td>

<tr>

 <td>Member No</td>
  <td><input name="member_id" type="text" id="member_id" value="<?php echo htmlspecialchars($member_id) ?>" /></td>
</tr>
   <tr>
  <td>First Name</td>

  <td><input name="FirstName" type="text" id="FirstName" value="<?php echo htmlspecialchars($FirstName) ?>" /></td>
</tr>
   <tr>
  <td>Surname</td>
 
  <td><input name="Surname" type="text" id="Surname" value="<?php echo htmlspecialchars($Surname) ?>" /></td>
</tr>
<tr>
  <td>&nbsp;</td>
  <td><input type="submit" name="Submit" value="Submit" /></td>
</tr>
[/php]

(I skipped some of fields, but you get an idea)

Ah yes I think I follow you. I will try and get back. Thank you for your prompt help. Trying to get tea for the little lady at present so may be a while!!!

I have made that various amendments as per the suggestion (by the way its member_id not eventid - my fault). Unfortunately I don’t seem to have got any further forward. The form doesn’t populate and the user still has to input all the fields. can you see where I have gone wrong:

I will put all the page code in incase the earlier code is affecting this element:

[code]<? require('each-sas.php'); ?><?php

$username = “";
$password = "
***”;
$hostname = “localhost”;

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die(“Unable to connect to MySQL”);
//select a database to work with
$selected = mysql_select_db(“eachfrie_Events”,$dbhandle)
or die(“Could not select eachfrie_Events”);
// query db and loop through rows example
/// query db and loop through rows example

$id = $_POST[‘member_id’];
$field2 = $_POST[‘FirstName’];
$field3 = $_POST[‘Surname’];
$field4 = $_POST[‘Role’];
$field5 = $_POST[‘Address1’];
$field6 = $_POST[‘Address2’];
$field7 = $_POST[‘Address3’];
$field8 = $_POST[‘Address4’];
$field9 = $_POST[‘PostCode’];
$field10 = $_POST[‘Telephone’];
$field11 = $_POST[‘email’];

if($id){

mysql_query("UPDATE tblMembers SET FirstName='$field2',Surname='$field3',Role='$field4',Address1='$field5',Address2='$field6',Address3='$field7',Address4='$field8',PostCode='$field9',Telephone='$field10',email='$field11' WHERE member_id ='$id'");

}else{
$note1 = “The table below is the current list of members for the group. To update any of the members please type the event no in the update table below
and complete all the fields. If you don’t update all fields only those that have been insert will be updated”;
$warning = “Please key an ID number of which record you want to update”;
$find = “find me”;
}
/// query db and loop through rows example

$query = mysql_query(“SELECT * FROM tblMembers”);

$table = "<table border=\"1\" cellspacing=\"10\" body bgcolor=\"#AABBCC\" Align=Center>\n";
			 " <tr>
					<td>Member No</td>
					<td>First Name</td>
					<td>Surname</td>
					<td>Role</td>
					<td>Address1</td>
					<td>Address2</td>
					<td>Address3</td>
					<td>Address4</td>
					<td>PostCode</td>
					<td>Telephone</td>
					<td>email</td>
					
			  </tr>";

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

$table .= "	<tr>
				<td>".$row['member_id']."</td>
				<td>".$row['FirstName']."</td>
				<td>".$row['Surname']."</td>
				<td>".$row['Role']."</td>
				<td>".$row['Address1']."</td>
				<td>".$row['Address2']."</td>
				<td>".$row['Address3']."</td>
				<td>".$row['Address4']."</td>
				<td>".$row['PostCode']."</td>
				<td>".$row['Telephone']."</td>
				<td>".$row['email']."</td>
			</tr>";

}

$table .= “”;

?>

<?php echo '<?xml version="1.0" encoding="utf-8"?>'; ?> AdminHomePage  
											</tr>
										</table>								
									</td>
								</tr>
								<!-- Begin FOOTER -->
								<tr>
									<td align="center" class="magin"></td>
								</tr>
								<!-- End FOOTER -->
								 

								<tr>
									<td align="center" valign="bottom">
										<table cellpadding="0" cellspacing="0">
											<tr>
												<td align="center" valign="bottom" class="marginpw"></td>
												<td width="8"></td>
												<td align="center" valign="bottom" class="marginpw"></td>
											</tr>
										</table>								
									</td>
								</tr>
							</table>		
						</td>
					</tr>
					<tr>
						<td class="bgbshadow"><img src="images/spacer.gif" alt="" width="1" height="18" /></td>
					</tr>
					<tr>
						<td class="bgfooter"><img src="/images/spacer.gif" alt="" width="1" height="30" /></td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</td>
The Friends Group for Peterborough
Supporting EACH a place for living
								<!-- Begin PATHWAY and ICON -->
								<tr>
									<td class="magin">
										<table cellpadding="0" cellspacing="0" width="100%">
											<tr>
												<!-- Begin PATHWAY -->
												<td align="left" width="99%"><?php include("/home/eachfrie/public_html/rvsincludefile/icon_UpdateMember.htm"); ?></td>
												<!-- End PATHWAY -->
												<!-- Begin ICON -->
												<td align="right"><?php include("/home/eachfrie/public_html/rvsincludefile/icon_a5bb94dfb5bd36a36f8071ca5a1ea33c.html"); ?></td>
												<!-- End ICON -->
											</tr>
										</table>								
									</td>
								</tr>
								<!-- End PATHWAY and ICON -->
								<tr>
									<td align="left" valign="top">
										<table cellpadding="0" cellspacing="0" width="100%">
											<tr>
												 

												<td align="left" valign="top" class="magin" id="rv_adjust_width_0" width="100%">

 

<?php echo $note1?>

 

<?php echo $table; ?>

 

<?php echo $warning; ?>

 

<?php $id = $_REQUEST['id']; if($id){ $r = mysql_query("SELECT * FROM tblMembers WHERE member_id ='$id'"); if(mysql_num_rows($r)){ $f = mysql_fetch_array($r); $member_id = $f["member_id"]; $FirstName = $f["FirstName"]; $Surname = $f["Surname"]; $Role = $f["Role"]; $Address1 = $f["Address1"]; $Address2 = $f["Address2"]; $Address3 = $f["Address3"]; $Address4 = $f["Address4"]; $PostCode = $f["PostCode"]; $Telephone = $f["Telephone"]; $email = $f["email"]; } } ?>
<td width="35%"></td>

<tr>

  <td>Member No</td>
  <td><input name="member_id" type="text" id="member_id" value="<?php echo htmlspecialchars($member_id) ?>"/></td>
</tr>
    <tr>
	<td>First Name</td>

  <td><input name="FirstName" type="text" id="FirstName" value="<?php echo htmlspecialchars($FirstName) ?>" /></td>
</tr>
    <tr>
	<td>Surname</td>
 
  <td><input name="Surname" type="text" id="Surname" value="<?php echo htmlspecialchars($Surname) ?>" /></td>
      <tr>
	  <td>Role</td>

  <td><input name="Role" type="text" id="Role" value="<?php echo htmlspecialchars($Role) ?>"/></td>
      <tr>
	  <td>Address</td>
 
  <td><input name="Address1" type="text" id="Address1" value="<?php echo htmlspecialchars($Address1) ?>"/></td>
</tr>
  <td></td>
  <td><input name="Address2" type="text" id="Address2" value="<?php echo htmlspecialchars($Address2) ?>"/></td>
</tr>
  <td><input name="PostCode" type="text" id="PostCode" value="<?php echo htmlspecialchars($PostCode) ?>"/></td>
</tr>
<td>Telephone</td>

  <td><input name="Telephone" type="text" id="Telephone" value="<?php echo htmlspecialchars($Telephone) ?>"/></td>
</tr>
<td>email</td>

  <td><input name="email" type="text" id="email" value="<?php echo htmlspecialchars($email) ?>"/></td>
</tr>
</tr>
</tr>
<tr>
  <td>&nbsp;</td>
  <td><input type="submit" name="Submit" value="Submit" /></td>
</tr>
Post Code

 

	<p>Please click button to logout. Don't forget to log out before
	returning to main website:</p> <?=$logout_button?>
<p>
[/code]

Hope you can help

When you go to editing record, you need to provide member id with the url. Say, if there is a link to edit member info, it should look like this:

<a href="edit.php?id=123">Edit member info</a>

Thats why we have this line before reading member info from the database to pre-fill form fields:
[php]$id = $_REQUEST[‘id’];[/php]

If you do not provide id in the query string (or in the hidden form field), program will not know whose info to populate to the form fields.

So are you saying I need another field in the form or on the page where the user inputs the member_id number? As I am pretty new to all this I am not sure I follow what you mean. Normally, as I already have a connection to the database at the top of the table I would have thought the MySQL query would have found the records.

I don’t really understand your

<a href="edit.php?id=123">Edit member info</a>

Can you explain where this should go and how I should amend it if I need to. Many thanks

Well, since you want your form to be pre-filled Before user press the submit button, you need to provide that member’s id at the time when form is loading. If I understand how your form currently work - user must guess and manually enter the member ID whose record they wish to update, correct?

What I suggest is that you can create a list of all members, or a select box with all members list. So that when someone want to update member record, they just click on a link like below:

<a href="edit.php?id=123">Edit member ABC info</a> <a href="edit.php?id=456">Edit member XYZ info</a>

and get to the edit form with pre-filled fields (read from that member’s record).

Hi

yes you are correct about the user entering a known member_id. Because the user may not know the id this is why at the top of the page I have created a table that shows the records in the table. It would be much better to have a select box with id and name showing from the table that the user can select. I don’t want to hard code the members names as I want his info to com from the table. I am not sure however how practically to do this in terms of code. I see your href suggestions but I don’t really understand them or where they would go.

You don’t have to hardcode member names. All you need to do is to modify the code where you pull all the members into html table. Update this line in your code:
[php]

“.$row[‘FirstName’].”[/php]

to this:
[php]

<a href=‘edit.php?id=".$row[‘member_id’]."’>".$row[‘FirstName’]."[/php]

(replace edit.php to the name of your php script with form)

At the risk of appear even more stupid that I must seem when you say replace the edit.php with the name of the script with form; I am not aware of a script that goes with this form - it is a simple html form. There is a script for another page that has a contact and email form but it is not related to this page. I have looked and cannot see any reference to another script.

The code what you posted in this post - it have both PHP code and html form. What is the file name where this entire code is located? Use this file name instead of edit.php in my code example.

OK so I think I have got that. My code for this now looks like:

[code]$query = mysql_query(“SELECT * FROM tblMembers”);

$table = "<table border=\"1\" cellspacing=\"10\" body bgcolor=\"#AABBCC\" Align=Center>\n";
			 " <tr>
					<td>Member No</td>
					<td>First Name</td>
					<td>Surname</td>
					<td>Role</td>
					<td>Address1</td>
					<td>Address2</td>
					<td>Address3</td>
					<td>Address4</td>
					<td>PostCode</td>
					<td>Telephone</td>
					<td>email</td>
					
			  </tr>";

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

$table .= "	<tr>
				<td><a href='Update-Member_test2.php?id=".$row['member_id']."'>".$row['FirstName']."</a></td>
				<td>".$row['FirstName']."</td>
				<td>".$row['Surname']."</td>
				<td>".$row['Role']."</td>
				<td>".$row['Address1']."</td>
				<td>".$row['Address2']."</td>
				<td>".$row['Address3']."</td>
				<td>".$row['Address4']."</td>
				<td>".$row['PostCode']."</td>
				<td>".$row['Telephone']."</td>
				<td>".$row['email']."</td>
			</tr>";

}

$table .= “”;[/code]

Having just done the First Name (and not changed the rest) the table now shows 2 FirstName results. Do the rest go on one line? If so what is the syntaxt between the fields please?

I did do a quick check with the form on this and it still requires every field to be completed!

Sorry I forgot the code tags

Sorry I forgot to put the code in tags.

This is the amended version

<td><a href='Update-Member_test2.php?id=".$row['member_id']."'>".$row['FirstName']."</a></td>

What is the dividing syntax to add the Surname etc please. I have tried ."’>". but this is clearly not right.

Forgive me the penny has finally dropped. Thank you for your help.

Sponsor our Newsletter | Privacy Policy | Terms of Service