Not updating certain fields in same row

--Apple-Mail-50-523897108
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

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@raoset.com




--Apple-Mail-50-523897108--

Re: Not updating certain fields in same row

On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim 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='".mys ql_real_escape_string($field3)."'
WHERE id='".mysql_real_escape_string($id)."' LIMIT 1";

--

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

Re: Not updating certain fields in same row

On Mar 25, 2008, at 1:09 PM, Daniel Brown wrote:
> On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim
> 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


>
>
> --
>
> 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@raoset.com




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

------=_Part_9483_32455380.1206465466217
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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;
}

Re: Not updating certain fields in same row

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;
> }
>
>

RE: Not updating certain fields in same row

You can't use the function empty() because as soon as the form is =
submitted,
the value of an empty field in the form, is a blank in the variable in =
your
script, so basically your password field is NOT empty, it has a value, a
blank.

So you need to test your password field to NOT to be empty, also use a
second password field, a confirmation field, so:

$querystring=3D"your SQL statement here";
If (passwd1 is not blank) and (passwd1==passwd2) then
$querystring.=3D"passwd=3D'passwd1'"
If not
You keep intact your first SQL statement without touching the passwd
field

My two niquel cents!
__________________
Miguel Guirao Aguilera, Linux+, ITIL
Sistemas de Informaci=F3n
Inform=E1tica R8
Ext. 7540

--> -----Original Message-----
--> From: Jason Pruim [mailto:japruim@raoset.com]
--> Sent: Tuesday, March 25, 2008 11:14 AM
--> To: Daniel Brown
--> Cc: php-db@lists.php.net
--> Subject: Re: [PHP-DB] Not updating certain fields in same row
-->=20
-->=20
--> On Mar 25, 2008, at 1:09 PM, Daniel Brown wrote:
--> > On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim
--> > 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 =3D "\t";
--> >> if (!isset($_POST['txtLoginName']) ||
--> >> empty($_POST['txtLoginName'])) {
--> >>
--> >> $loginName =3D
--> >> mysqli_real_escape_string($chpwpostlink,
--> >> $_POST['txtLoginName']);
--> >> }
--> >> else
--> >> {
--> >> $loginName =3D $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 =3D "UPDATE users SET
--> > Field1
--> > =3D
--> > '".mysql_real_escape_string
--> > ($field1)."',Field3=3D'".mysql_real_escape_string($field3)." '
--> > WHERE id=3D'".mysql_real_escape_string($id)."' LIMIT 1";
-->=20
--> the actual query I'm using is this:
-->=20
--> $chpwsql =3D "UPDATE current SET customerName=3D'$customerName',
--> loginName=3D'$loginName', loginPassword=3D'$PW', email=3D'$email',
--> adminLevel=3D'$adminLevel' WHERE Record=3D'$Record1'";
-->=20
--> 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
-->=20
-->=20
--> >
--> >
--> > --
--> >
--> > 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
--> >
--> >
-->=20
--> --
-->=20
--> Jason Pruim
--> Raoset Inc.
--> Technology Manager
--> MQC Specialist
--> 3251 132nd ave
--> Holland, MI, 49424-9337
--> www.raoset.com
--> japruim@raoset.com
-->=20
-->=20
-->=20
-->=20
--> --
--> PHP Database Mailing List (http://www.php.net/)
--> To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

------=_Part_9588_13848045.1206466973130
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I encrypt my pw's the same way but I usually don't include the password on
an "edit my info" page. I create a separate "change password" screen where
I force them to type in their old password and then type a new one twice. if
the encrypted, salted old password attempt does not match what is in the db,
the form doesn't validate and the password is not changed.

the "lost password" page is a different beast altogether....

but again, the password field is omitted from the "edit" form AND the update
statement so it is never touched in this process.

that goes for any other field you don't want the user to be able to edit too
-- just don't put it in the form or the update and they can't mess with it.
I usually have an "is_admin" (yes/no) field and don't want the user to
change that of course.

-- matt



On Tue, Mar 25, 2008 at 12:24 PM, Jason Pruim wrote:

>
> 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;
> > }
> >
> >

Re: Not updating certain fields in same row

On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim wrote:
>
> 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

Okay, since you won't only want to rely on isset() here (in case
someone accidentally hits a key into the field), try this:

