任何人都可以告诉这个查询有什么问题吗？

Question

    $sql    =   "select * from instalmentsdetails_ where studentFeeId='$savestudentfees' AND instalmentName='$instlnamefdtls'";
    $query  =   $this->db->query($sql);
    if($query->num_rows() > 0){
        echo($id);
        $this->db->where('instalmentsDetailsId',$id);
        $this->db->update('instalmentsdetails_',$instldata);    
    }else{
        echo($id);
        $id='';
        echo($id);
        $sql=   "INSERT INTO instalmentsdetails_` (`instalmentsDetailsId`, `studentFeeId`, `instalmentName`, `amount`, `dueDate`, `status`) VALUES ('$id', '$savestudentfees', '$instlnamefdtls', '$amtfdtls', '2011-10-06', '1')";
        $id=$this->db->insert_id();
        $query  =   $this->db->query($sql);
        return $query;
    }
    return $id;

This query first checks if there are any rows present, if there is, it is going to update the old record, otherwise it is going to create a new record, but for some reason it does not work as expected even when the query returns num_row() > 0.It's a model in codeigniter

Answer 1

Have you checked the output of $query->num_rows()?, eg echo $query->num_rows()

You could do this instead and save the bother in PHP

ALTER TABLE teami_db ADD UNIQUE INDEX(studentFeeId, instalmentName);

Then you can perform an ON DUPLICATE KEY UPDATE query like so.

INSERT INTO `instalmentsdetails_teami` (
  `instalmentsDetailsId`,
  `studentFeeId`,
  `instalmentName`,
  `amount`,
  `dueDate`,
  `status`
) VALUES (
  '$id',
  '$savestudentfees',
  '$instlnamefdtls',
  '$amtfdtls',
  '2011-10-06',
  '1'
) ON DUPLICATE KEY UPDATE
  `instalmentsDetailsId` = VALUES(`instalmentsDetailsId`),
  `studentFeeId`         = VALUES(`studentFeeId`),
  `instalmentName`       = VALUES(`instalmentName`),
  `amount`               = VALUES(`amount`),
  `dueDate`              = VALUES(`dueDate`),
  `status`               = VALUES(`status`)

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html