mysql query not running correctly from inside the application

Question

I am completely stumped. Here is my php (CodeIgniter) code:

function mod()
{
    $uid = $this->session->userdata('uid');
    $pid = $this->input->post('pid');
    if ($this->_verify($uid,$pid))
    {
        $name  = $this->input->post('name');
        $price = $this->input->post('price');
        $curr  = $this->input->post('curr');
        $url   = $this->input->post('url');

        $query = $this->db->query("UPDATE items SET
                        name=".$this->db->escape($name).",
                        price=".$this->db->escape($price).",
                        currency=".$this->db->escape($curr),",
                        url=".$this->db->escape($url)."
                        WHERE pid=".$this->db->escape($pid)." LIMIT 1");
    }
    header('location: '.$this->session->userdata('current'));

}

The purpose of this code is to modify the properties (name, price, currency, url) of a row in the 'items' table (priary key is pid). However, for some reason, allowing this function to run once modifies the name, price, currency and url of ALL entries in the table, regardless of their pid and of the LIMIT 1 thing I tacked on the end of the query. It's as if the last line of the query is being completely ignored.

As if this wasn't strange enough, I replaced "$query = $this->db->query(" with an "echo" to see the SQL query being run, and it outputs a query much like I would expect:

UPDATE items 
   SET name = 'newname', 
       price = 'newprice', 
       currency = 'newcurrency', 
       url = 'newurl' 
 WHERE pid = '10' 
 LIMIT 1

Copy-pasting this into a MySQL window acts exactly as I want: it modifies the row with the selected pid.

What is going on here???

Answer 1

Now I feel stupid: all it took was seeing my code in a different font. My code has

currency=".$this->db->escape($curr),",

instead of

currency=".$this->db->escape($curr).",

The echoing made it work just fine because apparently you can give echo more than one string, comma separated, and it concatenates them

cries I spent hours on this

Answer 2

I know you answered your own question, but let me just add this to the pile: You're not leveraging CodeIgniter AT ALL in this sort of query - which if you used CI as it's intended, you wouldn't have had that typo. Your query should look like this (among other things):

$query = $this->db->update('items', 
                           array('name' => $this->input->post('name'),
                                 'price' => $this->input->post('price'),
                                 'curr' => $this->input->post('curr')),
                           array('id' => $this->input->post('id')),
                           1);

By assembling the query string by hand, you're undoing what CI does for you. Only when you're using some complex JOIN statement should you be writing your own SQL in CI, and even then, you want to use the sprintf PHP function to make sure you're not introducing typos.