| Index
» Projects
» phpMyEdit
» Forum
» Lookup from another table |
 |
Leon
Joined: 19 Jan 2012
Posts: 7
Posted: 2012-01-19 19:33
Back to top
Reply with quote
|
I need to restrict listed records on a form to the values in anther table. I am brand new to phpMyEdit and just cannot solve this. Thanks however for a wonderfull product. It is extremely easy to understand even for a novice like myself and just what I have been looking for for the last 2 years! I need example code to do the following:
List the records from table "Filing" where the field "Co" in table "Filing" = the field "Co" in table "Companies" and field "User" in table Companies = "somebody".
All my users do not have access to all the companies so the idea is that I lookup the companies that a user may access in the table "Companies".
Please help!
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2012-01-20 15:28
Back to top
Reply with quote
|
Filters:
http://opensource.platon.sk/projects/doc.php/phpMyEdit/html/configuration.filters.html
Lookups:
http://opensource.platon.sk/projects/doc.php/phpMyEdit/html/configuration.input-restrictions.html#AEN958
See filter and lookup examples at
http://www.php-form-generator.com/phpmyedit/cheat-sheet/
Ideally, when users log into your system a session variable is set which somehow defines the user privileges. And then such variable is later used with a filter in phpMyEdit.
|
Leon
Joined: 19 Jan 2012
Posts: 7
Posted: 2012-01-23 11:27
Back to top
Reply with quote
|
Hi Doug.
Thanks for the prompt reply. I have however now spent the last 4 days trying to make this work from the links you posted and just cannot get it to work. Could you please write a working sample for me or alternatively advise who I can contact to write a piece of code for me. I am prepared to pay for this.
I have tried;
$opts['fdd']['Co']['values']['join'] =
'Filing.Co = Maatskappye.Co AND Maatskappye.User = "Willem" ';
I hope that from this you will see what I am trying to achieve.
I URGENTLY need to solve this problem as I cannot continue without it.
Regards,
Leon
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2012-01-24 19:21
Back to top
Reply with quote
|
You might find it useful to create the desired query using phpMyAdmin and then save that query as a VIEW. Then create a phpMyEdit script which reads the VIEW. The saved VIEW is not editable but will work for List mode with the phpMyEdit script.
It would otherwise be helpful if you posted the schema for both tables, as output using phpMyAdmin.
The following is a working JOIN example from one of my scripts. Though it sounds like a filter is really what is necessary, yet I can't locate a filter example which relies on a second table.
| Code: |
/*
CREATE TABLE IF NOT EXISTS `domain` (
`domain_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`domain` varchar(75) NOT NULL DEFAULT '',
`parked_on` varchar(255) NOT NULL DEFAULT '',
`host_name` varchar(50) NOT NULL DEFAULT '',
`owner_id` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`domain_id`),
KEY `domain` (`domain`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `contacts` (
`contact_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`last_name` varchar(20) NOT NULL DEFAULT '',
`first_name` varchar(20) NOT NULL DEFAULT '',
`email1` varchar(50) NOT NULL DEFAULT '',
`email2` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`contact_id`),
KEY `last_name` (`last_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
*/
$main_table = 'domain';
$main_column = 'owner_id';
$join_table = 'contacts';
$join_column = 'contact_id';
$join_description_1 = 'last_name';
$join_description_2 = 'first_name';
$opts['fdd'][$main_column] = array(
'default' => '',
'input' => '',
'name' => 'Owner ID',
'options' => 'ACPVDFL',
'required' => true,
'select' => 'D',
'size|ACP' => 5,
'sort' => true,
'sqlw' => 'TRIM("$val_as")',
'values' => array(
'join' => '$main_table.$main_column = $join_table.$join_column', // single quotes, not ""
'table' => $join_table,
'column' => $join_column,
'description' => array(
'columns' => array($join_description_1, $join_description_2), // last name, first name
'divs' => array(' ') // optional divider/separator
),
'filters' => $join_description_1.' <> "" AND '.$join_description_2.' <> ""', // Add/Change drop down restriction
'orderby' => $join_description_1.','.$join_description_2 // ORDER BY clause in Add/Change drop down
)
);
|
|
Leon
Joined: 19 Jan 2012
Posts: 7
Posted: 2012-01-26 21:53
Back to top
Reply with quote
|
Hi Doug,
Thanks again for your speedy replies. I have tried your code, but you are unfortunately correct in that I will need a filter from a second table. I am going to keep trying as I love everything else about this product.
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2012-01-26 23:54
Back to top
Reply with quote
|
Perhaps (???) you can re-visit the schema for the main table, add a field or two, and populate the new field(s) through a query executed using phpMyAdmin. So that you can apply a normal phpMyEdit filter to the main table.
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
