Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Development, Plugins and Globals :

How to convert this to a links sql global?

Oct 4, 2002, 8:26 AM
shriram
Novice (36 posts)
Oct 4, 2002, 8:26 AM
Post #1 of 9
Views: 3911
Shortcut
Quote Reply
How to convert this to a links sql global?
Hi,

I've got my main page built using vBulletin templates. I use the following code to build the latest links summary.

Code:


mysql_select_db("geobaby_resources",$linksdb_id);

$links_res = mysql_query("select Add_Date from lsql_Links group by Add_Date order by Add_Date desc limit 5");
while ($links = mysql_fetch_array($links_res)) {
$links_count_res = mysql_query("select count(*) as total from lsql_Links where Add_Date='$links[Add_Date]'");
$links_count = mysql_fetch_array($links_count_res);
eval("\$links_latest_bits .= \"".gettemplate("links_latest_bit")."\";");
}


How would I turn the "links_latest_bits" into a global that I can insert into a template?

I've figured it out to the point where I need to go to Build -> Template Globals and then add a global variable. Not sure how to code this in perl.

Can someone help me?

Thanks :)

Shri
Oct 4, 2002, 11:37 AM
Alex
Administrator (9387 posts)
Oct 4, 2002, 11:37 AM
Post #2 of 9
Views: 3830
Shortcut
Quote Reply
Re: [shriram] How to convert this to a links sql global? In reply to
Hi,

First off, are you using the PHP front end to Links SQL, or are you using the perl front end? You can use PHP globals, see:http://www.gossamer-threads.com/...i?post=203084#203084

It's a bit long, but should give you some pointers.

Cheers,

Alex
--
Gossamer Threads Inc.
Oct 4, 2002, 7:08 PM
pugdog
Veteran / Moderator (6956 posts)
Oct 4, 2002, 7:08 PM
Post #3 of 9
Views: 3809
Shortcut
Quote Reply
Re: [shriram] How to convert this to a links sql global? In reply to
I'm not sure if this is what you had in mind, and it's for the PERL version:

Code:
sub {
my $output ='';
my $db_links = $DB->table('Links');
$db_links->select_options('GROUP BY Add_Date', 'ORDER BY Add_Date Desc', 'LIMIT 5');
my $date_results = $db_links->select;
while (my $found_date = $date_results->fetchrow_hashref) {
$db_links->select ( {Add_Date => $found_date->{'Add_Date'} } );
my $links_count = $db_links->hits;
$output .= $found_date->{'Add_Date'} . " had ". $links_count . " new links added.<BR>\n";
}
return $output;
}


It will show the number of new links added on the 5 most recent days.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Oct 4, 2002, 8:13 PM
shriram
Novice (36 posts)
Oct 4, 2002, 8:13 PM
Post #4 of 9
Views: 3797
Shortcut
Quote Reply
Re: [Alex] How to convert this to a links sql global? In reply to
I'm using the perl front end. Found it easier to administrate. Still getting familar with it. :)
Oct 4, 2002, 8:15 PM
shriram
Novice (36 posts)
Oct 4, 2002, 8:15 PM
Post #5 of 9
Views: 3810
Shortcut
Quote Reply
Re: [pugdog] How to convert this to a links sql global? In reply to
Thanks pugdog, this looks a whole lot more convoluted than I had expected. :D

Will give it a try.
Oct 4, 2002, 8:29 PM
shriram
Novice (36 posts)
Oct 4, 2002, 8:29 PM
Post #6 of 9
Views: 3821
Shortcut
Quote Reply
Re: [shriram] How to convert this to a links sql global? In reply to
This is what I did.

Code:


sub { my $output ='';
my $db_links = $DB->table('Links');
$db_links->select_options('GROUP BY Add_Date', 'ORDER BY Add_Date Desc', 'LIMIT 5');
my $date_results = $db_links->select;
while (my $found_date = $date_results->fetchrow_hashref) {
$db_links->select ( {Add_Date => $found_date->{'Add_Date'} } );
my $links_count = $db_links->hits;
$output .= $found_date->{'Add_Date'} . " had <a href=/resources/page.cgi?g=New/"
. $found_date->{'Add_Date'}. ".html&d=1>"
. $links_count . " new links</a> added.<BR>\n"; }
return $output; }


Its on http://www.geobaby.com/...2Findex.html&d=1

Last edited by:

