sbuelow
Joined: 10 Jul 2009
Posts: 6
Posted: 2009-07-10 14:45
Back to top
Reply with quote
|
Hello there
I'm using phpMyEdit as frontend for Asterisk Realtime tables.
I can't get a result from the following section:
| Code: |
<$opts['fdd']['mailbox'] = array(
'name' => 'Voicemail',
'select' => 'D',
'maxlen' => 50,
'default' => '',
'js' => array('required' => true,
'regexp' => '/^[0-9]+$/',
'hint' => 'WARNING: Invalid input.'
),
'values' => array('table' => 'voicemail_users',
'column' => 'mailbox',
'description' => array('columns' => array(0 => 'branchid', 1 => 'mailbox',2 => 'email'),
'divs' => array(0 => '-', 1 => ' - ')
),
'orderby' => 'mailbox'
),
'values2' => array("" => "--- Empty ---"),
'sql' => "SUBSTRING_INDEX(PMEtable0.mailbox,'@',1)",
'sqlw' => 'IF($val_qas, CONCAT($val_qas, \'@\', context), NULL)',
'tab' => 'Voicemail',
'sort' => true
); |
This section reads a field from the table sip_buddies of asterisk. To make it meaningful for the end-user I'm trying to fetch the real email from another table, voicemail_users.
To make it worse, the mailbox info is stored as 'mailbox@context' in sip_buddies table, and just 'mailbox' in the other table.
When I started developing of this GUI phpMyEdit 5.6 was the current version, and it worked perfectly.... the main view shows just 'mailbox' in the column, but when I add or modify a record the full pull-down menu shows up, with real value already selected (in the modify situation).
Now I had to upgrade to phpMyEdit 5.7.1 to solve another problem and suddenly this field stopped working.
Now all I can see in the main view is just ' - - ' (the dividers from description). When adding or modifying record the pull-down fills correctly, but the correct value (in modify situation) is not selected anymore.
I just can't get it through...
I've tried everything .... changing the join condition, but came up with nothing...
I've tried to debug the resulting select statement, but I don't know the inner things of phpMyEdit.
These are the SELECT statement created:
BROKEN SELECT WITH PHPMYEDIT 5.7.1
| Quote: |
| SELECT `PMEtable0`.`id` AS `qf0`,`PMEtable0`.`branchid` AS `qf1`,`PMEtable0`.`branchid` AS `qf1_idx`,`PMEtable0`.`name` AS `qf2`,`PMEtable0`.`callerid` AS `qf7`,`PMEtable0`.`context` AS `qf9`,`PMEtable0`.`context` AS `qf9_idx`,CONCAT(IFNULL(CAST(`PMEjoin20`.`branchid` AS CHAR),""),"-",IFNULL(CAST(`PMEjoin20`.`mailbox` AS CHAR),"")," - ",IFNULL(CAST(`PMEjoin20`.`email` AS CHAR),"")) AS `qf20`,SUBSTRING_INDEX(PMEtable0.mailbox,'@',1) AS `qf20_idx`,`PMEtable0`.`language` AS `qf21`,`PMEtable0`.`language` AS `qf21_idx`,`PMEtable0`.`callgroup` AS `qf25`,`PMEtable0`.`callgroup` AS `qf25_idx` FROM `sip_buddies` AS `PMEtable0` LEFT OUTER JOIN `voicemail_users` AS `PMEjoin20` ON (`PMEjoin20`.`mailbox` = `PMEtable0`.`mailbox`) ORDER BY `PMEtable0`.`name` LIMIT 0, 20 |
WORKING SELECT WITH PHPMYEDIT 5.6
| Quote: |
| SELECT PMEtable0.id AS qf0,PMEtable0.branchid AS qf1,PMEtable0.branchid AS qf1_idx,PMEtable0.name AS qf2,PMEtable0.callerid AS qf7,PMEtable0.context AS qf9,PMEtable0.context AS qf9_idx,SUBSTRING_INDEX(PMEtable0.mailbox,'@',1) AS qf20,SUBSTRING_INDEX(PMEtable0.mailbox,'@',1) AS qf20_idx,PMEtable0.language AS qf21,PMEtable0.language AS qf21_idx,PMEtable0.callgroup AS qf25,PMEtable0.callgroup AS qf25_idx FROM sip_buddies AS PMEtable0 LEFT OUTER JOIN asterisk.voicemail_users AS PMEjoin20 ON (PMEjoin20.mailbox = PMEtable0.mailbox) ORDER BY PMEtable0.name LIMIT 0,20 |
Basically I see that in the second select the fields qf20 and qf20_idx are the same, while completely different in the first statement. But I don't really know the meaning or use of *_idx fields the SELECT.
Anybody got an idea ???
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2009-07-11 12:24
Back to top
Reply with quote
|
Probably unrelated to your problems, but you might want to use the CVS version of the class file by viewing/downloading it from http://opensource.platon.sk/cvs/cvs.php/phpMyEdit/
If I were trying to debug this, then I would remove:
| Code: |
| 'values2' => array("" => "--- Empty ---"), |
and change 'values' to 'values2'.
I doubt it is possible to use both 'values' and 'values2' in the same $opts['fdd']['mailbox'] array, but I'm not awake yet.
|
sbuelow
Joined: 10 Jul 2009
Posts: 6
Posted: 2009-07-12 15:05
Back to top
Reply with quote
|
Thanks doug.
I've already tried the CVS version... I'll check with it again.
AFAIK 'values' and 'value2' can coexist peacefully.
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2009-07-12 15:33
Back to top
Reply with quote
|
| sbuelow wrote: |
| AFAIK 'values' and 'value2' can coexist peacefully. |
I haven't the time to experiment with it, but I believe the above statement may only be correct when the values being stored are identical.
| Code: |
$opts['fdd']['mailbox'] = array(
'name' => 'Voicemail',
'select' => 'D',
'maxlen' => 50,
'default' => '',
'js' => array('required' => true,
'regexp' => '/^[0-9]+$/',
'hint' => 'WARNING: Invalid input.'
),
'values2' => array('table' => 'voicemail_users',
'column' => 'mailbox',
'description' => array('columns' => array(0 => 'branchid', 1 => 'mailbox',2 => 'email'),
'divs' => array(0 => '-', 1 => ' - ')
),
'orderby' => 'mailbox'
),
'sql' => "SUBSTRING_INDEX(PMEtable0.mailbox,'@',1)",
'sqlw' => 'IF($val_qas, CONCAT($val_qas, \'@\', context), NULL)',
'tab' => 'Voicemail',
'sort' => true
); |
|
sbuelow
Joined: 10 Jul 2009
Posts: 6
Posted: 2009-07-13 11:36
Back to top
Reply with quote
|
Damn!
With your suggestion now the mail window works, (list of accounts), but when in add/modify template the combo is not fetched the right way. Instead of join result now le options available are 'voicemail_users','mailbox','Array' and 'mailbox' again. It looks like the join is not evaluated with 'values2' option and parameters are used just like values.
On the other side, deactivating just 'values2' field simply removes the '---empty---' value from combo, but the main windows remains broken...
I'm losing my hopes here...
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2009-07-13 12:56
Back to top
Reply with quote
|
If you post the schema for both tables it might be helpful.
|
sbuelow
Joined: 10 Jul 2009
Posts: 6
Posted: 2009-07-13 13:23
Back to top
Reply with quote
|
Sure
Here they are
sip_buddies
| Code: |
+------------------+--------------+------+-----+-------------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+-------------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(80) | NO | MUL | | |
| accountcode | varchar(20) | YES | | NULL | |
| amaflags | varchar(13) | YES | | NULL | |
| callgroup | varchar(10) | YES | | NULL | |
| callerid | varchar(80) | YES | | NULL | |
| canreinvite | char(3) | YES | | yes | |
| context | varchar(80) | YES | | NULL | |
| defaultip | varchar(15) | YES | | NULL | |
| dtmfmode | varchar(7) | YES | | NULL | |
| fromuser | varchar(80) | YES | | NULL | |
| fromdomain | varchar(80) | YES | | NULL | |
| fullcontact | varchar(80) | YES | | NULL | |
| host | varchar(31) | NO | | | |
| insecure | varchar(4) | YES | | NULL | |
| language | char(2) | YES | | NULL | |
| mailbox | varchar(50) | YES | | NULL | |
| md5secret | varchar(80) | YES | | NULL | |
| nat | varchar(5) | NO | | no | |
| deny | varchar(95) | YES | | NULL | |
| permit | varchar(95) | YES | | NULL | |
| mask | varchar(95) | YES | | NULL | |
| pickupgroup | varchar(10) | YES | | NULL | |
| port | varchar(5) | NO | | | |
| qualify | varchar(5) | YES | | NULL | |
| restrictcid | char(1) | YES | | NULL | |
| rtptimeout | char(3) | YES | | NULL | |
| rtpholdtimeout | char(3) | YES | | NULL | |
| secret | varchar(80) | YES | | NULL | |
| type | varchar(6) | NO | | friend | |
| username | varchar(80) | NO | | | |
| disallow | varchar(100) | YES | | all | |
| allow | varchar(100) | YES | | g729;ilbc;gsm;ulaw;alaw | |
| musiconhold | varchar(100) | YES | | NULL | |
| regseconds | int(11) | NO | | 0 | |
| ipaddr | varchar(15) | NO | | | |
| regexten | varchar(80) | NO | | | |
| cancallforward | char(3) | YES | | yes | |
| setvar | varchar(100) | NO | | | |
| call-limit | tinyint(4) | YES | | 100 | |
| maint-prune | tinyint(4) | NO | | 0 | |
| regserver | varchar(100) | YES | | NULL | |
| defaultuser | varchar(80) | NO | | | |
| subscribecontext | varchar(80) | YES | | NULL | |
| branchid | varchar(10) | NO | MUL | | |
+------------------+--------------+------+-----+-------------------------+----------------+
|
voicemail_users
| Code: |
+----------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+-------------------+----------------+
| uniqueid | int(11) | NO | PRI | NULL | auto_increment |
| branchid | varchar(10) | NO | | | |
| customer_id | varchar(11) | NO | | 0 | |
| context | varchar(50) | NO | | | |
| mailbox | varchar(11) | NO | MUL | | |
| password | varchar(5) | NO | | 0 | |
| fullname | varchar(150) | NO | | | |
| email | varchar(50) | NO | | | |
| pager | varchar(50) | NO | | | |
| tz | varchar(10) | NO | | central | |
| attach | varchar(4) | NO | | yes | |
| saycid | varchar(4) | NO | | yes | |
| dialout | varchar(10) | NO | | | |
| callback | varchar(10) | NO | | | |
| review | varchar(4) | NO | | no | |
| operator | varchar(4) | NO | | no | |
| envelope | varchar(4) | NO | | no | |
| sayduration | varchar(4) | NO | | no | |
| saydurationm | tinyint(4) | NO | | 1 | |
| sendvoicemail | varchar(4) | NO | | no | |
| delete | varchar(4) | NO | | no | |
| nextaftercmd | varchar(4) | NO | | yes | |
| forcename | varchar(4) | NO | | no | |
| forcegreetings | varchar(4) | NO | | no | |
| hidefromdir | varchar(4) | NO | | yes | |
| stamp | timestamp | YES | | CURRENT_TIMESTAMP | |
| acdenable | tinyint(1) | NO | | 0 | |
| acdlist | varchar(255) | YES | | NULL | |
| acdforward | varchar(255) | YES | | NULL | |
| agent | tinyint(4) | NO | | 0 | |
+----------------+--------------+------+-----+-------------------+----------------+ |
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2009-07-13 14:00
Back to top
Reply with quote
|
There may be many issues. One that is obvious is that 'column' => 'mailbox' has to contain the unique key in the lookup table: 'column' => 'uniqueid' (not mailbox)
Since a `mailbox` column exists in both tables, then there may need to also be an explicit join ... which I seem to always struggle with since I do not often need them in my work. You might use the forum's Search feature to search for JOIN.
|
sbuelow
Joined: 10 Jul 2009
Posts: 6
Posted: 2009-07-13 14:48
Back to top
Reply with quote
|
Using 'column' => 'uniqueid' makes no sense in asterisk environment. I need to write voicemail_users.mailbox value (with addition of '@context' at the end) into sip_buddies.mailbox field, not the unique id of the row. There's no implicit relation in asterisk between the two tables. Anyway there are indexes defined in the tables that prevent messing up with double entries.
As for the explicit use of mailbox field, I've tried with it, but I can't get a syntax working in every situation.
If I use PMEtable0.mailbox it works in the main table but breaks in the add/modify dropdown combo, as it uses a PMEAlias3 alias in SELECT.
Right now I'm falling back to phpmyedit v5.6 where everything works as expected (as least for me :) ). But I still need v5.7.1 for another form, so I had to setup a double environment (dirty but works).
|
|
Post new topic
Reply to topic
|
|
Login
Registration
Slovak
