How to get all table and column names from database in Yii Framework

Question

I am working on a module where I want to do dynamic dependent dropdown table and column name functionality.

Ex. Fetch all table names and display it in dropdown fields and after selection of particular table I want to display its all column name again in dropdown field.

The issues are:

1) How to fetch all table name from db?

2) and how to fetch all column name from table?

I tried few articles and forums like http://www.yiiframework.com/forum/index.php/topic/5920-how-can-i-get-the-actual-full-table-name/ but its not working.

Any help would be appreciated.

Thanks

Answer 1

It's quite simple, using an instance of the CDbTableSchema class:

echo 'Name: ', $tbl->name, ' (raw: ', $tbl->rawName, ')';
echo 'Fields: ', implode(', ', $tbl->columnNames);

And so on. There's a lot of methods and properties for this
To get all tables, just use the CDbSchema class docs here.

the CDbSchema class has both the public tableNames property (an array of all tbl namnes) and a tables property, containing all meta-data. That's all, really.

To get to all these instances, the following code should suffice:

$connection = Yii::app()->db;//get connection
$dbSchema = $connection->schema;
//or $connection->getSchema();
$tables = $dbSchema->getTables();//returns array of tbl schema's
foreach($tables as $tbl)
{
    echo $tbl->rawName, ':<br/>', implode(', ', $tbl->columnNames), '<br/>';
}

To create a dropdown list, you simply use the standard CHtml object:

$options = array();
foreach($tables as $tbl)
{//for example
    $options[$tbl->rawName] = $tbl->name;
}
$dropDown = CHtml::dropDownList('tables',$tables[0]->rawName, $options);

Please, spend some time Reading the manual, it's all there. I haven't used Yii that extensively, well, I haven't used it at all, to be honest, yet it only took me 5 minutes to work this out. Just look at the source! Each and every method/class/property has a link to the exact line in the corresponding file!
Before asking others to figure something out for you, put in some effort.