Gossamer Forum
Home : Products : DBMan SQL : Discussion :

How to display unrelated files by date?

Quote Reply
How to display unrelated files by date?
Awhile ago TheStone supplied this Global to allow data from unrelated files to be used in templates

sub {
my ($table, $field, $value) = @_;
my $sth = $DB->table($table)->select({ $field => $value });
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}

It works very well but another requirement has raised it's ugly face and I need to do the same thing but display the items by date, most often the files are not entered in order and this global will just display the output as it finds them in the files.

Or is there another way?

Thanks.....
Quote Reply
Re: [bf] How to display unrelated files by date? In reply to
Hi,

In your DBManSQL Admin click on the following links -

Help / GT Module Documentation / SQL / Table.

I think what you are looking for is the select_options function. You can use ORDER BY to get your dates returned in order.

Try something like the following (with your field_name in the ORDER BY)

sub {
my ($table, $field, $value) = @_;
$DB->table($table)->select_options('ORDER BY field_name');
my $sth = $DB->select({ $field => $value });

my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}


Simon.
Quote Reply
Re: [jai] How to display unrelated files by date? In reply to
In Reply To:
Hi,

In your DBManSQL Admin click on the following links -

Help / GT Module Documentation / SQL / Table.

I think what you are looking for is the select_options function. You can use ORDER BY to get your dates returned in order.

Try something like the following (with your field_name in the ORDER BY)

sub {
my ($table, $field, $value) = @_;
$DB->table($table)->select_options('ORDER BY field_name');
my $sth = $DB->select({ $field => $value });

my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}


Simon. `````````````````````````````````` Thanks Simon, That sub gave this error

A fatal error has occured:
GT::SQL (24908): Unknown method 'select' called at (eval 20) line 4.
When I replaced the line (my $sth = $DB->select({ $field => $value }); ) with the original line (my $sth = $DB->table($table)->select({ $field => $value }); ) I didn't get the error but it didn't sort the fields either.
Quote Reply
Re: [bf] How to display unrelated files by date? In reply to
I'm no expert so I can't tell you why my last example didn't work (hopefully someone will jump in).

Try the following -

sub {
my ($table, $field, $value) = @_;
my $obj = $DB->table($table);
$obj->select_options ('ORDER BY field_name');
my $sth = $obj->select({ $field => $value });
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}


Simon
Quote Reply
Re: [jai] How to display unrelated files by date? In reply to
In Reply To:
I'm no expert so I can't tell you why my last example didn't work (hopefully someone will jump in).



I have to disagree, you just might be an expert.

The new one seems to work perfectly.

Now all I have to do is figure out why.............

Thanks you made my day Wink !!!
Quote Reply
Re: [bf] How to display unrelated files by date? In reply to
Excuse me adding another novice's question on this thread which I think could help me. Two questions: are we talking here about a global which retrieves data from a record in another table, ie the data from one of its fields/columns ? If so I and I think I'm tuned in correctly, how do you call this global in your template? If I give a concrete example : I want to get the textual data in the 'Title' column for record number 10 in the 'chickens' database. If I do <%globalname (chickens, Title, 10)%> it should work ? (using the last iteration of the global from jai above)
Thanks for your assistance
Quote Reply
Re: [charly] How to display unrelated files by date? In reply to
It should be:

<%global_name('chickens', 'Title', 10)%>

and:

sub {
my ($db, $column, $value) = @_;
....
}

TheStone.

B.
Quote Reply
Re: [TheStone] How to display unrelated files by date? In reply to
Nothing gets pushed out! (no error message)
this is what I have in template and in your global (first in thread):

<%get_data ('chickens', 'Title', 10)%>

sub {
my ($table, $field, $value) = @_;
my $sth = $DB->table($table)->select({ $field => $value });
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}

What am I missing?
Quote Reply
Re: [charly] How to display unrelated files by date? In reply to
Your template should be like:

<%get_data ('chickens', 'Title', 10)%>
<%loop loop_hash%>
<%field_name%>
....
<%endloop%>

TheStone.

B.

Last edited by:

TheStone: May 7, 2003, 2:48 PM
Quote Reply
Re: [TheStone] How to display unrelated files by date? In reply to
Yep, template is:

<%get_data ('chickens','Title',432)%>
<%loop loop_hash%>
<%Title%>
<%endloop%>

and nothing happens!
Quote Reply
Re: [charly] How to display unrelated files by date? In reply to
That means there is no entry in your database. Double check your database again.

TheStone.

B.
Quote Reply
Re: [TheStone] How to display unrelated files by date? In reply to
So as not to leave this thread hanging without a positive conclusion:
thanks to TheStone's further help on my site, the objective has been met with the following instruction to the global:

<%get_data('tablename','RecordID',1)%>
<%loop loop_hash%>
<%Title%>
<%endloop%>

where RecordID is the name of the table's primary key field, 1 is the record number and Title is the field whose content one wants to retrieve.
Things work perfect like that! Thanks again.