How to use field type "sql" for list all db tables in extension manifest

[alikon]

hi,

in an extension manifest is possible using the field type SQL  to get the list of db tables ?

i'm testing something like this

<field name="tables" type="sql" default="1" label="Select a table" query="SHOW TABLES" key_field="1" value_field=""/>  

but i'm not sure  how to fill key_field and value field

so switched to this one

<field name="tables" type="sql" default="1" label="Select a table" query="SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'mydb' key_field="table_name" value_field="table_name"/>  

 but with this i've a trouble for the db name  how i can not harcode the dbname ie using something like:

$config =& JFactory::getApplication(); 

$name = $config->getCfg('db');

can someone share some thoughts

[piotr_cz]

The type="sql" has some quirks and limitations. It's supposed to work
with MySQL (probably won't with SQLazure that's shipped with Joomla
2.5 and 5 others that are shipped with Joomla 3.0)

Maybe better solution is to create own JFormFieldDbtables that will be
included with extension and do exactly what you want, how you want it.

[piotr_cz]

By the way,
JDatabase has a method getTableList (Method to get an array of all
tables in the database, http://docs.joomla.org/JDatabase::getTableList/11.1)
that is driver-agnostic (should work across all database drivers).

So you should be fine with using:

$db = JFactory::getDbo();
$tables = $db->getTableList();

In JFormFieldDbtables that I suggested previously. You don't need to
access database details then or hardcode database name. If you don't
know how to create such form field, inspect how JFormFieldPlugins
works (/libraries/joomla/form/fields/plugins.php). It should be as
easy as

protected function getOptions()
{
// Get configured database tables
$db = JFactory::getDbo();
$tables = $db->getTableList();

// Merge with other options that might be available in the xml file
(like none, keep default)
$options = array_merge(parent::getOptions(), $tables);

return $options;
}


in extension xml file then use path to where JFormFieldDbtables is
placed:
<fieldset label="dbstuff" addfieldpath="/administrator/components/
com_hello/models/fields">
<field name="dbtables" type="dbtables" label="Database tables" />
</fieldset>


This might not be without errors (didn't test).

[alikon]

i was completely missed to think about db drivers portabilty

fantastic the form field  override

double*1000  thx

[Sam Moffatt]

Presuming your SQL is generic enough, the SQL field should work fine:
https://github.com/joomla/joomla-platform/blob/master/libraries/joomla/form/fields/sql.php#L53

As you can see, the field itself is quite simple. Nothing to limit it
to MySQL. If there is a need for a more complicated format to support
alternative SQL for alternative drivers then create a patch.

Though in this case I agree creating a new class just for getting to
the table list method is a better idea. I suggest copying the SQL one
and removing the key, value and query options then using the
getTableList method.

Cheers,

Sam Moffatt
http://pasamio.id.au


On Sat, Nov 17, 2012 at 2:58 AM, alikon <in...@alikonweb.it> wrote:
> sorry again for help with this one after saving no selection was save
> what i'm still missing ?
>
> protected function getInput() {
> // Initialize variables.
> $options = array();
>
> // Get database tables

> $db = JFactory::getDbo();
> $tables = $db->getTableList();
>

> $options[] = JHtml::_('select.option', '*', '- None Selected -');
> foreach ($tables as $table) {
> $options[] = JHtml::_('select.option', $table, $table);
>
> }
> return JHtml::_('select.genericlist', $options, 'dbtables', ' ',
> 'value', 'text', '*');
> }
>
> --
> You received this message because you are subscribed to the Google Groups
> "Joomla! General Development" group.
> To view this discussion on the web, visit
> https://groups.google.com/d/msg/joomla-dev-general/-/OZsNwTRVj1cJ.
>
> To post to this group, send an email to joomla-de...@googlegroups.com.
> To unsubscribe from this group, send email to
> joomla-dev-gene...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/joomla-dev-general?hl=en-GB.