Update and Return - Printable Version

Update and Return - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Update and Return (/showthread.php?tid=1220)

Update and Return - goodkarma - 02-19-2015

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

RE: Update and Return - dbui - 02-19-2015

//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

RE: Update and Return - Wouter60 - 02-20-2015

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.

RE: Update and Return - Nichiren - 02-23-2015

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.