Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Importing to Links from DBMan SQL??

Quote Reply
Importing to Links from DBMan SQL??
I have a MySQL database table with 10 columns and 52 rows (that were created using DBMan SQL 2.1.0). This is pulled onto a single This Week in History .php page, which changes weekly based on absolute week number

Is there a straightforward way to import this data into a new or existing installation of Links 3.3.0 and build static pages for the whole year?

Thanks for any assistance.
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

The "easiest" way is to probably export the data from DBMan into a flat-file database (ideally a CSV), and then using my Data_Import plugin you should be able to import the data manually (just copy+paste the CSV, and tell it which fields should match up with what)

Alternatively, a script could be written which would directly connect to DBMan SQL, grab the data - and then pass it along to GLinks ready to be inserted (let me know if you'd like you find out more, as I could do that as a custom job)

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] Importing to Links from DBMan SQL?? In reply to
Thanks Andy. I just got your plugin which sounds as though it should do the trick.

As mentioned, this is for a This Week in History page - but I'd like to have all 52 weeks available to view at any time.

Assuming I set this up in a new installation of Links, running statically, should it be possible to get the current week to display automatically, either on the index page or any specific page. Would that need a plugin?
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

Cool, just saw your order - so will set that up for you once I've replied to this :)

Quote:
As mentioned, this is for a This Week in History page - but I'd like to have all 52 weeks available to view at any time.

Assuming I set this up in a new installation of Links, running statically, should it be possible to get the current week to display automatically, either on the index page or any specific page. Would that need a plugin?

If you are going to be adding a selection of "links" per well, the easiest way would be to just set them all with an Add_Date as the same date (i.e all those this week, would have an add date of today - and then next monday, they would have the 6th of June, etc. Then, a little global to grab each weeks records should work (Date::Calc should do the job - as we can easily get the start date for each week , which I believe is a sunday as standard)

Anyway, lemme know once you have your data in - and I'll take a look at a quick global for ya =)

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] Importing to Links from DBMan SQL?? In reply to
Cheers Andy. Will do Blush
Quote Reply
Re: [Andy] Importing to Links from DBMan SQL?? In reply to
I can't quite get my head around this one and now that I'm actually working on it I'm wondering if I explained it right.

I have a single This Week in History .php page at:
http://www.englishclub.com/thisweek.php
which pulls data from the database and changes automatically each week based on the absolute week number. There is one "page" for each week so there are 52 "pages" in the database, but only the current page/week is visible or accessible to visitors.

What I would like to achieve is convert it all to gLinks run statically and get the following:

  • A main This Week In History page which displays the current week, changing automatically each week, at: http://www.englishclub.com/this-week-in-history

  • A static "archive" of the 52 weeks indexed and totally accessible, something like:
    http://www.englishclub.com/this-week-in-history/Detailed/Archive/Mount_Everest_Is_Conquered_1.htm
    http://www.englishclub.com/this-week-in-history/Detailed/Archive/First_Flight_in_Space_2.htm

The archive seems fairly straightforward. My question really is can the main page be made to automatically display the current week? Does it require a weekly cronjob? Could it be made as a php page and pull the data from the database much as it does now? Or is a global still the way to go? Or something else...

Thanks as always.

Jo

Last edited by:

iplay: Jun 3, 2011, 1:45 AM
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

Ah ok - is there any reason you have it stored in DBMan SQL, and not just directly in GLinks? It would be much simpler if you simply had the data in a new custom table - something like:

Code:
Table: glinks_Weekly
this_week_id (INT , auto increment)
this_week_week_number (INT)
this_week_week_year (INT)
this_week_title (CHAR 255)
this_week_contents (TEXT)
Then you could use a little script to build up the archive of them: (totally untested Wink)

First create that table (create the table, and then copy a .def file to it, and do a "resync" to make the .def file correct - or use jdgambles "Tables" plugin, which is available from this forum)

Then copy the attached script to your server and CHMOD 755. Change the "path" and "url" configs near the top

