Conditional updating...

Good day!
I have been working on this little "free" project for a while, and now I
have hit another major hiccup. Is there a simple way to only update fields
that have something in them?
The problem that I am having is that if someone fills out information and
submits it, it saves to the DB just fine. However, if they come back later
and just put in, for example, a new phone number, it replaces all of the
other information with blanks.
Here is my current $sql query:

$sql = "UPDATE $table
SET
first_name='$first_name',
last_name='$last_name',
hs_last_name='$hs_last_name',
guest_name='$guest_name',
street_address1='$street_address1',
street_address2='$street_address2',
city='$city',
state='$state',
zip='$zip',
phone1='$phone1',
phone2='$phone2',
email_address='$email_address',
farmers_barn='$farmers_barn',
wrhs_tour='$wrhs_tour',
crystal_rose='$crystal_rose',
registration_comments='$registration_comments',
date_registered='$today'
WHERE first_name='$first_name' AND last_name='$last_name'";



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

Re: Conditional updating...

Why don't you try populating the fields the user intends to modify
with the information you already have?

Alex T

On 6/17/06, Grae Wolfe - PHP wrote:
> Good day!
> I have been working on this little "free" project for a while, and now I
> have hit another major hiccup. Is there a simple way to only update fields
> that have something in them?
> The problem that I am having is that if someone fills out information and
> submits it, it saves to the DB just fine. However, if they come back later
> and just put in, for example, a new phone number, it replaces all of the
> other information with blanks.
> Here is my current $sql query:
>
> $sql = "UPDATE $table
> SET
> first_name='$first_name',
> last_name='$last_name',
> hs_last_name='$hs_last_name',
> guest_name='$guest_name',
> street_address1='$street_address1',
> street_address2='$street_address2',
> city='$city',
> state='$state',
> zip='$zip',
> phone1='$phone1',
> phone2='$phone2',
> email_address='$email_address',
> farmers_barn='$farmers_barn',
> wrhs_tour='$wrhs_tour',
> crystal_rose='$crystal_rose',
> registration_comments='$registration_comments',
> date_registered='$today'
> WHERE first_name='$first_name' AND last_name='$last_name'";
>
>
>
> --
> 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: Conditional updating...

That was the first thing that I was going to do, but there is a concern
there for security of the data being input... This is a registration site,
and I don't want to provide information on "John Smith" to anyone who just
happens to put his name in.


""Alejandro Tesone"" wrote in message
news:e13e3a560606171219w540e5b12oe81a3212d0746bc1@mail.gmail .com...
> Why don't you try populating the fields the user intends to modify
> with the information you already have?
>
> Alex T
>
> On 6/17/06, Grae Wolfe - PHP wrote:
>> Good day!
>> I have been working on this little "free" project for a while, and now
>> I
>> have hit another major hiccup. Is there a simple way to only update
>> fields
>> that have something in them?
>> The problem that I am having is that if someone fills out information
>> and
>> submits it, it saves to the DB just fine. However, if they come back
>> later
>> and just put in, for example, a new phone number, it replaces all of the
>> other information with blanks.
>> Here is my current $sql query:
>>
>> $sql = "UPDATE $table
>> SET
>> first_name='$first_name',
>> last_name='$last_name',
>> hs_last_name='$hs_last_name',
>> guest_name='$guest_name',
>> street_address1='$street_address1',
>> street_address2='$street_address2',
>> city='$city',
>> state='$state',
>> zip='$zip',
>> phone1='$phone1',
>> phone2='$phone2',
>> email_address='$email_address',
>> farmers_barn='$farmers_barn',
>> wrhs_tour='$wrhs_tour',
>> crystal_rose='$crystal_rose',
>> registration_comments='$registration_comments',
>> date_registered='$today'
>> WHERE first_name='$first_name' AND last_name='$last_name'";
>>
>>
>>
>> --
>> 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: Conditional updating...

You could do a conditional update based on whether the form input is empty/blank or not.
Use trim() on every input box entry, update that entry if there is any value

Make the query string based on non-empty inputs

trim(formput) != ''
//append to query string

Finally, your update query could look like

Update table
set fieldname1 = fieldvalue1,
fieldname2 = fieldvalue2

Make a special case for when only one value is inserted and another for when there are more than one.

Above is the idea/process, do use the correct syntax

Naintara

On Sat, 17 Jun 2006 17:06:41 -0600, "Grae Wolfe - PHP" wrote:
> That was the first thing that I was going to do, but there is a concern
> there for security of the data being input... This is a registration
> site,
> and I don't want to provide information on "John Smith" to anyone who just
> happens to put his name in.
>
>
> ""Alejandro Tesone"" wrote in message
> news:e13e3a560606171219w540e5b12oe81a3212d0746bc1@mail.gmail .com...
>> Why don't you try populating the fields the user intends to modify
>> with the information you already have?
>>
>> Alex T
>>
>> On 6/17/06, Grae Wolfe - PHP wrote:
>>> Good day!
>>> I have been working on this little "free" project for a while, and
> now
>>> I
>>> have hit another major hiccup. Is there a simple way to only update
>>> fields
>>> that have something in them?
>>> The problem that I am having is that if someone fills out information
>>> and
>>> submits it, it saves to the DB just fine. However, if they come back
>>> later
>>> and just put in, for example, a new phone number, it replaces all of
> the
>>> other information with blanks.
>>> Here is my current $sql query:
>>>
>>> $sql = "UPDATE $table
>>> SET
>>> first_name='$first_name',
>>> last_name='$last_name',
>>> hs_last_name='$hs_last_name',
>>> guest_name='$guest_name',
>>> street_address1='$street_address1',
>>> street_address2='$street_address2',
>>> city='$city',
>>> state='$state',
>>> zip='$zip',
>>> phone1='$phone1',
>>> phone2='$phone2',
>>> email_address='$email_address',
>>> farmers_barn='$farmers_barn',
>>> wrhs_tour='$wrhs_tour',
>>> crystal_rose='$crystal_rose',
>>> registration_comments='$registration_comments',
>>> date_registered='$today'
>>> WHERE first_name='$first_name' AND last_name='$last_name'";
>>>
>>>
>>>
>>> --
>>> 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: Conditional updating...

Perhaps I have misunderstood something here. But it seems to me that
anyone who just happens to put John Smith's name in could alter Mr.
Smith's data.

If users can update their own data, should you not have a log in process
to ensure that only the original user can update his data? Them once he
has logged in, you can populate all fields with data from the DB.

Jeffrey

Grae Wolfe - PHP wrote:
> That was the first thing that I was going to do, but there is a concern
> there for security of the data being input... This is a registration site,
> and I don't want to provide information on "John Smith" to anyone who just
> happens to put his name in.
>
>
> ""Alejandro Tesone"" wrote in message
> news:e13e3a560606171219w540e5b12oe81a3212d0746bc1@mail.gmail .com...
>
>>Why don't you try populating the fields the user intends to modify
>>with the information you already have?
>>
>>Alex T
>>
>>On 6/17/06, Grae Wolfe - PHP wrote:
>>
>>>Good day!
>>> I have been working on this little "free" project for a while, and now
>>>I
>>>have hit another major hiccup. Is there a simple way to only update
>>>fields
>>>that have something in them?
>>> The problem that I am having is that if someone fills out information
>>>and
>>>submits it, it saves to the DB just fine. However, if they come back
>>>later
>>>and just put in, for example, a new phone number, it replaces all of the
>>>other information with blanks.
>>> Here is my current $sql query:
>>>
>>>$sql = "UPDATE $table
>>>SET
>>>first_name='$first_name',
>>>last_name='$last_name',
>>>hs_last_name='$hs_last_name',
>>>guest_name='$guest_name',
>>>street_address1='$street_address1',
>>>street_address2='$street_address2',
>>>city='$city',
>>>state='$state',
>>>zip='$zip',
>>>phone1='$phone1',
>>>phone2='$phone2',
>>>email_address='$email_address',
>>>farmers_barn='$farmers_barn',
>>>wrhs_tour='$wrhs_tour',
>>>crystal_rose='$crystal_rose',
>>>registration_comments='$registration_comments',
>>>date_registered='$today'
>>>WHERE first_name='$first_name' AND last_name='$last_name'";
>>>
>>>
>>>
>>>--
>>>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: Conditional updating...

