Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

Your answer in the part of SAP HANA (J)ODBC driver not supporting named
parameters actually makes sense. However I don't understand why would there
be a difference between PDO and regular odbc (odbc_connect, etc.) commands
since they're obviously using the same driver.

My testing machine is on Windows and when using raw odbc commands, I pass
in the driver parameter 'HDBODBC32'. If I use PDO, I have to pass in the
ODBC DSN, which is created in the Windows ODBC management tool. I double
checked and the driver in use is the same. So what bugs me is why would raw
odbc commands accept parameters with ? and $ notations but PDO doesn't
accept neither (well, query executes but doesn't return any results).

I'd like to try and debug the PDO module but unfortunately that's beyond my
knowledge and time frame for the moment :) Perhaps any advice on this,
debugging PDO? If nothing else, using PDO would at least unify the database
access accross my application and it does seem cleaner to use.

On Tue, Jan 26, 2016 at 3:50 PM, Lester Caine  wrote:

> On 26/01/16 19:06, Alko Kotalko wrote:
> > I've tried all the notations with PDO as well and none of them work with
> > SAP HANA. It works with MySQL though. So I presume that there is either a
> > bug in PDO driver or there is some mismatch between PDO and SAP HANA.
>
> Firebird does not support named parameters so we stick with ?
> parameters. Not sure on SAP HANA, but a quick google gives
> "The SAP HANA JDBC driver doesn't support named parameters" and I would
> anticipate that the ODBC driver probably has the same problem, but the
> simpler ordered array of parameters should work ...
>
> There WAS a document about just what combinations HAVE been tested and
> worked, but I'm not even sure now where it as created :(
>
> From the generic ODBC driver -
> http://php.net/manual/en/function.odbc-prepare.php ...
> Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
> stored procedures that accept parameters of type IN, INOUT, and OUT as
> defined by the ODBC specification. However, the Unified ODBC driver
> currently only supports parameters of type IN to stored procedures.
>
> It is some time since I used the ODBC interface, but I think PDO_odbc
> still uses the generic ODBC inerface and this does have it's own
> restrictons based on what platform you are using. It may be worth trying
> the generic ODBC interface and see if this works any differently. The
> ODBC driver uses the same style of working as the Firebird/Interbase
> driver for binding variables and that does work.
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 19:06, Alko Kotalko wrote:
> I've tried all the notations with PDO as well and none of them work with
> SAP HANA. It works with MySQL though. So I presume that there is either a
> bug in PDO driver or there is some mismatch between PDO and SAP HANA.

Firebird does not support named parameters so we stick with ?
parameters. Not sure on SAP HANA, but a quick google gives
"The SAP HANA JDBC driver doesn't support named parameters" and I would
anticipate that the ODBC driver probably has the same problem, but the
simpler ordered array of parameters should work ...

There WAS a document about just what combinations HAVE been tested and
worked, but I'm not even sure now where it as created :(

>From the generic ODBC driver -
http://php.net/manual/en/function.odbc-prepare.php ...
Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
stored procedures that accept parameters of type IN, INOUT, and OUT as
defined by the ODBC specification. However, the Unified ODBC driver
currently only supports parameters of type IN to stored procedures.

It is some time since I used the ODBC interface, but I think PDO_odbc
still uses the generic ODBC inerface and this does have it's own
restrictons based on what platform you are using. It may be worth trying
the generic ODBC interface and see if this works any differently. The
ODBC driver uses the same style of working as the Firebird/Interbase
driver for binding variables and that does work.

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

Oh ok, thanks for the clarification. Sorry for the noise.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



On Jan 26, 2016, at 1:07 PM, Alko Kotalko  wrote:

> It shouldn't be like that because I'm preparing a statement, which would 
> later have had parameters passed to. I'm not trying to concatenate a string.
> (Sorry, forgot to reply to all before)
> 
> On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers  
> wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
> 
> 
> Pardon me for asking, but shouldn't this line..
> 
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
> 
> be
> 
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
> 
> or like this
> 
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  
> "
> 
> ?
> 
> I have found that letting php create the string without the quotes and 
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my experience.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

It shouldn't be like that because I'm preparing a statement, which would
later have had parameters passed to. I'm not trying to concatenate a string.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers 
wrote:

> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
>
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any
> experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
>
>
> Pardon me for asking, but shouldn't this line..
>
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
>
> be
>
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
>
> or like this
>
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND
> col3='".$1."'  "
>
> ?
>
> I have found that letting php create the string without the quotes and
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my
> experience.
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

This (my code) actually works so it's not part of the problem. The problem
is that I can NOT use the colon notation for named parameters, even though
that's my goal :) If I use the colon notation with MySQL (PDO) it works
fine. With ODBC and SAP HANA database it doesn't. I have to use either ? or
$ notation.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 9:01 AM, Jeff  wrote:

> Greetings,
>
> On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> > On Jan 26, 2016, at 7:10 AM, Alko Kotalko 
> wrote:
> >
> >> Hi,
> >>
> >> I have a working connection from PHP to SAP HANA through PDO and regular
> >> ODBC commands.
> >>
> [...snipped...]
>
> >>
> >> For example:
> >> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
>
> the "$" character within a php string enclosed with quotes (") is
> special. you /might/ have some success if you enclose the string in
> single quotes (') instead. also personally I prefer named parameters
> (:foo) or even "foo=?" (question mark) rather than $1...n etc.
>
> [...snipped...]
>
> regards,
> J
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

I've tried all the notations with PDO as well and none of them work with
SAP HANA. It works with MySQL though. So I presume that there is either a
bug in PDO driver or there is some mismatch between PDO and SAP HANA.

The queries get executed but the returned result (if I fetch one) is an
associative array with empty values. If I fetch all, PHP runs out of memory
but the arrays in array are the same, keys without values.

On Tue, Jan 26, 2016 at 9:17 AM, Lester Caine  wrote:

> On 26/01/16 13:10, Alko Kotalko wrote:
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
>
> This is the key to your problem ...
>
> The PDO generic process does not support named parameters directly, so
> the ? format duplicating the multiple use of a named parameter is
> required ... if the driver actually supports that?
>
> Does your setup work if the SQL is
>
> $stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
> col3=?");
> $stmt->bindParam(1, $S1);
> $stmt->bindParam(2, $S2);
> $stmt->bindParam(3, $S1);
>
> On some drivers the named parameters have to be converted to '?' format,
> but I can't find the notes now on what combination works and what does't :(
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 13:10, Alko Kotalko wrote:
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.

This is the key to your problem ...

The PDO generic process does not support named parameters directly, so
the ? format duplicating the multiple use of a named parameter is
required ... if the driver actually supports that?

Does your setup work if the SQL is

$stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
col3=?");
$stmt->bindParam(1, $S1);
$stmt->bindParam(2, $S2);
$stmt->bindParam(3, $S1);

On some drivers the named parameters have to be converted to '?' format,
but I can't find the notes now on what combination works and what does't :(

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Jeff]

Greetings,

On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
>> Hi,
>>
>> I have a working connection from PHP to SAP HANA through PDO and regular
>> ODBC commands.
>>
[...snipped...]

>>
>> For example:
>> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.

the "$" character within a php string enclosed with quotes (") is
special. you /might/ have some success if you enclose the string in
single quotes (') instead. also personally I prefer named parameters
(:foo) or even "foo=?" (question mark) rather than $1...n etc.

[...snipped...]

regards,
J


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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:

> Hi,
> 
> I have a working connection from PHP to SAP HANA through PDO and regular
> ODBC commands.
> 
> The issue is that through PDO I can not get any prepared statements to
> work. None of the notations (?, $, :) work. The response from the server
> (fetch) gets me empty field values for all selected columns and if I try
> fetchAll the PHP script runs out of memory.
> 
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.
> 
> For example:
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> 
> However it is not ideal. I would like to know if anyone has any experience
> with SAP HANA and I can offer my help and participation in order to debug
> the possible problems with PDO<->HANA connectivity issues (in regards to
> prepared statements).
> 
> BR Aleš


Pardon me for asking, but shouldn't this line..

"SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"

be

"SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "

or like this

"SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  "

?

I have found that letting php create the string without the quotes and periods 
in a SQL statement can cause issues.
Don't ask me why it causes issues, I couldn't tell you :)  Just my experience. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


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

Re: [PHP-DB] PDO Connection problem

[Aziz Saleh]

On Fri, Jan 10, 2014 at 10:13 AM, Jim Giner wrote:

> History:
> I'm trying to help a friend who is hosting his domain with the same
> company that I use.  I've been using this company for several years and
> have used a certain 'connection' script all the time.  Part of it looks
> like this:
>
> $host="mysql:host=mydomain.com;dbname=$sc_dbname;charset=utf8";
> $uid = "uid";
> $pswd = "pswd";
> Try
> {
> $mysql = new PDO($host,$uid,$pswd,$db_options);
> }
> .
>
> So - when I tried to provide this template to my friend (who is new to all
> of this) we went thru days of emails trying to make sure everything was
> setup correctly but could never get a connection using the above code.
>  Finally last night, after reviewing how my 'old' mysql interface
> connection worked, I experimented with the above changing my host= from my
> domain name to simply 'localhost'.  Voila - it worked for him.  It also
> worked for my site.
>
> Here's my question:  What would make by friend's account not work when
> referencing a true domain name in the host= attribute?  I'm assuming that
> our (shared) provider is setting up his many accounts & servers the same
> way, but I could be wrong.  And of course, I don't have a clue about what
> makes any of this work - I simply follow instructions/guidance I get from
> manuals and searches until I get things to work.  That's how I got his
> account to finally work, but I'd love to have an idea why it now does.
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
It could be that your MySQL configs -bind-address and skip-networking are
set, so it is not allowing for external connections. My guess (could be
wrong) that using yourdomain.com is the same as making an external call, as
opposed to using localhost (no DNS lookup).

Re: [PHP-DB] PDO Exceptions

[Jim Giner]

On 4/25/2013 6:49 AM, Niel Archer wrote:

Greetings,

I am new to this list. I have a question about which functions need to be 
included in a try block.

Of "new PDO", "prepare", "bindParam", "execute", "fetch", and "query", it seems 
that bindParam is the only one that throws an exception. So is this the only that needs to be put into a try block?

Thanks,

-KJW
Proverbs 3:5-6


Are you aware that there is a setting to control whether PDO objects throw
exceptions, warnings, or nothing? By default it does nothing except set
the error code and message.

See http://uk3.php.net/manual/en/pdo.error-handling.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk


And I learn something new today too!

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

Re: [PHP-DB] PDO Exceptions

[Niel Archer]

> Greetings,
> 
> I am new to this list. I have a question about which functions need to be 
> included in a try block. 
> 
> Of "new PDO", "prepare", "bindParam", "execute", "fetch", and "query", it 
> seems that bindParam is the only one that throws an exception. So is this the 
> only that needs to be put into a try block?
> 
> Thanks,
> 
> -KJW
> Proverbs 3:5-6

Are you aware that there is a setting to control whether PDO objects throw
exceptions, warnings, or nothing? By default it does nothing except set
the error code and message.

See http://uk3.php.net/manual/en/pdo.error-handling.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk


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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/25/2013 10:56 AM, Jim Giner wrote:

On 1/24/2013 7:07 PM, Matt Pelmear wrote:

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script)
into all of my possible folders under my web root.  NOt exactly an
elegant solution, but with the script, easy to maintain.



Honestly, you would be better off just putting those 6 overrides into
your common include file.
I'm curious why would you do that as opposed to just putting it in the
webroot with a single .htaccess file (or changing the global php.ini)?

I don't have access to the global ini file.  As for .htaccess - I
thought your previous (?) said I would need to have multiple of them also.
Upon further research - I find that my domain has .htaccess files 
throughout the entire directory structure - not by my doing.  So I'm 
guessing that would mean having my individual php settings applied to each?


I think that I'll stay with a php.ini override file in each of my 
executable folders - now that I have the script necessary to easily 
apply new versions from my master one when needed.


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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 7:07 PM, Matt Pelmear wrote:

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script)
into all of my possible folders under my web root.  NOt exactly an
elegant solution, but with the script, easy to maintain.



Honestly, you would be better off just putting those 6 overrides into
your common include file.
I'm curious why would you do that as opposed to just putting it in the
webroot with a single .htaccess file (or changing the global php.ini)?
I don't have access to the global ini file.  As for .htaccess - I 
thought your previous (?) said I would need to have multiple of them also.


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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script) 
into all of my possible folders under my web root.  NOt exactly an 
elegant solution, but with the script, easy to maintain.




Honestly, you would be better off just putting those 6 overrides into 
your common include file.
I'm curious why would you do that as opposed to just putting it in the 
webroot with a single .htaccess file (or changing the global php.ini)?


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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 6:09 PM, Matt Pelmear wrote:

On 01/24/2013 01:34 PM, Jim Giner wrote:


If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

It sounds like I'll have to modify multiple htaccess files - which is
about the same as this php.ini problem - having multiples perhaps.

I've been working fine with a php.ini that merges onto the "full" ini
file.  Suddenly this new appl is not seeing the overrides.

Any info you can point me to about how php.ini files work?  The manual
doesn't get very specific about it.



You would only have to modify multiple .htaccess files if you have
multiple document roots that need this change applied to them.
Otherwise just create/edit the .htaccess file in the document root for
the site you want the change applied to.
.htacess isn't always read by Apache. Some configurations tell it to
read or not read that file, so you may need to check that as well.

phpinfo() will tell you which php.ini file is being used-- if you edit
the one it says is being used you should see a change unless something
else is overriding the setting (like an .htaccess file or your code).

-Matt

I took my 6 line override php.ini file and replicated (with a script) 
into all of my possible folders under my web root.  NOt exactly an 
elegant solution, but with the script, easy to maintain.


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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 6:03 PM, Matt Pelmear wrote:

On 01/24/2013 01:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post



Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind
things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle
on it.

I'm writing out to my page an input tag with the following
value in it:

49'ers

I can confirm it by using my browser's "view source" to see
that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'
vars my
debugging steps are showing that the var $_POST['team']
contains the
above value with a backslash (\) already inserted. This is
causing me
a problem when I then try to use pdo->quote to safely encode it
for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.
Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in my
webroot folder. I was under the impression that the overrides would
be applied to all folders below my webroot, but apparently it is not
happening.

How do 'pros' replicate their .ini settings thru all of the
application folder?  Not thru settings within the scripts I hope - I
thought I read that the was not a very efficient way to do it and
that a php.ini file was the best since it would be merged with the
master one installed by my hoster.



Jim,

Personally I rarely have the need to override the php.ini settings
for a particular host on a server. (Granted I never work in shared
servers)
Assuming you are using Apache and the standard module configuration,
you can use .htaccess files to override many settings from php.ini

Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want
to read through the comments here, too)
http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or
doing a require_once() of your config changes?, or see the FastCGI
example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

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




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on
sanitizing yourself.