// NOTE: This assumes prior sanity checks and cleansing
// of variables, and is written like so to avoid breaking
// of the query due to mail client-enforced line breaks.
$chpwsql = "UPDATE current SET ";
$chpwsql .= "customerName='".$customername."',";
$chpwsql .= "loginName='".$loginName."',";
if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) {
// If it's between 5-16 case-insensitive alphanumeric
// characters, it's all good. If you want to allow symbols,
// simply modify the regexp accordingly.
$chpwsql .= "loginPassword='".$PW."',";
}
$chpwsql .= "email='".$email."',";
$chpwsql .= "adminLevel='".$adminLevel',";
$chpwsql .= " WHERE Record='".$Record1."'";
$chpwsql .= " LIMIT 1";

--

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

Re: Not updating certain fields in same row

I might be way off here. Php.net tells me that:

[quote]

mysql_real_escape_string — Escapes special characters in a string for
use in a SQL statement

string **mysql_real_escape_string** ( string $unescaped_string [,
resource $link_identifier ] )

[/quote]

and you use

[quote]

mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']);

[/quote]

I can't imagine that $_POST['txtLoginName'] is a resource identifier
(which is the actual connection to your database).

Also, this condition:

[quote]

if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName']))

[/quote]

is true if and only if no form element by the name txtLoginName existed
on the previous page - and on top of that, empty() does the same as
isset() and apart from that also checks whether, if the variable has
been set, it has a value (0, "", NULL or FALSE) that evaluates to FALSE.

I don't understand why you'd want to fill the username with a tab
either: "\t".

Maybe you can post your full code?

Evert


Daniel Brown wrote:
> On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim wrote:
>
>> 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
>>
>
> Okay, since you won't only want to rely on isset() here (in case
> someone accidentally hits a key into the field), try this:
>
> // NOTE: This assumes prior sanity checks and cleansing
> // of variables, and is written like so to avoid breaking
> // of the query due to mail client-enforced line breaks.
> $chpwsql = "UPDATE current SET ";
> $chpwsql .= "customerName='".$customername."',";
> $chpwsql .= "loginName='".$loginName."',";
> if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) {
> // If it's between 5-16 case-insensitive alphanumeric
> // characters, it's all good. If you want to allow symbols,
> // simply modify the regexp accordingly.
> $chpwsql .= "loginPassword='".$PW."',";
> }
> $chpwsql .= "email='".$email."',";
> $chpwsql .= "adminLevel='".$adminLevel',";
> $chpwsql .= " WHERE Record='".$Record1."'";
> $chpwsql .= " LIMIT 1";
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

Correction:
> Also, this condition:
>
> [quote]
>
> if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName']))
>
> [/quote]
>
> is true if and only if no form element by the name txtLoginName
> existed on the previous page - and on top of that, empty() does the
> same as isset() and apart from that also checks whether, if the
> variable has been set, it has a value (0, "", NULL or FALSE) that
> evaluates to FALSE.
>
This condition is of course also true when the form element has existed
but has no value.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

On Mar 25, 2008, at 2:57 PM, Evert Lammerts wrote:
> I might be way off here. Php.net tells me that:
>
> [quote]
>
> mysql_real_escape_string =97 Escapes special characters in a string =20=

> for use in a SQL statement
>
> string **mysql_real_escape_string** ( string $unescaped_string [, =20
> resource $link_identifier ] )
>
> [/quote]
>
> and you use
>
> [quote]
>
> mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']);
>
> [/quote]
>
> I can't imagine that $_POST['txtLoginName'] is a resource identifier =20=

