Gossamer Forum
Home : Products : Gossamer Links : Discussions :

General Perl sql question

Quote Reply
General Perl sql question
I am new to perl so this is just a general question.

Why does GL set up the sql queries like:
my $cond = GT::SQL::Condition->new( 'Full_Name', 'LIKE' 'catName%');
my $sth = $db->select( ['ID','Full_Name'],$cond );


and not
select * from $cat WHERE Full_Name LIKE catName%;


Forgive me if this is a stupid question but I thought Perl could run a regular sql query(i.e. "select * from...").

I am trying to get the hang of this Perl language and create my own globals so any help is welcome.

Thanks in advanced. Smile
Quote Reply
Re: [cezo] General Perl sql question In reply to
You can perform regular SQL queries like it's done in any other language. However, doing it like that allows our code to make the SQL portable between different databases (our software works with not only MySQL, but PostgreSQL, Microsoft SQL Server, and Oracle).

Adrian
Quote Reply
Re: [brewt] General Perl sql question In reply to
In Reply To:
You can perform regular SQL queries like it's done in any other language. However, doing it like that allows our code to make the SQL portable between different databases (our software works with not only MySQL, but PostgreSQL, Microsoft SQL Server, and Oracle).

so I could change this:
my $cond = GT::SQL::Condition->new( 'Full_Name', 'LIKE' 'catName%');
my $sth = $db->select( ['ID','Full_Name'],$cond );


to this:
my $sth = $db->select * FROM table WHERE ....
Quote Reply
Re: [cezo] General Perl sql question In reply to
No, that won't work and isn't recommended you use raw sql queries.

Adrian
Quote Reply
Re: [brewt] General Perl sql question In reply to
The reason why I ask this is because I am trying to create a global that would display the links in category 5
I used this query:

$db->select_options('ORDER BY Full_Name','LIMIT 10');
my $cond = GT::SQL::Condition->new( 'ID', '=>', '5');
my $sth = $db->select( ['ID','Full_Name'],$cond );


but of course it does not work.

How would I write it to just select the links from category ID 5. I have searched the board and tried different ways but none have worked for me.


Thanks for your prompt responce!!
Quote Reply
Re: [cezo] General Perl sql question In reply to
Try this:
Code:
my $cat = $DB->table('Links', 'CatLinks');
$cat->select_options('ORDER BY Title', 'LIMIT 10');
my $sth = $cat->select('ID', 'Full_Name', { CategoryID => 5 }, VIEWABLE);

A few notes:
- select_options is a method of a table object.
- there is no Full_Name column in the Links table (it's a Category column)
- you should use the VIEWABLE constant (it returns a GT::SQL::Condition object) to ensure that your select only gets validated and non-expired links

Adrian
Quote Reply
Re: [brewt] General Perl sql question In reply to
I am trying to pull just the links from category 5
Can someone tell what is wrong with this:

sub {
my ($output,$sth,$link);
my $cat = shift;
my $search_db = $DB->table('CatLinks', 'Links');
$search_db->select_options ('ORDER BY Add_Date DESC Limit 5');
$sth = $search_db->select ( {isNew => 'Yes', isValidated => 'Yes'}, CategoryID => $cat );
while ($link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display ('link1', $link);
}
return $output;
}
Quote Reply
Re: [cezo] General Perl sql question In reply to
I was searching for it but everything I found was a bit different or didnt work.

Any help is greatly appreciated!
Quote Reply
Re: [cezo] General Perl sql question In reply to
Untested:

sub {
my ($output,$sth,$link);
my $cat = shift;
my $search_db = $DB->table('CatLinks', 'Links');
$search_db->select_options ('ORDER BY Add_Date DESC Limit 5');
$sth = $search_db->select ( {isNew => 'Yes', isValidated => 'Yes', CategoryID => $cat} );
while ($link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display ('link1', $link);
}
return $output;
}
Quote Reply
Re: [afinlr] General Perl sql question In reply to
Quote:
$search_db->select_options ('ORDER BY Add_Date DESC Limit 5');


is there a way to have the links become randomly displayed?