Detect MySQL InnoDB via PHP

Question

How can I detect if a MySQL database table is using the InnoDB storage engine from within a PHP program?

The reason - the PHP program needs to utilize database transactions. InnoDB supports transactions. MyISAM (the MySQL default storage engine) does not. The program needs to fail if transactions are not supported.

Answer 1

Try this:

SHOW TABLE STATUS WHERE Name = 'xxx'

This will give you (among other things) an Engine column, which is what you want.

or if u want it at database level

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbname'

Answer 2

I would prefer:

SHOW CREATE TABLE tablename\G

Over:

SHOW TABLE STATUS WHERE Name = 'xxx'

Because you can easily specify tables outside of the current database, i.e.:

SHOW CREATE TABLE database.table\G

The \G formats the output nicely, but it is optional.