Sorry I have been out of touch... I thought I had this problem beat, but
I was wrong. I decided that the best thing to do was to filter the
variables as the $sql statement was being created. I tried using the
following code, and got a message back that it was invalid and my Query
couldn't execute... Can anyone tell me where I screwed this one up??

$sql = "UPDATE $table SET;


if(!empty($first_name))
$sql .='first_name='.$first_name.',';


if(!empty($last_name))
$sql .='last_name='.$last_name.',';


if(!empty($hs_last_name))
$sql .='hs_last_name='.$hs_last_name.',';


if(!empty($street_address1))
$sql .='street_address1='.$street_address1.',';


if(!empty($street_address2))
$sql .='street_address2='.$street_address2.',';


if(!empty($city))
$sql .='city='.$city.',';


if(!empty($state))
$sql .='state='.$state.',';


if(!empty($zip))
$sql .='zip='.$zip.',';


if(!empty($phone1))
$sql .='phone1='.$phone1.',';


if(!empty($phone2))
$sql .='phone2='.$phone2.',';


if(!empty($email_address))
$sql .='email_address='.$email_address.',';


if(!empty($current_info))
$sql .='current_info='.$current_info.',';


if(!empty($today))
$sql .='date_registered='.$today;


WHERE first_name='$first_name' AND last_name='$last_name'";



"Jeffrey" wrote in message
news:44966E2A.9070606@ungodly.com...
> Perhaps I have misunderstood something here. But it seems to me that
> anyone who just happens to put John Smith's name in could alter Mr.
> Smith's data.
>
> If users can update their own data, should you not have a log in process
> to ensure that only the original user can update his data? Them once he
> has logged in, you can populate all fields with data from the DB.
>
> Jeffrey
>
> Grae Wolfe - PHP wrote:
>> That was the first thing that I was going to do, but there is a concern
>> there for security of the data being input... This is a registration
>> site, and I don't want to provide information on "John Smith" to anyone
>> who just happens to put his name in.
>>
>>
>> ""Alejandro Tesone"" wrote in message
>> news:e13e3a560606171219w540e5b12oe81a3212d0746bc1@mail.gmail .com...
>>
>>>Why don't you try populating the fields the user intends to modify
>>>with the information you already have?
>>>
>>>Alex T
>>>
>>>On 6/17/06, Grae Wolfe - PHP wrote:
>>>
>>>>Good day!
>>>> I have been working on this little "free" project for a while, and now
>>>> I
>>>>have hit another major hiccup. Is there a simple way to only update
>>>>fields
>>>>that have something in them?
>>>> The problem that I am having is that if someone fills out information
>>>> and
>>>>submits it, it saves to the DB just fine. However, if they come back
>>>>later
>>>>and just put in, for example, a new phone number, it replaces all of the
>>>>other information with blanks.
>>>> Here is my current $sql query:
>>>>
>>>>$sql = "UPDATE $table
>>>>SET
>>>>first_name='$first_name',
>>>>last_name='$last_name',
>>>>hs_last_name='$hs_last_name',
>>>>guest_name='$guest_name',
>>>>street_address1='$street_address1',
>>>>street_address2='$street_address2',
>>>>city='$city',
>>>>state='$state',
>>>>zip='$zip',
>>>>phone1='$phone1',
>>>>phone2='$phone2',
>>>>email_address='$email_address',
>>>>farmers_barn='$farmers_barn',
>>>>wrhs_tour='$wrhs_tour',
>>>>crystal_rose='$crystal_rose',
>>>>registration_comments='$registration_comments',
>>>>date_registered='$today'
>>>>WHERE first_name='$first_name' AND last_name='$last_name'";
>>>>
>>>>
>>>>
>>>>--
>>>>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: Conditional updating...

Grae Wolfe - PHP wrote:
> Sorry I have been out of touch... I thought I had this problem beat, but
> I was wrong. I decided that the best thing to do was to filter the
> variables as the $sql statement was being created. I tried using the
> following code, and got a message back that it was invalid and my Query
> couldn't execute... Can anyone tell me where I screwed this one up??