if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself
as it will make you vulnerable.
But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




You shouldn't be sanitizing using addslashes() regardless, or using
magic_quotes_gpc at all really, if you can help it.
See: http://php.net/manual/en/security.magicquotes.whynot.php
(magic_quotes was deprecated because it is bad.)

-Matt


And I'm not.

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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 4:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post



Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind
things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on
it.

I'm writing out to my page an input tag with the following value
in it:

49'ers

I can confirm it by using my browser's "view source" to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'
vars my
debugging steps are showing that the var $_POST['team'] contains
the
above value with a backslash (\) already inserted. This is
causing me
a problem when I then try to use pdo->quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.
Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in my
webroot folder. I was under the impression that the overrides would
be applied to all folders below my webroot, but apparently it is not
happening.

How do 'pros' replicate their .ini settings thru all of the
application folder?  Not thru settings within the scripts I hope - I
thought I read that the was not a very efficient way to do it and
that a php.ini file was the best since it would be merged with the
master one installed by my hoster.



Jim,

Personally I rarely have the need to override the php.ini settings for
a particular host on a server. (Granted I never work in shared servers)
Assuming you are using Apache and the standard module configuration,
you can use .htaccess files to override many settings from php.ini

Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want to
read through the comments here, too)
http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing
a require_once() of your config changes?, or see the FastCGI example
here: http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

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




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
 $var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on
sanitizing yourself.

if (get_magic_quotes_gpc()) {
 $var = stripslashes($var);
 //do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself
as it will make you vulnerable.
But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


Not the problem.  I have other overrides that I need to include somehow.

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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 01:34 PM, Jim Giner wrote:


If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt
It sounds like I'll have to modify multiple htaccess files - which is 
about the same as this php.ini problem - having multiples perhaps.


I've been working fine with a php.ini that merges onto the "full" ini 
file.  Suddenly this new appl is not seeing the overrides.


Any info you can point me to about how php.ini files work?  The manual 
doesn't get very specific about it.




You would only have to modify multiple .htaccess files if you have 
multiple document roots that need this change applied to them.
Otherwise just create/edit the .htaccess file in the document root for 
the site you want the change applied to.
.htacess isn't always read by Apache. Some configurations tell it to 
read or not read that file, so you may need to check that as well.


phpinfo() will tell you which php.ini file is being used-- if you edit 
the one it says is being used you should see a change unless something 
else is overriding the setting (like an .htaccess file or your code).


-Matt


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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 01:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind 
things into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle 
on it.


I'm writing out to my page an input tag with the following 
value in it:


49'ers

I can confirm it by using my browser's "view source" to see 
that is

exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' 
vars my
debugging steps are showing that the var $_POST['team'] 
contains the
above value with a backslash (\) already inserted. This is 
causing me
a problem when I then try to use pdo->quote to safely encode it 
for

updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would 
be applied to all folders below my webroot, but apparently it is not 
happening.


How do 'pros' replicate their .ini settings thru all of the 
application folder?  Not thru settings within the scripts I hope - I 
thought I read that the was not a very efficient way to do it and 
that a php.ini file was the best since it would be merged with the 
master one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings 
for a particular host on a server. (Granted I never work in shared 
servers)
Assuming you are using Apache and the standard module configuration, 
you can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want 
to read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may 
need to consider other options (changing the setting globally or 
doing a require_once() of your config changes?, or see the FastCGI 
example here: 
http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

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




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on 
sanitizing yourself.


if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself 
as it will make you vulnerable.

But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




You shouldn't be sanitizing using addslashes() regardless, or using 
magic_quotes_gpc at all really, if you can help it.
See: http://php.net/manual/en/security.magicquotes.whynot.php 
(magic_quotes was deprecated because it is bad.)


-Matt


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

Re: [PHP-DB] pdo ?

[Karl DeSaulniers]

On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind  
things into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle  
on it.


I'm writing out to my page an input tag with the following  
value in it:


49'ers

I can confirm it by using my browser's "view source" to see  
that is

exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'  
vars my
debugging steps are showing that the var $_POST['team']  
contains the
above value with a backslash (\) already inserted. This is  
causing me
a problem when I then try to use pdo->quote to safely encode  
it for

updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.  
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in  
my webroot folder. I was under the impression that the overrides  
would be applied to all folders below my webroot, but apparently it  
is not happening.


How do 'pros' replicate their .ini settings thru all of the  
application folder?  Not thru settings within the scripts I hope -  
I thought I read that the was not a very efficient way to do it and  
that a php.ini file was the best since it would be merged with the  
master one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings  
for a particular host on a server. (Granted I never work in shared  
servers)
Assuming you are using Apache and the standard module configuration,  
you can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want  
to read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you  
may need to consider other options (changing the setting globally or  
doing a require_once() of your config changes?, or see the FastCGI  
example here: http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

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




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on  
sanitizing yourself.


if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself  
as it will make you vulnerable.

But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


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

Re: [PHP-DB] pdo ?

[Jim Giner]

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt
It sounds like I'll have to modify multiple htaccess files - which is 
about the same as this php.ini problem - having multiples perhaps.


I've been working fine with a php.ini that merges onto the "full" ini 
file.  Suddenly this new appl is not seeing the overrides.


Any info you can point me to about how php.ini files work?  The manual 
doesn't get very specific about it.


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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things 
into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value 
in it:


49'ers

I can confirm it by using my browser's "view source" to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' 
vars my

debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is 
causing me

a problem when I then try to use pdo->quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would be 
applied to all folders below my webroot, but apparently it is not 
happening.


How do 'pros' replicate their .ini settings thru all of the 
application folder?  Not thru settings within the scripts I hope - I 
thought I read that the was not a very efficient way to do it and that 
a php.ini file was the best since it would be merged with the master 
one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings for a 
particular host on a server. (Granted I never work in shared servers)
Assuming you are using Apache and the standard module configuration, you 
can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want to 
read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may 
need to consider other options (changing the setting globally or doing a 
require_once() of your config changes?, or see the FastCGI example here: 
http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear  wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's "view source" to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo->quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...


Matt & Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would be 
applied to all folders below my webroot, but apparently it is not happening.


How do 'pros' replicate their .ini settings thru all of the application 
folder?  Not thru settings within the scripts I hope - I thought I read 
that the was not a very efficient way to do it and that a php.ini file 
was the best since it would be merged with the master one installed by 
my hoster.


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

Re: [PHP-DB] pdo ?

[Richard Quadling]

On 24 January 2013 17:48, Matt Pelmear  wrote:
> On 01/24/2013 09:23 AM, Jim Giner wrote:
>>
>> On 1/24/2013 12:05 PM, Matt Pelmear wrote:
>>>
>>>
>>> http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post
>>>
>>>
>>> Every pro has this feature (magic_quotes_gpc) turned off. If you
>>> understand SQL Injection vulnerabilities, and properly bind things into
>>> your queries, I would recommend disabling it.
>>>
>>> -Matt
>>>
>>> On 01/24/2013 08:55 AM, Jim Giner wrote:

 ok - new to using pdo functions, but I thought I had a handle on it.

 I'm writing out to my page an input tag with the following value in it:

 49'ers

 I can confirm it by using my browser's "view source" to see that is
 exactly how it exists in the page.

 When I hit a submit button and my script retrieves the 'post' vars my
 debugging steps are showing that the var $_POST['team'] contains the
 above value with a backslash (\) already inserted. This is causing me
 a problem when I then try to use pdo->quote to safely encode it for
 updating my sql database.

 My question is - why does the POST var show the \ char before I
 execute the 'quote' function?

>>>
>> You're right!  But I must not understand something.
>>
>> My root folder has a php.ini file with the magic quotes set off. Doesn't
>> that carry on down to folders beneath it?
>>
>
> I would check phpinfo() to see if it is being overridden.
>
> -Matt

Create an info.php file containing ...

http://e-e.com/M_248814.html
Zend : http://bit.ly/9O8vFY

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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 09:23 AM, Jim Giner wrote:

On 1/24/2013 12:05 PM, Matt Pelmear wrote:
http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's "view source" to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo->quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't that carry on down to folders beneath it?




I would check phpinfo() to see if it is being overridden.

-Matt


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

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 12:05 PM, Matt Pelmear wrote:

http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's "view source" to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo->quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't that carry on down to folders beneath it?


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

Re: [PHP-DB] pdo ?

[Matt Pelmear]

http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post

Every pro has this feature (magic_quotes_gpc) turned off. If you 
understand SQL Injection vulnerabilities, and properly bind things into 
your queries, I would recommend disabling it.


-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's "view source" to see that is 
exactly how it exists in the page.


When I hit a submit button and my script retrieves the 'post' vars my 
debugging steps are showing that the var $_POST['team'] contains the 
above value with a backslash (\) already inserted. This is causing me 
a problem when I then try to use pdo->quote to safely encode it for 
updating my sql database.


My question is - why does the POST var show the \ char before I 
execute the 'quote' function?





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

Re: [PHP-DB] PDO ?

[Kapu®]

On 15. 1. 2013 18:25, Jim Giner wrote:

Doing some conversion - looking for a solution.

Currently I do something like this:

while (list($var1,$var2) = mysql_fetch_array($qrslts))
{
handle the vars
}

Is there something in the PDO functions that emulates this same 
ability?  Some of my uses of the sql syntax have many more vars and I'm
trying to avoid having to move the fetch-ed fields one at a time to 
vars that I wish to use.

I already tried:

list($var1,$var2) = $qrslts->fetch(PDO::FETCH_ASSOC);

this does not populate the vars.


Hi,

using PDO::FETCH_NUM instead PDO::FETCH_ASSOC should do the job.

Kapu

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

Re: [PHP-DB] PDO user question

[Matt Pelmear]

Brandon: RAM is cheap, but not always expendable depending on what 
you're doing.
Using count() on results from PDOStatement::fetchAll() is fine on small 
result sets.
Keep in mind that this will certainly blow up on very large result sets, 
especially depending on your memory_limit for php. Avoiding this problem 
is the primary reason I use prepared statements for retrieving data.


If you're working with small result sets you could avoid using prepared 
statements altogether, which would greatly simplify doing something like 
getting a row count.


If you don't absolutely need your code to be portable across multiple 
database servers, I would encourage you to experiment with the 
rowCount() method and see if it works for your setup. (I ran a quick 
test in my mysql environment and it worked fine for a SELECT statement. 
Sqlite doesn't seem to support it because the actual sqlite library 
doesn't support it. Haven't looked into postgres or others.)


Another thing to be aware of (at least with mysql), and speaking to 
Lester's point, is that you can use SQL_CALC_FOUND_ROWS (with mysql, 
anyway) to get a total count when you are using LIMITs on your result 
set (which would be advisable if you're returning these results in a web 
interface).


-Matt

On 09/09/2012 02:40 PM, brandon wrote:
That's how I'd do it. Extend the PDO interface on your abstract class 
to include a "num_rows()" method that utilizes that higher level 
count($this->result). It might be a little more overhead... but RAM is 
cheap... and there's always forking/extending the library in C/C++...


-Brandon

On 2012-09-09 11:49, Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize
PDOStatement::FetchAll() and then do a count() on that result set.

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort  
wrote:



Hi Jim

I've had some success with querying using pdo and prepared 
statements as

well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by a
Select?  The documentation is very clear that PDO doesn't return that
value for a Select statement (depending upon driver?) and there were a
couple of solutions that made no sense to me.  There was even one that
did a completely separate query just to get the row count which makes
even less sense.


I believe you are referring to "SELECT COUNT(*)..."
Because I couldn't find a different way I use that.

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







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

Re: [PHP-DB] PDO user question

[Lester Caine]

Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize 
PDOStatement::FetchAll() and then do a count() on that result set.


There are two things to bear in mind here. Often you are only displaying a 
subset of records - ten per page perhaps - and so a count of the total helps to 
populate the navigation tools. COUNT(*) is not the most efficient way of doing 
that when dealing with large numbers of records, and so keeping a table with a 
set of counters that are populated via triggers on insert and delete help to 
speed up that process. FetchAll() should then always produce a full set of 
records except for the last page ...


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk



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

Re: [PHP-DB] PDO user question

[brandon]

That's how I'd do it. Extend the PDO interface on your abstract class 
to include a "num_rows()" method that utilizes that higher level 
count($this->result). It might be a little more overhead... but RAM is 
cheap... and there's always forking/extending the library in C/C++...


-Brandon

On 2012-09-09 11:49, Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize
PDOStatement::FetchAll() and then do a count() on that result set.

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort  
wrote:



Hi Jim

I've had some success with querying using pdo and prepared 
statements as

well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by 
a
Select?  The documentation is very clear that PDO doesn't return 
that
value for a Select statement (depending upon driver?) and there 
were a
couple of solutions that made no sense to me.  There was even one 
that
did a completely separate query just to get the row count which 
makes

even less sense.


I believe you are referring to "SELECT COUNT(*)..."
Because I couldn't find a different way I use that.

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




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

Re: [PHP-DB] PDO user question

[Michael Stowe]

How are you using the number?  Probably the easiest way is to utilize 
PDOStatement::FetchAll() and then do a count() on that result set. 

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort  wrote:

> Hi Jim
> 
>> I've had some success with querying using pdo and prepared statements as
>> well.  One thing that I'm curious about is
>> 
>> How does one handle the need to get the number of rows returned by a
>> Select?  The documentation is very clear that PDO doesn't return that
>> value for a Select statement (depending upon driver?) and there were a
>> couple of solutions that made no sense to me.  There was even one that
>> did a completely separate query just to get the row count which makes
>> even less sense.
> 
> I believe you are referring to "SELECT COUNT(*)..."
> Because I couldn't find a different way I use that.
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

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

Re: [PHP-DB] PDO user question

[Stefan Wixfort]

Hi Jim


I've had some success with querying using pdo and prepared statements as
well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by a
Select?  The documentation is very clear that PDO doesn't return that
value for a Select statement (depending upon driver?) and there were a
couple of solutions that made no sense to me.  There was even one that
did a completely separate query just to get the row count which makes
even less sense.


I believe you are referring to "SELECT COUNT(*)..."
Because I couldn't find a different way I use that.

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

Re: [PHP-DB] PDO Vs MySQLi Vs SQL Queries.

[Vinay Kannan]

Hi Trevor,

Thank You for your suggestion, yeah i think caching is something i
definitely want to look at and something thats important, and there is a
big project coming up, its pretty much the biggest i've taken up so far,
theres also a mobile app for android which needs to be done, so caching
definitely is something i wanna look at.

I've also downloaded RedBean, will play around on the same, looks Cool ! :)

Thanks,
Vinay

On Tue, Feb 28, 2012 at 4:31 PM, Donahue Trevor wrote:

> Hi Vinay,
> No, using native mysql_* statement isn't always faster. Advantages are
> obvious, as mentioned in the article you shared (object mapping, security,
> performance), there's always things like caching and other stuff you don't
> want to reinvent the wheel for and on big projects tend to be really
> useful. So imho it's better using things like pdo.
> Another thing... try using ORM http://redbeanphp.com sliced my dev type
> in 4 :)
>
> On Tue, Feb 28, 2012 at 6:22 AM, Vinay Kannan  wrote:
>
>> Hey Guys,
>>
>> I came across this article.
>>
>> http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/
>>
>> Though I've been working with PHP for over 2 yrs, i never bothered to use
>> the techniques mentioned in the articles like this.
>> What i do for DB connections and queries..is just simple sql queries in
>> php,
>> $sql="{sql query}";
>> $runsql=mysql_query($sql);
>>
>> What i do take care is to clean the user inputs before querying the DB.
>>
>> Is this a good way? Coz i've seen more experienced programmers using the
>> techniques mentioned in the article, with PDOs, MySQLi etc...
>>
>> I always thought there is a chance that these might slow the querying
>> process?
>>
>> Please advice.
>>
>> Any help is appreciated.
>>
>> Thanks,
>> Vinay Kannan.
>>
>
>

Re: [PHP-DB] PDO Vs MySQLi Vs SQL Queries.

[Donahue Trevor]

Hi Vinay,
No, using native mysql_* statement isn't always faster. Advantages are
obvious, as mentioned in the article you shared (object mapping, security,
performance), there's always things like caching and other stuff you don't
want to reinvent the wheel for and on big projects tend to be really
useful. So imho it's better using things like pdo.
Another thing... try using ORM http://redbeanphp.com sliced my dev type in
4 :)

On Tue, Feb 28, 2012 at 6:22 AM, Vinay Kannan  wrote:

> Hey Guys,
>
> I came across this article.
>
> http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/
>
> Though I've been working with PHP for over 2 yrs, i never bothered to use
> the techniques mentioned in the articles like this.
> What i do for DB connections and queries..is just simple sql queries in
> php,
> $sql="{sql query}";
> $runsql=mysql_query($sql);
>
> What i do take care is to clean the user inputs before querying the DB.
>
> Is this a good way? Coz i've seen more experienced programmers using the
> techniques mentioned in the article, with PDOs, MySQLi etc...
>
> I always thought there is a chance that these might slow the querying
> process?
>
> Please advice.
>
> Any help is appreciated.
>
> Thanks,
> Vinay Kannan.
>

Re: [PHP-DB] PDO + The Role of PHP + SQLite - Am I missing something?

[Niel Archer]

> Hi List,
> 
> It seems that I'm a little confused (and thus frustrated) with PDO in
> general, and especially when it comes to PECL php_sqlite3.
> 
> I understand that PDO is a data-access abstraction layer, and as one, is
> really good. However, the way I see it, is that PDO should just be an added
> extra, and not the stock-standard for data-access. It should simply wrap a
> set of low-level libraries that are still exposed to developers should they
> choose not to use PDO (for whatever reason). While this is true for most
> databases, how long will it be until the current low-level libraries like
> PECL php_mysql and PECL php_mysqli are no longer maintained, and we are
> forced to use PDO?
> 
> This can be seen with the PECL php_sqlite extension, which is not maintained
> anymore. I understand why it's not maintained anymore, because there is PECL
> php_sqlite3. The problem here is that PECL php_sqlite3 only offers an OOP
> approach, and no procedural approach. So, for developers that want to
> develop their own data abstraction layers (access or model), they have to
> now wrap the PECL  php_sqlite3 SQLite3 set of classes. This makes the entire
> layer slower, more complex, and therefore more error-prone. Most developers
> are forced to use PDO for SQLite 3 access (due to PHP versions or other
> reasons), which is still a set of classes that bloat any further abstraction
> they may choose to develop.
> 
> The following posting shows the unnecessary confusion on this topic:
> 
> http://lists.macosforge.org/pipermail/macports-users/2009-January/013658.htm
> l

Most of the confusion there is based on misunderstandings, I would say.

> Confusion like this has led to the development of an SQLite 3 procedural
> extension:
> 
> http://php-sqlite3.sourceforge.net/pmwiki/pmwiki.php - not to be confused
> with the current PECL php_sqlite3 (dash vs underscore)
> 
> But surely if the PECL php_sqlite3 extension has already been developed and
> included in the default distributions of PHP, it must be possible to include
> a procedural interface as well? How long will it be before we are forced to
> use bloated objects for everything we need to do in PHP?

When no procedural developers step up and write the extensions. No point
complaining that some developers with an interest in an OOP
implementation of an OOP library took the time to do the work.

>Isn't that the
> great thing about PHP? That at its core it's a great, fast scripting engine
> with a few core libraries, and then everything else is implemented through
> extensions?

> I fully understand the power that OOP offers, and I understand that is a
> vital part to the maturity of PHP as a *language*, but I just can't see why
> the speed and elegance of interfacing with low-level extensions using
> procedural methods should be completely dropped. For me, as a developer, I
> have enjoyed the freedom (of choice and ability) that PHP offers - the
> ability to create the next best data-abstraction layer, or MVC framework.
> Why should these important innovations be made more difficult by forcing the
> use of bloated libraries

Something of an exaggeration. As the sqlite3 library has an OOP API, it's
more likely a procedural interface would be adding the bloat, if any.

>- simply because they adhere to strict OOP best
> practices, and ultimately force developers to create their own extensions,
> when it takes less time and work to expose the low-level API like the one
> already wrapped by PECL php_sqlite3.

Not true, as noted above, SQLite3 uses an OOP API at low-level

> Wouldn't it provide developers with more opportunity if the current PECL
> php_sqlite3 extension was dropped in favour of PDO, and replaced with a more
> low-level library like PECL php_sqlite for version 3 databases? That way
> developers would be able to develop their own access and model abstraction
> layers with good performance should they choose to, or make use of PDO
> should they need something a little more robust and feature-full? The Zend
> Framework's Zend_DB* objects provide both data-access-abstraction and
> data-model-abstraction, but still have to wrap PDO for data-access.

The PECL extension is NOT the current implementation of the extension.
The PDO/PDO_Sqlite extension/driver are separate to the Sqlite3
extension.
I'm not familiar with ZF's way of doing things, but I doubt is *has* to
use PDO. This is more likely a choice they made.

> The problem with developing another custom extension is that hosting
> providers only use stable releases of PHP and PECL, which makes it near
> impossible to have a custom extension enabled in a shared hosting
> environment. Not to mention, again, that the notion of developing a custom
> extension to expose functionality that already exists (like that already
> wrapped by PECL php-sqlite3), is insane.

 Agreed. So take your pick of PDO/PDO_sqlite or just SQLite3 extensions.
Both are enabled in PHP 5.3.0 by default, and should be in 5.2 builds
als

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Richard Quadling]

On 24 March 2010 18:04, Aaron Paetznick  wrote:
> Thanks for the advise.  I wanted a more automatic method of prefixing column
> names, but I ended up just aliasing them all.
>
> I know I could always just issue multiple SELECTs, but I wish MySQL would
> support this sort of functionality natively.  I'd really like to reference
> my results as $result["table0"]["column0"] from a single SELECT.
>
> Anyways, thanks all.
>
>
> --Aaron
>
>
> On 3/24/2010 11:28 AM, Niel Archer wrote:
>>>
>>> Many of my MySQL tables have columns with the same name.  I want to have
>>> PDO include table names in named result sets.  For example:
>>>
>>>    $sth = $dbh->prepare("SELECT * FROM table0, table1");
>>>    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
>>>
>>>
>>> I want $result to be organized like:
>>>
>>>    echo $result["table0.column0"];
>>>    echo $result["table0.column1"];
>>>    echo $result["table1.column0"];
>>>    echo $result["table1.column1"];
>>>
>>>
>>> Or, alternatively:
>>>
>>>    echo $result["table0"]["column0"];
>>>    echo $result["table0"]["column1"];
>>>    echo $result["table1"]["column0"];
>>>    echo $result["table1"]["column1"];
>>>
>>>
>>> Any ideas?  Thanks!
>>
>> Sounds like you want to UNION two SELECTs
>> http://dev.mysql.com/doc/refman/5.0/en/union.html
>>
>> (SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
>> UNION
>> (SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)
>>
>>
>>> --Aaron
>>>
>>>
>>> --
>>> PHP Database Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>> --
>> Niel Archer
>> niel.archer (at) blueyonder.co.uk
>>
>>
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

I use MS SQL and have used other DBs. I've never seen the table name
as part of the column name in a result set on any of them.


-- 
-
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

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

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Aaron Paetznick]

Thanks for the advise.  I wanted a more automatic method of prefixing 
column names, but I ended up just aliasing them all.


I know I could always just issue multiple SELECTs, but I wish MySQL 
would support this sort of functionality natively.  I'd really like to 
reference my results as $result["table0"]["column0"] from a single SELECT.


Anyways, thanks all.


--Aaron


On 3/24/2010 11:28 AM, Niel Archer wrote:

Many of my MySQL tables have columns with the same name.  I want to have
PDO include table names in named result sets.  For example:

$sth = $dbh->prepare("SELECT * FROM table0, table1");
$result = $sth->fetchAll(PDO::FETCH_ASSOC);


I want $result to be organized like:

echo $result["table0.column0"];
echo $result["table0.column1"];
echo $result["table1.column0"];
echo $result["table1.column1"];


Or, alternatively:

echo $result["table0"]["column0"];
echo $result["table0"]["column1"];
echo $result["table1"]["column0"];
echo $result["table1"]["column1"];


Any ideas?  Thanks!


Sounds like you want to UNION two SELECTs
http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
UNION
(SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)



--Aaron


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


--
Niel Archer
niel.archer (at) blueyonder.co.uk





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

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Richard Quadling]

On 24 March 2010 16:05, Aaron Paetznick  wrote:
> Many of my MySQL tables have columns with the same name.  I want to have PDO
> include table names in named result sets.  For example:
>
>  $sth = $dbh->prepare("SELECT * FROM table0, table1");
>  $result = $sth->fetchAll(PDO::FETCH_ASSOC);
>
>
> I want $result to be organized like:
>
>  echo $result["table0.column0"];
>  echo $result["table0.column1"];
>  echo $result["table1.column0"];
>  echo $result["table1.column1"];
>
>
> Or, alternatively:
>
>  echo $result["table0"]["column0"];
>  echo $result["table0"]["column1"];
>  echo $result["table1"]["column0"];
>  echo $result["table1"]["column1"];
>
>
> Any ideas?  Thanks!
>
>
> --Aaron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

The data coming from the server dictates the column names, not PDO.

If you need to identify the names, then use aliases in the SQL.





-- 
-
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

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

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Niel Archer]

> Many of my MySQL tables have columns with the same name.  I want to have 
> PDO include table names in named result sets.  For example:
> 
>$sth = $dbh->prepare("SELECT * FROM table0, table1");
>$result = $sth->fetchAll(PDO::FETCH_ASSOC);
> 
> 
> I want $result to be organized like:
> 
>echo $result["table0.column0"];
>echo $result["table0.column1"];
>echo $result["table1.column0"];
>echo $result["table1.column1"];
> 
> 
> Or, alternatively:
> 
>echo $result["table0"]["column0"];
>echo $result["table0"]["column1"];
>echo $result["table1"]["column0"];
>echo $result["table1"]["column1"];
> 
> 
> Any ideas?  Thanks!

Sounds like you want to UNION two SELECTs
http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
UNION
(SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)


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

--
Niel Archer
niel.archer (at) blueyonder.co.uk



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

Re: [PHP-DB] PDO PgSQL: _pdo_pgsql_notice

[Chris]

Samuel ROZE wrote:

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into "errmsg" (which I can get with PDO::errorInfo) the
notice.

The "_pdo_pgsql_notice" function is called everytime that there's an
notice. The notice message is in the "message" var. This what i had
tried:


You'll need to take this suggestion to the php-internals mailing list. 
They write and discuss the C code behind php so they'll be able to help you.


--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: [PHP-DB] PDO PgSQL: _pdo_pgsql_notice

[Samuel ROZE]

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into "errmsg" (which I can get with PDO::errorInfo) the
notice.

The "_pdo_pgsql_notice" function is called everytime that there's an
notice. The notice message is in the "message" var. This what i had
tried:

---
static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char *message) /*
{{{ */
{
pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data;
pdo_pgsql_error_info *einfo = &H->einfo;

einfo->errcode = 1;

if (einfo->errmsg) {
pefree(einfo->errmsg, dbh->is_persistent);
einfo->errmsg = NULL;
}
einfo->errmsg = _pdo_pgsql_trim_message(message, dbh->is_persistent);
}
---

And in "pdo_pgsql_fetch_error_func", i've added an else condition to
view if errmsg is null are not...

---
if (einfo->errcode) {
add_next_index_long(info, einfo->errcode);
add_next_index_string(info, einfo->errmsg, 1);
} else {
zend_throw_exception_ex(php_pdo_get_exception(), 0 TSRMLS_CC,
"einfo->errcode is false : %s , %s", einfo->errcode, einfo->errmsg);
}
---

And, I've a function which send some notices (when I throw exception
from "_pdo_pgsql_notice" with the "message" var, I see the notice ! :-)
But, einfo->errcode is null !

-> Fatal error: Uncaught exception 'PDOException' with message
'einfo->errcode is false : (null) , (null)'
in /etc/php-5.2.10/test/pgsql_notice.php:22
Stack trace:
#0 /etc/php-5.2.10/test/pgsql_notice.php(22): PDO->errorInfo()
#1 {main}
  thrown in /etc/php-5.2.10/test/pgsql_notice.php on line 22

Is anyone can help me ?
Thanks a lot !

Samuel. (French)

Le lundi 05 octobre 2009 à 20:58 +0200, Samuel ROZE a écrit :
> Hi !
> 
> I'm reading the source of PDO PostgreSQL driver and i see that there's
> an empty function, _pdo_pgsql_notice, which is very interesting ! I
> think that it is used when PostgreSQL throw Notices.
> But, nothing is done...
> 
> I want to know if somebody know why the content of the function is
> commented:
> 
> static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char *message) /*
> {{{ */
> {
> /*pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data; */
> }
> 
> (line 98 of /etc/pdo_pgsql/pgsql_driver.c - PHP 5.2 - r272374)
> 
> How can I store the content of "message" into a field of the
> PDOStatement ? I view that there's only a "pdo_dbh_t" param, I may just
> store this message into a field of the PDO class ?
> 
> Thanks in advance.
> Samuel.
> 
> 


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

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Samuel ROZE]

Thanks a lot ! I'm now understanding why PDO exists.. It is not for
replace the actual PostgreSQL/MySQL/SQLite/Firebird/... PHP drivers but
for help to developp with many DB with one code.

Well... I can developp an extension to PDO PostgreSQL driver, is that
correct ? It may help many users like me... :-)

Thanks Lester !
Samuel.

Le lundi 05 octobre 2009 à 08:36 +0100, Lester Caine a écrit :
> Samuel ROZE wrote:
> > Hi, 
> > Thanks for your reply.
> > 
> > In fact, my request returns a result which i get with the fetch method.
> > But, it must returns other informations, which are not in the result...
> > This informations are neturned before the result in console...
> > 
> > So, i don't know how I can get that.
> 
> PDO does not support many of the 'additional' features that databases 
> provide. It's designed to provide a 'lowest common denominator' solution 
> JUST for the data. If you are NOT planning to use any other database 
> then you may well find that the native postgres driver will provide 
> these additional facilities.
> 
> I use Firebird myself so can't comment on the 'fine detail' on postgres, 
> but some of the Firebird options can't easily be supported in PDO ;)
> 
> -- 
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk//
> Firebird - http://www.firebirdsql.org/index.php
> 


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

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Lester Caine]

Samuel ROZE wrote:
Hi, 
Thanks for your reply.


In fact, my request returns a result which i get with the fetch method.
But, it must returns other informations, which are not in the result...
This informations are neturned before the result in console...

So, i don't know how I can get that.


PDO does not support many of the 'additional' features that databases 
provide. It's designed to provide a 'lowest common denominator' solution 
JUST for the data. If you are NOT planning to use any other database 
then you may well find that the native postgres driver will provide 
these additional facilities.


I use Firebird myself so can't comment on the 'fine detail' on postgres, 
but some of the Firebird options can't easily be supported in PDO ;)


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

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

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Samuel ROZE]

Hi, 
Thanks for your reply.

In fact, my request returns a result which i get with the fetch method.
But, it must returns other informations, which are not in the result...
This informations are neturned before the result in console...

So, i don't know how I can get that.

Samuel.

Le lundi 05 octobre 2009 à 09:55 +1100, Kesavan Rengarajan a écrit :
> Hi,
> Query returns an iterable object (thanks to the comments in the php  
> site) and that's why when you do a dump you just see the String,
> If you just want to get one row from the resultset you can get it like  
> this:
> $stat = $sql->query('SELECT * FROM public.test_info()')->fetch();
> 
> More information on the PDO::query method can be found here: 
> http://au.php.net/manual/en/pdo.query.php
> 
> Cheers
> Sent from my iPhone
> 
> On 05/10/2009, at 2:06 AM, Samuel ROZE  wrote:
> 
> > Hi !
> >
> > I'm new on this mailling list, so i don't realy know if you know the
> > response and if it is realy here that I have to ask my question :-)
> >
> > I'm working with PostgreSQL (8.3 form sources) and PDO (PHP 5.2.10  
> > from
> > sources). In a Postgres function, I have a "RAISE NOTICE" command. My
> > function works like that in console:
> >
> > => SELECT * FROM public.test_info();
> > NOTICE: An information...
> >
> > test_info
> > 
> > ok
> > (1 line)
> >
> > There's a NOTICE, like I want ! :-) But, when i'm using PDO, I don't
> > know how I can get this NOTICE, which will provide me some  
> > informations
> > about the usage of the function.
> >
> > Code:
> > --
> >  > $sql = new PDO('...');
> >
> > $stat = $sql->query('SELECT * FROM public.test_info()');
> > var_dump($stat);
> > ?>
> > --
> >
> > Output:
> > --
> > object(PDOStatement)#4 (1) {
> >  ["queryString"]=>
> >  string(34) "SELECT * FROM public.test_info()"
> > }
> > --
> >
> > Is there someone who know how I can get that ? I read some thinks  
> > about
> > that and pg_last_notice may returns the NOTICE, but with PDO ?
> >
> > Thanks !
> > Samuel (French).
> >
> >
> > -- 
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >


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

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Kesavan Rengarajan]

Hi,
Query returns an iterable object (thanks to the comments in the php  
site) and that's why when you do a dump you just see the String,
If you just want to get one row from the resultset you can get it like  
this:

$stat = $sql->query('SELECT * FROM public.test_info()')->fetch();

More information on the PDO::query method can be found here: 
http://au.php.net/manual/en/pdo.query.php

Cheers
Sent from my iPhone

On 05/10/2009, at 2:06 AM, Samuel ROZE  wrote:


Hi !

I'm new on this mailling list, so i don't realy know if you know the
response and if it is realy here that I have to ask my question :-)

I'm working with PostgreSQL (8.3 form sources) and PDO (PHP 5.2.10  
from

sources). In a Postgres function, I have a "RAISE NOTICE" command. My
function works like that in console:

=> SELECT * FROM public.test_info();
NOTICE: An information...

test_info

ok
(1 line)

There's a NOTICE, like I want ! :-) But, when i'm using PDO, I don't
know how I can get this NOTICE, which will provide me some  
informations

about the usage of the function.

Code:
--
query('SELECT * FROM public.test_info()');
var_dump($stat);
?>
--

Output:
--
object(PDOStatement)#4 (1) {
 ["queryString"]=>
 string(34) "SELECT * FROM public.test_info()"
}
--

Is there someone who know how I can get that ? I read some thinks  
about

that and pg_last_notice may returns the NOTICE, but with PDO ?

Thanks !
Samuel (French).


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



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

Re: [PHP-DB] PDO bindValue ORDER BY

[Chris]

Chris wrote:

Matthew Peltzer wrote:

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = "SELECT * FROM `table` ORDER BY `$sort`";

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


Validate your data.

You're just making a bad query here.

I somehow get "orderby" to be "field_that_does_not_exist".

You end up with:

select * from table where blah order by `field_that_does_not_exist`;

which will generate a mysql_error and give no results at all.


Also mysql_real_escape_string needs a connection to do the escaping 
properly, so now you're doubling up.


1 connection for pdo
1 connection for mysql_real_escape_string to use

You could get away with mysql_escape_string however it doesn't take 
charset's into account so you may have some issues with those.


--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: [PHP-DB] PDO bindValue ORDER BY

[Chris]

Matthew Peltzer wrote:

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = "SELECT * FROM `table` ORDER BY `$sort`";

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


Validate your data.

You're just making a bad query here.

I somehow get "orderby" to be "field_that_does_not_exist".

You end up with:

select * from table where blah order by `field_that_does_not_exist`;

which will generate a mysql_error and give no results at all.

--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: [PHP-DB] PDO bindValue ORDER BY

[Post TUDBC]

Your workaround is probably what I would do myself.

Note: mysql_real_escape_string() is technically expecting a "string
value", although there is no harm using it, and it's not a bad idea to
avoid possible SQL malicious codes. Alternately, you can also write a
simple function using regular expression to make sure that $sort is
one of your possible sort fields and nothing else.

http://www.tudbc.org


On 11/1/08, Matthew Peltzer <[EMAIL PROTECTED]> wrote:
> ok... this makes more sense now.  I know in the past I tried to do
>  something similar with table names in the WHERE clause, and that
>  didn't work in the same manner.
>
>  Is there a better way to do what I'm trying to do? that is, sorting
>  within the SQL statement based on a supplied column name without out
>  writing multiple SQL statements?
>
>  for now my work around is to some thing like:
>
>  $sort = mysql_real_escape_string($sort);
>  $sql = "SELECT * FROM `table` ORDER BY `$sort`";
>
>  which makes me queasy because I spent a lot of time getting away from
>  inlining variables in SQL statements.
>
>
>
>  On Fri, Oct 31, 2008 at 6:46 PM, Post TUDBC <[EMAIL PROTECTED]> wrote:
>  > Technically, bound parameter is expecting a value, such as
>  >WHERE ID=:id
>  > However, ORDER BY is followed by a field name, such as
>  >   ORRDER BY ID
>  > So I don't think it should work.
>  >
>  > If it does work, then it is a sign that the database driver is not
>  > really preparing the statement (as it should for performance reason),
>  > but it is just substituiting values to compose a SQL (just for your
>  > convenience).
>  >
>  > On 10/31/08, Matthew Peltzer <[EMAIL PROTECTED]> wrote:
>  >> Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?
>  >>
>  >>  I find that in php 5.2.6 this works as expected:
>  >>
>  >>>>  $sql = 'SELECT * FROM `table` ORDER BY :sort';
>  >>  $stmt = $pdo->prepare($sql);
>  >>  $stmt->bindValue(':sort', $sort, PDO::PARAM_STR);
>  >>  $stmt->execute();
>  >>  print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
>  >>  ?>
>  >>
>  >>  but under php5.2.5 the ORDER BY clause silently fails.  Also,
>  >>  parameters bound to SELECT or WHERE or LIMIT clauses function
>  >>  correctly, but ORDE BY still has no effect.  If I remove the
>  >>  "$stmt->bindValue(':sort', $sort, PDO::PARAM_STR);" line or the "ORDER
>  >>  BY :sort" I get a "number of bound variables does not match number of
>  >>  tokens" error.
>  >>
>  >>  So it appears the parsing mechanism is funcitoning, but what ever is
>  >>  responsible for binding to ORDER BY is not.
>  >>
>  >>  I've looked in bug reports and the change logs, but did not find a
>  >>  explicit reference to this issue.
>  >>
>  >>
>  >>  --
>  >>  -- Matthew Peltzer
>  >>  -- [EMAIL PROTECTED]
>  >>
>  >>
>  >>  --
>  >>  PHP Database Mailing List (http://www.php.net/)
>  >>  To unsubscribe, visit: http://www.php.net/unsub.php
>  >>
>  >>
>  >
>
>
>
>
> --
>
> -- Matthew Peltzer
>  -- [EMAIL PROTECTED]
>
>  --
>  PHP Database Mailing List (http://www.php.net/)
>  To unsubscribe, visit: http://www.php.net/unsub.php
>
>

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

Re: [PHP-DB] PDO bindValue ORDER BY

[Matthew Peltzer]

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = "SELECT * FROM `table` ORDER BY `$sort`";

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


On Fri, Oct 31, 2008 at 6:46 PM, Post TUDBC <[EMAIL PROTECTED]> wrote:
> Technically, bound parameter is expecting a value, such as
>WHERE ID=:id
> However, ORDER BY is followed by a field name, such as
>   ORRDER BY ID
> So I don't think it should work.
>
> If it does work, then it is a sign that the database driver is not
> really preparing the statement (as it should for performance reason),
> but it is just substituiting values to compose a SQL (just for your
> convenience).
>
> On 10/31/08, Matthew Peltzer <[EMAIL PROTECTED]> wrote:
>> Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?
>>
>>  I find that in php 5.2.6 this works as expected:
>>
>>  >  $sql = 'SELECT * FROM `table` ORDER BY :sort';
>>  $stmt = $pdo->prepare($sql);
>>  $stmt->bindValue(':sort', $sort, PDO::PARAM_STR);
>>  $stmt->execute();
>>  print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
>>  ?>
>>
>>  but under php5.2.5 the ORDER BY clause silently fails.  Also,
>>  parameters bound to SELECT or WHERE or LIMIT clauses function
>>  correctly, but ORDE BY still has no effect.  If I remove the
>>  "$stmt->bindValue(':sort', $sort, PDO::PARAM_STR);" line or the "ORDER
>>  BY :sort" I get a "number of bound variables does not match number of
>>  tokens" error.
>>
>>  So it appears the parsing mechanism is funcitoning, but what ever is
>>  responsible for binding to ORDER BY is not.
>>
>>  I've looked in bug reports and the change logs, but did not find a
>>  explicit reference to this issue.
>>
>>
>>  --
>>  -- Matthew Peltzer
>>  -- [EMAIL PROTECTED]
>>
>>
>>  --
>>  PHP Database Mailing List (http://www.php.net/)
>>  To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>



-- 
-- Matthew Peltzer
-- [EMAIL PROTECTED]

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

Re: [PHP-DB] PDO bindValue ORDER BY

[Post TUDBC]

Technically, bound parameter is expecting a value, such as
WHERE ID=:id
However, ORDER BY is followed by a field name, such as
   ORRDER BY ID
So I don't think it should work.

If it does work, then it is a sign that the database driver is not
really preparing the statement (as it should for performance reason),
but it is just substituiting values to compose a SQL (just for your
convenience).

On 10/31/08, Matthew Peltzer <[EMAIL PROTECTED]> wrote:
> Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?
>
>  I find that in php 5.2.6 this works as expected:
>
>$sql = 'SELECT * FROM `table` ORDER BY :sort';
>  $stmt = $pdo->prepare($sql);
>  $stmt->bindValue(':sort', $sort, PDO::PARAM_STR);
>  $stmt->execute();
>  print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
>  ?>
>
>  but under php5.2.5 the ORDER BY clause silently fails.  Also,
>  parameters bound to SELECT or WHERE or LIMIT clauses function
>  correctly, but ORDE BY still has no effect.  If I remove the
>  "$stmt->bindValue(':sort', $sort, PDO::PARAM_STR);" line or the "ORDER
>  BY :sort" I get a "number of bound variables does not match number of
>  tokens" error.
>
>  So it appears the parsing mechanism is funcitoning, but what ever is
>  responsible for binding to ORDER BY is not.
>
>  I've looked in bug reports and the change logs, but did not find a
>  explicit reference to this issue.
>
>
>  --
>  -- Matthew Peltzer
>  -- [EMAIL PROTECTED]
>
>
>  --
>  PHP Database Mailing List (http://www.php.net/)
>  To unsubscribe, visit: http://www.php.net/unsub.php
>
>

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

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[Dee Ayy]

As you already know, you will dynamically create the $contents.

See if you can dynamically create the entire prepare statement which
includes the $contents, as well as dynamically create the bindValue
statement; then see if you can "eval" those dynamically created statements.

$commandPrepare = '$stmt = $pdo->prepare(\'
 select *
 from mytable
 where myfield IN ('.$dynamicallyCreatedContents.')\'
);';
eval($commandPrepare);

$commandBind = '$stmt->bindValue...  {escape quotes as needed -- I haven't
tested it}
eval($commandBind);
$stmt->execute();

On Tue, Jul 8, 2008 at 11:55 AM, TK <[EMAIL PROTECTED]> wrote:

> I'd like to use a PDO prepared statement to perform a MySQL query that uses
> the IN function.
>
> I.e.:
> $stmt = $pdo->prepare('
>   select *
>   from mytable
>   where myfield IN (:contents)
> );
> $stmt->bindValue(':contents', $contents);
> $stmt->execute();
>
> Here's the problem:
>
> If $contents is set to a single value, everything's fine:
>   $contents = 'mystring';
>
> How can I include multiple values in here?  If $contents is set to an
> array, PHP throws an "Array to string conversion" notice.
> i.e. $contents = array('mystring1', 'mystring2');
>
> If $contents is set to a comma-separated list, no matches are returned
> (probably because the entire "list" is being passed to MySQL as a single
> value, not multiple values).
> I.e. $contents = 'mystring1,mystring2';
>
> What's the proper way to do this?  Can it be done?  (Note that I do not
> know how many elements will be in $contents ahead of time, so something like
> IN (:contents1, :contents2) wouldn't make sense.)
>
> Thanks for any help!
>
> - TK
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[TK]

At 03:23 AM 7/10/2008, Thodoris wrote:
>Why don't you work this around. Since you may do anything with strings in php 
>using the (.) operator try this:
>
>$a = array('string1','string2');
>$str = implode("','",$a);
>$str = "'".$str."'";
>print $str;
>?>

Thanks, but this is missing the entire point of my question, which is *can it 
be done with PDO prepared statements*.  These solutions you're proposing do not 
involve PDO's prepared statements.  I already know how to do SQL queries by 
hand.

Anyway, per my latest message, I found where in the PHP manual it implies that 
PDO prepared statements cannot be used with the MySQL IN function, which speaks 
to my original question.

Thanks for trying!

- TK 


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

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[TK]

At 05:53 PM 7/8/2008, TK wrote:
>>>I'd like to use a PDO prepared statement to perform a MySQL query  
>>>that uses the IN function.

I may have found my answer (in the PHP manual, under PDO->prepare().  Go 
figure!):

"You cannot bind multiple values to a single named parameter in, for example, 
the IN() clause of an SQL statement."

Perhaps this means it cannot be done.

- TK


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

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[Thodoris]

O/H TK ??:

At 03:21 AM 7/9/2008, Thodoris wrote:
  

At 04:16 PM 7/8/2008, Philip Thompson wrote:


On Jul 8, 2008, at 11:55 AM, TK wrote:

I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo->prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
$contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an "Array to string conversion" notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire "list" is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)
 
  

$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = "'" . implode ("','", $contents) . "'";

Hope that helps,


Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).
 
  

Perhaps the implode suggested above is the solution for the prepared statement. 
I think that the problem is that you cannot bind a value with an array.
So you have  to implode the array into a string before binding it to a value. 
The other goes as you suggested:

$stmt = $pdo->prepare('
select *


>from mytable
  

where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Where contents is:

$pre_contents = array('mystring1', 'mystring2');

$contents = implode(',',$pre_contents);

or 
$contents = 'mystring';




As per my original question, that does not work:

  
If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire "list" is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';



Binding a comma-separated list (i.e. what implode creates) does not do what is wanted 
here - it appears instead to treat the whole "list" as one entry, i.e. it's 
like doing this:
   where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
   where myfield IN ('mystring1','mystring2')

Hence, my original question!  Is there a way to accomplish this with PDO and 
prepared statements?

- TK


  

Why don't you work this around. Since you may do anything with strings in php 
using the (.) operator try this:




I will print out:

'string1','string2'

--

Thodoris

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[TK]

At 03:21 AM 7/9/2008, Thodoris wrote:
>At 04:16 PM 7/8/2008, Philip Thompson wrote:
>>>On Jul 8, 2008, at 11:55 AM, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.

I.e.:
$stmt = $pdo->prepare('
 select *
 from mytable
 where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
 $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an "Array to string conversion" notice.
i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire "list" is being passed to  
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)
  
>>>$contents = array('string1', 'string2', 'string3');
>>>$ins = implode (',', $contents);
>>>$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";
>>>
>>>I'm not sure if the IN function only works on numeric values or if it  
>>>also works on strings as well. If contents contains strings, you may  
>>>need to put single quotes around each item. If so, change the above  
>>>to.
>>>
>>>$ins = "'" . implode ("','", $contents) . "'";
>>>
>>>Hope that helps,
>>
>>Thanks.  That's how to use the IN function in the first place, which I 
>>already know.  What I was asking about was how to do this using PDO and 
>>prepared statements.  It's the PDO prepared statement functionality that's 
>>got me stuck.  The issue is that I can't figure out how to bindParam or 
>>bindValue in this situation, where there is potentially a list of values (of 
>>arbitrary size).
>>  
>Perhaps the implode suggested above is the solution for the prepared 
>statement. I think that the problem is that you cannot bind a value with an 
>array.
>So you have  to implode the array into a string before binding it to a value. 
>The other goes as you suggested:
>
>$stmt = $pdo->prepare('
>select *
>from mytable
>where myfield IN (:contents)
>);
>$stmt->bindValue(':contents', $contents);
>$stmt->execute();
>
>Where contents is:
>
>$pre_contents = array('mystring1', 'mystring2');
>
>$contents = implode(',',$pre_contents);
>
>or 
>$contents = 'mystring';


As per my original question, that does not work:

>If $contents is set to a comma-separated list, no matches are  
>returned (probably because the entire "list" is being passed to  
>MySQL as a single value, not multiple values).
>I.e. $contents = 'mystring1,mystring2';

Binding a comma-separated list (i.e. what implode creates) does not do what is 
wanted here - it appears instead to treat the whole "list" as one entry, i.e. 
it's like doing this:
   where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
   where myfield IN ('mystring1','mystring2')

Hence, my original question!  Is there a way to accomplish this with PDO and 
prepared statements?

- TK


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

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[Thodoris]

At 04:16 PM 7/8/2008, Philip Thompson wrote:

On Jul 8, 2008, at 11:55 AM, TK wrote:


I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo->prepare('
 select *
 from mytable
 where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
 $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an "Array to string conversion" notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire "list" is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)


Thanks for any help!

- TK
  

$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = "'" . implode ("','", $contents) . "'";

Hope that helps,

~Philip




Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).

- TK 



  
Perhaps the implode suggested above is the solution for the prepared statement. 
I think that the problem is that you cannot bind a value with an array.

So you have  to implode the array into a string before binding it to a value. 
The other goes as you suggested:

$stmt = $pdo->prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Where contents is:

$pre_contents = array('mystring1', 'mystring2');

$contents = implode(',',$pre_contents);

or 


$contents = 'mystring';

--
Thodoris

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[TK]

At 04:16 PM 7/8/2008, Philip Thompson wrote:
>On Jul 8, 2008, at 11:55 AM, TK wrote:
>
>>I'd like to use a PDO prepared statement to perform a MySQL query  
>>that uses the IN function.
>>
>>I.e.:
>>$stmt = $pdo->prepare('
>>  select *
>>  from mytable
>>  where myfield IN (:contents)
>>);
>>$stmt->bindValue(':contents', $contents);
>>$stmt->execute();
>>
>>Here's the problem:
>>
>>If $contents is set to a single value, everything's fine:
>>  $contents = 'mystring';
>>
>>How can I include multiple values in here?  If $contents is set to  
>>an array, PHP throws an "Array to string conversion" notice.
>>i.e. $contents = array('mystring1', 'mystring2');
>>
>>If $contents is set to a comma-separated list, no matches are  
>>returned (probably because the entire "list" is being passed to  
>>MySQL as a single value, not multiple values).
>>I.e. $contents = 'mystring1,mystring2';
>>
>>What's the proper way to do this?  Can it be done?  (Note that I do  
>>not know how many elements will be in $contents ahead of time, so  
>>something like IN (:contents1, :contents2) wouldn't make sense.)
>>
>>Thanks for any help!
>>
>>- TK
>
>
>$contents = array('string1', 'string2', 'string3');
>$ins = implode (',', $contents);
>$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";
>
>I'm not sure if the IN function only works on numeric values or if it  
>also works on strings as well. If contents contains strings, you may  
>need to put single quotes around each item. If so, change the above  
>to.
>
>$ins = "'" . implode ("','", $contents) . "'";
>
>Hope that helps,
>
>~Philip


Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).

- TK 


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

Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"

[Philip Thompson]

On Jul 8, 2008, at 11:55 AM, TK wrote:

I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo->prepare('
  select *
  from mytable
  where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
  $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an "Array to string conversion" notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire "list" is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)


Thanks for any help!

- TK



$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = "'" . implode ("','", $contents) . "'";

Hope that helps,

~Philip


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

Re: [PHP-DB] PDO::TIME_ZONE and or Initializing Query

[Chris]

Paul Rogers wrote:

It would be helpful if the time zone of a session could be specified at
the time of the connection. This would avoid the overhead of a separate
database query and round-trip to the database server when a single,
non-default time zone is needed for the duration of the connection.

Naturally there is the option of using GMT and dealing with the time
zone offsets entirely in PHP or at some other layer. However, many large
databases systems support connection-specific time zones and I've found
them to be quite useful.

It may also be useful as a way to set the time zone after the connection
is made using "setAttribute".

Since several supported systems are not time-zone aware, such as Sqlite,
the attribute would not always have an effect. Or it could be
implemented as a server-specific attribute such as "MYSQL_TIME_ZONE" or
"PGSQL_TIME_ZONE".

If a TIME_ZONE attribute seems inappropriate then another idea is to
provide an optional, initialization query when creating the PDO object.
For example, a "SET TIME ZONE 'CST6CDT'" query could be issued to set
the time zone immediately after the connection is made. Such a mechanism
may have other uses for queries that do not require processing the
response.


I think the problem here is that each database type handles it all 
differently, hence why you have to do it yourself.


If you want to put it to the developers, join the -internals list and 
discuss the idea there.


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO and Oracle

[Roberto Mansfield]

Christopher Jones wrote:
> 
> 
> Roberto Mansfield wrote:
>> Folks,
>>
>> I was looking at rewriting some old DB classes using PDO with the Oracle
>> driver, but I'm a bit confused about the current state of things. Some
>> docs said the PDO Oracle driver was experimental and subject to change.
>> I also had a hard time trying to get the driver to compile against
>> Oracle's instant client. Are people using PDO and Oracle in production
>> environments? Am I just being dense and unable to find a guide to
>> compile against the instant client?  Thanks for the input.
>>
>> Roberto
>>
> 
> Hi Roberto,
> 
> I still recommend using OCI8 over PDO_OCI.  OCI8 has performance
> benefits, has been around for a while and is relatively stable.
> 
> You only have to scan the generic PDO bug list to see the number of
> issues with the generic PDO layer and many of the PDO_xxx drivers.
> 
> Having said that, there is gradual work being done on PDO and I would
> encourage people to pound on it.  I'm calling 2008 "the year of PDO".
> Sure hope I don't have to change that to "2009"!
> 
> What are the configure problems?

Thanks for the feedback. I had some initial problems compiling PDO_OCI
against oracle's instant client. A couple symlinks fixed those error:

cd $instant_client_home
ln -s lib .
ln -s sdk/include .

One OCI feature not implemented in PDO_OCI is bind_array_by_name(). I'm
using that to pass arrays to procedures so that means I can't use PDO
for now.

Thanks,
Roberto


-- 
Roberto Mansfield
Institutional Research and Application Development (IRAD)
SAS Computing

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

Re: [PHP-DB] PDO and Oracle

[Christopher Jones]

Roberto Mansfield wrote:
> Folks,
>
> I was looking at rewriting some old DB classes using PDO with the Oracle
> driver, but I'm a bit confused about the current state of things. Some
> docs said the PDO Oracle driver was experimental and subject to change.
> I also had a hard time trying to get the driver to compile against
> Oracle's instant client. Are people using PDO and Oracle in production
> environments? Am I just being dense and unable to find a guide to
> compile against the instant client?  Thanks for the input.
>
> Roberto
>

Hi Roberto,

I still recommend using OCI8 over PDO_OCI.  OCI8 has performance
benefits, has been around for a while and is relatively stable.

You only have to scan the generic PDO bug list to see the number of
issues with the generic PDO layer and many of the PDO_xxx drivers.

Having said that, there is gradual work being done on PDO and I would
encourage people to pound on it.  I'm calling 2008 "the year of PDO".
Sure hope I don't have to change that to "2009"!

What are the configure problems?

Chris

--
Christopher Jones, Oracle
Email: [EMAIL PROTECTED]Tel:  +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/   Free PHP Book: http://tinyurl.com/f8jad

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

Re: [PHP-DB] pdo::mysql and unbuffered queries

[Chris]

I tried using pdo (www.php.net/pdo) as it would be nice for my projects 
to be portable to another database systems, but quickly ran into problems.


The lesser important: One time, there was a simple typo in one of my 
queries and I got an error that php is not able to save session 
variables at the given path - I'm not joking! I worked several hours to 
solve that session problem just to realize the query had a bracket to 
few. I already got other errors in my queries where I was given a 
correct error trace, this is quite confusing.


Can't comment on that one but if you can prove it in a small test 
script, post a bug report on http://bugs.php.net



The bigger important: I was not able to build nested queries.


Post your code. I doubt that the guys that developed PDO would stop 
something like this.


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO Contact ?

[Chris]

I wish to know who i can contact from PDO Team to submit evolutions ?


No particular person. I think Wez was in charge when it was being built 
but it's all integrated into the core php now - so join the -internals 
list and make a proposal/suggestion.


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO and MS Sql Server

[Chris]

Bruce Cowin wrote:

Thanks for replying.  According to the PDO doco: "If the database driver supports it, you may 
also bind parameters for output as well as input."  So maybe this driver doesn't support it?  
I don't know.  I'll try and get the stored proc to return the value, but not sure how I'll capture 
that yet.  If that doesn't work, I'll just do a "select @@identity".


Ah didn't know that :)

Maybe check with the php-general list to see if anyone else had similar 
experience with mssql & the pdo driver.


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO and MS Sql Server

[Bruce Cowin]

Thanks for replying.  According to the PDO doco: "If the database driver 
supports it, you may also bind parameters for output as well as input."  So 
maybe this driver doesn't support it?  I don't know.  I'll try and get the 
stored proc to return the value, but not sure how I'll capture that yet.  If 
that doesn't work, I'll just do a "select @@identity".

Thanks again.



Regards,

Bruce

>>> Chris <[EMAIL PROTECTED]> 6/07/2007 12:44:34 p.m. >>>
Bruce Cowin wrote:
> I'm using PHP 5.1.  The documentation for PDO doesn't list MS Sql server as 
> one of the drivers that support PDO but there is a php_pdo_mssql.dll which 
> seems to work so I'm using that.
> 
> I need to get the id of a new record just inserted.  I can't use 
> lastInsertId() as I get a message saying it's not supported.  So I've created 
> a stored proc that returns the id just created (code below).  The insert 
> works fine but the $emailid variable is not populated.  I can run the stored 
> proc in query analyzer and it outputs the id correctly so I know the stored 
> proc works.  All the examples I see return strings, not sure if that has 
> anything to do with it.  And as for the parameter length for the output 
> parameter, I've tried nothing as well as 9.
> 
> $stmt = $this->dbh->prepare("exec usp_EmailInsert :projectid, :mailfrom, 
> :mailto, :mailcc, :subject, :body, :mimefilename, :emailid");
> $stmt->bindParam(':projectid', $projectid, PDO::PARAM_INT);
> $stmt->bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
> $stmt->bindParam(':mailto', $to, PDO::PARAM_STR, 500);
> $stmt->bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
> $stmt->bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
> $stmt->bindParam(':body', $body, PDO::PARAM_LOB);
> $stmt->bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);
> 
> $stmt->bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
> $stmt->execute();

prepared statements are for ingoing queries, they can't put results from 
that query into a binded parameter.

That is, when you bind a parameter it only works for the query TO the 
database, they are not filled in for outgoing results.

Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples 
here:

http://www.php.net/manual/en/function.PDO-prepare.php 

-- 
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO and MS Sql Server

[Chris]

Bruce Cowin wrote:

I'm using PHP 5.1.  The documentation for PDO doesn't list MS Sql server as one 
of the drivers that support PDO but there is a php_pdo_mssql.dll which seems to 
work so I'm using that.

I need to get the id of a new record just inserted.  I can't use lastInsertId() 
as I get a message saying it's not supported.  So I've created a stored proc 
that returns the id just created (code below).  The insert works fine but the 
$emailid variable is not populated.  I can run the stored proc in query 
analyzer and it outputs the id correctly so I know the stored proc works.  All 
the examples I see return strings, not sure if that has anything to do with it. 
 And as for the parameter length for the output parameter, I've tried nothing 
as well as 9.

$stmt = $this->dbh->prepare("exec usp_EmailInsert :projectid, :mailfrom, :mailto, 
:mailcc, :subject, :body, :mimefilename, :emailid");
$stmt->bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt->bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt->bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt->bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt->bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt->bindParam(':body', $body, PDO::PARAM_LOB);
$stmt->bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);  
 
$stmt->bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
$stmt->execute();


prepared statements are for ingoing queries, they can't put results from 
that query into a binded parameter.


That is, when you bind a parameter it only works for the query TO the 
database, they are not filled in for outgoing results.


Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples 
here:


http://www.php.net/manual/en/function.PDO-prepare.php

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO Exception Handling Question

[Jerry Schwartz]

I hope by now you figured this out, but you have to use $dbh->setAttribute() 
to enable exception handling for anything but the initial object creation.

Worse yet, as of 2007/04/20 the return values of setAttribute are reversed. 
It returns TRUE on failure and FALSE on success.

"Rodney Topor" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>I did some more checking.  If there is an error in the "new PDO(...)"
> constructor call, then it does get into the catch block and print an
> appropriate error message.  (Good.  Perhaps I was previously mistaken.)
>
> But if there is an error in the "SELECT" query (either syntax error, or
> reference to missing table), then no exception is raised, and control
> just continues in the try block with a null result from the query.
> Is this the intended behaviour?
>
> On Sun, 2 Apr 2006, chris smith wrote:
>
>> On 4/2/06, Rodney Topor <[EMAIL PROTECTED]> wrote:
>>> I've just started using PDO with PHP 5.1 and MySQL 5.0.  Everything
>>> seems to work fine, except for one issue with the following example
>>> from the PHP manual.
>>>
>>> >> try {
>>> $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
>>> foreach ($dbh->query('SELECT * from FOO') as $row) {
>>>   print_r($row);
>>> }
>>> $dbh = null;
>>> } catch (PDOException $e) {
>>> print "Error!: " . $e->getMessage() . "";
>>> die();
>>> }
>>> ?>
>>>
>>> If an error occurs in creating the new PDO instance, or in issuing the
>>> PDO query, then a PDOException should be raised.  But nothing is ever
>>> printed.  I've tried running this script, forcing an exception, both
>>> from the command line and from the server.  But nothing is ever
>>> printed.  What do I have to do to see the printed string in the
>>> catch-block?
>>
>> Do you mean it doesn't print anything or it doesn't get into the catch 
>> block?
>>
>> ie it prints "Error!:" or it doesn't get into that section at all?
>>
>> --
>> Postgresql & php tutorials
>> http://www.designmagick.com/
>> 

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

Re: [PHP-DB] PDO, fetch prepared statement

[Chris]

Rudi Worm wrote:

Hi

Using PDO to prepare a query, bind som values to it (using ? Placeholders)
and then executing it - how do i get the actual query afterwards?  With the
correct inserted values.


Use your system database logs. PDO doesn't give you the opportunity to 
grab them.


--
Postgresql & php tutorials
http://www.designmagick.com/

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

RE: [PHP-DB] PDO and Exceptions

[Vandegrift, Ken]

Thanks.

That was my issue. I had to separate each insert instead of grouping
them together and executing the query.  Exception is now thrown if a
primary key violation occurs and entire transaction is rolled back.


Ken Vandegrift

-Original Message-
From: Chris [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 21, 2006 5:39 PM
To: Vandegrift, Ken
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] PDO and Exceptions

Vandegrift, Ken wrote:
> Good Afternoon,
>  
> I have an instance of a PDO object set to throw a PDOException on 
> errors.  However, it does not appear to do this when a primary key 
> violation error occurs on an insert sql statement.  An exception 
> should be thrown in this case correct?
>  
> The insert statements are part of a transaction but I am unable to 
> rollback the transaction on error.
>  
> Any ideas would be greatly appreciated.
>  
> Code sample:
>  
> /** Excerpt from DB connection class **/ $this->db = new 
> PDO('odbc:DBNAME', $userid, $pwd); 
> $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
>  
> /**
>   * Updates order information in the database.
>   * Called from Order::dbSaveOrder
>   * @return boolean true on success
>   * @access protected
>   */
>  protected function dbUpdateOrder() {
>  
>   // VARIABLES TO TRACK PROPER SQL FORMATTING
>   $count = count($this->attributes);
>   $i = 1;
>  
>   // BUILD ORDER TABLE UPDATE QUERY
>   $sql = "UPDATE " . ORDER_TABLE . " SET ";
>   foreach ($this->attributes as $attribute => $value) {
>$attribute = strtoupper($attribute);
>$sql  .= (is_string($value) || is_null($value)) ? "{$attribute}
=
> '{$value}'" : "{$attribute} = {$value}";
>if ($i < $count) {
> $i++;
> $sql .= ", ";
>}
>   }
>   $sql .= " WHERE " . ORDER_TABLE_ID . " = :orderid;";
>  
>   try {
>$this->db->beginTransaction();
>$stmt = $this->db->prepare($sql);
>$stmt->execute(array(':orderid' => $this->attributes['order_id']));
>  
> / ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT 
> TRIGGER AN EXCEPTION ON DUPLICATE ENTRIES /
>  
>// SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
>if (!empty($this->gcnums)) {
> $sql = null;
> foreach ($this->gcnums as $itemid => $gcnumbers) {
>   foreach ($gcnumbers as $gcnum) {
> $sql .= "INSERT INTO GIFTCARD_RECORD " .
> "(" .
> "ORDER_ID, " .
> "ITEM_ID, " .
> "GC_NUM " .
> ") " .
> "VALUES" .
> "(" .
> "{$this->__get('order_id')}, " .
> "{$itemid}, " .
> "'{$gcnum}'" .
> ");";
>   } // End Inner FOREACH
>} // End Outer FOREACH
>$stmt = $this->db->prepare($sql);
>  $stmt->execute();
>}
>$this->db->commit();
>$stmt = null;
>return true;
>   }catch (PDOException $e) {
>$this->db->rollBack();
>Log::write(LOG_DIR, 'order',
>  "\r Order::dbUpdateOrder \r" .
>  "[UPDATE SQL] => " . $sql . "\r" .
>  "[DB ERROR] => " . $e->getMessage() . "\r" .
>  "**"
>);
>$this->error = 'The system has encountered an error and is unable 
> to update this order. ' .
>  'Please contact the system administrator.';
>$stmt = null;
>return false;
>   }
>  }
>  
> Ken Vandegrift
>  
> 

I can't see a "try" in the second part. Wouldn't you need:

try {
   update query
   try {
 insert query
   } catch exception
} catch exception

?

I could be on the completely wrong track but I think you need a try /
catch for each part.
--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO and Exceptions

[Chris]

Vandegrift, Ken wrote:

Good Afternoon,
 
I have an instance of a PDO object set to throw a PDOException on

errors.  However, it does not appear to do this when a primary key
violation error occurs on an insert sql statement.  An exception should
be thrown in this case correct?
 
The insert statements are part of a transaction but I am unable to

rollback the transaction on error.
 
Any ideas would be greatly appreciated.
 
Code sample:
 
/** Excerpt from DB connection class **/

$this->db = new PDO('odbc:DBNAME', $userid, $pwd);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
/**

  * Updates order information in the database.
  * Called from Order::dbSaveOrder
  * @return boolean true on success
  * @access protected
  */
 protected function dbUpdateOrder() {
 
  // VARIABLES TO TRACK PROPER SQL FORMATTING

  $count = count($this->attributes);
  $i = 1;
 
  // BUILD ORDER TABLE UPDATE QUERY

  $sql = "UPDATE " . ORDER_TABLE . " SET ";
  foreach ($this->attributes as $attribute => $value) {
   $attribute = strtoupper($attribute);
   $sql  .= (is_string($value) || is_null($value)) ? "{$attribute} =
'{$value}'" : "{$attribute} = {$value}";
   if ($i < $count) {
$i++;
$sql .= ", ";
   }
  }
  $sql .= " WHERE " . ORDER_TABLE_ID . " = :orderid;";
 
  try {

   $this->db->beginTransaction();
   $stmt = $this->db->prepare($sql);
   $stmt->execute(array(':orderid' => $this->attributes['order_id']));
 
/ ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT TRIGGER

AN EXCEPTION ON DUPLICATE ENTRIES /
 
   // SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER

   if (!empty($this->gcnums)) {
$sql = null;
foreach ($this->gcnums as $itemid => $gcnumbers) {
  foreach ($gcnumbers as $gcnum) {
$sql .= "INSERT INTO GIFTCARD_RECORD " .
"(" .
"ORDER_ID, " .
"ITEM_ID, " .
"GC_NUM " .
") " .
"VALUES" .
"(" .
"{$this->__get('order_id')}, " .
"{$itemid}, " .
"'{$gcnum}'" .
");";
  } // End Inner FOREACH
   } // End Outer FOREACH
   $stmt = $this->db->prepare($sql);
 $stmt->execute();
   }
   $this->db->commit();
   $stmt = null;
   return true;
  }catch (PDOException $e) {
   $this->db->rollBack();
   Log::write(LOG_DIR, 'order',
 "\r Order::dbUpdateOrder \r" .
 "[UPDATE SQL] => " . $sql . "\r" .
 "[DB ERROR] => " . $e->getMessage() . "\r" .
 "**"
   );
   $this->error = 'The system has encountered an error and is unable to
update this order. ' .
 'Please contact the system administrator.';
   $stmt = null;
   return false;
  }
 }
 
Ken Vandegrift
 



I can't see a "try" in the second part. Wouldn't you need:

try {
  update query
  try {
insert query
  } catch exception
} catch exception

?

I could be on the completely wrong track but I think you need a try / 
catch for each part.

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] PDO Exception Handling Question

[Cyril PIERRE de GEYER]

Read on otn :

"PDO_ERRMODE_SILENT
This is the default mode; it will simply set the error code for you to 
inspect using the errorCode() and errorInfo() methods of both the 
statement and database handle objects.


if (!$dbh->exec($sql)) {
echo $dbh->errorCode() . "";
$info = $dbh->errorInfo();
// $info[0] == $dbh->errorCode() unified error code
// $info[1] is the driver specific error code
// $info[2] is the driver specific error string"




Cyril PIERRE de GEYER a écrit :

And also my question is :

"Why did the PDO creators (Wez the King?) decide to choose the silent 
mode for being the default one ?"


Why not defining the ERRMODE_WARNING by default ?

Or perhaps I misunderstand something, and I would be very happy to learn 
a bit :)


Thx

Cyril


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

Re: [PHP-DB] PDO Exception Handling Question

[Cyril PIERRE de GEYER]

And also my question is :

"Why did the PDO creators (Wez the King?) decide to choose the silent 
mode for being the default one ?"


Why not defining the ERRMODE_WARNING by default ?

Or perhaps I misunderstand something, and I would be very happy to learn 
a bit :)


Thx

Cyril

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

Re: [PHP-DB] PDO Exception Handling Question

[Cyril PIERRE de GEYER]

But if there is an error in the "SELECT" query (either syntax error, or
reference to missing table), then no exception is raised, and control
just continues in the try block with a null result from the query.
Is this the intended behaviour?


Hey, it's really fun I was looking here and on internals to ask quite 
the same thing.


In fact what I have found is that PDO error default handler is silent. 
But you can change it using :


$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
or

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);

getMessage() . "";
die();
}

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);


$dbh->beginTransaction();
try {

$sql = "INSERT INTO produit (idproduit, nom, marque, prix)
VALUES (NULL,'CB500', 'Honda', '6000')";
$dbh->exec($sql);
$idInsere = $dbh->lastInsertId();

$sql2 = "INSERT INTO disponibilite (idproduit, quantite)
VALUES ('$idInsere', 5)";
$dbh->exec($sql2);


$dbh->commit();
}catch (PDOException $e){
$dbh->rollBack();
print "Erreur ! : " . $e->getMessage() . "";

}


?>

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

Re: [PHP-DB] PDO Exception Handling Question

[Rodney Topor]

I did some more checking.  If there is an error in the "new PDO(...)"
constructor call, then it does get into the catch block and print an
appropriate error message.  (Good.  Perhaps I was previously mistaken.)

But if there is an error in the "SELECT" query (either syntax error, or
reference to missing table), then no exception is raised, and control
just continues in the try block with a null result from the query.
Is this the intended behaviour?

On Sun, 2 Apr 2006, chris smith wrote:


On 4/2/06, Rodney Topor <[EMAIL PROTECTED]> wrote:

I've just started using PDO with PHP 5.1 and MySQL 5.0.  Everything
seems to work fine, except for one issue with the following example
from the PHP manual.

query('SELECT * from FOO') as $row) {
  print_r($row);
}
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "";
die();
}
?>

If an error occurs in creating the new PDO instance, or in issuing the
PDO query, then a PDOException should be raised.  But nothing is ever
printed.  I've tried running this script, forcing an exception, both
from the command line and from the server.  But nothing is ever
printed.  What do I have to do to see the printed string in the
catch-block?


Do you mean it doesn't print anything or it doesn't get into the catch block?

ie it prints "Error!:" or it doesn't get into that section at all?

--
Postgresql & php tutorials
http://www.designmagick.com/



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

Re: [PHP-DB] PDO Exception Handling Question

[chris smith]

On 4/2/06, Rodney Topor <[EMAIL PROTECTED]> wrote:
> I've just started using PDO with PHP 5.1 and MySQL 5.0.  Everything
> seems to work fine, except for one issue with the following example
> from the PHP manual.
>
>  try {
> $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
> foreach ($dbh->query('SELECT * from FOO') as $row) {
>   print_r($row);
> }
> $dbh = null;
> } catch (PDOException $e) {
> print "Error!: " . $e->getMessage() . "";
> die();
> }
> ?>
>
> If an error occurs in creating the new PDO instance, or in issuing the
> PDO query, then a PDOException should be raised.  But nothing is ever
> printed.  I've tried running this script, forcing an exception, both
> from the command line and from the server.  But nothing is ever
> printed.  What do I have to do to see the printed string in the
> catch-block?

Do you mean it doesn't print anything or it doesn't get into the catch block?

ie it prints "Error!:" or it doesn't get into that section at all?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] [PDO] Number of rows found by Select

[Rob C]

Cracked it! The buffer command 
(setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE)) needs to be 
sent to the PDO object, not PDO::Statement objects. The documentation is 
wrong here, in a number of ways.


This is the function that (fingers crossed) runs a SELECT query and 
returns the statement, while setting the variable passed as the second 
parameter to the number of rows. If no second paramater is passed, of 
course nothing is set and function avoids unnecessary DB calls. 
found_rows() returns the total number of turns regardless of any LIMIT 
clause in the SQL so if you use LIMIT, pass the same number as the last 
parameter. Offset doesn't matter.



Example Call (notice chopped SQL, no "SELECT"):

$c = -1;
$s = DM::select( '* FROM foo WHERE bar < 40 LIMIT 4 OFFSET 2', $c, 4);
var_dump($s->fetchAll()); # Normal dump of found data
var_dump($c); # The number of rows found, at most 4.


Function:

public static function select($sql, &$count = NULL, $limit = NULL)
{
  $db = self::handle(); # Get PDO

  $sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;

  try {
$stmt = $db->prepare($sql);
$stmt->execute();

if (!is_null($count)) {
  $rows = $db->prepare('SELECT found_rows() AS rows');
  $rows->execute();
  $rows_array = $rows->fetch(PDO::FETCH_NUM);
  $rows->closeCursor();
  $count = $rows_array[0];

  if (!is_null($limit) && $count > $limit) {
$count = $limit;
  }
}
  } catch (PDOException $e) {
# todo
  }

  return $stmt;
}

Rob



Rob C wrote:
So would it be possible to write a select() function to handle the 
wierdness? I'm attempting to write one but I'm getting buffered query 
errors. This function is part of a Database Manager class and is 
supposed to return a PDO::Statement with the number of rows being stored 
in a referenced paramater.


Error:
'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other 
unbuffered queries are active. Consider using PDOStatement::fetchAll(). 
Alternatively, if your code is only ever going to run against mysql, you 
may enable query buffering by setting the 
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in 
E:\Server\swi\qpf\mng\data.class.php:64 Stack trace: #0 
E:\Server\swi\qpf\mng\data.class.php(64): PDOStatement->execute() #1 
E:\Server\swi\www\db.php(13): DM::select('* FROM cdrs WHE...', 20, -1) 
#2 {main} thrown in E:\Server\swi\qpf\mng\data.class.php on line 64



Function:

public static function select($sql, &$count = NULL, $limit = NULL)
{
$db = self::handle(); #Singleton method to create/retrieve db handle
$buf = array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE);

$sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;
   
$stmt = $db->prepare($sql, $buf);

$stmt->execute();

if (!is_null($count)) {
$rows = $db->prepare('SELECT found_rows() AS rows', $buf);
$rows->execute(); #ERROR HERE
$rows_array = $rows->fetch(PDO::FETCH_NUM);
$rows->closeCursor();
$count = $rows_array[0];

if (!is_null($limit) && $count > $limit) {
$count = $limt;
}
}

return $stmt;
}

Where am I going wrong here?

Rob



Micah Stevens wrote:

There's a function called 'found_rows()' function, so you could try 
issuing a query, then issuing a second one 'SELECT FOUND_ROWS();' and 
it should give the number of rows returned by the previous select.

