Page 1 of 1

php postgres issue

Posted: Thu May 11, 2006 4:44 am
by annie
Hi

Can anyone help me work out whats going wrong in this bit of code. The first $sqlcom always results in a "database error" while the second works fine. If I copy the sql commands from the web page to the database they both give tables of results without error. So why doesn't the first work through php?

The only difference being the joined table, one of which has an extra column to join on.

Code: Select all

$sqlcom="select vennexp.clus_id,$DB1,$DB2,$DB3 from vennexp natural join lib_counts_clus;";  ###fails
	#$sqlcom="select vennexp.clus_id,$DB1,$DB2,$DB3 from vennexp natural join lib_counts;";        ###works
	
	print "$sqlcom <br>";

	$venn = pg_exec($dbconn, $sqlcom);				#execute the command to search lib_counts and venn tables

	if ( ! $venn ) {
		print "database error <br>";
		echo "Error : " + pg_errormessage( $dbconn );
		exit(); 
	}

The database tables are...

Code: Select all

lumbribase=# \d lib_counts
           Table "public.lib_counts"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 clus_id    | character varying(10) |
 contig     | integer               |
 lc1        | integer               |
 jv1        | integer               |
 ade        | integer               |
 cd2        | integer               |
 pah        | integer               |
 at1        | integer               |
 che        | integer               |
 rs1        | integer               |
 cu2cf      | integer               |
 libraries  | integer               |
 total_ests | integer               |
 
lumbribase=# \d lib_counts_clus
         Table "public.lib_counts_clus"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 clus_id    | character varying(10) |
 lc1        | integer               |
 jv1        | integer               |
 ade        | integer               |
 cd2        | integer               |
 pah        | integer               |
 at1        | integer               |
 che        | integer               |
 rs1        | integer               |
 cu2cf      | integer               |
 libraries  | integer               |
 total_ests | integer               |
 
lumbribase=# \d vennexp
           Table "public.vennexp"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 clus_id | character varying(12) |
 contig  | integer               |
 db1     | double precision      |
 db2     | double precision      |
 db3     | double precision      |
 db4     | double precision      |
 db5     | double precision      |
 db6     | double precision      |
 db7     | double precision      |
 db8     | double precision      |
 db9     | double precision      |
 db10    | double precision      |
 db11    | double precision      |
 db12    | double precision      |
 db13    | double precision      |
 db14    | double precision      |
 
lumbribase=#
PHP5
postgres (PostgreSQL) 8.0.3

Thanks

Posted: Thu May 11, 2006 7:41 am
by annie
Oh! honestly.

I hadn't granted select permission to webuser on the lib_counts_clus table. :oops:

Sorry to have bothered you.