Platon Technologies
neprihlásený Prihlásiť Registrácia
SlovakEnglish
open source software development oslavujeme 10 rokov vývoja otvoreného softvéru! Sobota, 8. február 2025
O nás
Magazín
Otvorený softvér
CVS
Služby
Index  »  Projekty  »  phpMyEdit  »  Fórum  »  Getting values description from a third table?

phpMyEdit Configuration     Getting values description from a third table?
Odoslať novú tému   Odpovedať na tému   Choď na stránku 1, 2, 3 ... 17, 18, 19  Ďalší  
 
Pixed     Založený: 28.01.2011   Príspevky: 7  
Príspevok 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)
Príspevok 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  
Príspevok Zaslal: 2011-02-24 09:28
Návrat hore  Odpovedať s citátom     

doug Napísal:
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


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)
Príspevok 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  
Príspevok 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)
Príspevok 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  
Príspevok 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  
Príspevok 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  
Príspevok 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  
Príspevok 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&#8217;ve got what it takes to get attention. Máy s&#7845;y nông s&#7843;n

 
cibogi     Založený: 12.06.2022   Príspevky: 1672  
Príspevok Zaslal: 2023-05-17 08:29
Návrat hore  Odpovedať s citátom     

Thank you very much for this great post. &#50952;&#51312;&#51060; &#47672;&#45768;&#49345;

 
cibogi     Založený: 12.06.2022   Príspevky: 1672  
Príspevok 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  
Príspevok 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  
Príspevok 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  
Príspevok 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ší  

Copyright © 2002-2006 Platon Group
Stránka používa redakčný systém Metafox
Na začiatok · Odkazový formulár · Prihláška
Upozorniť na chybu na PLATON.SK webstránke · Podmienky použitia · Ochrana osobných údajov