Then make 2 blank templates called this_week_archive_list_entry.html and this_week_archive_list.html

In this_week_archive_list.html, just place this to start with (so you can see it working);

Code:
<ul>
<%loop archive_loop%>
<li><a href="<%URL%>"><%this_week_title%></a></li>
<%endloop%>
</ul>

..and in the this_week_archive_list_entry.html one, you should have access to all the tags that are from the table above

Again - totally untested ... but lemme know if you would prefer me just to do the whole job (if there are minor questions/bugs with the above code, I don't mind helping with that)

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] Importing to Links from DBMan SQL?? In reply to
Andy wrote:
Ah ok - is there any reason you have it stored in DBMan SQL, and not just directly in GLinks?

Absolutely, that is what I intend. It's just been in the DBMan SQL for years and I intend to move it lock, stock and barrel to a new GLinks installation.

But for the rest of you answer I am completely lost CrazyCrazyCrazy I mean, I don't understand it at all Smile Thanks for the time you took over it though. Are you talking about transferring the data or actually building the html pages?? Or maybe both?

I was thinking I could create the 52 pages etc as a normal GLinks installation using Detailed pages or even Categories (one for each week/story). And that would run static.

But before even getting that far, my real question is - how to have the front page changing each week so it displays the current week's story??

Last edited by:

iplay: Jun 3, 2011, 9:30 AM
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

NP

To be honest, its a bit hard to explain how to do it (I could probably knock up the codes/globals to do it in 30-45 mins, but trying to explain would take a hell of a lot longer Wink)

The code I gave you would actually build a static page with the "archive" list, and then also build the pages for the "this week" stuff that has already happened (and the current week), while leaving the future "this week" stuff until the cron runs on that particular week (the idea being, you can pre-fill it with as much data as you want, and it will only go live once that week number comes up)

For the front page - it would just be a simple global that would grab the record for the current week, and pass the values back into the home.html template 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!
Quote Reply
Re: [Andy] Importing to Links from DBMan SQL?? In reply to
Thanks Andy. I know what you mean about explaining vs. doing Wink

Does it make any difference that the weeks would be the same every year? I probably didn't explain that.

There are only 52 stories, one for each week, and they will recur every year.

So the 52 Detailed pages (and the links to them) could be built normally by GLinks.

For the front page - it would just be a simple global that would grab the record for the current week, and pass the values back into the home.html template
This sounds like what is needed, but how would that home page get "built" or "changed" every week? Crazy
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Ok, so what happens when you get to week 52? Or do you literally just wanna build the 52 pages, and then on the homepage, simply link to it?

Lemme know, and I'll see if I can come up with a simple plugin for ya tomorrow Wink (had a beer now - so probably not a good idea to try coding <G>)

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] Importing to Links from DBMan SQL?? In reply to
When we get to week 52 we go back to week 1 and start all over again. There would be a list of links to all 52 pages somewhere, probably on a category page. And the Main Page would just show whichever is the current week. Thus:
  • Main Page - shows current week Detailed Page

    • Category (Archives) - links to all 52 weeks

      • Week 1 Detailed Page
      • Week 2 Detailed Page
      • Week 3 Detailed Page
      • etc up to
      • Week 52 Detailed Page
Enjoy the beer Angelic
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

Aaah ok - this should probably do the trick then: (add it as a new global)

get_this_week_detailed_page
Code:
sub {

my ($yyyy,$mm,$dd) = split /\-/, GT::Date::date_get();

my @week_nums;
$week_nums[0] = undef; # should really start here - but thought it would be crearer like this :)
$week_nums[1] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[2] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[3] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[4] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[5] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[6] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[7] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[8] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[9] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[10] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[11] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[12] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[13] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[14] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[15] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[16] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[17] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[18] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[19] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[20] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[21] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[22] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[23] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[24] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[25] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[26] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[27] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[28] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[29] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[30] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[31] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[32] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[33] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[34] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[35] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[36] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[37] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[38] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[39] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[40] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[41] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[42] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[43] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[44] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[45] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[46] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[47] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[48] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[49] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[50] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[51] = "http://www.yoursite.com/Detailed/12343.html";
$week_nums[52] = "http://www.yoursite.com/Detailed/12343.html";

use Date::Calc;
my ($week_num,$year) = Week_of_Year($yyyy,$mm,$dd);

return $week_nums[$week_num];

}

