PostgreSQL query many escape characters?

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PostgreSQL query many escape characters?

--------------000109020206030201070004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------000109020206030201070004--

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 . "
";
$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 . "
";
$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_s eq\"'),
$new_cont_rank, $new_cont_loc)";
echo "Third query: " . $query . "
";
$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
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PostgreSQL query many escape characters?

On May 1, 2009, at 3:59 PM, Carol Walter wrote:


It would be much easier to read and write like this:

$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);";


> 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.


What is the exact error message you are seeing?




John DeSoi, Ph.D.





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php