| Index
» Projects
» phpMyEdit
» Forum
» How to fill a dropdown box with values from multiple tables |
 |
Sebzap
Joined: 05 Oct 2007
Posts: 5
Posted: 2007-10-05 12:09
Back to top
Reply with quote
|
Hello all,
after searching through the forum and the documentation I can't find a solution for doing a join over multiple tables. The solution posted
here
is not working for the recent version.
Situation: I want to manage the basic function of a library: lend books to people.
The tables I use (with only the important fields to keep it short) are:
tbl_lendings
- id
- book_id
tbl_books
- id
- title
- author_id
tbl_authors
- id
- name
What I want to do is to create a dropdown box in the "add lending" screen where I can select a book. The descritption is supposed to show both the author and the title of the book, e.g. "Simon Singh - The Big Bang".
With the following definition I can show only the book title and the author's ID, but not his name ("1 - The Big Bang").
| Code: |
$opts['tb'] = 'tbl_lendings';
$opts['fdd']['book_id'] = array(
'name' => 'Book',
'select' => 'D',
'options' => 'AVCPD',
'sort' => true
);
$opts['fdd']['book_id']['values']['table']= 'tbl_books';
$opts['fdd']['book_id']['values']['column']= 'ID';
$opts['fdd']['book_id']['values']['description']['columns']['0'] = 'author_id';
$opts['fdd']['book_id']['values']['description']['divs']['0'] = '-';
$opts['fdd']['book_id']['values']['description']['columns']['1'] = 'title';
|
How can I accomplish what I want to do? Is the magic word "virtual fields"? Or is there another solution?
Thanks in advance for any reply!
Regards
Sebastian
|
michal
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2007-10-05 12:22
Back to top
Reply with quote
|
with current pme i see two solutions:
- create a view author_book
- in pme config something like
$opts['fdd']['dummy']'values2'] = array( 'table'=>'tbl_authors', 'column'=>'id', 'join'=>'tbl_book.author_id = tbl_author.id');
or something similar (create dummy field with all authors in, an in join part limit them to the book), i've never tested it.
btw, each book has just one author ? :)
|
Sebzap
Joined: 05 Oct 2007
Posts: 5
Posted: 2007-10-05 12:27
Back to top
Reply with quote
|
Thanks for the quick reply! Actually I'm not managing a library but a more specific application. I thought it's more easy for others to understand the problem if I use a more common example. You are right - the database scheme is not the best... :-)
I will try to get it work and come back to you.
|
Sebzap
Joined: 05 Oct 2007
Posts: 5
Posted: 2007-10-05 13:21
Back to top
Reply with quote
|
Sorry, I do not get any step close... I'm thinking about triggers, the php directive, ... My head is stuck.
May you give me a little bit more code?
What do you mean with "create a view author_books"?
Are triggers or the php directive an option?
|
michal
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2007-10-06 13:28
Back to top
Reply with quote
|
on the sql level, create a view (see man create view) that will include all necessary fields in one table/view.
|
Sebzap
Joined: 05 Oct 2007
Posts: 5
Posted: 2007-10-09 12:16
Back to top
Reply with quote
|
Okay, I thought you mean a view within PME. I created the view on the MySQL level and it works like a charme. Would have preferred to do this within PME but that is good enough. Thanks Michal!
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
