CodeIgniter Forums

Full Version: Update and Return
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Is there a way to update a column in a table and return the new value for use in the php program? In other words, combining the two statements below:

UPDATE TABLE
SET SEQ = SEQ+1
WHERE FIELD = 'NEXTINV '


SELECT SEQ
FROM TABLE
//Assume you have done with the update table
//From the second query, put extra where to it, i.e
$sql = "SELECT SEQ FROM TABLE WHERE FIELD='NEXTINV'";
$query = $this->db->query($sql);
$row = $query->row_array();
$seq = $row['seq']; // here it is
Shorter way:
Code:
$seq = $this->db->query("SELECT seq FROM table WHERE field='NEXTINV'")->row()->seq;
Make sure that field='NEXTINV' refers to a unique record. Best way is to refer to the ID column.
If I'm understanding your question correctly, no, you wouldn't be able to combine the two since MySQL's UPDATE statement only ever returns the number of rows that were actually changed and not the resulting value.

I haven't tried this myself but perhaps you can wrap these statements in a stored procedure in MySQL so at least both statements run on the database instead of making two separate PHP calls but if the goal of your question is to eke out more performance, there are probably better ways of doing that.