This will return the current weeks detailed page (probably easier to create a new category in GLinks, and then add an entry for each of these pages). Alternativly, you could use my PageCreator plugin which will let you create those pages without placing them in the general categories. This would also give you more flexability over the general layout of the pages.

To call the plugin on the homepage, you would just use:

Code:
<a href="<%get_this_week_detailed_page%>">This week in the past...</a>

Hope that helps

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] Importing to Links from DBMan SQL?? In reply to
Many thanks Andy.

Is this going to:
  1. automatically display the current week's data (Title, Text etc) on the homepage
    OR
  2. display a link to the Detailed Page on the homepage
?
Quote Reply
Re: [iplay] Importing to Links from DBMan SQL?? In reply to
Hi,

That one would simply show a link to the detailed page. It wouldn't be hard to tweak it to actually grab the title as well though: (untested Wink)

Code:
sub {

my ($yyyy,$mm,$dd) = split /\-/, GT::Date::date_get();

my @week_nums;
$week_nums[0] = undef; # should really start here - but thought it would be crearer like this :)
$week_nums[1] = 12343;
$week_nums[2] = 12343;
$week_nums[3] = 12343;
$week_nums[4] = 12343;
$week_nums[5] = 12343;
$week_nums[6] = 12343;
$week_nums[7] = 12343;
$week_nums[8] = 12343;
$week_nums[9] = 12343;
$week_nums[10] = 12343;
$week_nums[11] = 12343;
$week_nums[12] = 12343;
$week_nums[13] = 12343;
$week_nums[14] = 12343;
$week_nums[15] = 12343;
$week_nums[16] = 12343;
$week_nums[17] = 12343;
$week_nums[18] = 12343;
$week_nums[19] = 12343;
$week_nums[20] = 12343;
$week_nums[21] = 12343;
$week_nums[22] = 12343;
$week_nums[23] = 12343;
$week_nums[24] = 12343;
$week_nums[25] = 12343;
$week_nums[26] = 12343;
$week_nums[27] = 12343;
$week_nums[28] = 12343;
$week_nums[29] = 12343;
$week_nums[30] = 12343;
$week_nums[31] = 12343;
$week_nums[32] = 12343;
$week_nums[33] = 12343;
$week_nums[34] = 12343;
$week_nums[35] = 12343;
$week_nums[36] = 12343;
$week_nums[37] = 12343;
$week_nums[38] = 12343;
$week_nums[39] = 12343;
$week_nums[40] = 12343;
$week_nums[41] = 12343;
$week_nums[42] = 12343;
$week_nums[43] = 12343;
$week_nums[44] = 12343;
$week_nums[45] = 12343;
$week_nums[46] = 12343;
$week_nums[47] = 12343;
$week_nums[48] = 12343;
$week_nums[49] = 12343;
$week_nums[50] = 12343;
$week_nums[51] = 12343;
$week_nums[52] = 12343;

use Date::Calc;
my ($week_num,$year) = Week_of_Year($yyyy,$mm,$dd);

my $rec = $DB->table('Links')->get( { ID => $week_nums[$week_num] } ) || {};
$rec->{detailed_url} = $CFG->{build_detail_url} . "/Detailed/" . $DB->table('Links')->detailed_url( $hit->{ID} );

return { 'this_week' => $rec };

}

Then call with:

Code:
<%get_this_week_detailed_page%>
This week "<a href="<%this_week.detailed_url%>"><%this_week.Title%></a>" happened... <%this_week.Description%>

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!