Print out $sql and then try to manually do it in your mysql (or whatver
DB engine) shell.

I imagine you have a syntax error and that will tell you exactly what
and where it is :)

And I hope you're only criteria for the value of each colum isn't that
its just not empty.

If so you will be vilnerable to SQL injection attacks and your data will
be compromised. You should at the very least quote the values with a
valid SQL quoting function. (IE not just wraping it in quotes but one
that actually escapes certain characters and wraps it in quotes as need be)

Do not rely on that automaticaly being done (IE think how crappliy
unreliable and dangerous relying on "Magic Quotes" is, oi what pile
*that* is...)

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

Re: Conditional updating...

Thank you for the thought, however, I don't have a shell that I can run in,
hence, I have to rely on help from others.


""JupiterHost.Net"" wrote in message
news:449CA3D9.8080702@jupiterhost.net...
>
>
> Grae Wolfe - PHP wrote:
>> Sorry I have been out of touch... I thought I had this problem beat,
>> but I was wrong. I decided that the best thing to do was to filter the
>> variables as the $sql statement was being created. I tried using the
>> following code, and got a message back that it was invalid and my Query
>> couldn't execute... Can anyone tell me where I screwed this one up??
>
> Print out $sql and then try to manually do it in your mysql (or whatver DB
> engine) shell.
>
> I imagine you have a syntax error and that will tell you exactly what and
> where it is :)
>
> And I hope you're only criteria for the value of each colum isn't that its
> just not empty.
>
> If so you will be vilnerable to SQL injection attacks and your data will
> be compromised. You should at the very least quote the values with a valid
> SQL quoting function. (IE not just wraping it in quotes but one that
> actually escapes certain characters and wraps it in quotes as need be)
>
> Do not rely on that automaticaly being done (IE think how crappliy
> unreliable and dangerous relying on "Magic Quotes" is, oi what pile *that*
> is...)

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

Re: Conditional updating...

Grae Wolfe - PHP wrote:
> Thank you for the thought, however, I don't have a shell that I can run in,
> hence, I have to rely on help from others.

something like phpmyadmin, phppgadmin will do.

--
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: Conditional updating...

Grae Wolfe - PHP wrote:

> Thank you for the thought, however, I don't have a shell that I can run in,
> hence, I have to rely on help from others.

get a GUI client then and use that...

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

RE: Conditional updating...

I use:
$sql .= "`first_name` = '" . $first_name . "', ";
in the if statements. Notice the angled tick marks and single tick marks.

Then before executing the query, remove the last ", ".
$sql = substr($sql, 0, -2);
In your case, before you append the WHERE clause.

You should echo out your built-up $sql statement to see if it is missing
anything (maybe by executing that query in phpmyadmin or some other query
tool). What if nothing is "!empty"? Will you attempt to execute an
incorrect statement like:
UPDATE `aTable` SETWHERE first_name='' AND last_name=''
Notice "SETWHERE".

Regards,
Dwight

