Re: Feeds Integration - Mailing list pgsql-www

David,

    For GBorg specifically the news items are stored on the database. I
forget how it was setup for the main website to access the database
however I can provide a query that would give you the information you
are looking for so you can write a program. Having an RSS feed on GBorg
would be great too. If you have something I can you to generate the
files that would be great.

    Here is a query for getting the 10 latest news items for showing on
the main page of GBorg.

SELECT project_name,
       headline,
       body,
       submit_date   -- date not datetime :(
  FROM project_news, project
 WHERE (project_news.project_id=project.project_id)
   AND (project_news.active=true)
   AND (project_news.on_homepage=true)
 ORDER BY submit_date DESC
 LIMIT 10;


    I will leave issues of access to the GBorg database up to Marc.

Chris Ryan


--- David Costa <geeks@dotgeek.org> wrote:
> Hello All,
> I plan to move forward with a gborg and events feed. This as a nice
> addition of the existing one (beta version on dotgeek.org).
>
> Problem, for the beta news, I had to do a separate scripts to fetch
> the
> news because I don't have them in a database, then add these locally
> and fetch them
> for the XML feed.
>
> (how ?
>
> $handle = fopen("http://postgresql.dotgeek.org/", "r");
> $file_handler = "";
> while (!feof($handle))
>     $file_handler .= fgets($handle, 4096);
> if (!ereg("<!-- News -->(.+)<!-- Events -->", $file_handler, $news))
>     print "error";
> $news = str_replace("<tr>\n<td bgcolor=\"#6884A4\">\n<a
> name=\"news\">\n<font size=\"+2\"
> color=\"#FFFFFF\">News</font></a>\n</td>\n</tr>", "", $news[1]);
> $news = ereg_replace("(\n){3,}", "\n", $news);
> /*print $news;
> print "\n\n\n-------------------------------------------\n\n\n\n";*/
> $pattern = "<tr><td><a href=\"(news/[0-9]+\.html)\"
> style=\"font-weight: bold; font-size: medium; color: #020169\">";
> $pattern .= "([^(</)]+)</a><br>\n<i>Posted on ([0-9]+-[0-9]+-[0-9]+)
> by
> <font color=\"#020169\">([^(</)]+)</font>";
> $pattern .= "</i><br>\n([^(</)]+)</td></tr>\n";
> $rinfo = array();
> $i = 0;
> etc etc )
>
> Now, it would be useless to do the same exercise considered that you
> do
> have them in a database. So if I know the db schema, I could possibly
>
> have it to work locally
> or upload on my space on your server (thanks Marc !!) something ready
>
> to go.
>
> Ideally (this would work best for me) can someone move on my server
> space with you the tables Latest News (gborg) and (events) ? With
> these
> I can do a full test on my box and
> upload something ready to go.
>
> For the website integration, I can handle that too but..can I work on
>
> Alexey's beta ? This would allow me to add the feeds links without
> having to download
> the full site with a CVS checkout and upload something which will
> have
> in any case be modified for Alexey's  new layout.
>
> Of course if you want to implement the news feeds sooner, I could do
> a
> fresh checkout, upload, add the changes and leave it to you.
>
> Thanks again for your time and attention ;)
> Cheers
> David
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>       joining column's datatypes do not match


__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools