• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Update and Return

#1
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
Reply

#2
//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
Reply

#3
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.
Reply

#4
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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.