Gossamer Forum
Home : Products : DBMan SQL : Discussion :

select_options and query_sth

Quote Reply
select_options and query_sth
I have 3 questions about the following code -

(taken from sub modify_search_results in Dbsql/Home.pm )

1/ Can $obj->select_options be applied to $obj->query_sth in the code below or is it only used with $obj->select???

2/ After reading up on GROUP BY it appears that whatever columns are in the SELECT must also be in the GROUP BY. Does this mean that $obj->query_sth automatically selects only the columns named in the GROUP BY or does it select all columns by default??? If it selects all columns then the GROUP BY would not be reliable.

3/ (If the answer to question 1 is "NO") How can I impliment the GROUP BY into $obj->query_sth???

my $group = "GROUP BY col1, col2";
$self->{db}->select_options($group);


# Do the search and count the results.
my $sth = $self->{db}->query_sth ($self->{cgi}) or return $self->modify_search_form($GT::SQL::error);


Thank you.

Simon.
Quote Reply
Re: [jai] select_options and query_sth In reply to
Hi Simon,

1, Yes.

2. It will select all columns by default.
query_sth just provides a simple method to search a table so if you like to use GROUP BY (use with COUNT, AVG, SUM ....etc), you can use select function instead.
i.e :

$self->{db}->select_options("GROUP BY col1, col2");

my $sth = $self->{db}->select(["COUNT(*) AS count,col1,col2"]);

You could read the GT module help (GT::SQL::Table) in your admin panel for more information.

Cheers,
Jean
Gossamer Threads Inc.
Quote Reply
Re: [jean] select_options and query_sth In reply to
Hi jean,

Thanks for your reply.

The particular piece of code that I refered to in my last post takes the form input ($self->{cgi}) so I guess that's why it uses $obj->query_sth. I want to continue to use the form input ($self->{cgi}) BUT only SELECT on the two columns named in my GROUP BY not all columns. As you said, $obj->query_sth selects all columns by default so it's better to use $obj->select but I can't get it to accept the form input ($self->{cgi}). I thought something like -

my $sth = $self->{db}->select('column1','column',$self->{cgi});

may work but it doesn't process the input like $obj->query_sth (e.g sort out the empty fields,etc.) and I get errors in my WHERE clause because it's specifying columns that don't exist.

I want to SELECT on two columns, apply my GROUP BY clause and build a WHERE clause from the user's form input (not empty fields, hidden fields, other fields etc.)

Any ideas??

Thank you.

Simon.



PS I still need help with this one as well when you have time.

http://www.gossamer-threads.com/...i?post=233717#233717

Last edited by:

jai: Feb 27, 2003, 3:10 AM
Quote Reply
Re: [jai] select_options and query_sth In reply to
Any ideas about my question above????

Simon.


PS. I solved my plugin problem.

Last edited by:

jai: Mar 3, 2003, 10:02 PM
Quote Reply
Re: [jai] select_options and query_sth In reply to
Please GT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Quote Reply
Re: [jai] select_options and query_sth In reply to
Still no feedback!!!!!!!!!!!!!!

I'll try to give more details, maybe that will help.

The plugin sub below does SEEM to work ok but I am still concerned about the reliability of the red coding.

If my GROUP BY is being applied to query_sth it may not be reliable because query_sth SELECTs ALL fields by default and whatever columns are in the SELECT must also be in the GROUP BY. In my case, I only want two columns in the GROUP BY.

As I said in my post above, if I change the my $sth = $self->{db}->query_sth ($self->{cgi}) to my $sth = $self->{db}->select ($self->{cgi}) it doesn't process the form input in the same way and I get errors in my WHERE clause.

What should I do? Are the red areas in my sub reliable or not?

Thank you.

Simon.





