2. PHPRunner General questions
  3. [SOLVED]  Using Custom Links on List Page with INNER JOINs
This topic is locked
[SOLVED]

 Using Custom Links on List Page with INNER JOINs

 		9/19/2012 3:23:30 PM
PHPRunner General questions
L
 lane author

Hello everyone,
I have a List Page in which one column is populated with values from another table using INNER JOIN. This displays the appropriate values from the second table. I'd like to make each of those values a hyperlink to the appropriate Edit Page for that row in the second table. When I try to do this with the View As Custom tool, I lose the string values being JOINed from the second table and am only shown the numerical values present in the first table. I'll try to explain as best I can:
Say I have two tables, breakfast and fruit I always want to be sure I have some kind of fruit with breakfast, so I keep a list of fruits in a separate table and use the Lookup Wizard to show me the type of fruit I had that day on the List Page.



Table: fruit

fruit_id fruit_type

1 orange

2 apple

3 banana

4 grapefruit

5 mango




Table: breakfast

breakfast_id main_dish drink fruit_type

1 bacon and eggs milk 1

2 bacon and eggs water 3

3 waffles carrot juice 4


The SQL Query for the breakfast List Page would be as follows.



SELECT * FROM breakfast

INNER JOIN fruit ON breakfast.fruit_type = fruit.fruit_id


The result is as follows:


breakfast_id main_dish drink fruit_type

1 bacon and eggs milk orange

2 bacon and eggs water banana

3 waffles carrot juice grapefruit


This is exactly what I'd like. However, say I learn one day that what I've been calling oranges are actually nectarines - I'm not all that smart, after all. (I know my example is falling apart, but bear with me <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=20072&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' /> ). I'd like for each entry in the fruit_type column to be a hyperlink to the Edit page for that row in the table fruit so that I can correct my mistake. So, in the Editor, I use the View As Custom function and create the following entry:

$value = "<a href=\"fruit_edit.php?editid1=".urlencode($data["fruit_type"])."\">".$data["fruit_type"]."<a/>";


This creates the correct URL, but it removes the JOINed strings from the List Page, leaving me with:



breakfast_id main_dish drink fruit_type

1 bacon and eggs milk 1

2 bacon and eggs water 3

3 waffles carrot juice 4


where each entry in the fruit_type column is now a hyperlink to the correct edit page.
How can I retain the strings from the linked fields while also creating hyperlinks using the actual values in the fruit_type column?
Sergey Kornilov admin 9/20/2012

I'm not 100% sure I understand the problem but here I go.
Instead of joins you need to use Lookup wizards. Setup fruit_type field as a Lookup wizard in PHPRunner. Use fruit as a lookup table, fruit_id as a link field, fruit_type as a display field.
This is it, you get all functionality you need. Display field value will be displayed on List/View pages automatically.
L
 		lane author 9/20/2012



I'm not 100% sure I understand the problem but here I go.
Instead of joins you need to use Lookup wizards. Setup fruit_type field as a Lookup wizard in PHPRunner. Use fruit as a lookup table, fruit_id as a link field, fruit_type as a display field.
This is it, you get all functionality you need. Display field value will be displayed on List/View pages automatically.


Thanks for the response! I know my question is a little unclear which is why I tried to create an example scenario. The actual data is a complex contractor database and what I'm trying to accomplish makes more sense in that context, but I thought my breakfast example would be easier to diagnose.
I am using Lookup Wizards to populate the appropriate field in breakfast, and it works correctly. However, I'd like for those fields to be links to the Edit pages for those entries in fruit using the View As Custom functionality in the Editor. When I try to do this, the values do not populate on the breakfast List Page properly. I've created my example in PHPRunner and included screenshots that will hopefully show my problem.


I am using the Lookup Wizard to use values from the fruit table to populate the fruit field in breakfast. The results on the list page for breakfast look correct.


However, I would like the entries under Fruit Type to be clickable links that take me to the Edit page for the appropriate entry in the fruit table, so that a user can, for example, easily change all entries reading "orange" to "nectarine" without having to navigate to a different page. To do this, I have used the View As Custom function in PHPRunner to create the links using the code I posted earlier:


This achieves the functionality I want (making each value in the fruit column a clickable link that takes me here):


but reverts the values for fruit on the breakfast List Page to the numerical entries in the breakfast table instead of the values from fruit which the Lookup Wizard provides.


I know that the problem is probably in the code I am using in the View As Custom dialog, specifically the expression ".$data["fruit"]." between the two HTML tags:

$value = "<a href=\"fruit_edit.php?editid1=".urlencode($data["fruit"])."\">".$data["fruit"]."<a\>";


However, I do not know how to get the fruit column in the breakfast column to show the values I want while still making them clickable links.
I hope I have been clear enough, I know it's a funny question so I've tried my best to explain. I can upload the project to a demo account if that would be beneficial.
Sergey Kornilov admin 9/20/2012

Okay, this makes sense. You need to be able to access both fruit_id and fruit_type field values from lookup table. Luckily there is a way.
When you use 'View as' Custom you have access to display field via $value and to link field via $data["fieldname"]. That being said here is how your example is supposed to look:

$value = "<a href=\"fruit_edit.php?editid1=".$data["fruit_type"]."\">".$value."</a>";


PS. There were also a mistype in closing </a> tag.
L
 		lane author 9/20/2012



Okay, this makes sense. You need to be able to access both fruit_id and fruit_type field values from lookup table. Luckily there is a way.
When you use 'View as' Custom you have access to display field via $value and to link field via $data["fieldname"]. That being said here is how your example is supposed to look:

$value = "<a href=\"fruit_edit.php?editid1=".$data["fruit_type"]."\">".$value."</a>";


PS. There were also a mistype in closing </a> tag.



This worked perfectly! Thanks!