Is it possible to use value of first MySQL select as condition in second when autocommit is off?

Question

First of all, I'm still Learning how to work with PHP/MySQL... so is it possible at all to use transactions when selecting or only when inserting/updating?

If it is possible, then how would I solve this (pseudo-code):

Turn off autocommit

Select id from auth where alias = [user input]

Select active from flags where id = ID FROM ABOVE SELECT

If: no errors Commit

Else: Rollback

Fetch row from second select

---

Is the above possible to do with MySQL (PHP syntax, please)?

Answer 1

Using transactions would be rather useless here, since your query only deals with a SELECT.

There are 2 choices here. (Do note that I've only included the SQL-code.)

1. You can use a SELECT inside a SELECT statement.

SELECT active
  FROM flags
 WHERE id IN (
               SELECT id
                 FROM auth
                WHERE alias = 'userinput'
             );

2. Other possibility is to use a JOIN.

    SELECT f.active
      FROM flags f
INNER JOIN auth a
        ON f.id = a.id
     WHERE a.alias = 'userinput';

Here, aliases like f and a are used to designate the tables.