sub split_modify_search_results {
# -------------------------------------------------------------------
# This subroutine will get called whenever the hook 'modify_search_results'
# is run. You should call GT::Plugins->action ( STOP ) if you don't
# want the regular code to run, otherwise the code will continue as
# normal.
#
#----------------------------------------------------------------------
my $self = shift;
# Ignores the plugin if this is not contact table
($self->{cgi}->{db} ne 'public_holiday') and return @_;
GT::Plugins->action ( STOP );




return $self->home($self->_language('PER_MOD')) unless ( $self->{user}->{modify_p} );

# Make sure the user passed in some values to search on
$self->_check_opts or return $self->modify_search_form ($self->_language('SRC_FAILURE'));

# Format the cgi for searching
$self->format_search_cgi;

# Check if users can delete only their own records
if ( $self->{cfg}->{'auth_modify_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
$self->{cgi}->{$self->{cfg}->{'auth_user_field'}} = $self->{user}->{'Username'};
$self->{cgi}->{"$self->{cfg}->{'auth_user_field'}-opt"} = '=';
}



#################################

my $group = "GROUP BY public_holiday_201, public_holiday_owner_id";
$self->{db}->select_options($group);




# Do the search and count the results.
my $sth = $self->{db}->query_sth ($self->{cgi}) or return $self->modify_search_form($GT::SQL::error);
my $hits = $self->{db}->hits();


# Return if we haven't found anything.
if ( $hits == 0 ) {
#------------demo code-----------

return $self->modify_search_form ($self->_language('SRC_NOTFOUND'));
}

# Go straight to the modify form if we only have on result.
if ( $hits == 1 and !$self->{cgi}->{sdo} ) {
$self->{cgi}->{modify} = 0;
my $row = $sth->fetchrow_hashref;
foreach ( keys %$row ) {
$self->{cgi}->{$_} = $row->{$_};
}
return $self->modify_form();
}

# Build speed bar
my $speedbar = '';
if ( $hits > ($self->{cgi}->{mh}|| 25) ) {
my $name = GT::CGI->url(remove_empty => 1);
$speedbar .= $self->{disp}->toolbar($self->{cgi}->{nh}|| 1, $self->{cgi}->{mh}|| 25, $hits, $name);
}

# Return results
my @output;
my @pk = $self->{db}->pk;
my $i = 1;
while ( my $row = $sth->fetchrow_hashref ) {
my $hidden;
foreach my $key ( @pk ) {
if ( $self->{db}->can ('_complete_name') ) {
my $new = {};
for ( keys %{$row} ) {
$new->{$self->{db}->_complete_name ($_)} = $row->{$_};
}
$row = $new;
}
my $val = $row->{$key};
$hidden .= qq~<input type=hidden name="$i-$key" value="$val">~;
}
$row->{hidden} = $hidden;


push @output, $row;
$i++;
}

my $enctype = $self->{db}->_file_cols() ? 'enctype="multipart/form-data"' : '';

return ('search_results.html',{
header => $self->_language('HEA_SRC_RESULT'),
results => \@output,
enctype => $enctype,
speedbar=> $speedbar,
msg => $self->_language('SRC_RESULT', $hits),
hits => $hits
});

#end sub
}
Quote Reply
Re: [jai] select_options and query_sth In reply to
Hi Simon,

Sorry for late reply on this post.

You could use rs option in CGI input to force a select_returns (fields that you wish returned) from using query_sth function:


$self->{db}->select_options("GROUP BY public_holiday_201, public_holiday_owner_id");
$self->{cgi}->{rs} = 'public_holiday_201,public_holiday_owner_id';
# Do the search and count the results.
my $sth = $self->{db}->query_sth ($self->{cgi}) or return $self->modify_search_form($GT::SQL::error);


Hope that helps,
Jean
Gossamer Threads Inc.
Quote Reply
Re: [jean] select_options and query_sth In reply to
Hi jean,

Thanks for your help.

I've never seen the rs option ($self->{cgi}->{rs} = 'fieldname1,fieldname2';) before.

Is it in the DBManSQL documentation??

I assume that the rs option overrides the default of SELECT on ALL columns in query_sth and would only do a SELECT on the specified columns. This would therefore ensure the reliabilty of my GROUP BY statement (provided the same columns are listed in BOTH the rs and the GROUP BY).

Am I assuming correct?

Thank you for your continued support.

Simon.
Quote Reply
Re: [jai] select_options and query_sth In reply to
Yes, that's correct. 'rs' option allows you to do a SELECT on the specified columns when you use query_sth. It will do a 'SELECT COL1,COL2 FROM ...' (assuming that rs option equals 'COL1,COL2') instead of doing 'SELECT * FROM ...' as default.


Cheers,
Jean
Gossamer Threads Inc.
Quote Reply
Re: [jean] select_options and query_sth In reply to
hi jean,

How can I add in an aggregate function to the code below. I want to select min(public_holiday_id) along with the public_holiday_201 and public_holiday_owner_id. Note: public_holiday _id is the auto increment primary key.


$self->{db}->select_options("GROUP BY public_holiday_201, public_holiday_owner_id");
$self->{cgi}->{rs} = 'public_holiday_201,public_holiday_owner_id';
# Do the search and count the results.
my $sth = $self->{db}->query_sth ($self->{cgi}) or return $self->modify_search_form($GT::SQL::error);

Is it possible? Can you show me how to include it in my example.

Thank you.

Simon.
Quote Reply
Re: [jai] select_options and query_sth In reply to
You can try with:

$self->{cgi}->{rs} = 'MIN(public_holiday_id) as min_phd, public_holiday_201,public_holiday_owner_id';


then you can use min_phd in your templates.
Let me know if it works for you,
Cheers,
Jean
Gossamer Threads Inc.

Quote Reply
Re: [jean] select_options and query_sth In reply to
Thanks jean,

It works fine!!

Is there any documentation on the rs function ($self->{cgi}->{rs})???

Can we use any MySQL command in there?

Thank you.

Simon.
Quote Reply
Re: [jai] select_options and query_sth In reply to
I do not quite understand this thread...

I am wondering how to make search_results hook search only in a particular column?

What I have done is made 'query' be a set of space-separated zip codes and set the options to 'or'.
Although this works pretty well, It would be nice to search ONLY on the zip code column.

Can it be done easily?


Oh, this is for GLinks 3.x

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com

Last edited by:

rgbworld: May 22, 2005, 3:44 PM