Welcome to WebmasterWorld Guest from 54.145.208.64

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL Not Updating

MySQL won't update from php process - no error received.

   
7:01 pm on Jun 25, 2009 (gmt 0)

5+ Year Member



I'm trying to update tables with this form, not sure why it's not working.
Here's the form:

while ($row = mysql_fetch_array($result)) {
$first=$row['FIRST_NAME'];
$last=$row['LAST_NAME'];
$address=$row['ADDRESS'];
$city=$row['CITY'];
$phone=$row['PHONE'];
$email=$row['EMAIL'];
$ca_id=$row['CA_ID'];
$dname=$row['DOCTOR_NAME'];
$dlicense=$row['DOCTOR_LICENSE'];
$dphone=$row['DOCTOR_PHONE'];
$doh_id=$row['DOH_ID'];
$rec_i=$row['REC_ISSUE'];
$rec_e=$row['REC_EXP'];
$refer=$row['REFER'];
}
?>
<form action="updated.php" method="post">
<input type="hidden" name="name" value="<? echo $id; ?>">
First Name: <input type="text" name="FIRST_NAME" value="<? echo $first; ?>"><br>
Last Name: <input type="text" name="LAST_NAME" value="<? echo $last; ?>"><br>
Address: <input type="text" name="ADDRESS" value="<? echo $address; ?>"><br>
City: <input type="text" name="CITY" value="<? echo $city; ?>"><br>
Phone: <input type="text" name="PHONE" value="<? echo $phone; ?>"><br>
Email: <input type="text" name="EMAIL" value="<? echo $email; ?>"><br>
CA ID: <input type="text" name="CA_ID" value="<? echo $ca_id; ?>"><br>
Doctor's Name: <input type="text" name="DOCTOR_NAME" value="<? echo $dname; ?>"><br>
Doctor's License: <input type="text" name="DOCTOR_LICENSE" value="<? echo $dlicense; ?>"><br>
Doctor's Phone: <input type="text" name="DOCTOR_PHONE" value="<? echo $dphone; ?>"><br>
DOH ID: <input type="text" name="DOH_ID" value="<? echo $doh_id; ?>"><br>
Rec Issued: <input type="text" name="REC_I" value="<? echo $rec_i; ?>"><br>
Rec Expires: <input type="text" name="REC_E" value="<? echo $rec_e; ?>"><br>
Referred By: <input type="text" name="REFER" value="<? echo $refer; ?>"><br>
<input type="Submit" value="Update">
</form>

here's updated.php


if ($db_found) {
$SQL = "UPDATE patients SET (FIRST_NAME, LAST_NAME, BIRTHDATE, ADDRESS, CITY, PHONE, EMAIL, CA_ID, DOCTOR_NAME, DOCTOR_LICENSE, DOCTOR_PHONE, DOH_ID, REC_ISSUE, REC_EXP, REFER) VALUES ('$_POST[FIRST_NAME]', '$_POST[LAST_NAME]', '$_POST[BIRTHDATE]', '$_POST[ADDRESS]', '$_POST[CITY]', '$_POST[PHONE]', '$_POST[EMAIL]', '$_POST[CA_ID]', '$_POST[DOCTOR_NAME]', '$_POST[DOCTOR_LICENSE]', '$_POST[DOCTOR_PHONE]', '$_POST[DOH_ID]', '$_POST[REC_ISSUE]', '$_POST[REC_EXP]', '$_POST[REFER]')";

$result = mysql_query($SQL);

mysql_close($db_handle);

print "Records updated";
}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}

[edited by: jatar_k at 8:31 pm (utc) on June 25, 2009]
[edit reason] fixed sidescroll [/edit]

7:12 pm on Jun 25, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



That's not the proper syntax for an UPDATE. Also, without a WHERE clause, you will updated EVERY row in the table with that information!

Proper UPDATE syntax:
UPDATE TableName SET
FieldName1=newvalue1,
FieldName2=newvalue2,
...
FieldNameN=newvalueN
WHERE condition

8:33 pm on Jun 25, 2009 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



another thing that is good while testing new queries, change

$result = mysql_query($SQL);

to

$result = mysql_query($SQL) or die ("<p>died: $SQL<br>" . mysql_error());

that will output the actual error from mysql

7:10 pm on Jun 26, 2009 (gmt 0)

5+ Year Member



Thanks guys got it working!
6:32 am on Jun 27, 2009 (gmt 0)

10+ Year Member



If you don't want to do $result = mysql_query($SQL) or die ("<p>died: $SQL<br>" . mysql_error());

You can echo your variable $SQL that way you get the exact query running. Then copy that query and try it in your phpMyadmin and it will give you the error messages as well.