Query returns 0 affected rows

[eluser]CrossMotion[/eluser]
I have this strange problem, when I try to update an existing record codeigniter keeps returning 0 affected rows while records with the right content exist. the problem is in the weeknr. when I hard code it, it works. Do I need to add some ather kind of syntax to make it work?

below is the function in my model:

Code:

function add_item($input) {
    $this->db->where('userId',$input['userId']);
    $this->db->where('weeknr',$input['weeknr']);
    $this->db->update('cancelations_week',$input);

    if($this->db->affected_rows() == 0) {
        $this->db->insert('cancelations_week',$input);    
    }
}

And the code in my controller:

Code:

$this->load->model('afmelden_model');
$userId = $_SESSION['user_id'];
if ( $_SERVER ['REQUEST_METHOD'] == 'POST' ){
    for ($i = 0; $i <= 7; ++$i):
        $input['userId'] = $_SESSION['user_id'];
        $input['reservation'] = $this->input->post( 'week'.$i );
        $input['weeknr'] = $this->input->post( 'weeknummer'.$i );
        $this->afmelden_model->add_item($input);
    endfor;
}

Thx for the help!

[eluser]pistolPete[/eluser]
Enable the profiler to see which sql queries are actually generated.

Code:

$this->output->enable_profiler(TRUE);

How would the hardcoded line look like?

[eluser]TheFuzzy0ne[/eluser]
I'd strongly suggest some validation in there. I'm guessing that the query is not ending up the way you are wanting it.

Is 'weeknummer' a typo, or is that the correct spelling? If it's a typo, that's probably your problem.

I also suggest you check out CodeIgniter's session class, as it will probably help keep your code looking CI-exy. Also, $_SERVER['REQUEST_METHOD'] can be called like this:

Code:

$this->input->server('REQUEST_METHOD');

I agree that they both do the same thing, but if you use CodeIgniters input class, you should never have to call upon those super globals directly.

[eluser]CrossMotion[/eluser]
[quote author="pistolPete" date="1235426123"]Enable the profiler to see which sql queries are actually generated.

Code:

$this->output->enable_profiler(TRUE);

How would the hardcoded line look like?[/quote]

Below is the generated query:

Code:

UPDATE `cancelations_week` SET `userId` = '1', `reservation` = '1', `weeknr` = '2009_09' WHERE `userId` = '1' AND `weeknr` = '2009_09'

Funny thing is that when I remove the variable and replace it with a value like 2009_09, it does work.

[eluser]CrossMotion[/eluser]
[quote author="TheFuzzy0ne" date="1235426150"]I'd strongly suggest some validation in there. I'm guessing that the query is not ending up the way you are wanting it.

Is 'weeknummer' a typo, or is that the correct spelling? If it's a typo, that's probably your problem.

I also suggest you check out CodeIgniter's session class, as it will probably help keep your code looking CI-exy. Also, $_SERVER['REQUEST_METHOD'] can be called like this:

Code:

$this->input->server('REQUEST_METHOD');

I agree that they both do the same thing, but if you use CodeIgniters input class, you should never have to call upon those super globals directly.[/quote]

Weeknummer isn't a typo, its Dutch . I've changed the Request_method, indeed I agree its better to use the CodeIgniter version. This is my first CodeIgniter project so I'm still exploring .

[eluser]CrossMotion[/eluser]
Anyone a suggestion?

[eluser]TheFuzzy0ne[/eluser]
Would it be possible for you to attach a copy of your database if it doesn't contain sensitive information? Really, I just need the schema, and the row you feel should be retrieve, but the more, the merrier.

Thanks.

[eluser]CrossMotion[/eluser]
here is a screen shot from the database and the SOL for the table in question.

Thank you

[eluser]TheFuzzy0ne[/eluser]
I see nothing wrong with the query, and it seems to work fine for me. The only question it leaves is, are you sure the model method is actually being called?

[eluser]Kumar Chetan sharma[/eluser]
I am facing same issue. Here is my code:

Code:

$queryString = $_POST["text_area"];
                if(eregi("(select|insert|delete|update)", $queryString)!=1)
                {
                        echo 'Invalid query syntex!';
                        return 0;
                }
                if(!$this->db->simple_query($queryString)){
                        echo 'Invalid query syntex!';
                        return 0;
                }
                $selectQuery = ( eregi("(select)", $queryString)==1? 1 : 0 );
                $query = $this->db->query($queryString);
                if($selectQuery==1)
                {
                        $this->print_xml_header();
                        $docConfig = array('root' => 'rows', 'element' => 'row');
                        echo $this->dbutil->xml_from_result($query, $docConfig);
                        $query->free_result();
                }else{
                        echo '', $this->db->affected_rows(), ' rows affected!';
                        return 0;
                }

Whatever I do the update returns 0 rows affected...:-(