Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Discussions :

Haw to use mysql command in Links sql?

Jan 9, 2002, 7:00 PM
Troja
User (77 posts)
Jan 9, 2002, 7:00 PM
Post #1 of 7
Views: 2668
Shortcut
Quote Reply
Haw to use mysql command in Links sql?
Haw to use mysql command in Links sql?

Please help to search for:
select * from Links where City='London' and Ratings between 7 and 9 or Title='Free';

and than to return the results:

while (my $link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display('link', $link);
}

Thanks advanced.
Jan 9, 2002, 10:34 PM
brewt
Staff (4101 posts)
brewt's avatar
Jan 9, 2002, 10:34 PM
Post #2 of 7
Views: 2567
Shortcut
Quote Reply
Re: [Troja] Haw to use mysql command in Links sql? In reply to
In Reply To:
select * from Links where City='London' and Ratings between 7 and 9 or Title='Free';
The GT::SQL equivalent to that would be something like:
Code:
use GT::SQL::Condition;
my $cond1 = GT::SQL::Condition->new(['City', '=', 'London'], ['Ratings', '>=', 7], ['Ratings', '<=', '9']);
my $cond2 = GT::SQL::Condition->new($cond1, ['Title', '=', 'Free']);
$cond2->bool('OR');
my $sth = $DB->table('Links')->select($cond2);



Adrian

Last edited by:

brewt: Jan 9, 2002, 10:34 PM
Jan 12, 2002, 8:03 PM
Troja
User (77 posts)
Jan 12, 2002, 8:03 PM
Post #3 of 7
Views: 2568
Shortcut
Quote Reply
Re: [brewt] Haw to use mysql command in Links sql? In reply to
Thanks brewt!


Do you have idea haw to use this condition if the user do not fill the fild city?

use GT::SQL::Condition;
my $cond1 = GT::SQL::Condition->new(['City', '=', $city], ['Ratings', '>=', $ratings_min], ['Ratings', '<=', '$ratings_max']);
my $sth = $DB->table('Links')->select($cond1);

Thanks advanced!

Last edited by:

Troja: Jan 12, 2002, 8:17 PM
Jan 12, 2002, 8:42 PM
brewt
Staff (4101 posts)
brewt's avatar
Jan 12, 2002, 8:42 PM
Post #4 of 7
Views: 2555
Shortcut
Quote Reply
Re: [Troja] Haw to use mysql command in Links sql? In reply to
Just add an if statement checking for $city.
like
Code:
my $cond1;
if ($city) {
$cond1 = GT::SQL::Condition->new(['City', '=', $city], ['Ratings', '>=', $ratings_min], ['Ratings', '<=', '$ratings_max']);
}
else {
$cond1 = GT::SQL::Condition->new(['Ratings', '>=', $ratings_min], ['Ratings', '<=', '$ratings_max']);
}

Adrian
Jan 12, 2002, 8:51 PM
Troja
User (77 posts)
Jan 12, 2002, 8:51 PM
Post #5 of 7
Views: 2587
Shortcut
Quote Reply
Re: [brewt] Haw to use mysql command in Links sql? In reply to
It' s very clever but I have many fillds that I want to use like a same way. If use it like that I must make more than 10 IF combination. Is thera other way, Brewt?
Jan 12, 2002, 9:13 PM
brewt
Staff (4101 posts)
brewt's avatar
Jan 12, 2002, 9:13 PM
Post #6 of 7
Views: 2572
Shortcut
Quote Reply
Re: [Troja] Haw to use mysql command in Links sql? In reply to
Well then you could do something like this:
Code:
my @cond;

push @cond, ['City', '=', $city] if $city;
push @cond, ['Ratings', '>=', $ratings_min] if $ratings_min;
push @cond, ['Ratings', '<=', $ratings_max] if $ratings_max;

use GT::SQL::Condition;
my $cond = GT::SQL::Condition->new(@cond);
$cond->bool('AND'); # not really needed but we'll be explicit.

my $sth = $DB->table('Links')->select($cond);

Adrian

Last edited by:

brewt: Jan 12, 2002, 9:16 PM
Jan 13, 2002, 3:44 PM
Troja
User (77 posts)
Jan 13, 2002, 3:44 PM
Post #7 of 7
Views: 2530
Shortcut
Quote Reply
Re: [brewt] Haw to use mysql command in Links sql? In reply to
Thnaks again!