Pixed
Založený: 28.01.2011
Príspevky: 7
Zaslal: 2011-02-22 10:18
Návrat hore
Odpovedať s citátom
|
Hey all,
I can't figure out how I retrieve descriptions from another table than the actual values table.
Here are the current config:
$opts['tb'] = 'assigned_tasks';
$opts['fdd']['task_id'] = array(
'name' => 'Task',
'select' => 'D',
'maxlen' => 11,
'values' => array(
'table'=>'task',
'column'=>'id',
'description'=>'???',
),
'default' => '0',
'sort' => true
);
I have another table that contain task types (with actual name and description).
so three tables in all:
- assigned_tasks (what the config is for)
- tasks (actual scheduled tasks, with tasktype_id)
- tasktypes (the type of task)
I need to assign the tasktype.title to 'description' via a stored id in task table: task.tasktype_id
How do I get around that?
|
doug
Založený: 10.02.2003
Príspevky: 1013
Bydlisko: Denver, Colorado (USA)
Zaslal: 2011-02-23 16:25
Návrat hore
Odpovedať s citátom
|
To locate some examples, use the Search feature to look for JOIN and $main_table
http://opensource.platon.sk/projects/doc.php/phpMyEdit/html/configuration.input-restrictions.html#AEN1011
|
Pixed
Založený: 28.01.2011
Príspevky: 7
Zaslal: 2011-02-24 09:28
Návrat hore
Odpovedať s citátom
|
I tried hard to make that JOIN stuff work, but either I get no changed result or I get field errors.
I already looked at several similar questions on the forum, but all I can find is solutions with temporary VIEWS (in the database), which is not ideal here - I believe it must be possible to solve using the JOIN option somehow, I just can't figure it out.
Here is a simplified table layout:
assigned_tasks (table)
assigned_tasks.id
assigned_tasks.task_id (refers to table task)
task (table)
task.id
task.tasktype_id (refers to table tasktypes)
tasktypes (table)
tasktypes.id
tasktypes.name
(merging task and tasktype tables is not an option in the real full design)
Now looking at the config for assigned_tasks again:
$opts['fdd']['task_id'] = array(
'name' => 'Task',
'select' => 'D',
'values' => array(
'table'=>'task', // <- table task
'column'=>'id', // <- task.id
'description'=>???, // <- here I need to get tasktype.name from task.tasktype_id
'join'=> ???
),
);
It possibly involves a join, but how should that be constructed?
|
doug
Založený: 10.02.2003
Príspevky: 1013
Bydlisko: Denver, Colorado (USA)
Zaslal: 2011-02-26 17:24
Návrat hore
Odpovedať s citátom
|
The join syntax can be very tricky. Here is a working example.
Kód: |
// 2011-03-02
/*
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;
*/
$main_table = 'domain';
$main_column = 'owner_id';
/*
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;
*/
$join_table = 'contacts';
$join_column = 'contact_id';
$join_description_1 = 'last_name';
$join_description_2 = 'first_name';
$divs_1 = ', ';
$divs_2 = '';
$opts['fdd'][$main_column] = array(
'default' => '0',
'input' => '',
'maxlen' => 5,
'name' => 'Owner Id',
'options' => 'ACPVDFL',
'required' => false,
'select' => '',
'size|ACP' => 5,
'sqlw' => 'TRIM("$val_as")',
'sort' => true,
'values' => array(
'join' => '$main_table.$main_column = $join_table.$join_column', // single quotes, not ""
'table' => $join_table,
'column' => $join_column,
'description' => array(
// last name, first name
'columns' => array($join_description_1, $join_description_2),
// optional dividers displayed in List mode
'divs' => array($divs_1, $divs_2)
),
// Add/Change drop down filter
'filters' => $join_description_1.' > "" AND '.$join_description_2.' > ""',
// comma separated string for ORDER BY clause in Add/Change drop down filter
'orderby' => $join_description_1.','.$join_description_2
)
);
|
Naposledy upravil doug dňa 2011-03-02 17:29, celkom upravené 1 krát
|
Pixed
Založený: 28.01.2011
Príspevky: 7
Zaslal: 2011-02-26 19:40
Návrat hore
Odpovedať s citátom
|
Thanks doug - I did in fact try out that example code earlier - but its not really related to this problem I think..
In the example you posted there are only two tables in play - the main table and the join table.
I need to resolve an ID representing a third table (tasktypes). Please take a short look at my second post to see what I mean..
|
doug
Založený: 10.02.2003
Príspevky: 1013
Bydlisko: Denver, Colorado (USA)
Zaslal: 2011-02-27 18:47
Návrat hore
Odpovedať s citátom
|
I generally set up tables to avoid having to use JOIN, and then struggle a bit with joining two tables when required.
The below link relates to joining three tables, but given a glance I can't tell if there was success, or not. Note the article is rather old and may (or may not) require modifying the class file.
http://opensource.platon.sk/forum/projects/viewtopic.php?t=103
http://opensource.platon.sk/forum/projects/viewtopic.php?t=1046&highlight=join+virtual
Naposledy upravil doug dňa 2011-02-27 18:56, celkom upravené 1 krát
|
Fastjack
Založený: 19.03.2011
Príspevky: 0
Zaslal: 2011-03-19 02:07
Návrat hore
Odpovedať s citátom
|
Hi Pixed,
Have you ever been able to join three tables using phpMyEdit? I've been having a wonderful time using PME so far, but I'm now stuck with the following left JOINS to work with PME (3 tables):
Kód: |
SELECT medicalhx.medicalhxid,
practitioner.practitionerfirstname,
practitioner.practitionerlastname,
practitionertype.practitionertypename
FROM medicalhx
LEFT JOIN practitioner
ON medicalhx.medicalhxpractitionerid = practitioner.practitionerid
LEFT JOIN practitionertype
ON practitioner.practitionertypeid = practitionertype.practitionertypeid |
Basically, a
medical history
has a
practitioner
, which has a
type
:
Kód: |
medical history
|
+--> practitioner
|
+--> type
|
Results of the query looks like this:
Kód: |
+-------------+-----------------------+----------------------+----------------------+
| medicalhxid | practitionerfirstname | practitionerlastname | practitionertypename |
+-------------+-----------------------+----------------------+----------------------+
| 1 | John | Smith | Medical doctor |
| 447 | John | Smith | Medical doctor |
| 448 | John | Smith | Medical doctor |
| 449 | Guillaume | Jacques | Registered nurse |
| 450 | Johnny | Cain | Nurse practitioner |
+-------------+-----------------------+----------------------+----------------------+
|
Using two tables is easy (since I wouldn't use a join), but cannot find a way with three tables.
Any help, guidance, or examples will be gladly scrutinized. Meanwhile, I will keep looking.
Thanks in advance,
Fastjack
P.S. Here is structure of tables:
medicalhx
Kód: |
+-------------------------+------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------------+------+-----+-------------------+----------------+
| medicalhxid | int(11) | NO | PRI | NULL | auto_increment |
| medicalhxtimestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| medicalhxptid | int(11) | NO | | NULL | |
| medicalhxpractitionerid | int(11) | NO | | NULL | |
| medicalhxnotes | text | NO | | NULL | |
| medicalhxerrorid | tinyint(1) | NO | | 0 | |
| medicalhxerrortimestamp | timestamp | YES | | NULL | |
+-------------------------+------------+------+-----+-------------------+----------------+
|
practitioner
Kód: |
+-----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+----------------+
| practitionerid | int(11) | NO | PRI | NULL | auto_increment |
| practitionerfirstname | varchar(50) | YES | | NULL | |
| practitionerlastname | varchar(50) | YES | | NULL | |
| practitionertypeid | tinyint(4) | YES | MUL | NULL | |
+-----------------------+-------------+------+-----+---------+----------------+
|
practitionertype
Kód: |
+----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+----------------+
| practitionertypeid | tinyint(4) | NO | PRI | NULL | auto_increment |
| practitionertypename | varchar(50) | NO | | NULL | |
+----------------------+-------------+------+-----+---------+----------------+
|
Pixed Napísal: |
Thanks doug - I did in fact try out that example code earlier - but its not really related to this problem I think..
In the example you posted there are only two tables in play - the main table and the join table.
I need to resolve an ID representing a third table (tasktypes). Please take a short look at my second post to see what I mean.. |
[/code]
|
AnnetteBeshears
Založený: 14.02.2022
Príspevky: 2594
Zaslal: 2023-04-13 10:19
Návrat hore
Odpovedať s citátom
|
As the legalization of marijuana continues to sweep the nation, more and more people are exploring the world of THC vaping. With this growing interest comes a growing number of online THC vape stores, all vying for the attention of consumers. However, not all stores are created equal. Here, we explore the benefits of shopping at a high-quality THC vape store.
Buy THC vape carts Australia
|
AnnetteBeshears
Založený: 14.02.2022
Príspevky: 2594
Zaslal: 2023-04-16 09:23
Návrat hore
Odpovedať s citátom
|
Excellent and very exciting site. Love to watch. Keep Rocking.
www.ufa88s.info
|
ghori92
Založený: 17.01.2023
Príspevky: 290
Zaslal: 2023-05-10 14:21
Návrat hore
Odpovedať s citátom
|
This article is an appealing wealth of informative data that is interesting and well-written. I commend your hard work on this and thank you for this information. You’ve got what it takes to get attention.
Máy sấy nông sản
|
cibogi
Založený: 12.06.2022
Príspevky: 1672
Zaslal: 2023-05-17 08:29
Návrat hore
Odpovedať s citátom
|
Thank you very much for this great post.
윈조이 머니상
|
cibogi
Založený: 12.06.2022
Príspevky: 1672
Zaslal: 2023-05-27 12:00
Návrat hore
Odpovedať s citátom
|
Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work.
World Smm Panel
|
AnnetteBeshears
Založený: 14.02.2022
Príspevky: 2594
Zaslal: 2023-05-28 06:59
Návrat hore
Odpovedať s citátom
|
Cool stuff you have got and you keep update all of us.
more
|
cibogi
Založený: 12.06.2022
Príspevky: 1672
Zaslal: 2023-05-31 11:25
Návrat hore
Odpovedať s citátom
|
You have a real talent for writing unique content. I like how you think and the way you express your views in this article. I am impressed by your writing style a lot. Thanks for making my experience more beautiful.
Top Smm Panel
|
zeldawe
Založený: 17.02.2023
Príspevky: 5
Zaslal: 2023-06-05 03:30
Návrat hore
Odpovedať s citátom
|
Depending on your specific
rankdle
network setup, you may choose to use either a wired or wireless connection.
|
Odoslať novú tému
Odpovedať na tému
Choď na stránku 1, 2, 3 ... 17, 18, 19 Ďalší
|