#!/usr/bin/perl
use DBI;
use strict;
use CGI qw/:standard/;
use CGI::Carp qw/fatalsToBrowser/;
use LWP::Simple;

	my $host = 'localhost';
	my $dbh;
	my $user = 'ruby';
	my $password = 'ru67';
	my $database = 'dolive';
	my $dsn = "DBI:mysql:database=$database;host=$host;port=3306";
	my @temp;
	my @pdfs;

	$dbh = DBI->connect($dsn, $user, $password) || die "Cannot connect: $DBI::errstr";
	$dbh->{RaiseError} = 1;

	my $sql = "SELECT id, SUBSTRING(title,1,16), placespdf FROM articles WHERE site=4 AND placespdf LIKE 'http%'";	
	my $sth = $dbh->prepare($sql);
	$sth -> execute;	
	while (@temp = $sth->fetchrow_array ()){
		push @pdfs, [@temp];	 #add each array reference to the news array
	}
	
	my $i;
	my $url;
	my $file;

	for ($i = 0; $i <= $#pdfs; $i++){
		print "retrieving pdf " . $i . "\n";
		$pdfs[$i][1] =~ s/ /_/g;
		$pdfs[$i][1] =~ s/\'//g;
		$url = $pdfs[$i][2];
		$file = $pdfs[$i][1] . ".pdf";
		getstore( $url, $file ); 

		$sql = "INSERT INTO media SET created=NOW(), type='pdf', filename='$file', contenttype='article', contentid=$pdfs[$i][0]";
		$sth = $dbh->prepare($sql);
		$sth -> execute;
	}

	
