Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Tricky global with two tables

Quote Reply
Tricky global with two tables
Hello,

i have one tableX:
Username (PRI) Date1 Date2
------------------------
demo 2004-03-02 2004-03-01
testy 2004-03-20 2004-03-04

And i have a tableY:
Date(PRI) Attr1 Attr2
-----------------------
2004-03-02 A B
2004-03-03 C D
2004-03-04 E F

User testy is logged in!

So i need a global, which do this things:
  • Find from testy the value of Date2 in tableX ( --> 2004-03-04)
  • Search for this date in tableY and find in this datarecord the value of Attr2 ( --> "F")
  • Print this "F" out

Can anybody help me?

Thanks in advance,

Coyu
Quote Reply
Re: [Coyu] Tricky global with two tables In reply to
sub {
my $user = $USER->{Username};
my $table1 = $DB->table ('tableX');
my $date = $table1->select(['Date2'],{Username=>$user})->fetchrow_array;
my $table2 = $DB->table ('tableY');
return $table2->select(['Attr2'],{Date=>$date})->fetchrow_array;
}
Quote Reply
Re: [afinlr] Tricky global with two tables In reply to
hey afinir,

thank you very much!
I get this error:

A fatal error has occured:
Can't call method "fetchrow_array" on an undefined value at (eval 34) line 6.

Please enable debugging in setup for more details.

Can i use your global only with certain templates?

Greetings,

Coyu
Quote Reply
Re: [Coyu] Tricky global with two tables In reply to
No you should be able to use it on any page.
This error means that the second select didn't work - so either the fieldnames are wrong or the date is in the wrong format or something like that.

Try putting

return $date;

after the $date line and see whether you get a sensible value for that first.

Also you should probably add

$user or return;

after the $user line in case there is no logged in user.
Quote Reply
Re: [afinlr] Tricky global with two tables In reply to
Hey Affinir,
you was right! It works excellent! THANK YOU!!! :-)

Now i have a question:

I have 4 x 7 Attributes to make them visible! So i have to write 28 globals for them.

A possibility is to summarize attributes!
So ist it possible to combine html and the query-results in ONE global? Example1!
Or set them into seven variables so i can access them in the template? Look at Example2!

example1 of the last line:

Code:

return
<table><tr>
<td
<%attr1%>
</div></td>
<td>
<%attr2%>
</td>
<td
<%attr3%>
</td>
<td
<%attr4%>
</td>
<td>
<%attr5%>
</td>
<td
<%attr6%>
</td>
</tr></table>


The attributes are in Table2 fixed to 6!

And here is example 2:

Code:

return attr1 as $attr1 .... attr7 as $attr7

So in the template i can spread out the six results in my table!

Is one of the way possible?

Thanks in advance!

Coyu
Quote Reply
Re: [Coyu] Tricky global with two tables In reply to
Hi,

Not sure I completely understand but I think you want something like this?

sub {
my $user = $USER->{Username};
my $table1 = $DB->table ('tableX');
my $date = $table1->select(['Date2'],{Username=>$user})->fetchrow_array;
my $table2 = $DB->table ('tableY');
return $table2->select(['Attr1','Attr2','Attr3','Attr4','Attr5','Attr6'],{Date=>$date})->fetchrow_hashref;
}

If you leave out the red part it will return all the fields in the table. You need to put this global tag at the top of your page and then you can use the tags for each attribute wherever you need them in the page.
Quote Reply
Re: [afinlr] Tricky global with two tables In reply to
Hi Afinir, this is a very good idea!
But i need the same attributes from different IDs in ONE template?
How can i differentiate them?

e.g.
Show me Attr1, 2, 3, 4 where ID = "2004-03-10" AND
Show me Attr1, 2, 3, 4 where ID = "2004-03-11" AND
Show me Attr1, 2, 3, 4 where ID = "2004-03-12"
in one page!

Thanks in advance!

Coyu
Quote Reply
Re: [afinlr] Tricky global with two tables In reply to
Hey,
i think the best thing would be a global, which works so:
<%globalname(Date_No, Attribute_No)%>
So that means:
- Find the value of column Date_No in tableX
- Search in this value for the column Attribute_No in tableY and print it out

So when "testy" would be logged in and the global would have this parameters:
<%globalname(Date2, Attr2)%>
the output would be "F" with this two tables:

tableX:
Username (PRI) Date1 Date2
------------------------
demo 2004-03-02 2004-03-01
testy 2004-03-20 2004-03-04

tableY:
Date(PRI) Attr1 Attr2
-----------------------
2004-03-02 A B
2004-03-03 C D
2004-03-04 E F

So how the global should be?

Thanks in advance,

Coyu