Welcome Guest, Not a member yet? Register   Sign In
Updating Multiple Rows in One Table...
#1

[eluser]fancms[/eluser]
Right now I have this code to update multiple rows of one table, after validation has been run and no errors are found:
Code:
foreach($_POST as $key=>$val) {
      $this->db->where('variable', $key);
      $this->db->update('settings', $val);
      }
I'm very wary of using any type of $_POST value like that, even though I know the active record class will escape any values upon updating. This also results in 50+ queries.

Prior to CI I would update multiple rows like so:
Code:
$db->query("UPDATE tablename SET value = CASE
WHEN variable='variablename' THEN 'updatevalue'
(etc etc)
ELSE value END");
This way would allow me to update using just one query.

However, when I tried to use this way in CI, I kept getting errors, primarily error 1064, which, from what I was able to determine, is caused by apostrophes not being escaped. I did check that magic_quotes is disabled (it is). I also noticed it was throwing this error on anything I used $this->db->escape on that did *not* have an apostrophe.

Anyway, I guess what I want to know is if there is any way to update multiple rows using the second example (or something similar) and still being able to escape the data?
#2

[eluser]tonanbarbarian[/eluser]
I have never had any issues using $this->db->escape. Which database are you connecting to?

as for the first method if you want to do it safely try
Code:
foreach(array_keys($_POST) as $key) {
      $val = $this->input->post($key, true);
      $this->db->where('variable', $key);
      $this->db->update('settings', $val);
      }
so all we are doing here is looking at the keys in the POST and using the keys with the $this->input-post and forcing XSS_CLEAN on each post variable.
#3

[eluser]fancms[/eluser]
i am connecting to a mysql database. this is an example of the error i get:
Quote:An Error Was Encountered
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[email protected]'' WHEN variable='language' THEN 'english' WH' at line 3
this is the syntax around those lines:
Code:
$configupdate = $this->db->query("UPDATE ".$this->db->dbprefix."settings SET value = CASE
                         WHEN variable='domain' THEN '".$this->validation->domain."'
                         WHEN variable='site_email' THEN '".$this->db->escape($this->validation->site_email)."'
                         WHEN variable='language' THEN '".$this->validation->language."'
                         WHEN variable='dateformat' THEN '".$this->validation->dateformat."'

Quote:as for the first method if you want to do it safely try
foreach(array_keys($_POST) as $key) {
$val = $this->input->post($key, true);
$this->db->where(’variable’, $key);
$this->db->update(’settings’, $val);
}[/code]
thanks, tonanbarbarian - that does help Smile now if i can only figure out a way to reduce the number of queries
#4

[eluser]tonanbarbarian[/eluser]
the problem is that you are confusing the $this->db->escape and $this->db->escape_str

If you are using $this->db->escape you do not need to put your own singel quotes around the data

escape_str just makes sure that any quotes etc are escaped correctly in the string
escape uses escape_str and then puts the quotes around the string as well

try this
Code:
$configupdate = $this->db->query("UPDATE ".$this->db->dbprefix."settings SET value = CASE
  WHEN variable='domain' THEN ".$this->validation->domain."
  WHEN variable='site_email' THEN ".$this->db->escape($this->validation->site_email)."
  WHEN variable='language' THEN ".$this->validation->language."
  WHEN variable='dateformat' THEN ".$this->validation->dateformat."
#5

[eluser]fancms[/eluser]
i will give that a try Smile thanks!




Theme © iAndrew 2016 - Forum software by © MyBB