Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Dec 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Code:
    			$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";
    					}

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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/curre...-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.

  • #3
    New Coder
    Join Date
    Dec 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    would the error be in here?
    Code:
    $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

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ...

  • #5
    New Coder
    Join Date
    Dec 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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;

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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/s...lease-7-0.html

  • #7
    New Coder
    Join Date
    Dec 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    New Coder
    Join Date
    Dec 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    worked like a charm

    many kudos to you sir

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're welcome. Glad you got it running


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •