FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » update mysql without manually tying SET statements
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
update mysql without manually tying SET statements [message #178160] Wed, 16 May 2012 10:12 Go to next message
Alex Pavluck is currently offline  Alex Pavluck
Messages: 5
Registered: April 2011
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.

ex

$sql = "update myhugedatabase
SET
reporter = '$_POST[reporter]',
date = '$_POST[date]',
district = '$_POST[district]',
village = '$_POST[village]',
school = '$_POST[school]',
address = '$_POST[address]',
teacher = '$_POST[teacher]',
teacher_contact = '$_POST[teacher_contact]',
alt_teach = '$_POST[alt_teach]',
alt_teach_contact = '$_POST[alt_teach_contact]',
gps = '$_POST[gps]',
etc
etc
etc

The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.
Re: update mysql without manually tying SET statements [message #178161 is a reply to message #178160] Wed, 16 May 2012 10:23 Go to previous messageGo to next message
Shake is currently offline  Shake
Messages: 40
Registered: May 2012
Karma: 0
Member
add to buddy list
ignore all messages by this user
El 16/05/2012 16:12, apavluck(at)gmail(dot)com escribió:
> I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.
> [...]
> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.

Withouth entering in details, security and best practices...

$sqlString = ' UPDATE blablalba SET ';
foreach($_POST as $key => $value)
{
$sqlString .= " `$key` = '$value', ";
}

// You have to deal here a little with the last comma ;)

It's possible not all data in $_POST are fields of the database. You
could manage prefixing the indexes or other ways...

foreach($_POST as $key => $value)
{
if(!preg_match('/^my_prefix.+/',$key)) continue;
$sqlString .= " `$key` = '$value', ";
}

That's some basic ideas, but be carefull, because there are important
things to take in care when using this metodologies. Using POST data to
build SQL strings "automagically" implies taking care of sqlInjection
and thiese kind of things.

Greetings
Re: update mysql without manually tying SET statements [message #178165 is a reply to message #178161] Wed, 16 May 2012 12:06 Go to previous messageGo to next message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Shake wrote:

> El 16/05/2012 16:12, apavluck(at)gmail(dot)com escribió:
>> I am trying to setup some generic code that will allow me to update a
>> mysql database via form and php. I can capture the data in the row of
>> the database that I want to update. I can present that in a form
>> populated with the existing values. What I can't figure out how to do is
>> to avoid typing out the SET statements in the update query.
>> [...]
>> The reason I want to do this is because I manage many projects all with
>> their own variables and databases and the update is the only part that I
>> have to type in manually.
>
> Withouth entering in details, security and best practices...
>
> $sqlString = ' UPDATE blablalba SET ';

The leading space does not make sense.

> foreach($_POST as $key => $value)
> {
> $sqlString .= " `$key` = '$value', ";
> }
>
> // You have to deal here a little with the last comma ;)

That is one reason why you should _not_ build queries that way, but at least
join an array instead. See also <news:1762069(dot)ZWGnKmheAe(at)PointedEars(dot)de>.

> It's possible not all data in $_POST are fields of the database. You
> could manage prefixing the indexes or other ways...
>
> foreach($_POST as $key => $value)
> {
> if(!preg_match('/^my_prefix.+/',$key)) continue;
> $sqlString .= " `$key` = '$value', ";
> }

Or you filter out the items that you do not want, by key, using the
array_filter() function, which also returns the array to join that
I mentioned above.

<http://php.net/array_filter>


PointedEars
--
Sometimes, what you learn is wrong. If those wrong ideas are close to the
root of the knowledge tree you build on a particular subject, pruning the
bad branches can sometimes cause the whole tree to collapse.
-- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom(at)94(dot)75(dot)214(dot)39>
Re: update mysql without manually tying SET statements [message #178169 is a reply to message #178160] Wed, 16 May 2012 16:48 Go to previous messageGo to next message
M. Strobel is currently offline  M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Am 16.05.2012 16:12, schrieb apavluck(at)gmail(dot)com:
> I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.
>
> ex
>
> $sql = "update myhugedatabase
> SET
> reporter = '$_POST[reporter]',
> date = '$_POST[date]',
> district = '$_POST[district]',
> village = '$_POST[village]',
> school = '$_POST[school]',
> address = '$_POST[address]',
> teacher = '$_POST[teacher]',
> teacher_contact = '$_POST[teacher_contact]',
> alt_teach = '$_POST[alt_teach]',
> alt_teach_contact = '$_POST[alt_teach_contact]',
> gps = '$_POST[gps]',
> etc
> etc
> etc
>
> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.

If you don't use a framework, you must type in a SET statement for every single
column you want to save in the database. There is no getting around it. You must do
it at least once for every table.

You would normally do even more: apply some test/validation on every field, and make
sure required fields are not empty, and number fields contain numbers...

You do this nowadays with prepared statements, to be protected against SQL injection
attacks.

If you want generic code, you must define the fields and their properties somewhere
else in your code, this is normally not less work.

/Str.
Re: update mysql without manually tying SET statements [message #178170 is a reply to message #178160] Thu, 17 May 2012 07:09 Go to previous messageGo to next message
bill is currently offline  bill
Messages: 310
Registered: October 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 5/16/2012 10:12 AM, apavluck(at)gmail(dot)com wrote:
> I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.
>
> ex
>
> $sql = "update myhugedatabase
> SET
> reporter = '$_POST[reporter]',
> date = '$_POST[date]',
> district = '$_POST[district]',
> village = '$_POST[village]',
> school = '$_POST[school]',
> address = '$_POST[address]',
> teacher = '$_POST[teacher]',
> teacher_contact = '$_POST[teacher_contact]',
> alt_teach = '$_POST[alt_teach]',
> alt_teach_contact = '$_POST[alt_teach_contact]',
> gps = '$_POST[gps]',
> etc
> etc
> etc
>
> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.

if you have all the data in the row (or hidden fields) you can
just do a REPLACE instead of the UPDATE

bill
Re: update mysql without manually tying SET statements [message #178171 is a reply to message #178169] Thu, 17 May 2012 07:14 Go to previous messageGo to next message
IRC is currently offline  IRC
Messages: 6
Registered: April 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
On May 16, 9:48 pm, "M. Strobel" <sorry_no_mail_h...@nowhere.dee>
wrote:
> Am 16.05.2012 16:12, schrieb apavl...@gmail.com:
>
>
>
>
>
>
>
>
>
>> I am trying to setup some generic code that will allow me to update a mysql database via form and php.  I can capture the data in the row of the database that I want to update.  I can present that in a form populated with the existing values.  What I can't figure out how to do is to avoid typing out the SET statements in the update query.
>
>> ex
>
>> $sql = "update myhugedatabase
>> SET
>> reporter = '$_POST[reporter]',
>> date = '$_POST[date]',
>> district = '$_POST[district]',
>> village = '$_POST[village]',
>> school = '$_POST[school]',
>> address = '$_POST[address]',
>> teacher = '$_POST[teacher]',
>> teacher_contact = '$_POST[teacher_contact]',
>> alt_teach = '$_POST[alt_teach]',
>> alt_teach_contact = '$_POST[alt_teach_contact]',
>> gps = '$_POST[gps]',
>> etc
>> etc
>> etc
>
>> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.
>
> If you don't use a framework, you must type in a SET statement for every single
> column you want to save in the database. There is no getting around it. You must do
> it at least once for every table.
>
> You would normally do even more: apply some test/validation on every field, and make
> sure required fields are not empty, and number fields contain numbers...
>
> You do this nowadays with prepared statements, to be protected against SQL injection
> attacks.
>
> If you want generic code, you must define the fields and their properties somewhere
> else in your code, this is normally not less work.
>
> /Str.

I agree with Strobel.

You have to create a function once (which can be generic) which will
process your update statement and you just pass an array of $_POST
data into that function and it will process it accordingly.

It may be worthwhile to look into ADODB database library found on php
http://adodb.sourceforge.net/
Re: update mysql without manually tying SET statements [message #178176 is a reply to message #178161] Thu, 17 May 2012 14:06 Go to previous messageGo to next message
Alex Pavluck is currently offline  Alex Pavluck
Messages: 5
Registered: April 2011
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Shake's suggestion worked exactly how I needed it to work. I had to substring some things off as he said, but all in all, was just what was needed.

Thanks!!!!


On Wednesday, May 16, 2012 10:23:12 AM UTC-4, Shake wrote:
> El 16/05/2012 16:12, apavluck(at)gmail(dot)com escribi�:
>> I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.
>> [...]
>> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.
>
> Withouth entering in details, security and best practices...
>
> $sqlString = ' UPDATE blablalba SET ';
> foreach($_POST as $key => $value)
> {
> $sqlString .= " `$key` = '$value', ";
> }
>
> // You have to deal here a little with the last comma ;)
>
> It's possible not all data in $_POST are fields of the database. You
> could manage prefixing the indexes or other ways...
>
> foreach($_POST as $key => $value)
> {
> if(!preg_match('/^my_prefix.+/',$key)) continue;
> $sqlString .= " `$key` = '$value', ";
> }
>
> That's some basic ideas, but be carefull, because there are important
> things to take in care when using this metodologies. Using POST data to
> build SQL strings "automagically" implies taking care of sqlInjection
> and thiese kind of things.
>
> Greetings
Re: update mysql without manually tying SET statements [message #178177 is a reply to message #178176] Thu, 17 May 2012 15:06 Go to previous messageGo to next message
Shake is currently offline  Shake
Messages: 40
Registered: May 2012
Karma: 0
Member
add to buddy list
ignore all messages by this user
apavluck(at)gmail(dot)com a écrit :
> Shake's suggestion worked exactly how I needed it to work. I had to
> substring some things off as he said, but all in all, was just what was
> needed.
>
> Thanks!!!!
>

But remeber, I said "Withouth entering in details, security and best
practices"

This means that I explained "the basic" but there are better
"aproximations" to resolve this problem.

And some of them had been explained in this thread. The fact is that
usually are a better way to do the same.

Greetings
Re: update mysql without manually tying SET statements [message #178178 is a reply to message #178171] Fri, 18 May 2012 04:49 Go to previous message
Erwin Moller is currently offline  Erwin Moller
Messages: 228
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 5/17/2012 1:14 PM, IRC wrote:
> On May 16, 9:48 pm, "M. Strobel"<sorry_no_mail_h...@nowhere.dee>
> wrote:
>> Am 16.05.2012 16:12, schrieb apavl...@gmail.com:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> I am trying to setup some generic code that will allow me to update a mysql database via form and php. I can capture the data in the row of the database that I want to update. I can present that in a form populated with the existing values. What I can't figure out how to do is to avoid typing out the SET statements in the update query.
>>
>>> ex
>>
>>> $sql = "update myhugedatabase
>>> SET
>>> reporter = '$_POST[reporter]',
>>> date = '$_POST[date]',
>>> district = '$_POST[district]',
>>> village = '$_POST[village]',
>>> school = '$_POST[school]',
>>> address = '$_POST[address]',
>>> teacher = '$_POST[teacher]',
>>> teacher_contact = '$_POST[teacher_contact]',
>>> alt_teach = '$_POST[alt_teach]',
>>> alt_teach_contact = '$_POST[alt_teach_contact]',
>>> gps = '$_POST[gps]',
>>> etc
>>> etc
>>> etc
>>
>>> The reason I want to do this is because I manage many projects all with their own variables and databases and the update is the only part that I have to type in manually.
>>
>> If you don't use a framework, you must type in a SET statement for every single
>> column you want to save in the database. There is no getting around it. You must do
>> it at least once for every table.
>>
>> You would normally do even more: apply some test/validation on every field, and make
>> sure required fields are not empty, and number fields contain numbers...
>>
>> You do this nowadays with prepared statements, to be protected against SQL injection
>> attacks.
>>
>> If you want generic code, you must define the fields and their properties somewhere
>> else in your code, this is normally not less work.
>>
>> /Str.
>
> I agree with Strobel.
>
> You have to create a function once (which can be generic) which will
> process your update statement and you just pass an array of $_POST
> data into that function and it will process it accordingly.
>
> It may be worthwhile to look into ADODB database library found on php
> http://adodb.sourceforge.net/

+1 on adodb. I have always used it with pleasure: a simple clear approach.

But prepared statements are just one way to avoid SQL injection.
One could also simply use the right string-escape-function.
Both approaches are OK.

Regards,
Erwin Moller

--
"That which can be asserted without evidence, can be dismissed without
evidence."
-- Christopher Hitchens
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: table appears with empty rows, and row data appears before table. Code seems ok. What is going on?
Next Topic: Dynamic water marking (redirect?)
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Oct 17 09:26:41 EDT 2017

Total time taken to generate the page: 0.00700 seconds