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

Conditional table select in LSQL Coding

Quote Reply
Conditional table select in LSQL Coding
Hi, I was wondering how can I do a conditional table select?

Here's the current code...

Code:
my $Date = "$year-$month-$day"; my $table = $DB->table('Links','CatLinks');
my $sth = $table->select( { Live_Date => "$Date" },{ CategoryID => '7' } ,{ CategoryID => '7' } ); my $LinkID;
while (my $hit = $sth->fetchrow_hashref) {
$LinkID = $hit->{ID};
}
What I want to do is change the red line so that $sth contains EITHER the pointer to the row with
the corresponding live date OR if it doesn't exist, select a random ROW.


E.g.

my $sth;
IF (Live_Date => "$Date") DOESN'T EXIST/MATCH)
$sth = $table->select ({RANDOM ROW FROM CATEGORY 7))
ELSE {
$sth = $table->select( { Live_Date => "$Date" },{ CategoryID => '7' } ,{ CategoryID => '7' } );
}

Does anybody know the syntax for the 4 lines above of pseudocode? I imagine the major issue is how to do the match.

Basically, the question is probably even simpler.
What does a $table->select command return if there is no match Smile
Quote Reply
Re: [scorpioncapital] Conditional table select in LSQL Coding In reply to
I would personally use something like;

Code:
my $Date = "$year-$month-$day";
my $table = $DB->table('Links','CatLinks');

my $LinkID = $table->select( ['Links.ID'],{ Live_Date => "$Date" },{ CategoryID => '7' } )->fetchrow;

if ($LinkID !~ /\d+/) {
$table->select_options('ORDER BY RAND()','LIMIT 1');
$LinkID = $table->select( ['Links.ID'], { CategoryID => '7' } )->fetchrow;
}

This is untested, but should work Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Last edited by:

Andy: Nov 27, 2004, 8:01 AM
Quote Reply
Re: [Andy] Conditional table select in LSQL Coding In reply to
Ok, thanks, I was looking for something that would be the most minimal change, your code looks awesome, but radical. Smile I will try to incorporate it. Btw, are comments in perl same as in c++, can you use /* ... */ for multi line or you have to use # for every single line ?
Quote Reply
Re: [Andy] Conditional table select in LSQL Coding In reply to
Oh yeah, one last question is if $myLinks is defined in the if loop is it available outside of the loop, since I will need it later. I'm thinking the only mod would be to say 'my $myLinks' (oustide the if loop)
Quote Reply
Re: [scorpioncapital] Conditional table select in LSQL Coding In reply to
What's the purpose of the first 'if' statement?
Quote Reply
Re: [scorpioncapital] Conditional table select in LSQL Coding In reply to
Whoops, the if ( part shouldn't have been in there <G>

Basically;

my $Date = "$year-$month-$day";
my $table = $DB->table('Links','CatLinks');

... this is just your normal stuff.


my $LinkID = $table->select( ['Links.ID'],{ Live_Date => "$Date" },{ CategoryID => '7' } )->fetchrow;

... This will attempt to grab an ID number into $LinkID. If this fails, then it should give $LinkID a NULL value, and thus the below price of code will be run...

if ($LinkID !~ /\d+/) {
$table->select_options('ORDER BY RAND()','LIMIT 1');
$LinkID = $table->select( ['Links.ID'], { CategoryID => '7' } )->fetchrow;
}

Simple, when you know how :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Conditional table select in LSQL Coding In reply to
True art indeed! Only addition would be:

if ($LinkID !~ /\d+/) {
$table->select_options('ORDER BY RAND()','LIMIT 1');
$LinkID = $table->select( ['Links.ID'], { CategoryID => '7' } )->fetchrow;
}
else {
code to run if $linkId is NOT NULL
}
Quote Reply
Re: [scorpioncapital] Conditional table select in LSQL Coding In reply to
One line blows up :)

$table->select_options('ORDER BY RAND($seed)','LIMIT 1');
$LinkID = $table->select( ['Links.ID'], { CategoryID => '7' } )->fetchrow;
}

It blows up as:

(Can't call method "fetchrow" on an undefined value at ...
).

Is it because there should be an extra ',' or selection ?
Quote Reply
Re: [scorpioncapital] Conditional table select in LSQL Coding In reply to
Here is the MYSQL ERROR:

"GT::SQL::error = Failed to execute query: '
SELECT lsql_Links.ID
FROM lsql_CatLinks, lsql_Links
WHERE lsql_CatLinks.LinkID = lsql_Links.ID AND (lsql_CatLinks.CategoryID = '7')
ORDER BY RAND LIMIT 1
' Reason: Unknown column 'RAND' in 'order clause'
DBI::errstr = Unknown column 'RAND' in 'order clause'
@INC =