Hi,

I'm trying to update my Links2 directory to SQL but I have a lot of mods and I'm getting stuck. Please can anyone give me some hints on how to add featured sites (like an editor's pick) to the homepage? I have a flag for whether the links are featured or not - I just can't see how to get them on there.

Thanks,
Laura.

Create a global with the following code:

my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY Add_Date DESC', 'LIMIT 1');
my $sth = $search_db->select;
while (my $hit = $sth->fetchrow_hashref) {
$output .= qq!
$hit->{Title}<BR>$hit->{URL}
$hit->{Description}!; }
return $output;
~;

You can try that out but Im not sure if it is correct as I can't remember the table names of the top of my head.

Paul Wilson.
new - http://www.wiredon.net

Paul,

Thanks a lot. The global I ended up with is:

sub {
# Displays the featured links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY Add_Date DESC');
$sth = $search_db->select ( { isFeatured => 'Yes'});
while ($link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display ('link', $link);
}
return $output;
}

Laura.

Hope the code I provided was of some use.

Paul Wilson.
new - http://www.wiredon.net

Extremely useful - I had no idea where to start from.
Thanks again.

Laura.

Well it looks like you know what you are doing to me by the way you modified the code.

Paul Wilson.
new - http://www.wiredon.net

Thanks for the vote of confidence! I think that my perl isn't too bad but I'm only a few days into playing around with linksSQL so I haven't found where everything is yet. I didn't realise that it was so easy to change things using the globals and templates - I have been using site_html.pl in links2 but I wish I'd started using the templates straight away - it would have made the move much easier.

Laura.

Yes I had the same trouble. I started off using site_html.pl but then realised that using templates was a better option...even more so because most of the mods available are for template users although they can be modified to be used with site_html.pl

When you first install Links SQL it is very daunting compared to Links2 as there are many more files and it operates dfferently etc.......but once you get familiar with the script there is so much that can be done with it.

Paul Wilson.
new - http://www.wiredon.net

This forum is great!

That was exactly what I have been trying to do too. Thanks so much for sharing your knowledge with everyone, especially helpful to people like me who don't know perl at all.

I'm limiting my Featured list links to 3 links on the home page. What I would really like to do is have the routine pick 3 links at random to display.

That way, each time the home page is rebuilt, it will have three different people on it. Does anyone know how I would go about getting it to do that?

Thanks,

Bryan

Hi,

Something like:

should do the trick. What this does is find out the total number of validated links, then picks a random number from that total, and then selects the link. It does this 3 times and then returns the output.

This is untested, but should work.

Cheers,

Alex

--
Gossamer Threads Inc.

Just modify the line
$sth = $search_db->select ( {});
and in the brackets put your selection criteria such as Category => 'Name_of_Category'.

The Code didn't work, There is no Category field in Links table.

Regards

TheFox

Hi Alex,

Please Help!

regards

TheFox

Hi Alex,

Is there any way to specify the categories? I need to add links from a specify category into homepage ! Please Help.

Thanks

TheFox

Hi Alex,

Are you there???? Please Help!

regards

TheFox

You were told how to do this by afinlr.......

my $link_db = $DB->table('Links');
my $total = $link_db->count ( { isValidated => 'Yes' });
my $output;
for (1 .. 3) {
my $rand = int (rand() * $total);
$link_db->select_options ("LIMIT $rand, 1");
my $sth = $link_db->select ({ isValidated => 'Yes', Category => 'Your_category' });
my $link = $sth->fetchrow_hashref;
$output .= Links::SiteHTML::display ('link', $link);}
return $output;


Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Hi Paul,

It is not work!!

TheFox

A little more info please...........are there errors?....what type?...where did you add the code?

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Oh my bad.

You would need something like.....

my $cat_db = $DB->table('Category');
my $sth = $cat_db->select ({ Name => 'Your_category' });


Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Hi Paul,

The Full code please.

TheFox

You should try to do it yourself so you learn for next time.

You can't always take the easy route...but if you do, something is needed in return by the person giving ($)

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

I have try the code as follow

sub {
my ($showlinks,$sth,$link);
my $search_db = $DB->table('CatLinks','Links');
$search_db->select_options ('ORDER BY Links.Add_Date DESC','LIMIT 30');
$sth = $search_db->select ( { Links.isValidated => 'Yes' , Links.ID => CatLinks.LinkID ,CatLinks.CategoryID = '1'});
while ($link = $sth->fetchrow_hashref) {
$showlinks .= Links::SiteHTML::display ('link', $link);
}
return $showlinks;
}

But it's not work

Please Help

TheFox

The Error Message say:

Bareword "Links" not allowed while "strict subs" in use at (eval 13) line 6.
Bareword "Links" not allowed while "strict subs" in use at (eval 13) line 6.
Bareword "CatLinks" not allowed while "strict subs" in use at (eval 13) line 6.
Bareword "LinkID" not allowed while "strict subs" in use at (eval 13) line 6.
Bareword "CatLinks" not allowed while "strict subs" in use at (eval 13) line 6.
Bareword "CategoryID" not allowed while "strict subs" in use at (eval 13) line 6.

TheFox

You aren't following my example.....

Where did I mention this?


Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Hi Paul,why do you not help him?

You gave the advice with category => 'name'
but this couldn´t work, cause he tries to get data from Links;
then you advice to use the Cat table;
but this wont bring him the links;


I will take a look now to the old known code of 1.13; maybe i find something.

Robert

Uh well you tell me where the category column is in the Links table....

If he wants to select the links from a certain category then he can't use the LINKS table now can he!!

.....and I didn't tell him to use the Cat table, I suggested to use BOTH.

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

For the last 5 new on your homepage try this plz:

my ($sth, $root, $cat, $cat_list, $start_new, $total);

my $cat_db = $DB->table('Category');
my $link_db = $DB->table('Links');
$GRAND_TOTAL ||= _grand_total ($link_db);

my $output;
$link_db->select_options ("ORDER BY ID DESC LIMIT 5");
my $sth = $link_db->select ({ isNew => 'Yes', isValidated => 'Yes'});

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

Not all vas are needed, but its part of a bigger sub;
Important to understand are:

$link_db->select_options ("ORDER BY ID DESC LIMIT 5");

and


my $sth = $link_db->select ({ isNew => 'Yes', isValidated => 'Yes'});



Its the same like:

select from Links where isNew = 'Yes' and isValidate = 'Yes' Order by ID desc Limit 5

MEANS: Alle the field stuff to select; all the rest to select_options



Hope to have in the next minutes some specials like: only from this cat

then this must be done for every cat_page and we are through.

Robert

I don't see where you used this in the code....

my $cat_db = $DB->table('Category');



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

That´s in fact the problem, where i stuck at the moment:

My Query should be:

SELECT Links.*
FROM CatLinks, Links
WHERE CatLinks.LinkID = Links.ID
AND (CatLinks.CategoryID IN (114))

/* 114 is the cat i want the links from as an example.

I dont know how i can say Links:

From CatLinks, Links


If i understand your code right, you want:
-----------------------------------
my ($sth, $root, $cat, $cat_list, $start_new, $total);

my $cat_db = $DB->table('Category'); # <-------------
my $link_db = $DB->table('Links');
$GRAND_TOTAL ||= _grand_total ($link_db);

my $output;
$link_db->select_options ("ORDER BY ID DESC LIMIT 5");
my $sth = $link_db->select ({ isNew => 'Yes', isValidated => 'Yes'});

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

But that only says 'FROM Links', not both?

---

With:


my $cat_db = $DB->table('Category');
my $sth = $cat_db->select ({ Name => 'Your_category' });


You can get the CategoryID for a Category name; not more.


I want to have: get all links where some fields = value;
and the Category ID in CatLinks = CategoryName in category ;

With this you need three parts.

Without the name, using only the catID you need two steps only.

The problem is, i dont know how to say it to links cause Alex uses not the normal queries as in 1.13.

....

WIthout being able to figure out the problem here, and what's going on, the problems may be in the query and implicit joins.

To get the last 5 links, that were added to any category, on the category page, you'd want to do:

SELECT Links.*
FROM Links, CatLinks
WHERE Links.ID = CatLinks.LinkID
AND CatLinks.CategoryID = '33'
Order by Links.ID DESC Limit 5

If you are passing in the globals, you can make the '33' $input->{category_id} You can use it in the query:

SELECT Links.*,CatLinks.*
FROM Links, CatLinks
WHERE Links.ID = CatLinks.LinkID
AND CatLinks.CategoryID = $input->{category_id}
Order by Links.ID DESC Limit 5


You don't need to do dates, since the ID field is incremented, the newest links will always have a higher ID than the older ones.

If you are doing this in the "globals" just do a standard query, and put the results into a $return_value->{Tag_Name}

The <%Tag_Name%> would be a HASH .... whatever.

You can then use the <%loop%> variable to format the return values in the template itself, simplifying the overall code greatly.

I was asked to look in on this thread, but I'm not sure why.

I read the thread 3 times, and still don't get what the problem is, and why.


PUGDOG® Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Forum:http://LinkSQL.com/forum

Where does this code go? I tried adding it and got a compilation error.

Thanks
Alba