Re: Feeds Integration - Mailing list pgsql-www

On Feb 19, 2004, at 6:32 PM, Chris Ryan wrote:

> David,
>

Hi Chris! ;)

>     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.

yes I am doing it, and I try to avoid the same str_replace stuff, so db
is the way to go.

> If you have something I can you to generate the
> files that would be great.
>

well I have it done, will work with any database data in principle.

>     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;
>
>

Umh, so what about the link ? is it part of the body ? if that is the
case it might be a problem, can solve that, just not the fast way to
produce the xml from db data.

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

I can do it without access to gborg.(In my previous email I was under
the impression that you had a separate table for news, but this is
obviously not the case)
Without db access I can still do  the script and add some quick
comments to set it up the right way.

For now the query is fine, but I need to know where the link is stored,
on which row.  for example I used something like this for the main news
array("title" => $row["title"], "link" => $row["url"], "description" =>
$row['news'], "author" => $row["author"] , "date" => date("d-M-Y",
$row["date"]));
see I keep on the beta database the url as a separate entity assigned
to the link field in the XML generation.


For link I mean the "click here for the full story." thing.

Cheers
David Costa

>
> 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 DEFANGED_bgcolor='\"#6884A4\"'>\n<a
>> name=\"news\">\n<font DEFANGED_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 DEFANGED_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