Postgress SQL

So I know almost nothing about SQL and I came across a project a work. I was originally going to set up an ODBC connection from a server running my company's software to a Postgress server. Unfortunately, we have had quite a few setbacks. We wrote a php script to help us export the data that we needed from the Postgress database, and it worked the way it was supposed to. The only problem was that it takes 45 minutes for a query to run. That seems a little over the top, so we did some research and it appears that the driver we have for the database(newest one we could find), takes the entire table that we are querying and pulls it across the network to the machine that initiates the query and runs the query then. I have had a few ideas and thought maybe someone could help me fine tune them. Is it possible to run the query on the Postgress server and automate it? How about a batch file? I know that it's a little archaic, but I'm getting desperate!!!!. The query taking 45 minutes wouldn't be such a big deal, except that the data is kind of time sensitive. My company has notification software for schools and the query is for period attendance. I will be happy to supply any information requested, as long as it doesn't break my "close your mouth" contract. HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

What driver are you using?

http://nl2.php.net/pgsql

Sounds like you might have some problems in your php code. There are always two ways to do things. One would be to bring the data accross, and manipulate it in php. The other is to manipulate it via a query on the SQL server and just bring back the required data. I would venture to guess you are doing the first.

You might suggest a code review of sorts to find out if the code is using the SQL server efficiently.

Per Sir Dice's request the driver is MERANT 3.6 32-BIT Progress SQL92v9.1D. That is how the driver is listed in the system DSN tab. Here is the code, this is just a test bc we still need to get a preffered connection before we decide the exact query.

<?

$out = "/datadump.csv";
$user = "username";
$pass = "?????????";
$dsn = "databasename";
// select the date we wish to export attendance for
$todaydate = date('Y-m-d');

// build the query
$query = 'select "STUDENT-ATND-DETAIL"."ATND-DATE", "STUDENT-ENTITY"."STUDENT-STATUS", "STUDENT-ENTITY"."STUDENT-ID", "STUDENT"."STUDENT-ID", "STUDENT-ATND-DETAIL"."AAT-ID" from PUB.STUDENT Join PUB."STUDENT-ATND-DETAIL" ON STUDENT."STUDENT-ID" = "STUDENT-ATND-DETAIL"."STUDENT-ID" Join PUB."STUDENT-ENTITY" ON STUDENT."STUDENT-ID" = "STUDENT-ENTITY"."STUDENT-ID" where "STUDENT-ENTITY"."STUDENT-STATUS" = ' . "'" . "A" . "' and " . 'PUB."STUDENT-ATND-DETAIL"."ATND-DATE" = ' . "'" . $todaydate . "'";
echo $query . "\r\n";

function andList ($row) {
$line = NULL;
for ($x = 0; $x < count($row); $x++) {
if ($line == NULL) {
$line = $row[$x];
} else if ( $x == (count($row) -1) ) {
$line .= " and " . $row[$x];
} else {
$line .= ", " . $row[$x];
}
}
return $line;
}

// connect to the DSN
echo "Connecting to DSN." . "\r\n";
if (!$dbconnect = odbc_connect($dsn, $user, $pass)) {
echo "Failed to connect!" . "\r\n";
} else {
echo "Connected to the DSN." . "\r\n";
}

// load and execute the query against the DSN
echo "Executing query." . "\r\n";
if (!$dbdata = odbc_exec($dbconnect, $query)) {
echo "Failed to execute query!" . "\r\n";
} else {
echo "Executed query." . "\r\n";
}

// open the output file
if (!$datafile = fopen($out, "w+")) {
echo "Failed to open output file" . "\r\n";
} else {
echo "Opened output file." . "\r\n";
}

// read the returned rows
while ($row = odbc_fetch_array($dbdata) ) {

// check against today's date
//if ($row["ATND-DATE"] != $todaydate) {
// continue;
//}

// get the attendance codes in an array
//$attcodes = explode(";", $row["ATT-ID"]);
//echo "\r\n";
//echo $row["ATT-ID"] . "\r\n";
//echo $attcodes . "\r\n";

// set up an empty array to proccess the attendance codes into
//$attperiods = array("");

// process the attendance codes into numbers only catching the U code
//for ($y = 1; $y < count($attcodes); $y++) {
// if (($attcodes[$y] != "") AND ($attcodes[$y] == "U")) {
// $attperiods[] = $y;
// }
//}
//echo $attperiods . "\r\n";

// format the attendance field
//$outperiods = andlist($attperiods);
//echo $outperiods . "\r\n";

// create a line for the output data
$tmpline = '"' . $row["ATND-DATE"];
$tmpline .= '","' . $row["STUDENT-ID"];
//$tmpline .= '","' . $outperiods;
$tmpline .= "\"\r\n";

// if there were no matching attendance codes skip the student
//if ($outperiods == NULL) {
// echo "-";
// continue;
//} else {
// echo "+";
//}

// write the output line
fwrite($datafile, $tmpline);

}
echo "\r\n";

// close the output file
fclose($datafile);
echo "Closed ouput file." . "\r\n";

?>

I left out the server information for obvious reasons. I would like to stress that I am anything but proficient with php and am in the process of trying to learn it. I didn't write this code, another guy from my company did, but am working with it to see about making it successful. Is there a way to automate the query on the machine? The user says that they have a nice query that runs quickly on the machine, but that there is no way to automate it bc you have to open the tool and run the query even though it is saved in a format similar to a data view.