CodeIgniter Forums
DB Insert Problem: Unknown Column - Column never referenced in code - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: DB Insert Problem: Unknown Column - Column never referenced in code (/thread-29884.html)



DB Insert Problem: Unknown Column - Column never referenced in code - El Forum - 04-25-2010

[eluser]Tumac[/eluser]
I have a function that updates two tables. In my code I pass an ID field to be updated. The ID comes from a javascript function/application.

I have made no changes to my code and at one time a couple of weeks ago, the code was working perfectly.

The error below is referencing 'delivery_detail.poID'. The javascript does not reference delivery_detail, the PHP does not reference delivery_detail


The error is (from firebug):

<p>Error Number: 1054</p><p>Unknown column 'delivery_detail.poID' in 'where clause'</p><p>UPDATE `status_order` SET `status` = 'Stock', `stockDate` = 1272214095 WHERE `poID` = '52988'</p> </div>

CI Code:
Code:
//Controller
function convertToStock ($poID=null) {
        $poID=$this->input->post('poID');
        
        if ($poID) {
        $data=$this->inTransit_model->convertStock($poID);
        $this->_printSuccessTrue($data);
        } else {
            $this->_printSuccessFalse();
        }
        
    }
............
//Model
function convertStock($poID) {
        $this->load->helper('date');
        $orderArray = Array (
            'status' => 'Stock'
            ,'stockDate' =>now()
        );
        $this->db->where('poID', $poID);
        $this->db->set($orderArray);
        $this->db->update('status_order');
        
        $tallyArray = Array (
            'tallyStatus' => 'Stock'
            ,'stockDate' =>Now()
        );
        
        $this->db->where('poID', $poID);
        $this->db->set($tallyArray);
        $this->db->update('status_tally');
        
        return true;
        
    }



DB Insert Problem: Unknown Column - Column never referenced in code - El Forum - 04-26-2010

[eluser]mddd[/eluser]
I don't see how another table name could come in there, except if you were creating a db query somewhere else in your code that isn't finished.
The database class will remember parts of queries you are building, until you give a command like get() or update().

So it would go wrong if your controller contained code like this:
Code:
// here you are doing something about a delivery
$this->db->select('id');
$this->db->from('delivery_detail');

// now you are calling convert to stock
$this->convertToStock();   // but the 'from' information is still in the database library!

// and now you are finishing the query about delivery
$this->db->get();



DB Insert Problem: Unknown Column - Column never referenced in code - El Forum - 04-26-2010

[eluser]Tumac[/eluser]
I agree and that is why I am baffled. The other thing too is that the code is initiated by an AJAX call. The AJAX call is calling the right controller and procedure.

And from the error it is building the correct query but throwing in that table designation. In my controllers/models I am not mixing things up. Each table/module has its own controller and model. So this controller/model has no reference to delivery_detail....

All I can think is perhaps MySQL is caching something????? But if that is the case I would think it would cache it for a number of things, not just this piece of code. Maybe I should just call out the table first somehow?


DB Insert Problem: Unknown Column - Column never referenced in code - El Forum - 04-26-2010

[eluser]Tumac[/eluser]
SOLVED


I had a trigger in the database that is making the bad reference.


DB Insert Problem: Unknown Column - Column never referenced in code - El Forum - 04-26-2010

[eluser]mddd[/eluser]
I can't believe there is caching going on between requests. I think the problem must be somewhere in the code of the Controller, Model or maybe in their constructors?

To check, you could write
Code:
$this->db->_reset_select();
$this->db->_reset_write();
at the top of your convertStock method.
These commands will clear CI's internal query building.
So if there was some loose, unfinished query, it should be cleaned up by this.

edit:
You found the problem already. Well done.