pugdog: Oct 5, 2002, 5:19 AM
Oct 5, 2002, 5:20 AM
pugdog
Veteran / Moderator (6956 posts)
Oct 5, 2002, 5:20 AM
Post #7 of 9
Views: 3792
Shortcut
Quote Reply
Re: [shriram] How to convert this to a links sql global? In reply to
Here's a much improved version, that should take into account validated/unvalidated links, static and dynamic builds, your site's build-cutoff date, etc:

Code:
sub {
my $output ='';
my $db_links = $DB->table('Links');
$db_links->select_options('GROUP BY Add_Date', 'ORDER BY Add_Date Desc', 'LIMIT 5');
my $date_results = $db_links->select ( {isValidated =>'Yes'} );
while (my $found_date = $date_results->fetchrow_hashref) {
$db_links->select ( {Add_Date => $found_date->{'Add_Date'}, isValidated =>'Yes'} );
my $links_count = $db_links->hits;

Links::init_date(); ## you can either 'use GT::Date' or do it this way, this is probably better
GT::Date::date_set_format('%yyyy%-%mm%-%dd%'); ## this is the key to making it work
my $days_old = GT::Date::date_diff ( GT::Date::date_get(), $found_date->{'Add_Date'});

if ($days_old <= $CFG->{'build_new_cutoff'}) {
$output .= $found_date->{'Add_Date'}
. " had <a href=" . $CFG->{'build_new_url'} . "/" . $found_date->{'Add_Date'} . ".html>"
. $links_count . " new links</a> added.<BR>\n";
} else {
$output .= $found_date->{'Add_Date'} . " had ". $links_count . " new links added.<BR>\n";
}
}
return $output;
}


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Oct 5, 2002, 2:41 PM
Alex
Administrator (9387 posts)
Oct 5, 2002, 2:41 PM
Post #8 of 9
Views: 3762
Shortcut
Quote Reply
Re: [shriram] How to convert this to a links sql global? In reply to
Hi,

Something like this is a bit simpler and a lot better as it's one query and one template parse:

Code:
sub {
my $links_db = $DB->table('Links');
$link_db->select_options("GROUP BY Add_Date", "ORDER BY Add_Date DESC", "LIMIT 5");
my $dates = $link_db->select(
['Add_Date', 'COUNT(*) AS Link_Count'],
{ isValidated => 'Yes' }
)->fetchall_hashref;
return Links::user_page('links_latest_bit.html', { date_loop => $dates }, { print => 0 });
}


Then your links_latest_bit.html template should look like:

<%loop date_loop%>
Date: <%Add_Date%> has <%Link_Count%> links.
<%endloop%>

If you want to make sure only new links are considered in the count, just add isNew => 'Yes' besides the isValidated check.

Hope that helps,

Alex
--
Gossamer Threads Inc.
Oct 6, 2002, 7:25 PM
pugdog
Veteran / Moderator (6956 posts)
Oct 6, 2002, 7:25 PM
Post #9 of 9
Views: 3737
Shortcut
Quote Reply
Re: [Alex] How to convert this to a links sql global? In reply to
Quote:
If you want to make sure only new links are considered in the count, just add isNew => 'Yes' besides the isValidated check.


Alex, your code works on high-activity sites, but if all the links were older than new (a low-activity site) there would be no "hits"

The code I wrote above would still return a links count for the most recent 5 dates, but would not link to a "new" page, unless the new date was less than build_cutoff.

The following code should handle all this, and in one database query :)

Code:
sub {
my $output ='';
my $link_db = $DB->table('Links');
$link_db->select_options("GROUP BY Add_Date", "ORDER BY Add_Date DESC", "LIMIT 5");
my $dates = $link_db->select(
['Add_Date', 'COUNT(*) AS Link_Count'],
{ isValidated => 'Yes' }
);

Links::init_date(); ## you can either 'use GT::Date' or do it this way,
## this is probably better
GT::Date::date_set_format('%yyyy%-%mm%-%dd%'); ## this is the key to making it work

while (my $found_date = $dates->fetchrow_hashref) {
my $days_old = GT::Date::date_diff ( GT::Date::date_get(), $found_date->{'Add_Date'});

if ($days_old <= $CFG->{'build_new_cutoff'}) {
$output .= $found_date->{'Add_Date'}
. " had <a href=" . $CFG->{'build_new_url'} . "/" . $found_date->{'Add_Date'} . ".html>"
. $found_date->{'Link_Count'} . " new links</a> added.<BR>\n";
} else {
$output .= $found_date->{'Add_Date'}
. " had ". $found_date->{'Link_Count'}
. " new links added.<BR>\n";
}
}
return $output;
}


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.