update mysql not working

[TopTrainer]

I have this form that inserts data into the database:

<form action="add_members.php" method="post">
   <p>Firstname:
  <input type="text" name="firstname">
      Lastname:
     <input type="text" name="lastname">
   </p>
   <p>Company:
  <input name="Company" type="text" size="60">
     </p>
   <p>Category:
   <input name="Category" type="text" size="60">
   </p>
   <p>Description - what do you want other members to know about you?<br>
     <textarea name="Description" cols="50" rows="10" wrap="physical"></textarea>
   </p>  
   <p>Address 1:
     <input type="text" name="Address1">
   <p>Address 2:
     <input type="text" name="Address2">
   <p>City:
     <input type="text" name="City">
   State:
     <input name="State" type="text" size="4">
   Zip:
     <input name="Zip" type="text" size="9">
     
   <p>
      Email:
     <input name="Email" type="text" size="40">
</p>
   <p>
      Website:
     <input name="website" type="text" value="http://" size="40">
</p>

   <p>
      Office Phone:
     <input name="OfficePh" type="text" size="12">

      Cell Phone:
     <input name="CellPh" type="text" size="12">
</p>
<input type="submit">
 
 </form>

 

This file does the actual insertion

<?php
$con=mysqli_connect("mysql.mysite.org","swhrma","pwd","swhrmamembers");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$sql="INSERT INTO members (FirstName, LastName, Company, website, Category, Description, Address1, Address2, Email, City, State, Zip, OfficePh, CellPh)
VALUES
('$_POST[firstname]',
'$_POST[lastname]',
'$_POST[Company]',
'$_POST[website]',
'$_POST[Category]',
'$_POST[Description]',
'$_POST[Address1]',
'$_POST[Address2]',
'$_POST',
'$_POST[City]',
'$_POST[state]',
'$_POST[Zip]',
'$_POST[OfficePh]',
'$_POST[CellPH]')";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
 header('Location: thankyou3.htm');
 mysqli_close ($con);
 ?>

 

This site lists members in the database and the user can select the member to update

<?php
$host="mysql.mysite.org"; // Host name
$username="mysite"; // Mysql username
$password="pwd"; // Mysql password
$db_name="mysite"; // Database name
$tbl_name="members"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="6"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Company</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>id</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td><? echo $rows['FirstName']; ?></td>
<td><? echo $rows['LastName']; ?></td>
<td><? echo $rows['Company']; ?></td>
<td><? echo $rows['Email']; ?></td>
<td><? echo $rows['id']; ?></td>

<!-- link to update.php and send value of id-->
<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>
</tr>

<?php
}
?>

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

<?php
mysql_close();
?>

 

 

This file shows the user which member he has chosen to update and gives the user form fields to make changes.

<?php
$host="mysql.mysite.org"; // Host name
$username="mysite"; // Mysql username
$password="pwd"; // Mysql password
$db_name="mysite"; // Database name
$tbl_name="members"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];

// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>


<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>FirstName</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Company</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>ID</strong></td>
</tr>
<tr>
<td> </td>
<td align="center">
<input name="FirstName" type="text" id="FirstName" value="<? echo $rows['FirstName']; ?>">
</td>
<td align="center">
<input name="LastName" type="text" id="LastName" value="<? echo $rows['LastName']; ?>" size="15">
</td>
<td>
<input name="Company" type="text" id="Company" value="<? echo $rows['Company']; ?>" size="15">
</td>
<td>
<input name="Email" type="text" id="Email" value="<? echo $rows['Email']; ?>" size="15">
</td>
<td>
<input name="id" type="text" id="id" value="<? echo $rows['id']; ?>" size="8">
</td>
</tr>
<tr>
<td> </td>
<td>
<!--<input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">-->
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php
// close connection
mysql_close();
?>

 

this is the file that is supposed to make the update:

<?php
<?php
$host="mysql.sussexwarrenhrma.org"; // Host name
$username="swhrma"; // Mysql username
$password="Fall2013#"; // Mysql password
$db_name="swhrmamembers"; // Database name
$tbl_name="members"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//update data in mysql database
 echo $rows['id'];
echo "test";
$sql="UPDATE $tbl_name SET FirstName='$FirstName', LastName='$LastName', Email='$Email' WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error());

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//update data in mysql database
 echo $rows['id'];
echo "test";
$sql="UPDATE $tbl_name SET FirstName='$FirstName', LastName='$LastName', Email='$Email' WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error());

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

 

Everything works fine until the last file.  I get a successful message but nothing in the table changes.

 

Please help

[Ch0cu3r]

Because your are not defining these variables

$FirstName, $LastName, $Email and $id

 

You need to get these from $_POST first.

$FirstName = mysql_real_escape_string($_POST['FirstName']);
$LastName = mysql_real_escape_string($_POST['LastName']);
$Email = mysql_real_escape_string($_POST['Email']);
$id = intval($_POST['id']);

$sql="UPDATE $tbl_name SET FirstName='$FirstName', LastName='$LastName', Email='$Email' WHERE id='$id'";

This is bad

sql="INSERT INTO members (FirstName, LastName, Company, website, Category, Description, Address1, Address2, Email, City, State, Zip, OfficePh, CellPh)
VALUES
('$_POST[firstname]',
'$_POST[lastname]',
'$_POST[Company]',
'$_POST[website]',
'$_POST[Category]',
'$_POST[Description]',
'$_POST[Address1]',
'$_POST[Address2]',
'$_POST[Email]',
'$_POST[City]',
'$_POST[State]',
'$_POST[Zip]',
'$_POST[OfficePh]',
'$_POST[CellPH]')";

Never insert raw post values directly into a query. You should always sanitize user input before using it within a query to protect yourself from SQL injection. PHP provides a function to help prevent this from happening called mysql_real_escape_string

Edited January 3, 2014 by Ch0cu3r

[TopTrainer]

Thanks Ch0cu3r

That made it work.  And, yes I know I need to add some code to the post lines.  I wanted to make sure i could get it to work before I spent the time.

 

Really appreciate your help

[Ch0cu3r]

While you are still learning try to move your code over to mysqli

 

The mysql_* functions are deprecated and could soon be removed.