php and postgre calendar problems

company taking over hosting for a school districts website, the original server is running
freebsd not sure what version
php not sure what version
postgre not sure what version

the new server is running
redhat 7.3
php newest i assume
postgre newest

here the php error there getting. do you think it could be because there running newer versions of postgre or php?

Warning: pg_exec(): Query failed: ERROR: Type "datetime" does not exist . in /home/gps/public_html/handlers/calendar.php on line 527

Warning: pg_numrows(): supplied argument is not a valid PostgreSQL result resource in /home/gps/public_html/handlers/calendar.php on line 528

no events for this date range were found.

Here are lines starting at 527 from the php file.


$result = pg_exec( $database, $exec );
if( pg_numrows( $result ) )
{
for( $count = 0; $count < pg_numrows( $result ); $count++ )
{
$row = pg_fetch_array( $result, $count );

$date = date( "m/d/Y", strtotime( $row[ "date" ] ) );

if( $date != $previous_date )
{
if( $previous_date )
{
print "</blockquote>\n";
}

print "<h4 style=\"border-bottom: 1px solid;\">" . date( "l, F d, Y", strtotime( $row[ "date" ] ) ) . "</h4>\n";

print "<blockquote>\n";
}

Welcome here

It's exactly what the first error says : PostGreSQL doesn't have a datetime type. You need a timestamp

http://www.postgresql.org/docs/current/static/datatype-datetime.html

The second error ws caused by the firstso doens't need attention.
By the way: the error is on line 527 buth what causes it (the sql-statement) is above that.

would the error be in here?

$constraint .= " ( date( datetime_start ) >= '$start_date' AND date( datetime_start ) <= '$end_date' )";

if( $constraint )
{
$exec .= $constraint;
}

$exec .= " ORDER BY date, time, name; " ;


have to excuse me, i'm not too good with php

put this

echo ('sql = ' . $exec) ;
die () ;

in front of the

$result = pg_exec( $database, $exec );

Then reload the file in your browser and look at the printed sql-statement. It will tell you which statement is executed.
Maybe then copy the statement there and post it here so that we can have a look at it.
It looks like a strange error for a selectstatement. I would expect such an error on a 'create' or 'alter' statement ...

sql = SELECT id, timestamp, author, school, school_name_by_id( school ) AS school_name, subject, calendar_subject_name_by_id( subject ) AS subject_name, visible, datetime_start AS datetime_start, ( datetime_end::datetime - datetime_start::datetime )::timespan AS duration, datetime_end, date( datetime_start ), datetime_start::time AS time, name, body, allday FROM calendar WHERE ( date( datetime_start ) >= '12/01/2003' AND date( datetime_start ) <= '12/31/2003' ) ORDER BY date, time, name;

these "::datetime" are the pronblem. Your typecasting to a non-existing type (datetime is depreciated from version 7 on).
I'm not a postgres expert, but change the
::datetime into ::timestamp

you'll probably also need to change the

::timespan into ::interval

http://www.postgresql.org/docs/7.4/static/release-7-0.html

i under what you mean, but the way this code it.. its trying to pull everything from the begining of the database i don't see any function to pull all times/dates.

here is the full file

if you wouldn't mind taking a look

What i mean is: change line 488 into:

$exec = "SELECT id, timestamp, author, school, school_name_by_id( school ) AS school_name, subject, calendar_subject_name_by_id( subject ) AS subject_name, visible, datetime_start AS datetime_start, ( datetime_end::timestamp - datetime_start::timestamp )::interval AS duration, datetime_end, date( datetime_start ), datetime_start::time AS time, name, body, allday FROM calendar";

and then try again.

worked like a charm :thumbsup:

many kudos to you sir

You're welcome. Glad you got it running :thumbsup: