calling mysql procedure using codeigniter [Error: result consisted more than one row]

Question

I have table status and status_stage . After insert in status table, I want to add it to the status_stage table. I have this procedure:

DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `addStatus`(status VARCHAR(45), stage int (8))
BEGIN

    DECLARE status_id INT DEFAULT NULL; 

    INSERT INTO status (status)
    VALUES (status);

    SELECT id INTO status_id FROM status WHERE status = status;

    INSERT INTO stage_status (stage,status)
    VALUES (stage,id);
END

And I call that function with this:

$result= $this->db->query("call addStatus('$status', $stage)"); 
return ($result->num_rows()>0);

When I tried it it gives me this Error:

Result consisted of more than one row

Answer 1

Your SELECT ... INTO statement got more than one result because of your WHERE clause with status = status, this equals forever

you need to change the param name to distinguish it from the table column name (eg. status_param)