update mysql not working

TopTrainer · January 3, 2014

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>


<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.

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 · January 3, 2014

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

TopTrainer · January 3, 2014

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 · January 4, 2014

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

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

Sign In

update mysql not working

Recommended Posts

TopTrainer

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

TopTrainer

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information