Platon Technologies
neprihlásený Prihlásiť Registrácia
SlovakEnglish
open source software development oslavujeme 10 rokov vývoja otvoreného softvéru! Štvrtok, 13. november 2025
O nás
Magazín
Otvorený softvér
CVS
Služby
Index  »  Projekty  »  phpMyEdit  »  Fórum  »  Possible to show attributes of a foreign table
Hlavné | Zobraziť chyby | Pridať chybu | Zhrnutie | Fórum | Dokumenty | Download

phpMyEdit General     Possible to show attributes of a foreign table
Odoslať novú tému   Odpovedať na tému   Choď na stránku Predchádzajúci  1, 2  
 
willirl     Založený: 04.04.2004   Príspevky: 14  
Príspevok Zaslal: 2004-04-09 21:07
Návrat hore  Odpovedať s citátom     

Apologies. I like this product and would really like to use it (to save me lots of time) and it has been difficult to get an answer so far.

I'm not sure how much detail you want when you ask for "schema". Do you want all the fields of both tables (even though they do not relate to the problem?)

Here is the problem statement again:

Citácia:

Two tables. Columns of interest:

cost.id

items.id
items.name

When showing the cost table I also want to the items.name in a new column. (id is the related field)


Is this sufficient information? I have already run the phpMyEditSetup on the cost table and everything there works fine. I want to show another column on the display of the name field from the items table.

I'm not sure what you mean by "join" field. The two tables are related by the id field. I.e. when showing the row with id = 101 in of the cost table, I also want to show an addition column on the display of the name column of the items table whose id = 101.

If this isn't enough information can you be specific about what information you need?

Thanks,
Richard

 
ajh     Založený: 17.12.2003   Príspevky: 236  
Príspevok Zaslal: 2004-04-10 11:14
Návrat hore  Odpovedať s citátom     

I think that the bottom line is that PME can't do what you need without some customisation. I've tried a number of ways of doing it and I can't find a way of doing it. The key issue is that PME is really only designed to handle single tables at present. It can use additional tables to perform lookups for field entry, but that's about it.

It isn't clear exactly what you're trying to achieve as you haven't given enough information. If you have one 'item' record for every 'cost' record then you should probably combine the two tables and then this wouldn't be an issue.

However, I'm assuming that what you actually have is multiple cost records per items record (like stock control batches etc) - in which case I think I get what you're trying to do. Not that this helps.

If I get a chance I'll look to see if there's a way of modding PME to do it easily.

 
ajh     Založený: 17.12.2003   Príspevky: 236  
Príspevok Zaslal: 2004-04-10 12:42
Návrat hore  Odpovedať s citátom     

OK - think I've done it. I've posted it as an extension on a bug report. Basically I've added in the capability to link any field to a foreign table. This makes the columns from the foreign table available in an 'sql' option for a virtual field (which I think is still an undeclared feature).

If all of this sounds like gobbledey gook then I suggest that you do a bit of reading up about SQL databases - concepts like joins and foreign keys/tables are pretty fundamental.

Code and details are in this bug report:
http://opensource.platon.sk/projects/bug_view_advanced_page.php?f_bug_id=218
The example I've used should be pretty much usable as is for your application.

Note - this has been coded against the latest CVS version of PME rather than 5.4, so download that from the CVS (left hand nav, choose CVS / Browser).

To use the extension, you need to download the attached file into a sub-directory of your PME directory on your web server called 'extensions'. Then you need to replace the following lines in your calling script:
Kód:
require_once 'phpMyEdit.class.php';
new phpMyEdit($opts);


with:
Kód:
require_once 'extensions/phpMyEdit-foreign.class.php';
new phpMyEdit_foreign($opts);


 
willirl     Založený: 04.04.2004   Príspevky: 14  
Príspevok Zaslal: 2004-04-11 15:25
Návrat hore  Odpovedať s citátom     

Thanks for your efforts.

I created the extensions folder and installed the phpMyEdit-foreign.class.php file there.

I edited my cost.php to require your new file instead of phpMyEdit.class.php.

I modified the 'id' field def to add the new 'foreign' like this:

