Updating Multiple Rows in One Table...

Updating Multiple Rows in One Table... - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Updating Multiple Rows in One Table... (/showthread.php?tid=4962)

Updating Multiple Rows in One Table... - El Forum - 12-27-2007

[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?

Updating Multiple Rows in One Table... - El Forum - 12-27-2007

[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.

Updating Multiple Rows in One Table... - El Forum - 12-27-2007

[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

Updating Multiple Rows in One Table... - El Forum - 12-27-2007

[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."

Updating Multiple Rows in One Table... - El Forum - 12-27-2007

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

thanks!