Here's details:
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html



On Tuesday 08 November 2005 9:13 am, Micah Stevens wrote:


yeah, it would help if I read the whole post. Sorry.

On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:


I suppose you could use "count( PDOStatement::fetchAll() )", but I
understand your amazement.

mysql_num_rows() is specific to MySQL.  He wants a PDO version.

-Original Message-
From: Micah Stevens [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 08, 2005 10:51 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] [PDO] Number of rows found by Select



mysql_num_rows()

On Tuesday 08 November 2005 5:17 am, Rob C wrote:


What is the recommended way to find the number of rows found by a
SELECT query? PDOStatement::rowCount() doesn't work with MySQL and 
is a

bit of a hack anyway. Doing a COUNT(*) before the SELECT is very
hackish - the data could have changed and it's an extra query. What is
there that's better than either of these? Is there any way to use
COUNT(*) without risking data change, such as inside a transaction?

I'm amazed that there is no mysql_num_rows() equivilent, I get the
feeling that I'm missing something obvious. I can only presume 
there is
some t

Re: [PHP-DB] [PDO] Number of rows found by Select

[Micah Stevens]

Ick.. this is why I don't use OOP DBA libraries.. 

Uhm.. without spending a bit of time learning more about PDO, I'd be hard 
pressed to answer this one. Why are you getting issues with asynchronous 
unbuffered queries? If they're unbuffered, how is the code execution 
continuing before you get data returned?

Seems weird to me, but I'm probably misunderstanding the error message.

-Micah 

On Wednesday 09 November 2005 9:21 am, Rob C wrote:
> So would it be possible to write a select() function to handle the
> wierdness? I'm attempting to write one but I'm getting buffered query
> errors. This function is part of a Database Manager class and is
> supposed to return a PDO::Statement with the number of rows being stored
> in a referenced paramater.
>
> Error:
> 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other
> unbuffered queries are active. Consider using PDOStatement::fetchAll().
> Alternatively, if your code is only ever going to run against mysql, you
> may enable query buffering by setting the
> PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in
> E:\Server\swi\qpf\mng\data.class.php:64 Stack trace: #0
> E:\Server\swi\qpf\mng\data.class.php(64): PDOStatement->execute() #1
> E:\Server\swi\www\db.php(13): DM::select('* FROM cdrs WHE...', 20, -1)
> #2 {main} thrown in E:\Server\swi\qpf\mng\data.class.php on line 64
>
>
> Function:
>
> public static function select($sql, &$count = NULL, $limit = NULL)
> {
>  $db = self::handle(); #Singleton method to create/retrieve db handle
>  $buf = array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE);
>
>  $sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;
>
>  $stmt = $db->prepare($sql, $buf);
>  $stmt->execute();
>
>  if (!is_null($count)) {
>  $rows = $db->prepare('SELECT found_rows() AS rows', $buf);
>  $rows->execute(); #ERROR HERE
>  $rows_array = $rows->fetch(PDO::FETCH_NUM);
>  $rows->closeCursor();
>  $count = $rows_array[0];
>
>  if (!is_null($limit) && $count > $limit) {
>  $count = $limt;
>  }
>  }
>
>  return $stmt;
> }
>
> Where am I going wrong here?
>
> Rob
>
> Micah Stevens wrote:
> > There's a function called 'found_rows()' function, so you could try
> > issuing a query, then issuing a second one 'SELECT FOUND_ROWS();' and it
> > should give the number of rows returned by the previous select.
> >
> > Here's details:
> > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html
> >
> > On Tuesday 08 November 2005 9:13 am, Micah Stevens wrote:
> >>yeah, it would help if I read the whole post. Sorry.
> >>
> >>On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:
> >>>I suppose you could use "count( PDOStatement::fetchAll() )", but I
> >>>understand your amazement.
> >>>
> >>>mysql_num_rows() is specific to MySQL.  He wants a PDO version.
> >>>
> >>>-Original Message-
> >>>From: Micah Stevens [mailto:[EMAIL PROTECTED]
> >>>Sent: Tuesday, November 08, 2005 10:51 AM
> >>>To: php-db@lists.php.net
> >>>Subject: Re: [PHP-DB] [PDO] Number of rows found by Select
> >>>
> >>>
> >>>
> >>>mysql_num_rows()
> >>>
> >>>On Tuesday 08 November 2005 5:17 am, Rob C wrote:
> >>>>What is the recommended way to find the number of rows found by a
> >>>>SELECT query? PDOStatement::rowCount() doesn't work with MySQL and is a
> >>>>bit of a hack anyway. Doing a COUNT(*) before the SELECT is very
> >>>>hackish - the data could have changed and it's an extra query. What is
> >>>>there that's better than either of these? Is there any way to use
> >>>>COUNT(*) without risking data change, such as inside a transaction?
> >>>>
> >>>>I'm amazed that there is no mysql_num_rows() equivilent, I get the
> >>>>feeling that I'm missing something obvious. I can only presume there is
> >>>>some technical limitation that I'm not appreciating, if anyone can shed
> >>>>some light on this, I'd like to know.
> >>>>
> >>>>I'm new to both PDO and this mailing list, so please be gentle with me.
> >>>>I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.
> >>>>
> >>>>Rob
> >>>
> >>>--

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

Re: [PHP-DB] [PDO] Number of rows found by Select

[Rob C]

So would it be possible to write a select() function to handle the 
wierdness? I'm attempting to write one but I'm getting buffered query 
errors. This function is part of a Database Manager class and is 
supposed to return a PDO::Statement with the number of rows being stored 
in a referenced paramater.


Error:
'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other 
unbuffered queries are active. Consider using PDOStatement::fetchAll(). 
Alternatively, if your code is only ever going to run against mysql, you 
may enable query buffering by setting the 
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in 
E:\Server\swi\qpf\mng\data.class.php:64 Stack trace: #0 
E:\Server\swi\qpf\mng\data.class.php(64): PDOStatement->execute() #1 
E:\Server\swi\www\db.php(13): DM::select('* FROM cdrs WHE...', 20, -1) 
#2 {main} thrown in E:\Server\swi\qpf\mng\data.class.php on line 64



Function:

public static function select($sql, &$count = NULL, $limit = NULL)
{
$db = self::handle(); #Singleton method to create/retrieve db handle
$buf = array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE);

$sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;

$stmt = $db->prepare($sql, $buf);
$stmt->execute();

if (!is_null($count)) {
$rows = $db->prepare('SELECT found_rows() AS rows', $buf);
$rows->execute(); #ERROR HERE
$rows_array = $rows->fetch(PDO::FETCH_NUM);
$rows->closeCursor();
$count = $rows_array[0];

if (!is_null($limit) && $count > $limit) {
$count = $limt;
}
}

return $stmt;
}

Where am I going wrong here?

Rob



Micah Stevens wrote:
There's a function called 'found_rows()' function, so you could try issuing a 
query, then issuing a second one 'SELECT FOUND_ROWS();' and it should give 
the number of rows returned by the previous select. 


Here's details:
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html



On Tuesday 08 November 2005 9:13 am, Micah Stevens wrote:


yeah, it would help if I read the whole post. Sorry.

On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:


I suppose you could use "count( PDOStatement::fetchAll() )", but I
understand your amazement.

mysql_num_rows() is specific to MySQL.  He wants a PDO version.

-----Original Message-
From: Micah Stevens [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 08, 2005 10:51 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] [PDO] Number of rows found by Select



mysql_num_rows()

On Tuesday 08 November 2005 5:17 am, Rob C wrote:


What is the recommended way to find the number of rows found by a
SELECT query? PDOStatement::rowCount() doesn't work with MySQL and is a
bit of a hack anyway. Doing a COUNT(*) before the SELECT is very
hackish - the data could have changed and it's an extra query. What is
there that's better than either of these? Is there any way to use
COUNT(*) without risking data change, such as inside a transaction?

I'm amazed that there is no mysql_num_rows() equivilent, I get the
feeling that I'm missing something obvious. I can only presume there is
some technical limitation that I'm not appreciating, if anyone can shed
some light on this, I'd like to know.

I'm new to both PDO and this mailing list, so please be gentle with me.
I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.

Rob


--


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

Re: [PHP-DB] [PDO] Number of rows found by Select

[Micah Stevens]

There's a function called 'found_rows()' function, so you could try issuing a 
query, then issuing a second one 'SELECT FOUND_ROWS();' and it should give 
the number of rows returned by the previous select. 

Here's details:
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html



On Tuesday 08 November 2005 9:13 am, Micah Stevens wrote:
> yeah, it would help if I read the whole post. Sorry.
>
> On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:
> > I suppose you could use "count( PDOStatement::fetchAll() )", but I
> > understand your amazement.
> >
> > mysql_num_rows() is specific to MySQL.  He wants a PDO version.
> >
> > -Original Message-
> > From: Micah Stevens [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, November 08, 2005 10:51 AM
> > To: php-db@lists.php.net
> > Subject: Re: [PHP-DB] [PDO] Number of rows found by Select
> >
> >
> >
> > mysql_num_rows()
> >
> > On Tuesday 08 November 2005 5:17 am, Rob C wrote:
> > > What is the recommended way to find the number of rows found by a
> > > SELECT query? PDOStatement::rowCount() doesn't work with MySQL and is a
> > > bit of a hack anyway. Doing a COUNT(*) before the SELECT is very
> > > hackish - the data could have changed and it's an extra query. What is
> > > there that's better than either of these? Is there any way to use
> > > COUNT(*) without risking data change, such as inside a transaction?
> > >
> > > I'm amazed that there is no mysql_num_rows() equivilent, I get the
> > > feeling that I'm missing something obvious. I can only presume there is
> > > some technical limitation that I'm not appreciating, if anyone can shed
> > > some light on this, I'd like to know.
> > >
> > > I'm new to both PDO and this mailing list, so please be gentle with me.
> > > I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.
> > >
> > > Rob
> >
> > --

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

Re: [PHP-DB] [PDO] Number of rows found by Select

[Micah Stevens]

yeah, it would help if I read the whole post. Sorry. 

On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:
> I suppose you could use "count( PDOStatement::fetchAll() )", but I
> understand your amazement.
>
> mysql_num_rows() is specific to MySQL.  He wants a PDO version.
>
> -Original Message-
> From: Micah Stevens [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 08, 2005 10:51 AM
> To: php-db@lists.php.net
> Subject: Re: [PHP-DB] [PDO] Number of rows found by Select
>
>
>
> mysql_num_rows()
>
> On Tuesday 08 November 2005 5:17 am, Rob C wrote:
> > What is the recommended way to find the number of rows found by a SELECT
> >   query? PDOStatement::rowCount() doesn't work with MySQL and is a bit
> > of a hack anyway. Doing a COUNT(*) before the SELECT is very hackish -
> > the data could have changed and it's an extra query. What is there
> > that's better than either of these? Is there any way to use COUNT(*)
> > without risking data change, such as inside a transaction?
> >
> > I'm amazed that there is no mysql_num_rows() equivilent, I get the
> > feeling that I'm missing something obvious. I can only presume there is
> > some technical limitation that I'm not appreciating, if anyone can shed
> > some light on this, I'd like to know.
> >
> > I'm new to both PDO and this mailing list, so please be gentle with me.
> > I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.
> >
> > Rob
>
> --

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

RE: [PHP-DB] [PDO] Number of rows found by Select

[Dwight Altman]

I suppose you could use "count( PDOStatement::fetchAll() )", but I
understand your amazement.

mysql_num_rows() is specific to MySQL.  He wants a PDO version.

-Original Message-
From: Micah Stevens [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 08, 2005 10:51 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] [PDO] Number of rows found by Select



mysql_num_rows()


On Tuesday 08 November 2005 5:17 am, Rob C wrote:
> What is the recommended way to find the number of rows found by a SELECT
>   query? PDOStatement::rowCount() doesn't work with MySQL and is a bit
> of a hack anyway. Doing a COUNT(*) before the SELECT is very hackish -
> the data could have changed and it's an extra query. What is there
> that's better than either of these? Is there any way to use COUNT(*)
> without risking data change, such as inside a transaction?
>
> I'm amazed that there is no mysql_num_rows() equivilent, I get the
> feeling that I'm missing something obvious. I can only presume there is
> some technical limitation that I'm not appreciating, if anyone can shed
> some light on this, I'd like to know.
>
> I'm new to both PDO and this mailing list, so please be gentle with me.
> I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.
>
> Rob

--

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

Re: [PHP-DB] [PDO] Number of rows found by Select

[Micah Stevens]

mysql_num_rows()


On Tuesday 08 November 2005 5:17 am, Rob C wrote:
> What is the recommended way to find the number of rows found by a SELECT
>   query? PDOStatement::rowCount() doesn't work with MySQL and is a bit
> of a hack anyway. Doing a COUNT(*) before the SELECT is very hackish -
> the data could have changed and it's an extra query. What is there
> that's better than either of these? Is there any way to use COUNT(*)
> without risking data change, such as inside a transaction?
>
> I'm amazed that there is no mysql_num_rows() equivilent, I get the
> feeling that I'm missing something obvious. I can only presume there is
> some technical limitation that I'm not appreciating, if anyone can shed
> some light on this, I'd like to know.
>
> I'm new to both PDO and this mailing list, so please be gentle with me.
> I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.
>
> Rob

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