Re: 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 ('foz@indiana.edu', '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: mail@danaketh.com ICQ: 160914875 MSN: danaketh@hotmail.com Jabber: danaketh@jabbim.cz