CODEWITHSUNDEEP
mysqlsqlselectsql-update
pepe
pepe

Reputation: 9909

Is there a way of combining these 2 MySQL statements?

I would like to run a query that adds 1 to the qa_votes field, and then retrieves the resulting value of that field so I can show it on the front end.

So the code draft would be 

    $this->db->query("
    UPDATE  qa
    SET     qa_votes = qa_votes +1
    WHERE   qa_id = $question_id;
    ");

followed by

    $query = $this->db->query("
    SELECT  qa_id, qa_votes
    FROM    qa
    WHERE   qa_id = $question_id;
    ");

Is there a way of combining these 2 into a single query?

Upvotes: 2

Views: 91

Answers (3)

sagi
sagi

Reputation: 5737

Yes. You can use LAST_INSERT_ID

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID()

So you can do something like:

$this->db->query("
UPDATE  qa
SET     qa_votes = LAST_INSERT_ID(qa_votes +1)
WHERE   qa_id = $question_id;
");

Followed by

$query = $this->db->query("SELECT LAST_INSERT_ID()");

The MySQL API that you use may have a shortcut for retrieving the last (e.g. mysql_insert_id() in PHP).

Upvotes: 1

MatBailie
MatBailie

Reputation: 86735

Just combine them both into a single SQL string?

 $query = $this->db->query("
    UPDATE  qa
    SET     qa_votes = qa_votes +1
    WHERE   qa_id = $question_id;

    SELECT  qa_id, qa_votes
    FROM    qa
    WHERE   qa_id = $question_id;
    ");

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Short answer: No.

Long Answer: Not with an out-of-the-box command. UPDATE command does not provide that. You could, however, write a Stored Procedure that updates and then brings back the result.

Upvotes: 7

Related Questions