[SOLVED] Problem with UPDATE table

[steviemac]

I am trying to update a table from a form.  Right now it is not updating.  I'm still new to the php mysql world so most of code comes form books I have or searches on the internet.  I have been trying to figure this one out for a while.

 

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);
//Connect to DB
$db = mysql_connect("localhost", "xx", "xx");
mysql_select_db("xx");
if (isset($_POST['Submit']))  {


     $result = "UPDATE ActiveMembers SET
last_name = '{$_POST['last_name']}', 
first_name = '{$_POST['first_name']}', 
Rank = '{$_POST['Rank']}',  
Agency = '{$_POST['Agency']}', 
Address = '{$_POST['Address']}', 
CSZ = '{$_POST['CSZ']}', 
Phone = '{$_POST['Phone']}', 
Fax = '{$_POST['Fax']}', 
email = '{$_POST['email']}', 
ResidenceAddress = '{$_POST['ResidenceAddress']}', 
RCSZ = '{$_POST['RCSZ']}', 
ResidencePhone = '{$_POST['ResidencePhone']}', 
id_number ='{$_POST['id_number']}'  
WHERE  id_number = '$id_number'";
mysql_query($result) or die(mysql_error());
// echo out the query
if (mysql_affected_rows()==1){
     echo "Successfully Updated";

} else {
    echo "Did Not Update";

}
}
?>

 

I appreciate any help.  Thank you

[Fadion]

First you must setup a correct table, where it has an id (in your case id_number) and must be set as Primary and auto_increment, so that mysql handles ids by itself. After that when you construct an update query u must use it like this:

 

UPDATE table SET column1='$value', column2='$value2' WHERE id='$id'

 

Hope u got the concept.

[yarnold]

For a start, you need to escape all input to prevent the risk of malicious activity being carried out through the form.  Use mysql_real_escape_string() around all of the $_POST variables in your update query...

[steviemac]

I have the concept.  What I tried to do was use the id_number they are assigned because it is unique but I did not set it as primary.  I made a column and made it primary and auto incrinment.  Could you give me an example of the mysql_real_escape_string()  in use.

 

Thank you

[steviemac]

I tried it the way described and it still will not update. 

[yarnold]

last_name = '".mysql_real_escape_string($_POST['last_name'])."', 

[steviemac]

OK this is what I have now and still not updating ???

 

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);
//Connect to DB
$db = mysql_connect("localhost", "x", "xx");
mysql_select_db("xx");
if(isset ($_POST['submit']))  {
//CHANGE TABLE NAME

      $result = "UPDATE ActiveMembers SET 
last_name='".mysql_real_escape_string($_POST['last_name'])."', 
first_name='".mysql_real_escape_string($_POST['first_name'])."',  
Rank = '".mysql_real_escape_string($_POST['Rank'])."',  
Agency = '".mysql_real_escape_string($_POST['Agency'])."', 
Address = '".mysql_real_escape_string($_POST['Address'])."', 
CSZ = '".mysql_real_escape_string($_POST['CSZ'])."', 
Phone = '".mysql_real_escape_string($_POST['Phone'])."', 
Fax = '".mysql_real_escape_string($_POST['Fax'])."',  
email = '".mysql_real_escape_string($_POST['email'])."', 
ResidenceAddress = '".mysql_real_escape_string($_POST['ResidenceAddress'])."', RCSZ = '".mysql_real_escape_string($_POST['RCSZ'])."',  
ResidencePhone = '".mysql_real_escape_string($_POST['ResidencePhone'])."', 
id_number = '".mysql_real_escape_string($_POST['id_number'])."' 
WHERE  id = '$id'";
mysql_query($result) or die(mysql_error());
// echo out the query
if (mysql_affected_rows()==1){
     echo Success";
    } else {
    echo "Failure";

}
}
?>

 

I am getting the failure echo??????????

[yarnold]

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);
//Connect to DB
$db = mysql_connect("localhost", "x", "xx");
mysql_select_db("xx");
if(isset ($_POST['submit']))  {
//CHANGE TABLE NAME

      $result = "UPDATE ActiveMembers SET 
last_name='".mysql_real_escape_string($_POST['last_name'])."', 
first_name='".mysql_real_escape_string($_POST['first_name'])."',  
Rank = '".mysql_real_escape_string($_POST['Rank'])."',  
Agency = '".mysql_real_escape_string($_POST['Agency'])."', 
Address = '".mysql_real_escape_string($_POST['Address'])."', 
CSZ = '".mysql_real_escape_string($_POST['CSZ'])."', 
Phone = '".mysql_real_escape_string($_POST['Phone'])."', 
Fax = '".mysql_real_escape_string($_POST['Fax'])."',  
email = '".mysql_real_escape_string($_POST['email'])."', 
ResidenceAddress = '".mysql_real_escape_string($_POST['ResidenceAddress'])."', RCSZ = '".mysql_real_escape_string($_POST['RCSZ'])."',  
ResidencePhone = '".mysql_real_escape_string($_POST['ResidencePhone'])."', 
id_number = '".mysql_real_escape_string($_POST['id_number'])."' 
WHERE  id = '".$id."'";
mysql_query($result) or die(mysql_error());
// echo out the query
if (mysql_affected_rows()==1){
     echo "Success";
    } else {
    echo "Failure";

}
}
?>

 

I fixed a syntax error above.

 

One question - where are you setting $id?  If you're getting it from $_POST, you need to assign $id before you construct the query.

 

$id = mysql_real_escape_string($_POST['id']);

[steviemac]

I did that and I solved it.  The problem was in the origional form.  I did not give id a value in the form so it did not know what row to update.  I'm a rookie at this so sometimes the simple things catch me.  The silver lining is I learned about my mysql_real_escape_string().

Thank you for you time and patiences.

[yarnold]

Just remember that you've got to stripslashes($field); when you extract the data from the database

[steviemac]

OK.  I have seen that before thanks!