Re: [PHP-DB] PostgreSQL query many escape characters?

Okay,

Here are the queries I've written.  I gave up on trying to make  
pg_query_params() work but this is what I have using pg_query().  I  
have been printing the query.  In fact, I can take the query as  
constructed, paste it into psql and it runs just fine.

I would be grateful for any help you can give.  This program will have  
three other queries which are more complex that this one.



/* Build variables by concatenating with single quotes.  */

                                    $new_f_name_new = "'" .  
$f_name_new . "'";
    				   $new_m_name_new = "'" . $m_name_new . "'";
    				   $new_l_name_new = "'" . $l_name_new . "'";
    				   $new_ivl_web_peop = "'" . $ivl_web_peop . "'";
    				   $new_cns_web_peop = "'" . $cns_web_peop . "'";
    				   $new_cont_rank = "'" . $cont_rank . "'";
    				   $new_cont_loc = "'" . $cont_loc . "'";
    				   $new_contact_info1 = "'" . $contact_info1 . "'";
    				   $new_contact_type = "'" . $contact_type . "'";

   /* Execute queries to insert record in people table, contact table,  
and people - contact bridge table.  */

            		   	$query = "INSERT INTO \"tblPeople\"(\"fName\",\"mName 
\",\"lName\", ivlweb, cnsweb)
              		            	 VALUES ($new_f_name_new,  
$new_m_name_new, $new_l_name_new, $new_ivl_web_peop,  
$new_cns_web_peop);";
              			echo "First query: " . $query . "<br />";
               	  		$pg_peop_ins = pg_query($query);
               	  		echo pg_result_error($pg_peop_ins);
               	
               	  		 $query = "INSERT INTO \"tblContactInformation 
\"(\"contactItem\",\"contactType\")
              		             	VALUES ($new_contact_info1,  
$new_contact_type)";
              			echo "Second query: " . $query . "<br />";
               	   		$pg_contact_ins = pg_query($query) or die("Can't  
execute 2nd query");
               	   		$query = "INSERT INTO  
\"brdgPeopleContactInformation\" (\"peopleId\",\"contactInformationId 
\",rank, type)
               	                 	VALUES  
(currval('\"tblPeople_peopleId_seq 
\"'),currval('\"tblContactInformation_contactInformationId_seq\"'),  
$new_cont_rank, $new_cont_loc)";
               	    		echo "Third query: " . $query . "<br />";
               	    		$pg_peop_cont_ins = pg_query($query) or  
die("Can't execute 3rd query");

When I execute the code, the page has this...

First query: INSERT INTO "tblPeople"("fName","mName","lName", ivlweb,  
cnsweb) VALUES ('Frank', 'D', 'Oz', 't', 't');
Second query: INSERT INTO  
"tblContactInformation"("contactItem","contactType") VALUES ('[email protected] 
', '0010')
Can't execute 2nd query

Actually, neither query executes.

Thanks again.

Carol
               	
On May 1, 2009, at 2:53 PM, danaketh wrote:

> Hi,
>
>   you should post the INSERT query too. I'd recommend you to print  
> the query before running it. Just put echo $query; before/after  
> pg_query(). You'll see what are you sending to the database. This  
> may help you find the problem. Also using ` instead of " in query  
> may help you to make it easier to read.
>
> ------------------------
> Carol Walter napsal(a):
>> Hello,
>>
>> I am very new to this process, so please forgive me if this is a  
>> silly question.  I have a number of PostgreSQL tables that use the  
>> naming convention that capitalizes the first character of each  
>> word, therefor the data names require quotes.  When I put together  
>> my queries using parameters for the selected columns and the table  
>> names and use pg_query_params instead of pg_query to build the  
>> query?  Doing it without the parameterization looks like this...
>>
>> $query = "SELECT tp.\"peopleId\",\"fName\",\"mName\",\"lName\", 
>> \"contactItem\"
>>                     FROM \"tblPeople\" tp
>>                      LEFT JOIN \"brdgPeopleContactInformation\"  
>> bpci ON tp.\"peopleId\" = bpci.\"peopleId\"
>>                      LEFT JOIN \"tblContactInformation\" tci ON  
>> bpci.\"contactInformationId\" =
>>                                          tci.\"contactInformationId\"
>>                      WHERE (\"lName\" like '$l_name' AND \"fName\"  
>> IS NULL) OR (\"lName\" like '$l_name' AND \"fName\"
>>                                       LIKE '$f_name')
>>                      ORDER BY \"lName\",\"fName\",\"mName\"";
>>    $SciName = pg_query($query);
>>
>> This actually works, believe it or not, but my next query where I'm  
>> doing an insert to the tables does not.
>>
>> Thank you in advance for your time.
>>
>> Carol
>>
>>
>
> -- 
>
> S pozdravem
>
> Daniel Tlach
> Freelance webdeveloper
>
> Email: [email protected]
> ICQ: 160914875
> MSN: [email protected]
> Jabber: [email protected]
>