Not updating certain fields in same row
in [Databases]
|
From: Jason Pruim on 25 Mar 2008 12:59 Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = "\t"; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com japruim(a)raoset.com
From: "Daniel Brown" on 25 Mar 2008 13:09 On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim <japruim(a)raoset.com> wrote: > Hi everyone, > > I am attempting to update a record for a login system while leaving > certain fields untouched if they arn't changed, and am running into > issues. [snip!] > > I have tried this code: > $tab = "\t"; > if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { > > $loginName = mysqli_real_escape_string($chpwpostlink, > $_POST['txtLoginName']); > } > else > { > $loginName = $tab; > } Mmm-hmm.... and exactly how do that work to update the database? The SQL query you're probably looking for would be like this: $sql = "UPDATE users SET Field1='".mysql_real_escape_string($field1)."',Field3='".mysql_real_escape_string($field3)."' WHERE id='".mysql_real_escape_string($id)."' LIMIT 1"; -- </Daniel P. Brown> Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283
From: Jason Pruim on 25 Mar 2008 13:14 On Mar 25, 2008, at 1:09 PM, Daniel Brown wrote: > On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim <japruim(a)raoset.com> > wrote: >> Hi everyone, >> >> I am attempting to update a record for a login system while leaving >> certain fields untouched if they arn't changed, and am running into >> issues. > [snip!] >> >> I have tried this code: >> $tab = "\t"; >> if (!isset($_POST['txtLoginName']) || >> empty($_POST['txtLoginName'])) { >> >> $loginName = >> mysqli_real_escape_string($chpwpostlink, >> $_POST['txtLoginName']); >> } >> else >> { >> $loginName = $tab; >> } > > Mmm-hmm.... and exactly how do that work to update the database? > The SQL query you're probably looking for would be like this: > > $sql = "UPDATE users SET > Field1 > = > '".mysql_real_escape_string > ($field1)."',Field3='".mysql_real_escape_string($field3)."' > WHERE id='".mysql_real_escape_string($id)."' LIMIT 1"; the actual query I'm using is this: $chpwsql = "UPDATE current SET customerName='$customerName', loginName='$loginName', loginPassword='$PW', email='$email', adminLevel='$adminLevel' WHERE Record='$Record1'"; What it is doing now is if I don't set a a field I am replacing the content of it with a tab, which isn't what I want. Basically what I'm looking for is if loginPassword hasn't changed... don't clear the contents of it. if it has changed, then update loginPassword > > > -- > </Daniel P. Brown> > Forensic Services, Senior Unix Engineer > 1+ (570-) 362-0283 > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com japruim(a)raoset.com
From: "Matt Anderton" on 25 Mar 2008 13:17 I usually pre-populate the form with the values that are already in the record: (... using PEAR's MDB2 package -- http://pear.php.net/packages/MDB2 ) $query = "SELECT * FROM member WHERE username = '" . $_POST['username'] . "'"; $result = $db->query($query); $member = $result->fetchRow(MDB2_FETCHMODE_ASSOC); foreach ($member as $key => $value) { $$key = $value; } <form name="member_edit" action="<?= $_SERVER['PHP_SELF'] ?>" method="POST"> <input type="hidden" name="_submit" value="1" /> <input type="text" name="fname" value="<?= $fname ?>" /> <input type="text" name="lname" value="<?= $lname ?>" /> <input type="text" name="email" value="<?= $email ?>" /> .... .... .... </form> then when they submit, the record is just repopulated with whatever is in the form when they submit. ie -- only fields they changed get changed in the db. if($_POST['_submit']) { $update = "UPDATE member SET ...... blah, blah..." } that way, none of the fields are blank unless they were in the db blank to begin with. and you can add client or server-side validation to prevent that. -- matt On Tue, Mar 25, 2008 at 11:59 AM, Jason Pruim <japruim(a)raoset.com> wrote: > Hi everyone, > > I am attempting to update a record for a login system while leaving > certain fields untouched if they arn't changed, and am running into > issues. > > Basically what I want to do, is say I have these fields: > > Field1 > Field2 > Field3 > Field4 > > I update Field1 and Field3 but not Field2 and Field4. What I want to > do is change the values in Field1 and Field3 without touching the > values in Field2 and Field4. > > I have tried this code: > $tab = "\t"; > if (!isset($_POST['txtLoginName']) || > empty($_POST['txtLoginName'])) { > > $loginName = > mysqli_real_escape_string($chpwpostlink, > $_POST['txtLoginName']); > } > else > { > $loginName = $tab; > } > > which works the fields that I've changed, but if I don't submit a > value in the form it sets the field to be blank in MySQL. Which is > what I am trying to avoid. Any ideas? > > -- > > Jason Pruim > Raoset Inc. > Technology Manager > MQC Specialist > 3251 132nd ave > Holland, MI, 49424-9337 > www.raoset.com > japruim(a)raoset.com > > > >
From: Jason Pruim on 25 Mar 2008 13:24
On Mar 25, 2008, at 1:17 PM, Matt Anderton wrote: > I usually pre-populate the form with the values that are already in > the > record: > > (... using PEAR's MDB2 package -- http://pear.php.net/packages/MDB2 ) > > $query = "SELECT * FROM member WHERE username = '" . > $_POST['username'] . > "'"; > $result = $db->query($query); > $member = $result->fetchRow(MDB2_FETCHMODE_ASSOC); > > foreach ($member as $key => $value) { > $$key = $value; > } > > <form name="member_edit" action="<?= $_SERVER['PHP_SELF'] ?>" > method="POST"> > <input type="hidden" name="_submit" value="1" /> > <input type="text" name="fname" value="<?= $fname ?>" /> > <input type="text" name="lname" value="<?= $lname ?>" /> > <input type="text" name="email" value="<?= $email ?>" /> > .... > .... > .... > </form> > > then when they submit, the record is just repopulated with whatever > is in > the form when they submit. ie -- only fields they changed get > changed in > the db. > > if($_POST['_submit']) { > $update = "UPDATE member SET ...... blah, blah..." > } > > that way, none of the fields are blank unless they were in the db > blank to > begin with. and you can add client or server-side validation to > prevent > that. > > -- matt Hi Matt, That's what I'm doing for most of the fields, but how would you handle a password that has been MD5'ed and includes some variables to make it harder to crack? :) ie: $PW = md5("$salt$password"); I can't undo the MD5 and I don't really want to... Just want to be able to change it rather then view what it is. > > > > > > > On Tue, Mar 25, 2008 at 11:59 AM, Jason Pruim <japruim(a)raoset.com> > wrote: > >> Hi everyone, >> >> I am attempting to update a record for a login system while leaving >> certain fields untouched if they arn't changed, and am running into >> issues. >> >> Basically what I want to do, is say I have these fields: >> >> Field1 >> Field2 >> Field3 >> Field4 >> >> I update Field1 and Field3 but not Field2 and Field4. What I want to >> do is change the values in Field1 and Field3 without touching the >> values in Field2 and Field4. >> >> I have tried this code: >> $tab = "\t"; >> if (!isset($_POST['txtLoginName']) || >> empty($_POST['txtLoginName'])) { >> >> $loginName = >> mysqli_real_escape_string($chpwpostlink, >> $_POST['txtLoginName']); >> } >> else >> { >> $loginName = $tab; >> } >> >> which works the fields that I've changed, but if I don't submit a >> value in the form it sets the field to be blank in MySQL. Which is >> what I am trying to avoid. Any ideas? >> >> -- >> >> Jason Pruim >> Raoset Inc. >> Technology Manager >> MQC Specialist >> 3251 132nd ave >> Holland, MI, 49424-9337 >> www.raoset.com >> japruim(a)raoset.com >> >> >> >> -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com japruim(a)raoset.com |