> -----Original Message-----
> From: Grae Wolfe - PHP [mailto:php@graewolfe.com]
> Sent: Friday, June 23, 2006 8:16 PM
> To: php-db@lists.php.net
> Subject: Re: [PHP-DB] Conditional updating...
>
> Sorry I have been out of touch... I thought I had this problem beat,
> but
> I was wrong. I decided that the best thing to do was to filter the
> variables as the $sql statement was being created. I tried using the
> following code, and got a message back that it was invalid and my Query
> couldn't execute... Can anyone tell me where I screwed this one up??
>
> $sql = "UPDATE $table SET;
>
>
> if(!empty($first_name))
> $sql .='first_name='.$first_name.',';
>
>
> if(!empty($last_name))
> $sql .='last_name='.$last_name.',';
>
>
> if(!empty($hs_last_name))
> $sql .='hs_last_name='.$hs_last_name.',';
>
>
> if(!empty($street_address1))
> $sql .='street_address1='.$street_address1.',';
>
>
> if(!empty($street_address2))
> $sql .='street_address2='.$street_address2.',';
>
>
> if(!empty($city))
> $sql .='city='.$city.',';
>
>
> if(!empty($state))
> $sql .='state='.$state.',';
>
>
> if(!empty($zip))
> $sql .='zip='.$zip.',';
>
>
> if(!empty($phone1))
> $sql .='phone1='.$phone1.',';
>
>
> if(!empty($phone2))
> $sql .='phone2='.$phone2.',';
>
>
> if(!empty($email_address))
> $sql .='email_address='.$email_address.',';
>
>
> if(!empty($current_info))
> $sql .='current_info='.$current_info.',';
>
>
> if(!empty($today))
> $sql .='date_registered='.$today;
>
>
> WHERE first_name='$first_name' AND last_name='$last_name'";
>
>
>
> "Jeffrey" wrote in message
> news:44966E2A.9070606@ungodly.com...
> > Perhaps I have misunderstood something here. But it seems to me that
> > anyone who just happens to put John Smith's name in could alter Mr.
> > Smith's data.
> >
> > If users can update their own data, should you not have a log in process
> > to ensure that only the original user can update his data? Them once he
> > has logged in, you can populate all fields with data from the DB.
> >
> > Jeffrey
> >
> > Grae Wolfe - PHP wrote:
> >> That was the first thing that I was going to do, but there is a concern
> >> there for security of the data being input... This is a registration
> >> site, and I don't want to provide information on "John Smith" to anyone
> >> who just happens to put his name in.
> >>
> >>
> >> ""Alejandro Tesone"" wrote in message
> >> news:e13e3a560606171219w540e5b12oe81a3212d0746bc1@mail.gmail .com...
> >>
> >>>Why don't you try populating the fields the user intends to modify
> >>>with the information you already have?
> >>>
> >>>Alex T
> >>>
> >>>On 6/17/06, Grae Wolfe - PHP wrote:
> >>>
> >>>>Good day!
> >>>> I have been working on this little "free" project for a while, and
> now
> >>>> I
> >>>>have hit another major hiccup. Is there a simple way to only update
> >>>>fields
> >>>>that have something in them?
> >>>> The problem that I am having is that if someone fills out
> information
> >>>> and
> >>>>submits it, it saves to the DB just fine. However, if they come back
> >>>>later
> >>>>and just put in, for example, a new phone number, it replaces all of
> the
> >>>>other information with blanks.
> >>>> Here is my current $sql query:
> >>>>
> >>>>$sql = "UPDATE $table
> >>>>SET
> >>>>first_name='$first_name',
> >>>>last_name='$last_name',
> >>>>hs_last_name='$hs_last_name',
> >>>>guest_name='$guest_name',
> >>>>street_address1='$street_address1',
> >>>>street_address2='$street_address2',
> >>>>city='$city',
> >>>>state='$state',
> >>>>zip='$zip',
> >>>>phone1='$phone1',
> >>>>phone2='$phone2',
> >>>>email_address='$email_address',
> >>>>farmers_barn='$farmers_barn',
> >>>>wrhs_tour='$wrhs_tour',
> >>>>crystal_rose='$crystal_rose',
> >>>>registration_comments='$registration_comments',
> >>>>date_registered='$today'
> >>>>WHERE first_name='$first_name' AND last_name='$last_name'";
> >>>>
> >>>>
> >>>>
> >>>>--
> >>>>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: Conditional updating...

------=_Part_19530_8956408.1151477811070
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

try this one.

just pass for $_value an array with your table fieldname as your key

function constructUpdate( $_tbl_name, $_where, $_values )
{
$valstr = '';
$firstval = false;
if (is_array($_values)) {
foreach( $_values as $key=>$val ) {
if ($val != '') {
if( $firstval )
$valstr.= ',';

if (is_string($val)) {
$valstr.= " $key = '$val'";
} else {
$valstr.= " $key = $val";
}
$firstval = true;
}
}
} else {
$valstr = $_values;
}
$retStr = "update $_tbl_name set $valstr";
if( $_where )
$retStr.= " where $_where";

return $retStr;
}

------=_Part_19530_8956408.1151477811070--