> (which is the actual connection to your database).

It's not... see: =
http://us2.php.net/manual/en/function.mysqli-real-escape-str ing.php=20
Notice the I after mysql
>
>
> Also, this condition:
>
> [quote]
>
> if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName']))
>
> [/quote]
>
> is true if and only if no form element by the name txtLoginName =20
> existed on the previous page - and on top of that, empty() does the =20=

> same as isset() and apart from that also checks whether, if the =20
> variable has been set, it has a value (0, "", NULL or FALSE) that =20
> evaluates to FALSE.
>
> I don't understand why you'd want to fill the username with a tab =20
> either: "\t".
I didn't want to... What I am attempting to do is if the field is NOT =20=

changed, don't touch it in the database. The main issue I had was with =20=

the password field for updating a password since I"m not going to read =20=

a MD5 hash back to the user to be edited.

>
>
> Maybe you can post your full code?
>
> Evert
>
>
> Daniel Brown wrote:
>> On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim =20
>> wrote:
>>
>>> the actual query I'm using is this:
>>>
>>> $chpwsql =3D "UPDATE current SET =
customerName=3D'$customerName',
>>> loginName=3D'$loginName', loginPassword=3D'$PW', email=3D'$email',
>>> adminLevel=3D'$adminLevel' WHERE Record=3D'$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 =20=

>>> I'm
>>> looking for is if loginPassword hasn't changed... don't clear the
>>> contents of it. if it has changed, then update loginPassword
>>>
>>
>> Okay, since you won't only want to rely on isset() here (in case
>> someone accidentally hits a key into the field), try this:
>>
>> // NOTE: This assumes prior sanity checks and cleansing
>> // of variables, and is written like so to avoid breaking
>> // of the query due to mail client-enforced line breaks.
>> $chpwsql =3D "UPDATE current SET ";
>> $chpwsql .=3D "customerName=3D'".$customername."',";
>> $chpwsql .=3D "loginName=3D'".$loginName."',";
>> if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) {
>> // If it's between 5-16 case-insensitive alphanumeric
>> // characters, it's all good. If you want to allow symbols,
>> // simply modify the regexp accordingly.
>> $chpwsql .=3D "loginPassword=3D'".$PW."',";
>> }
>> $chpwsql .=3D "email=3D'".$email."',";
>> $chpwsql .=3D "adminLevel=3D'".$adminLevel',";
>> $chpwsql .=3D " WHERE Record=3D'".$Record1."'";
>> $chpwsql .=3D " LIMIT 1";
>>
>>
>
>
> --=20
> 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@raoset.com




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

Jason Pruim 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?

$fields = array('Field1', 'Field2', 'Field3', 'Field4');

$update_fields = array();
foreach ($fields as $form_field_name) {
if (!isset($_POST[$form_field_name]) || empty($_POST[$form_field_name])
|| $_POST[$form_field_name] == '') {
// ignore the blank/empty fields
continue;
}
$update_fields[] = $form_field_name . "='" .
mysqli_real_escape_string($conn, $_POST[$form_field_name]) . "'";
}

if (!empty($update_fields)) {
$query = 'update table set ';
$query .= implode(',', $update_fields);
$query .= ' WHERE recordid='x';
}


If you're just ignoring password fields, you have a confirm password box
too don't you?

You can also use that:

$password_one = $_POST['password'];
$password_confirm = $_POST['password_confirm'];

if ($password_one == '' || $password_confirm == '') {
// skip adding the pw field to the update query
}

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Not updating certain fields in same row

Hi Everyone

Thanks for all the info and help with this, I have decided to write a
separate function for changing the password. That way I can compare
the original password with the one inputted in the database, and then
change it after both have been the old and new password have been
verified :)

So Thanks! :)



On Mar 25, 2008, at 12:59 PM, Jason Pruim 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@raoset.com
>
>
>

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
japruim@raoset.com




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php