Kód:

   'foreign' => array(
      'table' => 'items',
      'column' => 'id')


I verified that the table is named "items" and the field is named "id" in my database.

I downloaded the latest (1.130 rev) from CVS phpMyEdit directory replacing my existing file.

I added this new column def:


Kód:

$opts['fdd']['name'] = array(
'name' => 'Name',
'select' => 'T',
'input' => 'V',
'maxlen' => 40,
'sort' => true,
'sql' => 'PMEjoin1.name'
);


Did not work. Got the following error:

Citácia:

MySQL error 1109

Unknown table 'PMEjoin1' in field list
phpMyEdit error: invalid SQL query

SELECT PMEtable0.id AS qf0,PMEtable0.vendor AS qf1,PMEtable0.cost AS qf2,PMEjoin1.name AS qf3 FROM cost AS PMEtable0 LEFT OUTER JOIN inventory.items AS PMEjoin0 ON PMEjoin0.id = PMEtable0.id ORDER BY PMEtable0.id LIMIT 840,30




By the way, if I were coding this discretely, I would do this sql:

Kód:

select o.id, o.cost, i.name
  from items i, cost o
  where i.id = o.id;


Richard

 
ajh     Založený: 17.12.2003   Príspevky: 236  
Príspevok Zaslal: 2004-04-11 19:17
Návrat hore  Odpovedať s citátom     

It's not PMEjoin1 in your code, it's PMEjoin0. The number after the PMEjoin is actually defined by which field has the foreign option on it in your calling file. So if you have 10 fields defined and the joining field is the 8th, it would be PMEjoin7 (as you have to start counting from 0). In your case it is the first field, so it is PMEjoin0. You can actually see PMEjoin0 declared in your SQL statement.

 
willirl     Založený: 04.04.2004   Príspevky: 14  
Príspevok Zaslal: 2004-04-11 19:39
Návrat hore  Odpovedať s citátom     

I'm certain you are trying to tell me something but I'm not quite sure what it is.

If you are telling me what my problem is you will need to be a little more "application specific".

Richard

 
ajh     Založený: 17.12.2003   Príspevky: 236  
Príspevok Zaslal: 2004-04-11 20:32
Návrat hore  Odpovedať s citátom     

Not sure I can be more specific!!

Use 'PMEjoin0.name' rather than 'PMEjoin1.name' in your 'sql' option for your 'name' field.

 
willirl     Založený: 04.04.2004   Príspevky: 14  
Príspevok Zaslal: 2004-04-12 00:18
Návrat hore  Odpovedať s citátom     

That worked great! This is exactly what I need. Thanks a million for your patience and expertise.

Regards,
Richard

 
ned@kelly.org.au     Založený: 08.04.2004   Príspevky: 22   Bydlisko: Mosquito Flat, Australia
Príspevok Zaslal: 2004-06-30 08:10
Návrat hore  Odpovedať s citátom     

Hi

Can willirl post the contents of your cost.php file on the forum - it would be very helpfull

Thanks

Ned

 
debstar     Založený: 13.09.2004   Príspevky: 4   Bydlisko: UK/Australia
Príspevok Zaslal: 2004-09-14 21:57
Návrat hore  Odpovedať s citátom     

... and a round of applause for ajh's good humour and patience.
:o)

 
pilavdzic     Založený: 26.10.2004   Príspevky: 9  
Príspevok Zaslal: 2004-10-26 18:10
Návrat hore  Odpovedať s citátom     

Yes, please do post the final result. I am trying to achieve this as well.

Thanks!

 
hbernard     Založený: 23.03.2003   Príspevky: 159   Bydlisko: FRANCE
Príspevok Zaslal: 2004-11-01 21:49
Návrat hore  Odpovedať s citátom     

And you all may have a look at

http://opensource.platon.sk/projects/bug_view_advanced_page.php?f_bug_id=272

for a similar case, and solution without extension.

_________________
hbernard - phpMyEdit updater.
 
Odoslať novú tému   Odpovedať na tému   Choď na stránku Predchádzajúci  1, 2  

Hlavné | Zobraziť chyby | Pridať chybu | Zhrnutie | Fórum | Dokumenty | Download
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