...

View Full Version : php and postgre calendar problems



duggins
12-15-2003, 07:27 PM
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";
}

raf
12-16-2003, 01:04 AM
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.

duggins
12-16-2003, 02:16 PM
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

raf
12-16-2003, 02:23 PM
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 ...

duggins
12-16-2003, 02:32 PM
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;

raf
12-16-2003, 02:59 PM
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

duggins
12-16-2003, 03:42 PM
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

raf
12-16-2003, 04:20 PM
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.

duggins
12-16-2003, 04:32 PM
worked like a charm :thumbsup:

many kudos to you sir

raf
12-16-2003, 04:43 PM
You're welcome. Glad you got it running :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum