Combine two different select and update statements

Question

I have one select query and one update query and I want to combine both of them.

The select query is like:

select questionDesc from myTable
where 
  questionId >= (
    select currentQuestionid from userTable 
    where userId='1'
  )
  and questionId <= (
    select currentQuestionid+4 from userTable 
    where userId='1'
  )

For user=1, this query tries to fetch all the question Descriptions from myTable whose questionId lies between currentQuestionid and currentQuestionid+4 (currentQuestionid is a column specific to a user in the userTable). I will later use this result in my front-end.

Now, I want to update the currentQuesionid to currentQuestionid+5. This could be achieved using:

UPDATE userTable SET currentQuesionid = currentQuestionid+5 WHERE userId ='1'

I want to achieve both these queries in one database hit so as to improve the performance.

Is there any way to combine the two. I am using WAMP and the code is written in php scripts.

Any help is appreciated.

Answer 1

I think I have found the answer.

For combining multiple queries together we can use mysqli_multi_query() function. It is available for MySQL server versions 4.1.3 and newer. It takes multiple queries as input parameter and performs them in one db hit.

for example:

//sql queries should be separated by semi-colons

$sql = "SELECT Lastname FROM Persons ORDER BY LastName;";
$sql .= "SELECT Country FROM Customers";

// Execute multi query
if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch row one and one
      while ($row=mysqli_fetch_row($result))
        {
        printf("%s\n",$row[0]);
        }

      // Free result set
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($con));
}

Source: http://www.w3schools.com/php/func_mysqli_multi_query.asp