[postgis-users] Problems with PHP and PostgreSQL/PostGIS

[Obe, Regina]

Yap that was what I was saying that I thought you might have problems
with your date.  Sorry didn't notice the $datum declaration you had at
the top.
 
I think the way you format the date has to match with your PostGreSQL
date style or I think also the ISO date standard which is YYYY-MM-DD
 
and as I mentioned you need to quote your date field in your sql so your
sql should be
 
INSERT INTO fahrtenbuchtabelle(fid,date_start_fahrt,date_insert,strecke)
VALUES
($z_eintraege,'$datum','$datum',GeometryFromText('LINESTRING($breitengra
d1 $laengengrad1, $breitengrad $laengengrad)',4326))"; 
 
 
I also tend to go with the ISO date standard - less headache since it is
not ambiguous like the other formats.  So your $datum would be declared
 
$datum = date("Y-m-d", $timestamp);
 
Also since you renamed your table, make sure to change the entry in
geometry_columns table if it is not correct otherwise you may run into
issues displaying the data.
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
Sent: Friday, April 13, 2007 8:55 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Problems with PHP and PostgreSQL/PostGIS


I have renamed the tablename, but now there are other errors:

Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
column "date_start_fahrt" is of type date but expression is of type
integer at character 36 HINT: You will need to rewrite or cast the
expression. in C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on
line 103

Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
invalid input syntax for type numeric: "" in
C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on line 112
 

php Code:
 
    $timestamp=time();
    $datum=date("d/m/y",$timestamp);

    if($z_eintraege==2)
    {
       $sql_befehl="INSERT INTO
fahrtenbuchtabelle(fid,date_start_fahrt,date_insert,strecke) VALUES
($z_eintraege,$datum,$datum,GeometryFromText('LINESTRING($breitengrad1
$laengengrad1, $breitengrad $laengengrad)',4326))"; 
       $res=pg_query($connection,$sql_befehl);
       $sql_befehl_select="SELECT max(FID) as fid from
fahrtenbuchtabelle";
       $res_fid=pg_query($connection,$sql_befehl_select);
       $fahrtnummer=pg_result($res_fid,"fid"); 
     
    }
    if($z_eintraege>2)
    {
      $sql_befehl_append="UPDATE fahrtenbuchtabelle SET
strecke=AddPoint(strecke,GeomFromText('POINT($breitengrad
$laengengrad)',4326)) where fid='$fahrtnummer'"; 
      $result=pg_query($connection,$sql_befehl_append);
    }

Gerald


2007/4/13, Obe, Regina <robe.dnd at cityofboston.gov>: 

	When you created your table it appears you created it as
FahrtenbuchTabelle, but in your PHP sql, you don't have the table name
quoted, it will take what you have and insert into a table called
fahrtenbuchtabelle.  All table names that are not quoted in sql will be
lowercased when passed to the processor.
	 
	To minimize on headache, you really should rename your table in
PostgreSQL to fahrtenbuchtabelle.  Note that PostgreSQL table names are
case sensitive so FahrtenbuchTabelle  and fahrtenbuchtabelle are not the
same name.
	 
	Other possibly potential problem, I can't tell how you are
passing in your $datum date, but may need to be quoted.
	 
	Hope that helps,
	Regina

________________________________

	From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
	Sent: Friday, April 13, 2007 8:14 AM
	To: postgis-users at postgis.refractions.net
	Subject: [postgis-users] Problems with PHP and
PostgreSQL/PostGIS
	
	
	
	Hi everyone!
	
	I have created a table and now I want to put a GPS Logfile into
this table with a php file. But the php file doesn't do the inserts.
	
	Please can someone help me!
	
	
	The error message from the php file in firefox:
	
	Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
relation "fahrtenbuchtabelle" does not exist in
C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on line 103
	
	
	Here are the commands in the php file:
	
	    if($z_eintraege==2)
	    {
	       $sql_befehl="INSERT INTO
FahrtenbuchTabelle(FID,Date_Start_Fahrt,Date_Insert,Strecke) VALUES
($z_eintraege,$datum,$datum,GeometryFromText('LINESTRING($breitengrad1
$laengengrad1,    $breitengrad $laengengrad)',4326))"; 
	       $res=pg_query($connection,$sql_befehl);
	       $sql_befehl_select="SELECT max(FID) as fid from
FahrtenbuchTabelle";
	       $fahrtnummer=pg_query($connection,$sql_befehl_select);
	     
	    } 
	    if($z_eintraege>2)
	    {
	      $sql_befehl_append="UPDATE FahrtenbuchTabelle SET
Strecke=AddPoint(Strecke,GeomFromText('POINT($breitengrad
$laengengrad)',4326)) where FID='$fahrtnummer'"; 
	      $result=pg_query($connection,$sql_befehl_append);
	    }
	
	And there is my table in postgresql/postgis:
	
	CREATE TABLE "FahrtenbuchTabelle"
	(
	  "FID" numeric NOT NULL, 
	  "Date_Start_Fahrt" date NOT NULL,
	  "Date_Insert" date NOT NULL,
	  "Strecke" geometry,
	  CONSTRAINT "FahrtID" PRIMARY KEY ("FID"),
	  CONSTRAINT "enforce_dims_Strecke" CHECK (ndims("Strecke") =
2), 
	  CONSTRAINT "enforce_geotype_Strecke" CHECK
(geometrytype("Strecke") = 'LINESTRING'::text OR "Strecke" IS NULL),
	  CONSTRAINT "enforce_srid_Strecke" CHECK (srid("Strecke") =
4326) 
	) 
	WITHOUT OIDS;
	ALTER TABLE "FahrtenbuchTabelle" OWNER TO postgres;
	
	Already thankful for any ideas.
	

	

________________________________

	


	
	The substance of this message, including any attachments, may be
	confidential, legally privileged and/or exempt from disclosure
	pursuant to Massachusetts law. It is intended solely for the
	addressee. If you received this in error, please contact the
sender
	and delete the material from any computer.
	


	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net 
	http://postgis.refractions.net/mailman/listinfo/postgis-users
	
	


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070413/90cd6ccd/attachment.html>