MySQL database not updating

Question

output from the database seems fine, but input from the update doesn't pass onto the database

edit.php - from here all values displays correctly

<?php
session_start();

$name = $_SESSION['name'];
$sur = $_SESSION['sur'];
$pass = $_SESSION['pass'];

echo $name.' '.$sur.' '.$pass;

//connect
$dbh = mysql_connect ("localhost", "xxx_admin", "xxx") 
       or die ('ERROR!');
       mysql_select_db ("xxx_database"); 

$query="SELECT * FROM client_info WHERE (first='$name' AND last='$sur' AND password='$pass')";
$result=mysql_query($query);


    $id = mysql_result($result,$i,"id");
    $first = mysql_result($result,$i,"first");
    $last = mysql_result($result,$i,"last");
    $phone = mysql_result($result,$i,"phone");
    $mob = mysql_result($result,$i,"mob");
    $fax = mysql_result($result,$i,"fax");
    $email = mysql_result($result,$i,"email");
    $web = mysql_result($result,$i,"web");
    $com = mysql_result($result,$i,"com");
    $add = mysql_result($result,$i,"add");
    $city = mysql_result($result,$i,"city");
    $state = mysql_result($result,$i,"state");
    $zip = mysql_result($result,$i,"zip");
    $zone = mysql_result($result,$i,"zone");
    $office = mysql_result($result,$i,"office");
    $office_num = mysql_result($result,$i,"office_num");
    $ext_mob = mysql_result($result,$i,"ext_mob");
    $ext_phone = mysql_result($result,$i,"ext_phone");
    $ext_office = mysql_result($result,$i,"ext_office");
    $srv = mysql_result($result,$i,"srv");
    $stype = mysql_result($result,$i,"stype");
    $voip = mysql_result($result,$i,"voip");
    $vpass = mysql_result($result,$i,"vpass");
    $regDate = mysql_result($result,$i,"regDate");
    $acct = mysql_result($result,$i,"acct");
    $Nagent = mysql_result($result,$i,"Nagent");
    $agents = mysql_result($result,$i,"agents");
    $password = mysql_result($result,$i,"password");

?>
<html>
<head>
<title></title>
</head>

<body>

<form method="post" action="update.php" name="gen">
<b>Personal Info:</b>
<p>First Name:<input type="text" name="first" size="20" value="<?php echo $first; ?>"/></p>
<p>Last Name:<input type="text" name="last" size="20" value="<?php echo $last; ?>"/></p>
<p>Mob:<input type="text" name="mob" size="20" value="<?php echo $mob; ?>"/>
ext:<input type="text" name="ext_mob" size="4" value="<?php echo $ext_mob; ?>"/></p>
<p>Phone:<input type="text" name="phone" size="20" value="<?php echo $phone; ?>"/>
ext:<input type="text" name="ext_phone" size="4" value="<?php echo $ext_phone; ?>"/></p>
<p>Fax:<input type="text" name="fax" size="20" value="<?php echo $fax; ?>"/></p>
<p>E-mail:<input type="text" name="email" size="35" value="<?php echo $email; ?>"/></p>
<p>Address:<input type="text" name="add" size="40" value="<?php echo $add; ?>"/></p>
<p>City:<input type="text" name="city" size="20" value="<?php echo $city; ?>"/></p>
<p>State:<input type="text" name="state" size="20" value="<?php echo $state; ?>"/></p>
<p>Zip Code:<input type="text" name="zip" size="5" value="<?php echo $zip; ?>"/></p>
<p>Zone:<input type="text" name="zone" size="5" value="<?php echo $zone; ?>"/></p>
<br>

<b>Office Info:</b>
<p>Company:<input type="text" name="com" size="40" value="<?php echo $com; ?>"/></p>
<p>Office Address:<input type="text" name="office" size="40" value="<?php echo $office; ?>"/></p>
<p>Office Num #:<input type="text" name="office_num" size="15" value="<?php echo $office_num; ?>"/>
ext:<input type="text" name="ext_office" size="4" value="<?php echo $ext_office; ?>"/></p>
<p>Website:<input type="text" name="web" size="30" value="<?php echo $web; ?>"/></p><br>

Old password:
<input type="password" size="20" name="oldpassword">
New password:
<input type="password" size="20" name="newpassword">
verify new password
<input type="password" size="20" name="verpassword">

<input type="text" size="20" name="id" value="<?php echo $id; ?>">
<input type="submit" value="Update Database">
</form>




</body>
</html>

update.php - it says update success , but there are no changes on my database

<?php
session_start();

$ud_id=$_POST['id'];
$name = $_SESSION['name'];
$sur = $_SESSION['sur'];
$pass = $_SESSION['pass'];

$ud_first = $_POST['first'];
$ud_last = $_POST['last'];
$ud_phone = $_POST['phone'];
$ud_mob = $_POST['mob'];
$ud_fax = $_POST['fax'];
$ud_email = $_POST['email'];
$ud_web = $_POST['web'];
$ud_com = $_POST['com'];
$ud_add = $_POST['add'];
$ud_city = $_POST['city'];
$ud_state = $_POST['state'];
$ud_zip = $_POST['zip'];
$ud_zone = $_POST['zone'];
$ud_office = $_POST['office'];
$ud_office_num = $_POST['office_num'];
$ud_ext_mob = $_POST['ext_mob'];
$ud_ext_phone = $_POST['ext_phone'];
$ud_ext_office = $_POST['ext_office'];
$ud_password = $_POST['newpassword'];

//connect
$dbh = mysql_connect ("localhost", "xxx_admin", "xxx") 
       or die ('ERROR!');
       mysql_select_db ("xxx_database"); 


$query="UPDATE client_info SET first='$ud_first',last='$ud_last',phone='$ud_phone',mob='$ud_mob',fax='$ud_fax',email='$ud_email',web='$ud_web',com='$ud_com',add='$ud_add',city='$ud_city',state='$ud_state',zip='$ud_zip',zone='$ud_zone',office='$ud_office',office_num='$ud_office_num',ext_mob='$ud_ext_mob',ext_phone='$ud_ext_phone',ext_office='$ud_ext_office',password='$ud_password' WHERE id='$ud_id'";

mysql_query($query);

echo "Record Updated at ID: ".$ud_id;
mysql_close();     

?>

It says success because it has no other option as long as the DB connect works. Try printing out the post values and make sure you're actually getting values where you should be and for the record that is seriously insecure code. — Rick Calder
– Rick Calder, Commented Oct 29, 2012 at 5:48
Change: mysql_query($query); -to- mysql_query($query) or die(mysql_error()); — Richi González
– Richi González, Commented Oct 29, 2012 at 6:30
do one thing, echo your update query and paste it into mysql and see what error. — pkachhia
– pkachhia, Commented Oct 29, 2012 at 7:09

Vinay · Accepted Answer · 2012-10-29 06:58:28Z

1

As I don't know your table structure, Try this and debug accordingly.

$query="UPDATE client_info SET first='$ud_first',
last='$ud_last',
phone='$ud_phone',
mob='$ud_mob',
fax='$ud_fax',
email='$ud_email',
web='$ud_web',
com='$ud_com',
add='$ud_add',
city='$ud_city',
state='$ud_state',
zip='$ud_zip',
zone='$ud_zone',
office='$ud_office',
office_num='$ud_office_num',
ext_mob='$ud_ext_mob',
ext_phone='$ud_ext_phone',
ext_office='$ud_ext_office',
password='$ud_password' WHERE id=$ud_id;";

mysql_query($query) or die(mysql_error());

answered Oct 29, 2012 at 6:58

Vinay

2,5944 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Louis Loudog Trottier · Accepted Answer · 2012-10-29 05:52:58Z

0

of all i STRONGLY suggest you escape your user input before sending anything to the DB. it will escape special charaters and pevent use of keywords to mess your db. to do so, simply use mysql_real_escape_string() like so;

$ud_first = mysql_real_escape_string($_POST['first']);
You sould check mysqli as mysql being decretated. If your just starting your code, i usggest you switch as soon as possible. Efficiency and security will increase.
Add an ; at the end of you query. Will help the server to know where it ends.
change or die ('ERROR!'); by or die (mysql_error()); This will echo the last error encourtered by mysql server. It will most likely tell you what your probelm is.

I give you those hints as i don't see why your code don't work as expected.

answered Oct 29, 2012 at 5:52

Louis Loudog Trottier

4872 silver badges10 bronze badges

2 Comments

Louis Loudog Trottier Over a year ago

thinking twice about it, it might be just an ' some where in you values that is not escaped that mess up the server.

telexper Over a year ago

host only offers mysql still don't have mysqli on my hostserver

halfer · Accepted Answer · 2020-05-16 17:03:11Z

0

Something out of the range of this question but why assign each of the result values separately?

You expect one result and as $i not set is actually null it works maybe you could simplify your life and code by using

$r = mysql_fetch_assoc($result);
foreach($r as $key=>$value){ $$key=$value; }

If there would be more rows expected or possible, you would have to close it in a foreach loop or similar solution. Other side, processing the post and securing input may be short&simple too

$allowed_post_vars=array('city','zip',...); // define ok variables
foreach($allowed_post_vars as $postvar)// each of them
{ $ud='ud_'.$postvar; // prepare longer name, like $ud_city etc 
  $$ud=mysql_real_escape_string($_POST[$postvar]); //assign it sanitized value
}

edited May 16, 2020 at 17:03

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Oct 29, 2012 at 7:33

Peminator

8621 gold badge13 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL database not updating

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related