Codeigniter database update

问题:

The table is like this

and I want to update DecryptionDate by specify ArchiveID and RecipientID

this is my code

$this->load->database();
$date = date("Y-m-d H:i:s");
$data = array('DecryptionDate' => $date);
$array = array('ArchiveID'=>$archiveID.'','RecipientID'=>$userID.'');
$this->db->where($array);
$this->db->update('log', $data);
if ($this->db->affected_rows() > 0) {
    echo "SUCCESS";
} else {
    echo "FAIL";
}

my problem is I can update the data only when $archiveID is 911 and $userID is test01 but the program fail to update when $archiveID is 911 and $userID is test02

after added echo $this->db->last_query(); I've got

UPDATE log SET DecryptionDate = '2011-11-16 20:01:39' WHERE ArchiveID = '911' AND RecipientID = 'test01'

when ArchiveID is test01 and the update is SUCCESS

and

UPDATE log SET DecryptionDate = '2011-11-16 20:03:10' WHERE ArchiveID = '911' AND RecipientID = 'test02'

when ArchiveID is test02 and the update is FAIL

I've try this

$this->load->database();
$date = date("Y-m-d H:i:s");
$this->db->query('UPDATE log
    SET DecryptionDate = \''.$date.'\'
    WHERE ArchiveID = \''.$archiveID.'\' AND RecipientID = \''.$userID.'\'');
if ($this->db->affected_rows() > 0) {
    echo "SUCCESS";
    return TRUE;
} else {
    echo "FAIL";
    return FALSE;
}

but the result's still the same

and try check only the RecipientID like this

$this->load->database();
$date = date("Y-m-d H:i:s");
$this->db->query('UPDATE log
    SET DecryptionDate = \''.$date.'\'
    WHERE RecipientID = \''.$userID.'\'');
if ($this->db->affected_rows() > 0) {
    echo "SUCCESS";
    return TRUE;
} else {
    echo "FAIL";
    return FALSE;
}

make the update success with only record that match with the RecipientID but not with duplicate ArchiveID with other record

like this

Finally, I've test update with common php file with following code instead of via CI and also result is fail

$date = date("Y-m-d H:i:s");
$strSQL = "UPDATE log SET DecryptionDate = '".$date."' WHERE ArchiveID = '911' AND RecipientID = 'test02' ";
$objQuery = mysql_query($strSQL);
if( mysql_affected_rows($objQuery) != 0 ) 
{ 
    echo (" SUCCESS ");
} else { 

    echo (" FAIL ");
}

so I think this must be database problem

here is the DB structure

and ArchiveID and RecipientID are index

回答1:

The code looks fine. Please check that all your fields are correctly spelt, and values are as expected.

Try with a more verbose and debugging-friendly way like this, it should help you spot the problem (maybe the $userID contains a space, or something trivial like that). Call last_query() at the end to see if the query is really the one you wanted

//...
var_dump($archiveID);
var_dump($userID);
$this->db->where('ArchiveID',$archiveID);
$this->db->where('RecipientID',$userID);
$data = array('DecryptionDate' => $date);
$this->db->update('log', $data);

// call this to test the correct query; see if is what you intended
echo $this->db->last_query();

回答2:

When you say no, do you mean no you cannot connect to MySQL or phpMyAdmin, or no, the query on test2 doesn't return any results? If the second one is true, can you do a select * and verify the fields existance? I cant help but think something may have happened to your data.

回答3:

I try export the data from database and truncate it then import the data back, it can help, the problem solved.

回答4:

I have use this code for checking update query status.

$this->db->where($array);
$status = $this->db->update('log', $data);
if($status)
   echo 'Success';
else
    echo 'Fail';