[PHP-DB] MySQLi, MongoDB and MySQL work together

[Matt Zand]

Hi,

I have a large application that uses MySQLi and MySQL. We are planning to
add few custom plug-in written in MEAN stack with Node.JS and MongoDB.

Before integration, is there any precaution we should consider. Also, if
data is shared between MongoDB db and MySQLi, should I create an API to
synchronize data or what is best way to achieve that?

I have basic knowledge of MongoDB, so I appreciate your advice.

-- 
Cheers,

Matt Zand
Cell: 202-420-9192
Work: 240-200-6131
High School Technology Services 
DC Web Makers 
Coding Bootcamps 

Re: [PHP-DB] MySQLi

[Karl DeSaulniers]

On Sep 15, 2014, at 11:51 AM, Jasper Kips  wrote:

> 
> 
>> Op 15 sep. 2014 om 01:08 heeft Karl DeSaulniers  het 
>> volgende geschreven:
>> 
>> Procedural php. Haven't had a chance to learn OOP. Would love to though. : /
>> I think I may be moling it a bit too. Guess I was hoping for an easy fix, 
>> don't judge me.. :P
>> Thanks for all your responses everyone. 
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com
>> 
>> 
>> 
>>> On Sep 14, 2014, at 2:48 AM, Lester Caine  wrote:
>>> 
 On 14/09/14 04:57, Karl DeSaulniers wrote:
 Awesome, thanks for the link. I know even less about PDO then I do regular 
 MySQL however.
 I am hoping MySQLi isn't too far off a shoot. Just need to sit down with 
 it all and figure out a path.
>>> 
>>> PDO is still a bit of a grey area. It was intended to make changes
>>> between database engines more transparent, and MySQL to MySQLi is
>>> essentially just a different front end both targeting MySQL. The problem
>>> is that it only ever did half the job, trying to make the data returned
>>> 'transparent' while ignoring the SQL. Aziz's approach is one way of
>>> going, but just like the e_strict problems with PHP itself, it's the
>>> subtle changes to the language used which cause problems when
>>> 'translating' from one to the other. It will depend on the style of
>>> MySQL you are using currently as to how easy it is to 'translate'. My
>>> own database is Firebird which has SQL functions that have yet to appear
>>> in MySQL, some of those are exposed in MySQLi so you can ignore them
>>> when upgrading but it is always those fringe cases that take the most
>>> time to resolve? :(
>>> 
>>> -- 
>>> Lester Caine - G8HFL
>> 
>> 
>> 
>> 
>> -- 
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>> 
> 
> Problem with easy fixes is that they lead to tremendous problems. Best is to 
> take the more difficult road, it'll be way quicker.
> Yup off topic I know
> 
> Jasper
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 


Truth. Thanks.

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] MySQLi

[Jasper Kips]

> Op 15 sep. 2014 om 01:08 heeft Karl DeSaulniers  het 
> volgende geschreven:
> 
> Procedural php. Haven't had a chance to learn OOP. Would love to though. : /
> I think I may be moling it a bit too. Guess I was hoping for an easy fix, 
> don't judge me.. :P
> Thanks for all your responses everyone. 
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> 
>> On Sep 14, 2014, at 2:48 AM, Lester Caine  wrote:
>> 
>>> On 14/09/14 04:57, Karl DeSaulniers wrote:
>>> Awesome, thanks for the link. I know even less about PDO then I do regular 
>>> MySQL however.
>>> I am hoping MySQLi isn't too far off a shoot. Just need to sit down with it 
>>> all and figure out a path.
>> 
>> PDO is still a bit of a grey area. It was intended to make changes
>> between database engines more transparent, and MySQL to MySQLi is
>> essentially just a different front end both targeting MySQL. The problem
>> is that it only ever did half the job, trying to make the data returned
>> 'transparent' while ignoring the SQL. Aziz's approach is one way of
>> going, but just like the e_strict problems with PHP itself, it's the
>> subtle changes to the language used which cause problems when
>> 'translating' from one to the other. It will depend on the style of
>> MySQL you are using currently as to how easy it is to 'translate'. My
>> own database is Firebird which has SQL functions that have yet to appear
>> in MySQL, some of those are exposed in MySQLi so you can ignore them
>> when upgrading but it is always those fringe cases that take the most
>> time to resolve? :(
>> 
>> -- 
>> Lester Caine - G8HFL
> 
> 
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

Problem with easy fixes is that they lead to tremendous problems. Best is to 
take the more difficult road, it'll be way quicker.
Yup off topic I know

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

Re: [PHP-DB] MySQLi

[Karl DeSaulniers]

Procedural php. Haven't had a chance to learn OOP. Would love to though. : /
I think I may be moling it a bit too. Guess I was hoping for an easy fix, don't 
judge me.. :P
Thanks for all your responses everyone. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



On Sep 14, 2014, at 2:48 AM, Lester Caine  wrote:

> On 14/09/14 04:57, Karl DeSaulniers wrote:
>> Awesome, thanks for the link. I know even less about PDO then I do regular 
>> MySQL however.
>> I am hoping MySQLi isn't too far off a shoot. Just need to sit down with it 
>> all and figure out a path.
> 
> PDO is still a bit of a grey area. It was intended to make changes
> between database engines more transparent, and MySQL to MySQLi is
> essentially just a different front end both targeting MySQL. The problem
> is that it only ever did half the job, trying to make the data returned
> 'transparent' while ignoring the SQL. Aziz's approach is one way of
> going, but just like the e_strict problems with PHP itself, it's the
> subtle changes to the language used which cause problems when
> 'translating' from one to the other. It will depend on the style of
> MySQL you are using currently as to how easy it is to 'translate'. My
> own database is Firebird which has SQL functions that have yet to appear
> in MySQL, some of those are exposed in MySQLi so you can ignore them
> when upgrading but it is always those fringe cases that take the most
> time to resolve? :(
> 
> -- 
> Lester Caine - G8HFL




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

Re: [PHP-DB] MySQLi

[Lester Caine]

On 14/09/14 04:57, Karl DeSaulniers wrote:
> Awesome, thanks for the link. I know even less about PDO then I do regular 
> MySQL however.
> I am hoping MySQLi isn't too far off a shoot. Just need to sit down with it 
> all and figure out a path.

PDO is still a bit of a grey area. It was intended to make changes
between database engines more transparent, and MySQL to MySQLi is
essentially just a different front end both targeting MySQL. The problem
is that it only ever did half the job, trying to make the data returned
'transparent' while ignoring the SQL. Aziz's approach is one way of
going, but just like the e_strict problems with PHP itself, it's the
subtle changes to the language used which cause problems when
'translating' from one to the other. It will depend on the style of
MySQL you are using currently as to how easy it is to 'translate'. My
own database is Firebird which has SQL functions that have yet to appear
in MySQL, some of those are exposed in MySQLi so you can ignore them
when upgrading but it is always those fringe cases that take the most
time to resolve? :(

-- 
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] MySQLi

[Karl DeSaulniers]

Awesome, thanks for the link. I know even less about PDO then I do regular 
MySQL however.
I am hoping MySQLi isn't too far off a shoot. Just need to sit down with it all 
and figure out a path.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



On Sep 13, 2014, at 10:52 PM, Aziz Saleh  wrote:
> 
> I am not sure about any MySQL -> MySQLI wrappers, but I did built a MySQL -> 
> PDO Wrapper a while back that you can use if you have PDO_MySQL enabled:
> 
> https://github.com/azizsaleh/mysql

Re: [PHP-DB] MySQLi

[Aziz Saleh]

On Sat, Sep 13, 2014 at 8:55 PM, Karl DeSaulniers 
wrote:

> On Sep 13, 2014, at 7:40 AM, Lester Caine  wrote:
>
> > On 13/09/14 11:40, Karl DeSaulniers wrote:
> >> Hope this message finds you well. Quick question about MySQLi and PHP.
> >> I have a website that was built back in 2012 that is still on PHP 5.2
> and MySQL
> >> and I am wanting to update it to PHP 5.7 with MySQLi without headaches.
> >> I am dreading this like a spoonful of molasses. Is there any sugar
> remedy for this medicine
> >> or do I just grow a pair and take it?
> >
> > Well a few problems 5ere ...
> > PHP5.2 had already been shelved at end of 2010, but I know why it was
> > probably used 2 years later. I'm STILL running 5.2 on servers as the
> > time needed to convert those sites is just not available and can't be
> > justified cost wise :(
> >
> >> Any MySQL => MySQLi converters out there?
> >> Any PHP5.2 => PHP 5.7 cheat sheets?
> >>
> >> If I update my server to PHP 5.7 is everything going to break? Or
> stupid question of course it is?
> > PHP5.7 will not be around any time soon, PHP5.6 has just been released.
> > But converting from 5.2 all the way to 5.6 is not something that is easy
> > to do. I'm still only moving 5.2 to 5.4 at presnt.
> >
> > http://php.net/manual/en/migration53.php and
> > http://php.net/manual/en/migration54.php is the starting point, but
> > things depricated in 5.3 were removed in 5.4 so if you are using any of
> > those methods then they need removing. You can switch the later PHP
> > servers to ignore e_strict warning/errors, but this is the major problem
> > area. and really the only way to move forward is clear all of those
> > problems before moving forward. Unless you caa ensure your server will
> > always be switched bak to a compatible mode of working.
> >
> > And all that before even looking at MySQL ... I've never used it, so
> > hopefully someone else will cover that side.
> >
> > --
> > Lester Caine - G8HFL
>
> Ah, I see. Now Glob Design's comment makes sense.
> Thanks Lester.
>
> 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
>
>
I am not sure about any MySQL -> MySQLI wrappers, but I did built a MySQL
-> PDO Wrapper a while back that you can use if you have PDO_MySQL enabled:

https://github.com/azizsaleh/mysql

Re: [PHP-DB] MySQLi

[Karl DeSaulniers]

On Sep 13, 2014, at 7:40 AM, Lester Caine  wrote:

> On 13/09/14 11:40, Karl DeSaulniers wrote:
>> Hope this message finds you well. Quick question about MySQLi and PHP.
>> I have a website that was built back in 2012 that is still on PHP 5.2 and 
>> MySQL 
>> and I am wanting to update it to PHP 5.7 with MySQLi without headaches.
>> I am dreading this like a spoonful of molasses. Is there any sugar remedy 
>> for this medicine 
>> or do I just grow a pair and take it?
> 
> Well a few problems 5ere ...
> PHP5.2 had already been shelved at end of 2010, but I know why it was
> probably used 2 years later. I'm STILL running 5.2 on servers as the
> time needed to convert those sites is just not available and can't be
> justified cost wise :(
> 
>> Any MySQL => MySQLi converters out there?
>> Any PHP5.2 => PHP 5.7 cheat sheets?
>> 
>> If I update my server to PHP 5.7 is everything going to break? Or stupid 
>> question of course it is?
> PHP5.7 will not be around any time soon, PHP5.6 has just been released.
> But converting from 5.2 all the way to 5.6 is not something that is easy
> to do. I'm still only moving 5.2 to 5.4 at presnt.
> 
> http://php.net/manual/en/migration53.php and
> http://php.net/manual/en/migration54.php is the starting point, but
> things depricated in 5.3 were removed in 5.4 so if you are using any of
> those methods then they need removing. You can switch the later PHP
> servers to ignore e_strict warning/errors, but this is the major problem
> area. and really the only way to move forward is clear all of those
> problems before moving forward. Unless you caa ensure your server will
> always be switched bak to a compatible mode of working.
> 
> And all that before even looking at MySQL ... I've never used it, so
> hopefully someone else will cover that side.
> 
> -- 
> Lester Caine - G8HFL

Ah, I see. Now Glob Design's comment makes sense. 
Thanks Lester.

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] MySQLi

[Lester Caine]

On 13/09/14 11:40, Karl DeSaulniers wrote:
> Hope this message finds you well. Quick question about MySQLi and PHP.
> I have a website that was built back in 2012 that is still on PHP 5.2 and 
> MySQL 
> and I am wanting to update it to PHP 5.7 with MySQLi without headaches.
> I am dreading this like a spoonful of molasses. Is there any sugar remedy for 
> this medicine 
> or do I just grow a pair and take it?

Well a few problems 5ere ...
PHP5.2 had already been shelved at end of 2010, but I know why it was
probably used 2 years later. I'm STILL running 5.2 on servers as the
time needed to convert those sites is just not available and can't be
justified cost wise :(

> Any MySQL => MySQLi converters out there?
> Any PHP5.2 => PHP 5.7 cheat sheets?
> 
> If I update my server to PHP 5.7 is everything going to break? Or stupid 
> question of course it is?
PHP5.7 will not be around any time soon, PHP5.6 has just been released.
But converting from 5.2 all the way to 5.6 is not something that is easy
to do. I'm still only moving 5.2 to 5.4 at presnt.

http://php.net/manual/en/migration53.php and
http://php.net/manual/en/migration54.php is the starting point, but
things depricated in 5.3 were removed in 5.4 so if you are using any of
those methods then they need removing. You can switch the later PHP
servers to ignore e_strict warning/errors, but this is the major problem
area. and really the only way to move forward is clear all of those
problems before moving forward. Unless you caa ensure your server will
always be switched bak to a compatible mode of working.

And all that before even looking at MySQL ... I've never used it, so
hopefully someone else will cover that side.

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

[PHP-DB] MySQLi

[Karl DeSaulniers]

Hello All,
Hope this message finds you well. Quick question about MySQLi and PHP.
I have a website that was built back in 2012 that is still on PHP 5.2 and MySQL 
and I am wanting to update it to PHP 5.7 with MySQLi without headaches.
I am dreading this like a spoonful of molasses. Is there any sugar remedy for 
this medicine 
or do I just grow a pair and take it?

Any MySQL => MySQLi converters out there?
Any PHP5.2 => PHP 5.7 cheat sheets?

If I update my server to PHP 5.7 is everything going to break? Or stupid 
question of course it is?
lol

TIA,
Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

[PHP-DB] MySQLi binding params and results and where in a loop they should be placed.

[Richard Quadling]

Hi.

Just looking for some clarity before I spend an hour trying to figure out
if I'm doing it right.

I'm reading a stream of product data from an external source.

I'm needing to tag the data as it goes through the system, indicating if
the product has been seen before.

So, I'm using a prepared statement with 2 params that identify the product
uniquely from the external source.

So, I have a prepare(), bind_param(), execute(), bind_result() and fetch().

I think prepare() and bind_param() take place outside of the loop.

And population of the bound variables, execute(), bind_result() and fetch()
all take place inside the loop.

Is that right?

Or can I bind_result BEFORE execute(), so all I need to do is populate the
bound variable (which is done from the reading from the external stream),
execute() and fetch().

The examples in the manual don't really demonstrate the re-use of a
prepared statement, making it both a little vague and pointless (excluding
the OBVIOUS advantage of sql injection protection - I'm using prepared
statements mainly for that reason).


-- 
Richard Quadling
Twitter : EE : Zend : Fantasy Shopper
@RQuadling : http://e-e.com/M_248814.html : http://bit.ly/9O8vFY :
http://fan.sh/106/5tw

[PHP-DB] RE: RE :[PHP-DB] MySQLi Help

[jstarritt]

 
Oliver,
 
Gah -- thank you.
 
James
 
-Original Message-
From: "Olivier Desmares" 
Sent: Saturday, June 16, 2012 10:58am
To: "jstarr...@selagodesign.com" , 
"php-db@lists.php.net" 
Subject: RE :[PHP-DB] MySQLi Help



Hi James,

The manual page for mysqli_stmt::execute has the following note :

When using mysqli_stmt_execute(), the  
[http://fr2.php.net/manual/en/mysqli-stmt.fetch.php] mysqli_stmt_fetch() 
function must be used to fetch the data prior to performing any additional 
queries.

So you cannot execute the inner (sub) query without first retrieving all the 
rows from the outer (main) query.

Olivier Desmares
 $stmt = mysqli_prepare($db, $sql);
 mysqli_stmt_execute($stmt);
 mysqli_stmt_bind_result($stmt, $authkv);

 while (mysqli_stmt_fetch($stmt)) {
 echo "MAIN: $authkv";
 sub($db);
} 

 mysqli_stmt_close($stmt);

This simple change results in the output.

MAIN: 7
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt,  boolean 
given in /var/www/html/xgwebapi/mysqli.php on line 21  Warning: 
mysqli_stmt_bind_result() expects parameter 1 to be  mysqli_stmt, boolean given 
in /var/www/html/xgwebapi/mysqli.php on line  22  Warning: mysqli_stmt_fetch() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 24  Warning: mysqli_stmt_close() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 28




*
James Starritt
Selago Design, Inc.

US: (312) 239 0592
jstarr...@selagodesign.com
*
 

[PHP-DB] RE :[PHP-DB] MySQLi Help

[Olivier Desmares]

Hi James,

The manual page for mysqli_stmt::execute has the following note :

When using mysqli_stmt_execute(), the 
mysqli_stmt_fetch() function must be used to fetch the
data prior to performing any additional queries.
   

So you cannot execute the inner (sub) query without first retrieving all the 
rows from the outer (main) query.

Olivier Desmares

 $stmt = mysqli_prepare($db, $sql);
 mysqli_stmt_execute($stmt);
 mysqli_stmt_bind_result($stmt, $authkv);
 
 while (mysqli_stmt_fetch($stmt)) {
 echo "
MAIN: $authkv
";
 sub($db);
} 
 
 mysqli_stmt_close($stmt);
 
This simple change results in the output.
 
MAIN: 7
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt,  boolean 
given in /var/www/html/xgwebapi/mysqli.php on line 21  Warning: 
mysqli_stmt_bind_result() expects parameter 1 to be  mysqli_stmt, boolean given 
in /var/www/html/xgwebapi/mysqli.php on line  22  Warning: mysqli_stmt_fetch() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 24  Warning: mysqli_stmt_close() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 28

[PHP-DB] MySQLi Help

[jstarritt]

 
Hi,
 
I am trying to slowly convert some old MySQL based code to the newer MySQLi 
extension and have come across something that I want to call a bug, but thought 
maybe as a newb with the extension that this might be something at least a few 
of you are familiar with.  Basically depending on where I call a function from 
I either get errors but there does not seem to be much rhyme or reason too it.  
Here is a very simple code sample -- yes its procedural ...
 
 SUB: $authkv";
 }
 
 mysqli_stmt_close($stmt);
}

function main($db) {

 $authkey = '';

 $sql = "select kv from obj order by kv limit 5";
 $stmt = mysqli_prepare($db, $sql);
 mysqli_stmt_execute($stmt);
 mysqli_stmt_bind_result($stmt, $authkv);

 while (mysqli_stmt_fetch($stmt)) {
 echo "MAIN: $authkv";
 }
 
 sub($db);
 mysqli_stmt_close($stmt);
}

main($db);

?>
 
I get the output I expect:
 
MAIN: 7
MAIN: 8
MAIN: 9
MAIN: 10
MAIN: 11
SUB: 7
SUB: 8
SUB: 9
SUB: 10
SUB: 11
 
This works perfectly however when I call sub from within the while loop in the 
function main this break down. Here is the modification to main:
 
function main($db) {
 
 $authkey = '';
 
 $sql = "select kv from obj order by kv limit 5";
 $stmt = mysqli_prepare($db, $sql);
 mysqli_stmt_execute($stmt);
 mysqli_stmt_bind_result($stmt, $authkv);
 
 while (mysqli_stmt_fetch($stmt)) {
 echo "MAIN: $authkv";
 sub($db);
} 
 
 mysqli_stmt_close($stmt);
 }
 
This simple change results in the output.
 
MAIN: 7
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt,  boolean 
given in /var/www/html/xgwebapi/mysqli.php on line 21  Warning: 
mysqli_stmt_bind_result() expects parameter 1 to be  mysqli_stmt, boolean given 
in /var/www/html/xgwebapi/mysqli.php on line  22  Warning: mysqli_stmt_fetch() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 24  Warning: mysqli_stmt_close() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 28
 
MAIN: 8
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt,  boolean 
given in /var/www/html/xgwebapi/mysqli.php on line 21  Warning: 
mysqli_stmt_bind_result() expects parameter 1 to be  mysqli_stmt, boolean given 
in /var/www/html/xgwebapi/mysqli.php on line  22  Warning: mysqli_stmt_fetch() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 24  Warning: mysqli_stmt_close() 
expects parameter 1 to be mysqli_stmt,  boolean given in 
/var/www/html/xgwebapi/mysqli.php on line 28
 
I do not understand why this would be the case -- clearly.  Does anybody have a 
clue for me?  I am using PHP 5.3.3 on CentOS.  And I am very very very stuck.
 
James

[PHP-DB] MYSQLi fetch corrupting bound variables

[Stuart Milne]

Hi there

Getting a weird issue with MySQLi fetch when using bound variables.

First of all here is some example code.

prepare('SELECT `id`, name FROM MYTABLE WHERE id = ?)
{

 if ($stmt->bind_param('i', $pID))
 {
 if ($stmt->bind_result($recordID, $name)
 {
 if ($stmt->execute())
{
 $stmt->fetch();
 }
 }
 }

}

?>

Now I initialize the $recordID variable to 0;

The SELECT statement then does a SELECT where id = 0; In my table there is no 
record with the id of 0;

Also notice that in my bind_result i am binding the the result into the 
$recordID variable.

Now this is where it gets weird for me. On some versions of PHP and MYSQl the 
$recordID variable is still set to 0 but on other versions of PHP mYSQL the 
$recordID seems to be overwritten and set to NULL from the fetch.

Any ideas as to why these differences are occurring.

Regards
Stu

E-MAIL DISCLAIMER
The information in this email is confidential and may be legally privileged. It 
is intended solely for the addressee. Access to this email by anyone else is 
unauthorised. If you are not the intended recipient, any disclosure, copying, 
distribution or any action taken or omitted to be taken in reliance on it, is 
prohibited and may be unlawful. All information provided, including but not 
limited to, quotations, system specifications and suggestions concerning 
hardware/software/services configurations are strictly subject to our standard 
terms and conditions of business, copies of which are available on request.


This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk

Re: [PHP-DB] mysqli mysqli_stmt::bind_param using bigint

[Maarten Foqué]

That's quite easy:

10358490906 gives a 8 byte long hexadecimal value of:
5AF325D8631A  
Since integer is 4 bytes long, only the last 4 bytes are probably stored
(or maybe the first 4, that could depend on the code, or your
endianness, but I don't know that).

Anywhay, 25D8631A gives a nice value of 634938138.  

Your second is hex 5AF310CBF00C, last 4 bytes 10CBF00C gives =>
281800716

Getting the original value back is not that difficult, just add
100  hex (5AF3)

Now this may just work for you, remember that you can't actually know
what was in those first 4 so adding 5AF3 may just be wrong.
However for the examples you gave, it seems to add up. 

So you need to have some indication of the range the originals would've
had or the numbers you get are a multiple of 2^32 off.

regards,
Maarten

On Thu, 2009-10-08 at 17:09 +0200, Philipp Moeser wrote:
> Hi all,
> 
> we used the bind_param function to insert a lot of integers into our 
> database. As I just found out now quite a few of them were bigints, and 
> did not end up correctly in the database, because the integer parameter 
> supports only integers up to 2^32.
> 
> Now my question: Anybody know how I can calculate the ID that I have in 
> my database out of the original ID that was supposed to go there?
> 
> Two examples:
> original ID: 10358490906
> result in DB: 634938138
> 
> original ID: 10005353484
> result in DB: 281800716
> 
> I suppose I cannot get the original ID from what i have in the DB, but 
> how does mysqli calculate that shorter value it passes to DB?
> 
> Thanks for any help
> Philipp
> 
> 

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

[PHP-DB] mysqli mysqli_stmt::bind_param using bigint

[Philipp Moeser]

Hi all,

we used the bind_param function to insert a lot of integers into our 
database. As I just found out now quite a few of them were bigints, and 
did not end up correctly in the database, because the integer parameter 
supports only integers up to 2^32.


Now my question: Anybody know how I can calculate the ID that I have in 
my database out of the original ID that was supposed to go there?


Two examples:
original ID: 10358490906
result in DB: 634938138

original ID: 10005353484
result in DB: 281800716

I suppose I cannot get the original ID from what i have in the DB, but 
how does mysqli calculate that shorter value it passes to DB?


Thanks for any help
Philipp


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

Re: [PHP-DB] mysqli error

[Niel Archer]

> Whenever I run the following code, I get the error: "Commands out of sync;
> you can't run this command now" as I try to execute my prepared Update
> statement.
> 
>  $fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open In File ");
> 
> //Connect to mySQL server
> $mysqli = new mysqli('localhost', 'user', 'pswd', 'db');
> if ($mysqli->connect_error) { die('Could not connect:
> '.$mysqli->connect_error); }
> else{ echo "Connected successfully\n"; }
> 
> $seqno = 0;
> $k = 'Kev';
> 
> $sql1 = 'SELECT UNIQUE_NUM, AM_CITY FROM db.kb_addr WHERE UNIQUE_NUM = ?';
> $sth1 = $mysqli->prepare($sql1);
> 
> $sql2 = 'UPDATE db.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM = ?';
> $sth2 = $mysqli->prepare($sql2);
> 
> while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
> 
> if($seqno == 0){
> $x= count($inrec);
> $arrFields = array();
> for ($y = 0; $y < $x; $y++) {
> $arrFields[$inrec[$y]] = $y; //creates associative array that
> associates fields with the index in $inrec
> }
> 
> echo "Array of Field Names From Header Record in Input data is \n";
> print_r($arrFields);
> $seqno++;
> continue;}
> 
> $key = 0+$inrec[$arrFields['Unique #']];
> 
> //Select Statement
> $sth1->bind_param('i',$key);
> $sth1->execute();
> $sth1->bind_result($un,$ac);
> $sth1->fetch();
> 
> //Update Statement
> $sth2->bind_param('si',$k,$key);
> echo "after bind: ".$sth2->error."\nThe object error is:
> $mysqli->error\n";
> $sth2->execute();
> echo "after execute:  ".$sth2->error."\nThe object error is:
> $mysqli->error\n";
> 
> if($seqno > 1000) break;
> $seqno++;
> }
> 
> fclose($fpiDataAddr) or die("can not close file");
> 
> //disconnect
> $sth1->close();
> $sth2->close();
> $mysqli->close();
> ?>
> 
> 
> 
> However, if I close $sth1 (the select statement) before executing $sth2 (the
> update statement), it works, but since I just closed $sth1, I have to
> prepare it again. This is pretty inefficient considering the large data set
> that I'm working with and the fact that I have to prepare and close my
> select statement every single time I loop through. Is there any way that I
> can run these statements error-free without having to close the select
> statement ($sth1) every single time I want to execute my update statement
> ($sth2)?

You do not initialise your statement object.  I would guess this is a
large part of the problem, although I'm not that familiar with prepared
statements in MySQLi.

 See the documentation for mysqli::stmt_init[1]  and the example for
mysqli_stmt::prepare[2] in the php documentation.

[1] http://docs.php.net/manual/en/mysqli.stmt-init.php
[2] http://docs.php.net/manual/en/mysqli-stmt.prepare.php


> Thanks,
>   Kevin

--
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] mysqli error

[Jason Gerfen]

Jason Gerfen wrote:
> Kevin Castiglia wrote:
>> Whenever I run the following code, I get the error: "Commands out of sync;
>> you can't run this command now" as I try to execute my prepared Update
>> statement.
>>
>> > $fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open In File ");
>>
>> //Connect to mySQL server
>> $mysqli = new mysqli('localhost', 'user', 'pswd', 'db');
> 
> Google... http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html
> 
> 
>> if ($mysqli->connect_error) { die('Could not connect:
>> '.$mysqli->connect_error); }
>> else{ echo "Connected successfully\n"; }
>>
>> $seqno = 0;
>> $k = 'Kev';
>>
>> $sql1 = 'SELECT UNIQUE_NUM, AM_CITY FROM db.kb_addr WHERE UNIQUE_NUM = ?';
>> $sth1 = $mysqli->prepare($sql1);
>>
>> $sql2 = 'UPDATE db.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM = ?';
>> $sth2 = $mysqli->prepare($sql2);
>>
>> while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
>>
>> if($seqno == 0){
>> $x= count($inrec);
>> $arrFields = array();
>> for ($y = 0; $y < $x; $y++) {
>> $arrFields[$inrec[$y]] = $y; //creates associative array that
>> associates fields with the index in $inrec
>> }
>>
>> echo "Array of Field Names From Header Record in Input data is \n";
>> print_r($arrFields);
>> $seqno++;
>> continue;}
>>
>> $key = 0+$inrec[$arrFields['Unique #']];
>>
>> //Select Statement
>> $sth1->bind_param('i',$key);
>> $sth1->execute();
>> $sth1->bind_result($un,$ac);
>> $sth1->fetch();
>>
>> //Update Statement
>> $sth2->bind_param('si',$k,$key);
>> echo "after bind: ".$sth2->error."\nThe object error is:
>> $mysqli->error\n";
>> $sth2->execute();
>> echo "after execute:  ".$sth2->error."\nThe object error is:
>> $mysqli->error\n";
>>
>> if($seqno > 1000) break;
>> $seqno++;
>> }
>>
>> fclose($fpiDataAddr) or die("can not close file");
>>
>> //disconnect
>> $sth1->close();
>> $sth2->close();
>> $mysqli->close();
>> ?>
>>
>>
>>
>> However, if I close $sth1 (the select statement) before executing $sth2 (the
>> update statement), it works, but since I just closed $sth1, I have to
>> prepare it again. This is pretty inefficient considering the large data set
>> that I'm working with and the fact that I have to prepare and close my
>> select statement every single time I loop through. Is there any way that I
>> can run these statements error-free without having to close the select
>> statement ($sth1) every single time I want to execute my update statement
>> ($sth2)?
>>

I am unaccustomed to utilizing the mysqli functionality but the older
mysql functions allow you to do something within your loops without the
need to close then prepare a new connection/query each time.

mysql_pconnect()
mysql_select_db()
for( $x > $y ) {
 mysql_query()
 mysql_fetch_array()
 mysql_free_result()
}
mysql_close()

These functions may be more appropriate for your datasets

>> Thanks,
>>   Kevin
>>
> 
> 


-- 
Jas

"Tomorrow isn't promised so we live for today"

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

Re: [PHP-DB] mysqli error

[Jason Gerfen]

Kevin Castiglia wrote:
> Whenever I run the following code, I get the error: "Commands out of sync;
> you can't run this command now" as I try to execute my prepared Update
> statement.
> 
>  $fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open In File ");
> 
> //Connect to mySQL server
> $mysqli = new mysqli('localhost', 'user', 'pswd', 'db');

Google... http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html


> if ($mysqli->connect_error) { die('Could not connect:
> '.$mysqli->connect_error); }
> else{ echo "Connected successfully\n"; }
> 
> $seqno = 0;
> $k = 'Kev';
> 
> $sql1 = 'SELECT UNIQUE_NUM, AM_CITY FROM db.kb_addr WHERE UNIQUE_NUM = ?';
> $sth1 = $mysqli->prepare($sql1);
> 
> $sql2 = 'UPDATE db.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM = ?';
> $sth2 = $mysqli->prepare($sql2);
> 
> while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
> 
> if($seqno == 0){
> $x= count($inrec);
> $arrFields = array();
> for ($y = 0; $y < $x; $y++) {
> $arrFields[$inrec[$y]] = $y; //creates associative array that
> associates fields with the index in $inrec
> }
> 
> echo "Array of Field Names From Header Record in Input data is \n";
> print_r($arrFields);
> $seqno++;
> continue;}
> 
> $key = 0+$inrec[$arrFields['Unique #']];
> 
> //Select Statement
> $sth1->bind_param('i',$key);
> $sth1->execute();
> $sth1->bind_result($un,$ac);
> $sth1->fetch();
> 
> //Update Statement
> $sth2->bind_param('si',$k,$key);
> echo "after bind: ".$sth2->error."\nThe object error is:
> $mysqli->error\n";
> $sth2->execute();
> echo "after execute:  ".$sth2->error."\nThe object error is:
> $mysqli->error\n";
> 
> if($seqno > 1000) break;
> $seqno++;
> }
> 
> fclose($fpiDataAddr) or die("can not close file");
> 
> //disconnect
> $sth1->close();
> $sth2->close();
> $mysqli->close();
> ?>
> 
> 
> 
> However, if I close $sth1 (the select statement) before executing $sth2 (the
> update statement), it works, but since I just closed $sth1, I have to
> prepare it again. This is pretty inefficient considering the large data set
> that I'm working with and the fact that I have to prepare and close my
> select statement every single time I loop through. Is there any way that I
> can run these statements error-free without having to close the select
> statement ($sth1) every single time I want to execute my update statement
> ($sth2)?
> 
> Thanks,
>   Kevin
> 


-- 
Jas
"Tomorrow isn't promised so we live for today"

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

[PHP-DB] mysqli error

[Kevin Castiglia]

Whenever I run the following code, I get the error: "Commands out of sync;
you can't run this command now" as I try to execute my prepared Update
statement.

connect_error) { die('Could not connect:
'.$mysqli->connect_error); }
else{ echo "Connected successfully\n"; }

$seqno = 0;
$k = 'Kev';

$sql1 = 'SELECT UNIQUE_NUM, AM_CITY FROM db.kb_addr WHERE UNIQUE_NUM = ?';
$sth1 = $mysqli->prepare($sql1);

$sql2 = 'UPDATE db.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM = ?';
$sth2 = $mysqli->prepare($sql2);

while($inrec = fgetcsv($fpiDataAddr,0,',','"')){

if($seqno == 0){
$x= count($inrec);
$arrFields = array();
for ($y = 0; $y < $x; $y++) {
$arrFields[$inrec[$y]] = $y; //creates associative array that
associates fields with the index in $inrec
}

echo "Array of Field Names From Header Record in Input data is \n";
print_r($arrFields);
$seqno++;
continue;}

$key = 0+$inrec[$arrFields['Unique #']];

//Select Statement
$sth1->bind_param('i',$key);
$sth1->execute();
$sth1->bind_result($un,$ac);
$sth1->fetch();

//Update Statement
$sth2->bind_param('si',$k,$key);
echo "after bind: ".$sth2->error."\nThe object error is:
$mysqli->error\n";
$sth2->execute();
echo "after execute:  ".$sth2->error."\nThe object error is:
$mysqli->error\n";

if($seqno > 1000) break;
$seqno++;
}

fclose($fpiDataAddr) or die("can not close file");

//disconnect
$sth1->close();
$sth2->close();
$mysqli->close();
?>



However, if I close $sth1 (the select statement) before executing $sth2 (the
update statement), it works, but since I just closed $sth1, I have to
prepare it again. This is pretty inefficient considering the large data set
that I'm working with and the fact that I have to prepare and close my
select statement every single time I loop through. Is there any way that I
can run these statements error-free without having to close the select
statement ($sth1) every single time I want to execute my update statement
($sth2)?

Thanks,
  Kevin

[PHP-DB] MySQLi connections

[Fergus Gibson]

-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Fri, Nov 28, 2008 at 12:47 AM
Subject: Re: Fwd: Fwd: [PHP-DB] MySQLi connections
To: "J. Hill" <[EMAIL PROTECTED]>


On Wed, Nov 26, 2008 at 9:38 PM, J. Hill <[EMAIL PROTECTED]> wrote:
> I understand there are many cases where database abstraction layers are
> preferable, but in some applications which are heavily dependent on a
> specific database's unique functions, it seems to me that avoiding
> abstraction would decrease the complexity and improve the performance of the
> application -- at the cost of making it more difficult to switch database
> servers.

I don't necessarily mean something pre-built.  You can create your own
abstraction by writing a class composed of the database object you
want to use.  As long as you have a consistent interface and use
encapsulation, you get all the benefits of abstraction.  It actually
hides complexity at the same time that it makes it easier if you need
to migrate to a different database server in the future.  The key to
abstraction is to hide everything but the interface from the rest of
the application so that the other elements can't become dependent on
the specific implementation.

[...]
> instead just put
> all the core mysqli calls into a library -- cleaner than scattering them
> across all the files.

That's pretty much the same thing. ;)  The point is that only one
component of your application should have to know about the database.
The rest of it should be completely ignorant.  That way, you only have
to update that one component in a migration.  What sort of object or
functions you use to do the actual database operations is not my
point.  It's about ensuring that the rest of your application never
knows if you're using MySQL, SQL Server, Oracle, Postgres, or
something else.

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

Re: [PHP-DB] MySQLi not closing connections

[Jonathan Langevin]

I was thinking the same bug, except that I'm not using real_connect
(pass login params when initializing the class), and also the issue
only occurs when logging is enabled.

I'm really at a loss here :-\

--
Jonathan Langevin
PHP Site Solutions
http://www.phpsitesolutions.com



On Wed, Nov 26, 2008 at 2:50 PM, Fergus Gibson <[EMAIL PROTECTED]> wrote:
> On Wed, Nov 26, 2008 at 10:36 AM, Jonathan Langevin
> <[EMAIL PROTECTED]> wrote:
>> I would normally think there were problems elsewhere in the code, if
>> the issues didn't stop once I comment out the logging functionality.
>
> I don't see anything that would account for your symptoms in that code
> snippet, but it's important to remember that sometimes code in one
> place can interact with code in another place to expose a bug.  When
> your application uses ext/mysql, it uses only built-in functions.
> It's only when it uses ext/mysqli that it uses a customized sub-class.
>  I would look in that subclass for the problem.
>
> But honestly, I'm flummoxed, Jon.  Since non-persistent connections
> should automatically close when the script ends (normally or for an
> error), I don't understand why the connections are remaining open only
> when logging is enabled.  I would expect it to be a by-product of the
> connection process instead or a bug.
>
> Does the application use mysql_real_connect() or mysqli_real_connect()
> at all?  If so, are you using a version of PHP pre-5.3?  There is a
> bug and a bug fix for it.
>
> http://bugs.mysql.com/bug.php?id=33831
> http://bugs.php.net/bug.php?id=39457
>

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

Re: Fwd: Fwd: [PHP-DB] MySQLi connections

[Fergus Gibson]

On Wed, Nov 26, 2008 at 1:45 PM, Chris <[EMAIL PROTECTED]> wrote:
>> It's bad if you ever want to use something other than mysqli!  Imagine
>> your company switching to another database server.  You'd have to
>> rewrite code in hundreds of functions!
[...]
> You're going to have a lot more problems with changing db servers than just
> search/replac'ing mysqli calls. Eg - 'LIMIT' - the format is different for
> postgres, mssql doesn't have it nor does oracle.

In case it was unclear, that's precisely my point.  Abstraction would
make it much easier to change databases later if need be because all
the SQL could be contained in a class with a simple interface exposed
to the rest of the application.  Switching databases would only
involve making changes to that class then, which is much easier than
trying to update many functions in many different places.  In the
current scheme Mr. Hill describes, each function is tightly coupled to
the database because they all feed SQL to the mysqli class directly.
He asked what's wrong with the scheme, and I think it's fair to say
that's the problem.

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

Re: Fwd: Fwd: [PHP-DB] MySQLi connections

[Chris]

Fergus Gibson wrote:

-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Wed, Nov 26, 2008 at 11:55 AM
Subject: Re: Fwd: [PHP-DB] MySQLi connections
To: "J. Hill" <[EMAIL PROTECTED]>


On Tue, Nov 25, 2008 at 3:12 PM, J. Hill <[EMAIL PROTECTED]> wrote:

I am used to creating a class and a database handle for functions to use,
but I inherited an intranet that just uses a single "$mysqli =
mysqli_connect " in a global main file and the just uses "global
$mysqli" in all of it's functions (several hundred) that interact with the
database.

[...]

Could anyone point me towards any documentation on why such a structure is
bad?


It's bad if you ever want to use something other than mysqli!  Imagine
your company switching to another database server.  You'd have to
rewrite code in hundreds of functions!  This is where a database
wrapper class could have saved a lot of headache.  As it is, the least
expensive way to migrate databases permitted by the original
programmer's choice would be to write an adapter class and substitute
it in place of mysqli and hope there are no SQL incompatibilities.
Not very efficient though.


You're going to have a lot more problems with changing db servers than 
just search/replac'ing mysqli calls. Eg - 'LIMIT' - the format is 
different for postgres, mssql doesn't have it nor does oracle.


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


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

Fwd: Fwd: [PHP-DB] MySQLi connections

[Fergus Gibson]

-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Wed, Nov 26, 2008 at 11:55 AM
Subject: Re: Fwd: [PHP-DB] MySQLi connections
To: "J. Hill" <[EMAIL PROTECTED]>


On Tue, Nov 25, 2008 at 3:12 PM, J. Hill <[EMAIL PROTECTED]> wrote:
> I am used to creating a class and a database handle for functions to use,
> but I inherited an intranet that just uses a single "$mysqli =
> mysqli_connect " in a global main file and the just uses "global
> $mysqli" in all of it's functions (several hundred) that interact with the
> database.
[...]
> Could anyone point me towards any documentation on why such a structure is
> bad?

It's bad if you ever want to use something other than mysqli!  Imagine
your company switching to another database server.  You'd have to
rewrite code in hundreds of functions!  This is where a database
wrapper class could have saved a lot of headache.  As it is, the least
expensive way to migrate databases permitted by the original
programmer's choice would be to write an adapter class and substitute
it in place of mysqli and hope there are no SQL incompatibilities.
Not very efficient though.

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

Fwd: [PHP-DB] MySQLi not closing connections

[Fergus Gibson]

-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Wed, Nov 26, 2008 at 11:50 AM
Subject: Re: [PHP-DB] MySQLi not closing connections
To: Jonathan Langevin <[EMAIL PROTECTED]>


On Wed, Nov 26, 2008 at 10:36 AM, Jonathan Langevin
<[EMAIL PROTECTED]> wrote:
> I would normally think there were problems elsewhere in the code, if
> the issues didn't stop once I comment out the logging functionality.

I don't see anything that would account for your symptoms in that code
snippet, but it's important to remember that sometimes code in one
place can interact with code in another place to expose a bug.  When
your application uses ext/mysql, it uses only built-in functions.
It's only when it uses ext/mysqli that it uses a customized sub-class.
 I would look in that subclass for the problem.

But honestly, I'm flummoxed, Jon.  Since non-persistent connections
should automatically close when the script ends (normally or for an
error), I don't understand why the connections are remaining open only
when logging is enabled.  I would expect it to be a by-product of the
connection process instead or a bug.

Does the application use mysql_real_connect() or mysqli_real_connect()
at all?  If so, are you using a version of PHP pre-5.3?  There is a
bug and a bug fix for it.

http://bugs.mysql.com/bug.php?id=33831
http://bugs.php.net/bug.php?id=39457

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

Fwd: Fwd: [PHP-DB] MySQLi not closing connections

[Fergus Gibson]

-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Wed, Nov 26, 2008 at 11:34 AM
Subject: Re: Fwd: [PHP-DB] MySQLi not closing connections
To: Chris <[EMAIL PROTECTED]>


On Tue, Nov 25, 2008 at 1:39 PM, Chris <[EMAIL PROTECTED]> wrote:
> Not really true.
>
> pconnect leaves the connection open ('persistent'). From the manual: "when
> connecting, the function would first try to find a (persistent) link that's
> already open with the same host, username and password."

Here's a citation from Zend.com:

"The mysql_pconnect() function was designed to provide a mechanism for
reducing the cost of establishing and closing connections to the MySQL
server. Unfortunately, due to an interaction between the architecture
of the Apache server and the architecture of PHP, high traffic on a
site that used pconnects could quickly clog up the MySQL server with
many unused connections that could prevent many of the active
connections from accessing the database."

http://devzone.zend.com/node/view/id/686#fn1

I understand how mysql_pconnect() is meant to work, which is what you
quote from the documentation, but I also believe the claims that there
is an incompatibility between Apache specifically and
mysql_pconnect().

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

Re: [PHP-DB] MySQLi not closing connections

[Jonathan Langevin]

Hi Fergus, you're correct, the original code was developed in a hasty
fashion by the developer that I am now working alongside.
It's a situation where he's been rushed for a year, and hadn't had the
time to get more organized.

I've been going through the code, and cleaning up where possible,
while also attempting to retain compatibility with existing code.

The mysql/mysqli implementation in the central config.php, only allows
either solution (mysql or mysqli) to be instantiated once by
config.php, based on the existence of certain constants. (And this is
regardless of how many times a script may try to include config.php)

I have not yet found *any* instance of pconnect being used anywhere.
Additionally, as mentioned, this issue only happens when I have the
logging functionality enabled, which is 4-5 lines of code that exist
in the shutdown function.

I would normally think there were problems elsewhere in the code, if
the issues didn't stop once I comment out the logging functionality.

Hopefully someone can see an error on my part. Here is my shutdown
function (instantiated in config.php):

query_log);

$activity_log['query_log'] = 
serialize($mysqli->query_log);

$mysqli->set('date_added', 'NOW()', false);
$mysqli->insert('activity_log', $activity_log);

$mysqli->close();
}else{
$activity_log = array_map('mysql_real_escape_string', 
$activity_log);
$activity_log['date_added'] = 'NOW()';
$sql = 'INSERT INTO `activity_log` (`' . implode('`,`',
array_keys($activity_log)) . '`) VALUES ("' . implode('","',
$activity_log) . '")';
mysql_query($sql);

mysql_close();
}

exit;
}
/* /Snippet */
?>

--
Jonathan Langevin
PHP Site Solutions
http://www.phpsitesolutions.com



On Tue, Nov 25, 2008 at 3:02 PM, Fergus Gibson <[EMAIL PROTECTED]> wrote:
> On Tue, Nov 25, 2008 at 6:31 AM, Jonathan Langevin
> <[EMAIL PROTECTED]> wrote:
>> The problem is, this activity log, when enabled, results in all MySQL
>> connections being used, and we run out of open connections. I'm not
>> sure where the error is.
>>
>> Our config.php, when included, initializes the MySQL or MySQLi
>> connection as decided by the script that included the config.
> [...]
>
> Hi, Jon.  It's difficult to offer specific replies without specific
> code, but I did raise my eyebrow at the notion of mixing the mysql and
> mysqli extensions in your application.  I'm guessing you've inherited
> some pretty messy code that is time-consuming to refactor.
>
> Is any of the code working against the mysql extension calling
> mysql_pconnect() to create a persistent database connection?
> Connections so opened CANNOT be closed using mysql_close().
>
> I have never written an application using persistent connections, but
> I did get hired at a company where the other programmers describe the
> same problem you're having (a proliferation of connections that
> overwhelmed the server).  They blamed mysql_pconnect() and the lead
> programmer said that after he banned the use of that function, the
> problem went away completely.  A comment to the PHP documentation
> suggests this is the result of a bad interaction between Apache and
> PHP.
>
>
>> Within the same file, a shutdown function is registered to
>> automatically close the MySQL or MySQLi connection at the end of
>> script execution (based on which connection was initialized to begin
>> with).
>
> This should not be necessary.  The script will close all open
> resources when it terminates anyway.  The reason for having and using
> close functions is to free resources while the script is still
> running.  As mentioned above, persistent connections created by
> mysql_pconnect() cannot be closed by mysql_close() under any
> circumstances, nor will they automatically close on completion of the
> script.  I believe that's your problem.
>

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

Re: Fwd: [PHP-DB] MySQLi connections

[J. Hill]

Thank you for your response.

I am glad to hear that the structure is not a problem, but I suspect he 
new about singletons (I forgot that term) as he was an old C/C++ 
progammer (now retired), although I gather he was not a PHP guy.


In all other respects, his code and documentation seem very good in 
comparison with other code from php programmers I've had to modify. It's 
a little difficult for me at times because he modified the PHP source 
code, adding his own custom functions.


I'm just glad it's not something I have too worry about. I wish I had 
the time to become a PHP expert, a C++ expert, a Java expert, and time 
to save the world.


Thanks again,

Jeff

Chris wrote:

J. Hill wrote:
Reading the thread on mysqli connection issues, I am curious if 
anyone knows of a downside to creating a connection from a 
configuration page and using it as a global in all functions?


Good way to do it. You create the connection at the start and use the 
same thing throughout the whole script.


I am used to creating a class and a database handle for functions to 
use, but I inherited an intranet that just uses a single "$mysqli = 
mysqli_connect " in a global main file and the just uses "global 
$mysqli" in all of it's functions (several hundred) that interact 
with the database.


Since I have not seen this structure used elsewhere, I assume there 
is a good reason not to use it, but I haven't found one (except for 
the security issue in the use of globals).


Just "old style", nothing wrong with using it that way. The person who 
wrote it probably just didn't know about singletons. I can't see a 
security issue with it either.


$mysqli is set in the first file included (an 'init' type script).

As long as register_globals is off, it can't be overwritten by a $_GET 
or $_POST .. of course you can destroy it yourself, but that's it.





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

Re: Fwd: [PHP-DB] MySQLi connections

[Chris]

J. Hill wrote:
Reading the thread on mysqli connection issues, I am curious if anyone 
knows of a downside to creating a connection from a configuration page 
and using it as a global in all functions?


Good way to do it. You create the connection at the start and use the 
same thing throughout the whole script.


I am used to creating a class and a database handle for functions to 
use, but I inherited an intranet that just uses a single "$mysqli = 
mysqli_connect " in a global main file and the just uses "global 
$mysqli" in all of it's functions (several hundred) that interact with 
the database.


Since I have not seen this structure used elsewhere, I assume there is a 
good reason not to use it, but I haven't found one (except for the 
security issue in the use of globals).


Just "old style", nothing wrong with using it that way. The person who 
wrote it probably just didn't know about singletons. I can't see a 
security issue with it either.


$mysqli is set in the first file included (an 'init' type script).

As long as register_globals is off, it can't be overwritten by a $_GET 
or $_POST .. of course you can destroy it yourself, but that's it.


--
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: Fwd: [PHP-DB] MySQLi connections

[J. Hill]

Reading the thread on mysqli connection issues, I am curious if anyone 
knows of a downside to creating a connection from a configuration page 
and using it as a global in all functions?


I am used to creating a class and a database handle for functions to 
use, but I inherited an intranet that just uses a single "$mysqli = 
mysqli_connect " in a global main file and the just uses "global 
$mysqli" in all of it's functions (several hundred) that interact with 
the database.


Since I have not seen this structure used elsewhere, I assume there is a 
good reason not to use it, but I haven't found one (except for the 
security issue in the use of globals).


Could anyone point me towards any documentation on why such a structure 
is bad?


Thanks,

Jeff



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

Re: Fwd: [PHP-DB] MySQLi not closing connections

[Chris]

I have never written an application using persistent connections, but
I did get hired at a company where the other programmers describe the
same problem you're having (a proliferation of connections that
overwhelmed the server).  They blamed mysql_pconnect() and the lead
programmer said that after he banned the use of that function, the
problem went away completely.  A comment to the PHP documentation
suggests this is the result of a bad interaction between Apache and
PHP.


Not really true.

pconnect leaves the connection open ('persistent'). From the manual: 
"when connecting, the function would first try to find a (persistent) 
link that's already open with the same host, username and password."


If you're on a shared host with lots of different mysql 
username/passwords floating around, pconnect will be a very bad idea - 
because you'll end up with lots of connections sitting around doing 
nothing (and never being re-used).


If you're on dedicated hardware, pconnect can cut down the connection 
time - but when using them, you need to tune apache as well.


There's lots of notes here:

http://www.php.net/manual/en/features.persistent-connections.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] MySQLi not closing connections

[Fergus Gibson]

On Tue, Nov 25, 2008 at 10:27 AM, Jack Mays <[EMAIL PROTECTED]> wrote:
> I'm not sure why the connections are staying open, but I would suggest using
> mysqli_real_connect with the flag to timout connections.
[...]
> If this is way off base, let me know.

Jack, I think Jon shouldn't implement this suggestion.  Adjusting the
timeout isn't a good way to address spawning a plethora of
connections.  Jon should find and fix the bug that is causing so many
connections to be opened or to persist in the first place rather than
simply asking the database to close connections more quickly (your
suggestion).  Opening or maintaining all those connections is a big
waste of resources in the first place.  Logically, it's also bad form
to make the connections close faster than they should because that
means more resources will be used setting up and tearing down
connections.

I'm all for timeout tuning, but it's a separate issue here.

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

Fwd: [PHP-DB] MySQLi not closing connections

[Fergus Gibson]

Darn it.  Didn't change the e-mail recipient to be the list.


-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Tue, Nov 25, 2008 at 12:02 PM
Subject: Re: [PHP-DB] MySQLi not closing connections
To: Jonathan Langevin <[EMAIL PROTECTED]>


On Tue, Nov 25, 2008 at 6:31 AM, Jonathan Langevin
<[EMAIL PROTECTED]> wrote:
> The problem is, this activity log, when enabled, results in all MySQL
> connections being used, and we run out of open connections. I'm not
> sure where the error is.
>
> Our config.php, when included, initializes the MySQL or MySQLi
> connection as decided by the script that included the config.
[...]

Hi, Jon.  It's difficult to offer specific replies without specific
code, but I did raise my eyebrow at the notion of mixing the mysql and
mysqli extensions in your application.  I'm guessing you've inherited
some pretty messy code that is time-consuming to refactor.

Is any of the code working against the mysql extension calling
mysql_pconnect() to create a persistent database connection?
Connections so opened CANNOT be closed using mysql_close().

I have never written an application using persistent connections, but
I did get hired at a company where the other programmers describe the
same problem you're having (a proliferation of connections that
overwhelmed the server).  They blamed mysql_pconnect() and the lead
programmer said that after he banned the use of that function, the
problem went away completely.  A comment to the PHP documentation
suggests this is the result of a bad interaction between Apache and
PHP.


> Within the same file, a shutdown function is registered to
> automatically close the MySQL or MySQLi connection at the end of
> script execution (based on which connection was initialized to begin
> with).

This should not be necessary.  The script will close all open
resources when it terminates anyway.  The reason for having and using
close functions is to free resources while the script is still
running.  As mentioned above, persistent connections created by
mysql_pconnect() cannot be closed by mysql_close() under any
circumstances, nor will they automatically close on completion of the
script.  I believe that's your problem.

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

Re: [PHP-DB] MySQLi not closing connections

[Jack Mays]

Jonathan Langevin wrote:

Hoping someone may have some insight.

I'm a PHP developer for my current employer. Recently, a coworker and
myself started revamping our PHP-based intranet to add more OO
functionality and replace some of the repetitive procedural code that
was in place.

In this revamp, we also converted the majority of our scripts to use
MySQLi instead of MySQL. We're using the MySQLi class, which I've
extended to add some logging functionality, and also incorporated some
query builder methods (similar to what you'll see in CodeIgniter's
Active Record class).

To better troubleshoot semi-random bugs in our intranet, I've
implemented an activity log in the database, to record all data
in/out, current page, queries used, etc, which logs at the end of each
page load.

The problem is, this activity log, when enabled, results in all MySQL
connections being used, and we run out of open connections. I'm not
sure where the error is.


Our config.php, when included, initializes the MySQL or MySQLi
connection as decided by the script that included the config.
Within the same file, a shutdown function is registered to
automatically close the MySQL or MySQLi connection at the end of
script execution (based on which connection was initialized to begin
with).

We never have connection issues during normal usage, but once the
activity log is enabled (which will execute during shutdown), that is
when we see the connections fill up fast.
The weird thing is, to my knowledge, the same DB connection is being
used, never duplicated. I've seen a PHP <5.3 bug that results in
multiple connections being allowed when using the OO mysqli methods,
but the mysqli class is only instantiated *once* per page load, same
for the mysql procedural instance.

The only possibility I can think of, is that normal mysql is being
instantiated outside of the config.php include, but then we'd have
connections disappearing continuously, not just when the activity log
is enabled.

I realize this is a long, and probably none-to-helpful email. If
anyone can assist, I'd appreciate it. Any info you need, let me know.

--
Jon L.
  
I'm not sure why the connections are staying open, but I would suggest 
using mysqli_real_connect with the flag to timout connections.


http://www.php.net/manual/en/mysqli.real-connect.php

If this is way off base, let me know.

--
Jack

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

[PHP-DB] MySQLi not closing connections

[Jonathan Langevin]

Hoping someone may have some insight.

I'm a PHP developer for my current employer. Recently, a coworker and
myself started revamping our PHP-based intranet to add more OO
functionality and replace some of the repetitive procedural code that
was in place.

In this revamp, we also converted the majority of our scripts to use
MySQLi instead of MySQL. We're using the MySQLi class, which I've
extended to add some logging functionality, and also incorporated some
query builder methods (similar to what you'll see in CodeIgniter's
Active Record class).

To better troubleshoot semi-random bugs in our intranet, I've
implemented an activity log in the database, to record all data
in/out, current page, queries used, etc, which logs at the end of each
page load.

The problem is, this activity log, when enabled, results in all MySQL
connections being used, and we run out of open connections. I'm not
sure where the error is.


Our config.php, when included, initializes the MySQL or MySQLi
connection as decided by the script that included the config.
Within the same file, a shutdown function is registered to
automatically close the MySQL or MySQLi connection at the end of
script execution (based on which connection was initialized to begin
with).

We never have connection issues during normal usage, but once the
activity log is enabled (which will execute during shutdown), that is
when we see the connections fill up fast.
The weird thing is, to my knowledge, the same DB connection is being
used, never duplicated. I've seen a PHP <5.3 bug that results in
multiple connections being allowed when using the OO mysqli methods,
but the mysqli class is only instantiated *once* per page load, same
for the mysql procedural instance.

The only possibility I can think of, is that normal mysql is being
instantiated outside of the config.php include, but then we'd have
connections disappearing continuously, not just when the activity log
is enabled.

I realize this is a long, and probably none-to-helpful email. If
anyone can assist, I'd appreciate it. Any info you need, let me know.

--
Jon L.

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

Re: [PHP-DB] Mysqli returning result set from a function to calling script

[Radek Zajkowski]

Hi Tobias and thanks,

this looks like what we are trying to get at. I can see that PDO is 
distributed with Zend Core, which looks promising. From your own 
experience, is PDO here to stay and evolve?


Thank you,

Radek.


Tobias Franzén wrote:

Hi Radek,

Are you looking for something like the fetchAll function?

http://php.net/manual/en/function.PDOStatement-fetchAll.php

/Tobias

Radek Zajkowski wrote:

Hi all,

we've trying to write a wrapper class that would execute a prepared  
statement and return obtained dataset/resultset to the calling script.


It would:

  1. Prepare statement
  2. execute
  3. call something like $stmt->store_result(); (which we could assing
 to a var or return)
  4. return the results set

however, there seems to be no method or property representing tables 
inside the $stmt var


In .NET, for example (please don't spit), you can get at the results 
by accessing e.g. DataSet.Tables["TableName"].


How do we do that in PHP5?

Many thanks,

Radek.





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

Re: [PHP-DB] Mysqli returning result set from a function to calling script

[Tobias Franzén]

Hi Radek,

Are you looking for something like the fetchAll function?

http://php.net/manual/en/function.PDOStatement-fetchAll.php

/Tobias

Radek Zajkowski wrote:

Hi all,

we've trying to write a wrapper class that would execute a prepared  
statement and return obtained dataset/resultset to the calling script.


It would:

  1. Prepare statement
  2. execute
  3. call something like $stmt->store_result(); (which we could assing
 to a var or return)
  4. return the results set

however, there seems to be no method or property representing tables 
inside the $stmt var


In .NET, for example (please don't spit), you can get at the results 
by accessing e.g. DataSet.Tables["TableName"].


How do we do that in PHP5?

Many thanks,

Radek.



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

[PHP-DB] Mysqli returning result set from a function to calling script

[Radek Zajkowski]

Hi all,

we've trying to write a wrapper class that would execute a prepared  
statement and return obtained dataset/resultset to the calling script.


It would:

  1. Prepare statement
  2. execute
  3. call something like $stmt->store_result(); (which we could assing
 to a var or return)
  4. return the results set

however, there seems to be no method or property representing tables 
inside the $stmt var


In .NET, for example (please don't spit), you can get at the results by 
accessing e.g. DataSet.Tables["TableName"].


How do we do that in PHP5?

Many thanks,

Radek.

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

[PHP-DB] MySQLi and SSL in FreeBSD

[Tobias Franzén]

Hi.

I'm trying to secure the connections to my MySQL server, since the MySQL 
server and most clients are on separate machines.

I'm running a few FreeBSD 5.5 machines under VMware Server 1.0.4.

I wrote a simple test script (I also tried it using the full mysqli_* 
functions):

real_connect("host","user","password",false,NULL,NULL,MYSQLI_CLIENT_SSL);
$res = $link->query("SHOW STATUS LIKE 'Ssl_cipher';");
print_r($res->fetch_row());
echo "Finished.";
?>

An equivalent script for plain old regular mysql_*:


Executing this with the "php" or "php-cgi" binaries from the command 
line, I get an expected result like:

Array
(
   [0] => Ssl_cipher
   [1] => DHE-RSA-AES256-SHA
)
Finished.

The Ssl_cipher attribute will only have a non-empty value if a secure 
connection has been successful.


The problem I'm having, however, is that if I run this through Apache, 
using mod_php, the httpd server process crashes with Segmentation fault 
(11). Today I tracked down the crash to the closing of the mysqli 
connection. If I flush the output buffers, I can get all the data until 
the end of the script, or if I put a $link->close() or 
mysqli_close($link) then up to the line where I put that.


The history of my problem also has had a weird chain of events that I 
will try to explain. Three days ago, I was trying to use phpMyAdmin, 
connecting to my MySQL server with SSL, and it wouldn't let me log in 
without it crashing when I enabled it. So I tracked it to their database 
connection code, where I found it crashing if I let it run the 
mysqli_real_connect() line with the MYSQLI_CLIENT_SSL flag. If I instead 
of the SSL flag put in a call to mysqli_ssl_set() before 
mysqli_real_connect(), and pointing out my CA certificate, I would 
achieve an SSL connection without the crash. So I figured it was 
something to do with the MYSQLI_CLIENT_SSL flag. But I could not find 
anyone with a similar problem when searching in many different places.


So the next day when I got home from work I went to do some more 
phpMyAdmin trekkin', and once again it wouldn't let me log in. It had 
gone back to crashing even with the solution which was working the day 
before. And now for some reason, I can connect to the mysql server using 
SSL, but I get the crash when the connection is being closed instead.


Throughout the entire time the scripts have still worked flawlessly when 
being run via the "php" or "php-cgi" binaries, even when crashing when 
run via mod_php.


Tests on other systems:
Today I installed the latest binary packages of Apache 2.2.6 and PHP 
5.2.5 in a VM with Windows, and there I have no problems running the 
test script from Apache with mod_php.
I also installed another VM with FreeBSD 6.0 (as opposed to version 5.5 
in my regular VMs), and encountered the same segmentation fault crash in 
the same way as in the most recent testings in FreeBSD 5.5.


In FreeBSD I'm using Apache 2.2.6 and PHP 5.2.5, both compiled from the 
latest FreeBSD ports, with the only difference from default 
configuration being to build the PHP Apache module. MySQL, also compiled 
from the latest FreeBSD ports, is version 5.1.22 (both client and server 
on all machines).



Could this segmentation fault crash in some way be configuration 
related, or might there be some bug in mod_php on FreeBSD? If it's a 
bug, I will report it. I just want to make sure first. Maybe someone 
using FreeBSD and has some time to spare could verify if my error is 
related to FreeBSD in general, or to my FreeBSDs in particular.


/Zxinn

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

Re: [PHP-DB] mysqli auto rollback on script termination

[Hodicska Gergely]

Reason I'm asking is, I've seen some deadlock issues in a script (using 
FOR UPDATE and LOCK IN SHARE MODE), that looks like they are caused by 
transactions not being rolled back (releasing locks) correctly.

Do you use pconnect? If I right remember there was a bug in some version
of mysql that in the case of using pconnect in some cases the automatic
rollback didn't happen. The solution was: after pconnect one had to
start with a rollback command.


Regards,
Felhő

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

Re: [PHP-DB] mysqli auto rollback on script termination

[Martin Koch Andersen]

Chris skrev:
It doesn't explicitly say anything about when a connection is lost but 
I'm guessing it would rollback the transaction (well I'd sure hope so).


Me too. In any case, I'm now also using register_shutdown_function, 
which calls a function that does rollback of not committed transations.


Thanks.

--
Martin - http://925.dk
"Shoot for the moon, even if you miss, you'll land among the stars."

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

Re: [PHP-DB] mysqli auto rollback on script termination

[Chris]

Martin Koch Andersen wrote:

Chris skrev:
It should be rolled back when a connection is lost or a transaction 
isn't explicitly committed.


Can you find documentation on this specific issue anywhere?

I think the same as you, but I find it odd, it is not documented.

Reason I'm asking is, I've seen some deadlock issues in a script (using 
FOR UPDATE and LOCK IN SHARE MODE), that looks like they are caused by 
transactions not being rolled back (releasing locks) correctly.


This suggests they should be released:
http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html

Locks set by IN SHARE MODE and FOR UPDATE reads are released when the 
transaction is committed or rolled back.



It doesn't explicitly say anything about when a connection is lost but 
I'm guessing it would rollback the transaction (well I'd sure hope so).


--
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] mysqli auto rollback on script termination

[Martin Koch Andersen]

Chris skrev:
It should be rolled back when a connection is lost or a transaction 
isn't explicitly committed.


Can you find documentation on this specific issue anywhere?

I think the same as you, but I find it odd, it is not documented.

Reason I'm asking is, I've seen some deadlock issues in a script (using 
FOR UPDATE and LOCK IN SHARE MODE), that looks like they are caused by 
transactions not being rolled back (releasing locks) correctly.


--
Martin - http://925.dk
"Shoot for the moon, even if you miss, you'll land among the stars."

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

Re: [PHP-DB] mysqli auto rollback on script termination

[Chris]

Martin Koch Andersen wrote:

Hi,

In case the PHP script dies (from fatal error, die() or similar), is any 
started transaction (BEGIN TRANSACTION) automatically rolled back 
(ROLLBACK) by PHP then?


It should be rolled back when a connection is lost or a transaction 
isn't explicitly committed.


Whether that's a php script dying or the server being shut down 
mid-transaction or anything similar (eg a remote server's lan connection 
goes down).


Of course this assumes you're using innodb tables and not myisam - 
myisam doesn't support transactions..


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

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

[PHP-DB] mysqli auto rollback on script termination

[Martin Koch Andersen]

Hi,

In case the PHP script dies (from fatal error, die() or similar), is any 
started transaction (BEGIN TRANSACTION) automatically rolled back 
(ROLLBACK) by PHP then?


I can't find any documentation about this.

Thanks in advance for hints, links etc.

--
Martin - http://925.dk
"Shoot for the moon, even if you miss, you'll land among the stars."

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

Re: [PHP-DB] MySQLi Prepared Statements

[Georg Richter]

Am Mo, den 27.03.2006 schrieb Rob Hamilton um 19:37:

Hello Rob,

> Is it possible to construct an array of this type and pass it to these 
> functions, and if so how?

For such cases you should use call_user_func_array.

/Georg

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

[PHP-DB] MySQLi Prepared Statements

[Rob Hamilton]

Hi

I am current trying to migrate an existing system using MySQLi to use 
PDO and I have a quick question with regards to Prepared Statements. 
Essentially I'd like to replicate the Prepared Statement functionality 
of PDO using MySQLi so that I can gradually update all the required 
pages to use similar function calls, and then swap everything over in 
one go rather than spending ages on a big rewrite.

Using PDO you can pass parameters as an array which are bound to the 
query. Something like:

$stmt = $pdo->prepare("SELECT * FROM Blah WHERE foo = ?");
$params = array("SomeValue");
$stmt->execute($params)

The PDO function "execute" takes 1 parameter which is an array.

There are similar functions in MySQLi such as bind_param and bind_result 
of which one of the parameters is of type "mixed &var1 [, mixed &...]".

Is it possible to construct an array of this type and pass it to these 
functions, and if so how?

I know is's possible to call these functions with something like:

$stmt = $db->prepare("SELECT * FROM Blah WHERE foo = ?");
$paramTypes = "s";
$params = array("SomeValue");
$stmt->bind_param($paramTypes, $params[0]);

But it seems you cannot pass the array itself using the '&' operator.

Cheers

Rob Hamilton

-- 
___
Play 100s of games for FREE! http://games.mail.com/

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

[PHP-DB] Mysqli Problems with 5.1.0b3

[Charlie Davis]

Hey all!

So two days ago, just for kicks, I thought I'd update to 5.1.0b3.

On to my problem.

I use a custom Session class to basically store my session data in MysQL.

Here's my class:

class DBSession
{
static $dbh;

function open($save_path, $session_name)
{
self::$dbh = new mysqli("localhost", "...", "...", "lists");
return(true);
}

function close()
{   
return(true);
}

function read($id)
{
$query = "select session_data from sessions where session_id = 
'".$id."'";
if(!$result = self::$dbh->query($query))
throw new MysqlException($this->dbh);
if($result->num_rows == 0)
{
return "";
}
$row = $result->fetch_assoc();
return $row['session_data'];
}

function write($id, $sess_data)
{
$clean_data = addslashes($sess_data);
*Error Here:	if(self::$dbh->query("update sessions set session_data 
='".$clean_data."', modtime=NOW() where id='".$id."'"))

return(true);
else
throw new MysqlException(self::$dbh);
}


function destroy($id)
{
if(self::$dbh->query("delete from sessions where session_id = '$id'"))
return(true);
else
return(false);
}

function gc($maxlifetime)
{
$ts = time() - $maxlifetime;
		if(self::$dbh->query("delete from sessions where modtime < 
from_unixtimestamp($ts)"))

return(true);
else
return(false);
}
}

And the error I get is "Warning: Couldn't fetch mysqli in the line that starts with a *"


This works in 5.0.4. I haven't changed a thing and been beating myself 
against a wall trying to figure this out. Did something change in 5.1 
that I should know about?


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

[PHP-DB] MySQLi group

[john]

Caio,
I have started a new Google group for those of us here that may run into
issues specifically with MySQLi in PHP5.
http://groups-beta.google.com/group/MySQLi
Regards,
John
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] mysqli connection problem

[Denis Gerasimov]

Hello,

I've made some tests and now I suspect that what I see is a DB package bug.

I run the following script and it works fine:

host_info);

if ($result = $mysqli->query("SELECT * FROM contents")) {
printf("Select returned  %d rows", $result->num_rows);

}

while ($row = mysqli_fetch_assoc($result)) {
print_r($row);
}

/* close connection */
$mysqli->close();

?>

While trying to connect to the same db using PEAR::DB_DataObject (connection
string: mysqli://[EMAIL PROTECTED]/testdb)

Still gives me this error
"Can't connect to local MySQL server through socket '' (111)"

> -Original Message-
> From: Martin Norland [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 08, 2005 10:19 PM
> To: php-db@lists.php.net
> Cc: 'MySQL General List'; 'PHP General List'
> Subject: Re: [PHP-DB] mysqli connection problem
> 
> My intention of setting the reply-to was not to offend, or claim you
> were wrong - merely to move the discussion to the list I felt (and still
> feel - as I've done it again) is most appropriate for the question.
> 
> anyway, back to the task at hand
> 
> ( first: I'm assuming you restarted apache after changing your php.ini -
> otherwise, that's your problem. )
> 
> Whatever the case, the error message clearly shows that it is not
> reading the value, however it's set - because the error is still showing
> a blank string for the socket.  If it were a permissions problem with
> the socket, you wouldn't be getting that exact error - at the very
> least, it would still show the correct socket path.  More likely, it
> would also error with a "cannot open socket" or equivalent.
> 
> You may wish to examine ini_get(), ini_get_all(), phpinfo(), or
> php_ini_scanned_files(), to verify if it is set, or to see if it's being
> overwritten somewhere.
> 
> Cheers,
> 
> Denis Gerasimov wrote:
> > Didn't help me. MySQL socket does exist - /var/lib/mysql/mysql.sock
> (default
> > location).
> > mysqli.default_socket is NOT set to null but to this value (manually, as
> you
> > advised)
> >
> > Again,
> >
> > 1. Connection to localhost still fails with message "Can't connect to
> local
> > MySQL server through socket '' (111)"
> >
> > 2. Connection to server's domain name fails too (!) with message "Access
> > denied for user 'user'@'example.com' (using password: YES)"
> >
> > 3. Connection to localhost through mysql shows the following config
> >
> > mysql> status
> > --
> > mysql  Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)
> >
> > Connection id:  42
> > Current database:
> > Current user:   [EMAIL PROTECTED]
> > SSL:Not in use
> > Current pager:  stdout
> > Using outfile:  ''
> > Using delimiter:;
> > Server version: 4.1.9-standard
> > Protocol version:   10
> > Connection: Localhost via UNIX socket
> > Server characterset:latin1
> > Db characterset:latin1
> > Client characterset:latin1
> > Conn.  characterset:latin1
> > UNIX socket:/var/lib/mysql/mysql.sock
> > Uptime: 1 day 3 hours 1 min 7 sec
> >
> > Threads: 3  Questions: 272  Slow queries: 0  Opens: 64  Flush tables: 1
> > Open tables: 26  Queries per second avg: 0.003
> > --
> >
> > Seems to be a security issue... Any more ideas about these symptoms?
> >
> >
> >>-
> >>I can't see any reason why you wouldn't be able to use the same socket -
> >>you're connecting to the same exact server, it's just a path.  You
> >>should be perfectly able to set your mysqli.default_socket to the same
> >>as the mysql.default_socket.  I can't tell you what the default for your
> >>system is, but there are php functions to get configuration options, if
> >>you don't have it in any obvious places.
> >>
> >>Cheers,
> >>--
> >>- Martin Norland, Database / Web Developer, International Outreach x3257
> >>The opinion(s) contained within this email do not necessarily represent
> >>those of St. Jude Children's Research Hospital.
> >>
> >>
> >>
> >>--
> >>MySQL General Mailing List
> >>For list archives: http://lists.mysql.com/mysql
> >>To unsubscribe:
> >>http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 
> 
> --
> - Martin Norland, Database / Web Developer, International Outreach x3257
> The opinion(s) contained within this email do not necessarily represent
> those of St. Jude Children's Research Hospital.
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]

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

Re: [PHP-DB] mysqli connection problem

[Martin Norland]

My intention of setting the reply-to was not to offend, or claim you 
were wrong - merely to move the discussion to the list I felt (and still 
feel - as I've done it again) is most appropriate for the question.

anyway, back to the task at hand
( first: I'm assuming you restarted apache after changing your php.ini - 
otherwise, that's your problem. )

Whatever the case, the error message clearly shows that it is not 
reading the value, however it's set - because the error is still showing 
a blank string for the socket.  If it were a permissions problem with 
the socket, you wouldn't be getting that exact error - at the very 
least, it would still show the correct socket path.  More likely, it 
would also error with a "cannot open socket" or equivalent.

You may wish to examine ini_get(), ini_get_all(), phpinfo(), or 
php_ini_scanned_files(), to verify if it is set, or to see if it's being 
overwritten somewhere.

Cheers,
Denis Gerasimov wrote:
Didn't help me. MySQL socket does exist - /var/lib/mysql/mysql.sock (default
location).
mysqli.default_socket is NOT set to null but to this value (manually, as you
advised)
Again,
1. Connection to localhost still fails with message "Can't connect to local
MySQL server through socket '' (111)"
2. Connection to server's domain name fails too (!) with message "Access
denied for user 'user'@'example.com' (using password: YES)"
3. Connection to localhost through mysql shows the following config
mysql> status
--
mysql  Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)
Connection id:  42
Current database:
Current user:   [EMAIL PROTECTED]
SSL:Not in use
Current pager:  stdout
Using outfile:  ''
Using delimiter:;
Server version: 4.1.9-standard
Protocol version:   10
Connection: Localhost via UNIX socket
Server characterset:latin1
Db characterset:latin1
Client characterset:latin1
Conn.  characterset:latin1
UNIX socket:/var/lib/mysql/mysql.sock
Uptime: 1 day 3 hours 1 min 7 sec
Threads: 3  Questions: 272  Slow queries: 0  Opens: 64  Flush tables: 1
Open tables: 26  Queries per second avg: 0.003
--
Seems to be a security issue... Any more ideas about these symptoms?

-
I can't see any reason why you wouldn't be able to use the same socket -
you're connecting to the same exact server, it's just a path.  You
should be perfectly able to set your mysqli.default_socket to the same
as the mysql.default_socket.  I can't tell you what the default for your
system is, but there are php functions to get configuration options, if
you don't have it in any obvious places.
Cheers,
--
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent 
those of St. Jude Children's Research Hospital.

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

RE: [PHP-DB] mysqli connection problem

[Denis Gerasimov]

> > Hello,
> >
> > This question was asked many times, but I can't find a good answer.
> > I am getting this error message while trying to connect to MySQL server
> (PHP
> > + PEAR::DB_DataObject):
> >
> > "Can't connect to local MySQL server through socket '' (111)"
> >
> > (notice '' - is that right?)
> >
> > Does anyone have any ideas about how to get rid of this error?
> >
> > Configuration:
> > * RedHat Enterprise Linux AS3
> > * Apache 2.0.46
> > * PHP 5.0.2
> > * MySQL 4.1.9, mysqli ext.
> >
> > Thanks!
> 
> [Note: reply-to set to php-db as it's multimailed and I don't see this
> as a mysql general issue, nor php general, but feh.]

Well, I don't know the possible reasons for this, so I mailed it to all
suitable lists.

> 
> Looks to be set blank/null/etc.
> 
> You may be slightly tricked by the existence of both of these - from
> your standard php.ini...
> -
> ; Default socket name for local MySQL connects.  If empty, uses the built-
> in
> mysql.default_socket =
> 
> ; Default socket name for local MySQL connects.  If empty, uses the built-
> in
> ; MySQL defaults.
> mysqli.default_socket =

Didn't help me. MySQL socket does exist - /var/lib/mysql/mysql.sock (default
location).
mysqli.default_socket is NOT set to null but to this value (manually, as you
advised)

Again,

1. Connection to localhost still fails with message "Can't connect to local
MySQL server through socket '' (111)"

2. Connection to server's domain name fails too (!) with message "Access
denied for user 'user'@'example.com' (using password: YES)"

3. Connection to localhost through mysql shows the following config

mysql> status
--
mysql  Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)

Connection id:  42
Current database:
Current user:   [EMAIL PROTECTED]
SSL:Not in use
Current pager:  stdout
Using outfile:  ''
Using delimiter:;
Server version: 4.1.9-standard
Protocol version:   10
Connection: Localhost via UNIX socket
Server characterset:latin1
Db characterset:latin1
Client characterset:latin1
Conn.  characterset:latin1
UNIX socket:/var/lib/mysql/mysql.sock
Uptime: 1 day 3 hours 1 min 7 sec

Threads: 3  Questions: 272  Slow queries: 0  Opens: 64  Flush tables: 1
Open tables: 26  Queries per second avg: 0.003
--

Seems to be a security issue... Any more ideas about these symptoms?

> -
> I can't see any reason why you wouldn't be able to use the same socket -
> you're connecting to the same exact server, it's just a path.  You
> should be perfectly able to set your mysqli.default_socket to the same
> as the mysql.default_socket.  I can't tell you what the default for your
> system is, but there are php functions to get configuration options, if
> you don't have it in any obvious places.
> 
> Cheers,
> --
> - Martin Norland, Database / Web Developer, International Outreach x3257
> The opinion(s) contained within this email do not necessarily represent
> those of St. Jude Children's Research Hospital.
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]

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

Re: [PHP-DB] mysqli connection problem

[Martin Norland]

Denis Gerasimov wrote:
Hello,
This question was asked many times, but I can't find a good answer.
I am getting this error message while trying to connect to MySQL server (PHP
+ PEAR::DB_DataObject):
"Can't connect to local MySQL server through socket '' (111)"
(notice '' - is that right?)
Does anyone have any ideas about how to get rid of this error?
Configuration:
* RedHat Enterprise Linux AS3
* Apache 2.0.46
* PHP 5.0.2
* MySQL 4.1.9, mysqli ext.
Thanks!
[Note: reply-to set to php-db as it's multimailed and I don't see this 
as a mysql general issue, nor php general, but feh.]

Looks to be set blank/null/etc.
You may be slightly tricked by the existence of both of these - from 
your standard php.ini...
-
; Default socket name for local MySQL connects.  If empty, uses the built-in
mysql.default_socket =

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysqli.default_socket =
-
I can't see any reason why you wouldn't be able to use the same socket - 
you're connecting to the same exact server, it's just a path.  You 
should be perfectly able to set your mysqli.default_socket to the same 
as the mysql.default_socket.  I can't tell you what the default for your 
system is, but there are php functions to get configuration options, if 
you don't have it in any obvious places.

Cheers,
--
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent 
those of St. Jude Children's Research Hospital.

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

[PHP-DB] mysqli connection problem

[Denis Gerasimov]

Hello,

This question was asked many times, but I can't find a good answer.
I am getting this error message while trying to connect to MySQL server (PHP
+ PEAR::DB_DataObject):

"Can't connect to local MySQL server through socket '' (111)"

(notice '' - is that right?)

Does anyone have any ideas about how to get rid of this error?

Configuration:
* RedHat Enterprise Linux AS3
* Apache 2.0.46
* PHP 5.0.2
* MySQL 4.1.9, mysqli ext.

Thanks!

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

Re: [PHP-DB] MySQLi connection character set problem

[Jean-Philippe Côté]

Thanks, this is exactly what I needed. It seems I got tricked by what I 
expected the mysqli_character_set_name function to do.

It seems this function only "returns the DEFAULT character set for the 
database connection" (quoted from manual) and not the actual character 
set defined. For example, if you run the code below:

// bla bla bla
$mysqli->options(MYSQLI_INIT_COMMAND, "SET NAMES 'UTF8'");
print mysqli_character_set_name();
it will still output (in my case) "latin1_swedish_ci" although the 
connection, at this stage, is set to UTF-8.

If the mysqli developers are reading this, it could be useful to have a 
function to fetch the currently defined connection character set 
(mysqli_current_character_set_name perhaps ?).

Thanks again.
On 05-01-11, at 03:45, Unreal HSHH wrote:
I have to use this
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_INIT_COMMAND, "SET NAMES 'UTF8'");
$mysqli->real_connect($mysqli_addr, $mysqli_user, $mysqli_pass, 
$mysqli_db);
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] MySQLi connection character set problem

[Jean-Philippe Côté]

Greetings!
How can I set the connection character encoding that will be used by 
mysqli to talk to a MySQL 4.1.8 database (from PHP 5.0.3) ?

I want to use UTF-8 all the way. The data that PHP sends to the 
database is in UTF-8 already and all my tables are using UTF-8 as well. 
The only problem is that the connection character set (as reported by 
mysqli_character_set_name) always defaults to latin1_swedish_ci (which 
I believe is the default for latin1).

There must be a way to tell mysqli to use UTF-8 for the connection, 
right ? I've currently falled back to using the following (which seems 
like a big waste):

// The content is encoded in this charset when it leaves the client
$sql_result = mysqli_query($db_link, "SET NAMES 'latin1'");
// The content should be translated to this charset when it arrives at 
the mysql server
$sql_result = mysqli_query($db_link, "SET CHARACTER SET 'utf8'");

I also tried setting defaults in /etc/my.cnf but mysqli (or libmysql) 
doesn't seem to be reading those.

Any ideas would be greatly appreciated.  TIA
Jean-Philippe
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] mysqli->multi_query dosen't works properly with stored procedurs with select statement without INTO param

[Gleb Paharenko]

Hi.
I've found a letter in mysql users mailing list in which
user tells us that mysqli doesn't properly works with stored procedurs.
when we use select statment without INTO param.
Here it is:
###3
Hi.
I am using MySQL 5.0.1 (snapshot) and PHP 5.0.2 with mysqli interface 
(latest snapshot) under Linux (SUSE 9.1).

I would like to call a SP using PHP 5, and I want to get back the SELECT 
results.

This is my SP:
CREATE PROCEDURE `test`()
BEGIN
  SELECT * FROM t1;
END
From the comments in http://bugs.mysql.com/bug.php?id=2273 I
understand I 
have to use mysqli_multi_query if I want to get rowsets from a SP. Fine:

  $ok = $mysqli->multi_query("CALL test()");
  if($ok) {
echo "OK\n";
do {
  echo "result\n";
  $result = $mysqli->store_result();
  if($result) {
show_table($result);  // shows result details
$result->close();
  }
} while($mysqli->next_result());
  }
I don't get any results, $ok is FALSE.
If I instead use the following code, everything works fine, I receive both 
rowsets. So, the PHP code above seems to be ok as long as I don't call a SP.

$ok = $mysqli->multi_query("SELECT * FROM t1; SELECT * FROM t1");
Any ideas? Error in MySQL or in PHP?
Thank you,
 Michael Kofler
http://www.kofler.cc/
3
I've checked this information,and found 
that  code really doesn't work when i used
$mysqli=mysqli_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3")
to connect to server.
But when i change that to
$mysqli=mysqli_init();
$mysqli->real_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3");
Every thing works fine.
May be it is a php bug.

My code:
real_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3");
//$mysqli=mysqli_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3");
$ok = $mysqli->multi_query("call test();");
echo $mysqli->info;
//$ok = $mysqli->multi_query("select * from t1;");
//if($ok) {
//echo "OK\n";
//echo $mysqli->mysqli_errno()."\n";
//echo $mysqli->mysqli_sqlstate."\n";
do {
echo "result\n";
$result = $mysqli->store_result();
if($result) {
while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) {
echo "\t\n";
foreach ($line as $col_value) {
echo "\t\t$col_value\n";
}
echo "\t\n";
}
$result->close();
}
} while($mysqli->next_result());
//}
?>



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

RE: [PHP-DB] mysqli prepared statement query result sets

[Norland, Martin]

Wishful thinking on my part, since it specified no version info because
it was likely only in CVS - I thought maybe it was *really new* :).

That is unfortunate then, definitely.  Perhaps a
mysqli_stmt_fetch_assoc() is forthcoming :(

- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.


-Original Message-
From: Gerard Samuel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 26, 2004 8:49 AM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] mysqli prepared statement query result sets


Norland, Martin wrote:

>The functions you want exist in php5, if that's an option: 
>http://us2.php.net/manual/en/ref.mysqli.php -> 
>http://us2.php.net/manual/en/function.mysqli-fetch-assoc.php
>
>  
>
No this is not an option.
mysqli_fetch_* needs a "result" resource id to work.
mysqli statement functions needs object references (plus there are other
quirks). You cannot use mysqli_fetch_* functions with the mysqli
prepared statement functions...

-- 
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] mysqli prepared statement query result sets

[Hans Lellelid]

Hi Gerard,

> I believe he is talking about ->
> http://dev.mysql.com/doc/mysql/en/SQLPS.html
> But IMHO, I think emulating via php would be faster, as that is
> alot of talking to a database to get the job done, especially if the
> database is on a remote host.

Ahh, ok.  Yes, this is interesting, but certainly not ideal from a uniform
API perspective.  I will stick w/ emulation in PHP.

> Granted, he is correct.  We have no right to say that is sucks.
> Me personally, I do not know at whom to point a finger to blame (maybe
> the mysql C API), but it is what it is unfortunately.
> We just have to make the best of it...

Yes, you're right.  Saying the API sucks was certainly an exaggeration. 
It is a disappointment, but no one sucks.  Georg did an awesome job
bringing this to PHP5. I'll stop complaining & just stick w/ classic mysql
API ;)

-Hans

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

Re: [PHP-DB] mysqli prepared statement query result sets

[Gerard Samuel]

Norland, Martin wrote:
The functions you want exist in php5, if that's an option:
http://us2.php.net/manual/en/ref.mysqli.php ->
http://us2.php.net/manual/en/function.mysqli-fetch-assoc.php
 

No this is not an option.
mysqli_fetch_* needs a "result" resource id to work.
mysqli statement functions needs object references (plus there are other
quirks).
You cannot use mysqli_fetch_* functions with the
mysqli prepared statement functions...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] mysqli prepared statement query result sets

[Gerard Samuel]

Hans Lellelid wrote:
Hi Georg,
 

Am Di, den 26.10.2004 schrieb Hans Lellelid um 12:57:
   

Ooops, I guess I should have searched the list itself; I did some google
searches to no avail.  This is really unfortunate.  This API sucks!
 

Prepared api calls use a binary protocol, which is totally different
from the "old" protocol. If you don't need binary protocol for prepared
statements, why don't you use native SQL Implementation for prepared
statements instead ??
   

What is "native SQL Implementation for prepared statments"?  All I want to
do is use prepared statements in a non-emulated way.  Like ODBC, OCI8,
mssql callable statements, etc.  I want the speed improvement of using
non-emulated prepared statements.
 

I believe he is talking about ->
http://dev.mysql.com/doc/mysql/en/SQLPS.html
But IMHO, I think emulating via php would be faster, as that is
alot of talking to a database to get the job done, especially if the
database is on a remote host.
It's always easy to claim that stuff other people created (and spent a
lot of time on it) sucks. But it sucks definitely more, if people are
not able to read the PHP and MySQL Documentation carefully.
   

Yes, it is easy, you're right. :) I was looking forward to the speed
increase of using native prepared statements, but it is not useful for
anything I'm doing if the prepared statement API is completely different
from standard queries.
I don't think that there's an issue with documentation here.  The behavior
of mysqli is consistent with everything I read, I just couldn't believe
that it was actually implemented that way.
Anyway, I assume this is a MySQL API, and doesn't have anything to do with
the PHP port.
Hopefully PDO can / does take advantage of the native prepared statements
without the exceptional API.
Granted, he is correct.  We have no right to say that is sucks.
Me personally, I do not know at whom to point a finger to blame (maybe
the mysql C API), but it is what it is unfortunately.
We just have to make the best of it...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] mysqli prepared statement query result sets

[Norland, Martin]

The functions you want exist in php5, if that's an option:
http://us2.php.net/manual/en/ref.mysqli.php ->
http://us2.php.net/manual/en/function.mysqli-fetch-assoc.php


Cheers,
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.


-Original Message-
From: Hans Lellelid [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 26, 2004 5:57 AM
To: Gerard Samuel
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] mysqli prepared statement query result sets


Hi Gerard,

Thanks for the reply.

>> I'm writing a db abstraction layer driver for MySQLi.  I'm glad to
>> finally get a chance to play around with these new functions, but am 
>> completely stumped by this question:
>>
>> Is there no way to get back a standard resultset when using prepared
>> statement queries?
>>
>> I don't see the solution, but I hope I'm just missing something 
>> because I've been staring at it too long.
> 
> 
> 
> No you're not missing anything...
> I ran into the same thing... 
> http://marc.theaimsgroup.com/?l=php-db&m=109625996830773&w=2
> So I ended up simulating "prepared" statements via php itself. The way

> the mysqli extension is currently setup, is that you can either use 
> the normal functions by themselves, or you use the statement functions

> by themselves.  They cannot be used together, which I think Never 
> mind, I'll keep my thoughts to myself...

Ooops, I guess I should have searched the list itself; I did some google

searches to no avail.  This is really unfortunate.  This API sucks!  I 
was looking forward to being able to use native prepared statements 
rather than emulating, but I guess that's really not feasible for this 
project.  Argh.

Thanks again for response.

Hans

-- 
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] mysqli prepared statement query result sets

[Hans Lellelid]

Hi Georg,

> Am Di, den 26.10.2004 schrieb Hans Lellelid um 12:57:
>
>> Ooops, I guess I should have searched the list itself; I did some google
>> searches to no avail.  This is really unfortunate.  This API sucks!
>
>
> Prepared api calls use a binary protocol, which is totally different
> from the "old" protocol. If you don't need binary protocol for prepared
> statements, why don't you use native SQL Implementation for prepared
> statements instead ??

What is "native SQL Implementation for prepared statments"?  All I want to
do is use prepared statements in a non-emulated way.  Like ODBC, OCI8,
mssql callable statements, etc.  I want the speed improvement of using
non-emulated prepared statements.

> It's always easy to claim that stuff other people created (and spent a
> lot of time on it) sucks. But it sucks definitely more, if people are
> not able to read the PHP and MySQL Documentation carefully.

Yes, it is easy, you're right. :) I was looking forward to the speed
increase of using native prepared statements, but it is not useful for
anything I'm doing if the prepared statement API is completely different
from standard queries.

I don't think that there's an issue with documentation here.  The behavior
of mysqli is consistent with everything I read, I just couldn't believe
that it was actually implemented that way.

Anyway, I assume this is a MySQL API, and doesn't have anything to do with
the PHP port.

Hopefully PDO can / does take advantage of the native prepared statements
without the exceptional API.

Cheers,
Hans

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

Re: [PHP-DB] mysqli prepared statement query result sets

[Georg Richter]

Am Di, den 26.10.2004 schrieb Hans Lellelid um 12:57:

> Ooops, I guess I should have searched the list itself; I did some google 
> searches to no avail.  This is really unfortunate.  This API sucks!  


Prepared api calls use a binary protocol, which is totally different
from the "old" protocol. If you don't need binary protocol for prepared
statements, why don't you use native SQL Implementation for prepared
statements instead ??

It's always easy to claim that stuff other people created (and spent a
lot of time on it) sucks. But it sucks definitely more, if people are
not able to read the PHP and MySQL Documentation carefully.

Just my 2 cents

Georg

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

Re: [PHP-DB] mysqli prepared statement query result sets

[Hans Lellelid]

Hi Gerard,
Thanks for the reply.
I'm writing a db abstraction layer driver for MySQLi.  I'm glad to 
finally get a chance to play around with these new functions, but am 
completely stumped by this question:

Is there no way to get back a standard resultset when using prepared 
statement queries?

I don't see the solution, but I hope I'm just missing something 
because I've been staring at it too long.

No you're not missing anything...
I ran into the same thing...
http://marc.theaimsgroup.com/?l=php-db&m=109625996830773&w=2
So I ended up simulating "prepared" statements via php itself.
The way the mysqli extension is currently setup, is that you can either
use the normal functions by themselves, or you use the statement functions
by themselves.  They cannot be used together, which I think
Never mind, I'll keep my thoughts to myself...
Ooops, I guess I should have searched the list itself; I did some google 
searches to no avail.  This is really unfortunate.  This API sucks!  I 
was looking forward to being able to use native prepared statements 
rather than emulating, but I guess that's really not feasible for this 
project.  Argh.

Thanks again for response.
Hans
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] mysqli prepared statement query result sets

[Gerard Samuel]

Hans Lellelid wrote:
Hi,
I'm writing a db abstraction layer driver for MySQLi.  I'm glad to 
finally get a chance to play around with these new functions, but am 
completely stumped by this question:

Is there no way to get back a standard resultset when using prepared 
statement queries?

I can't believe this would be the case, but it seems that the only 
option when using prepared statements is to call 
myslqi_stmt_bind_result(), binding results to php variables and then 
call mysqli_stmt_fetch($stmt) until it returns null.  i.e.

$sql = "SELECT name, age FROM friends WHERE country = ?";
$stmt = mysqli_prepare($link, $sql);
$country = "Haiti";
mysqli_bind_param($stmt, "s", $country);
mysqli_stmt_execute($stmt);
   /* bind result variables */
   mysqli_stmt_bind_result($stmt, $name, $age);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
printf ("%s (%s)\n", $name, $code);
}
What I want to be able to do is use things like mysqli_fetch_assoc() 
instead of this weird, side-effect-prone mysqli_stmt_fetch() to 
retrieve the results.

I don't see the solution, but I hope I'm just missing something 
because I've been staring at it too long.

No you're not missing anything...
I ran into the same thing...
http://marc.theaimsgroup.com/?l=php-db&m=109625996830773&w=2
So I ended up simulating "prepared" statements via php itself.
The way the mysqli extension is currently setup, is that you can either
use the normal functions by themselves, or you use the statement functions
by themselves.  They cannot be used together, which I think
Never mind, I'll keep my thoughts to myself...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] mysqli prepared statement query result sets

[Hans Lellelid]

Hi,
I'm writing a db abstraction layer driver for MySQLi.  I'm glad to 
finally get a chance to play around with these new functions, but am 
completely stumped by this question:

Is there no way to get back a standard resultset when using prepared 
statement queries?

I can't believe this would be the case, but it seems that the only 
option when using prepared statements is to call 
myslqi_stmt_bind_result(), binding results to php variables and then 
call mysqli_stmt_fetch($stmt) until it returns null.  i.e.

$sql = "SELECT name, age FROM friends WHERE country = ?";
$stmt = mysqli_prepare($link, $sql);
$country = "Haiti";
mysqli_bind_param($stmt, "s", $country);
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $name, $age);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
printf ("%s (%s)\n", $name, $code);
}
What I want to be able to do is use things like mysqli_fetch_assoc() 
instead of this weird, side-effect-prone mysqli_stmt_fetch() to retrieve 
the results.

I don't see the solution, but I hope I'm just missing something because 
I've been staring at it too long.

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

[PHP-DB] mysqli and prepare

[Joerg Forg]

Hi @ all,

i´ve some questions about the new mysqli. I build my own dbmanagement for
mysqli
and now i´ve a problem with dynamic bind for params.
bind_param only alows to set primitive datatypes and i don´t have any idea
to make it
dynamic like: function bind_MyParams($array_myParams) like pear::db who
works with arrays but
don´t use the bind_param. My only intention is to use eval to build it, but
this is not really
performant...does anyone have another idea? Thnxs!

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

[PHP-DB] mysqli bind_param un-matched number of variables woes

[Jason Wilkes]

Hi All,

I'm using PHP5/mysqli with object style bind_param,
which always reports "mysqli_stmt::bind_param() Number
of variables doesn't match number of parameters in
prepared statement" - even if they do match! - and
even using the sample code from the zend tutorial.

I have included the (slightly modified) zend code
below (I had to include a $mysqli->stmt_init() call
for it to work this far). All works well until the
$stmt->bind_param which causes the error.

I'm using PHP5/mysqli/MySQL4.1.3b-beta and WinXP

Does anybody have any thoughts/ideas?
Any help would be greatly appreciated.

regards

Jason

stmt_init();
$stmt->prepare("INSERT INTO CountryLanguage VALUES (?,
?, ?, ?)"); 
$stmt->bind_param('sssd', $code, $language, $official,
$percent); 

$code = 'DEU'; 
$language = 'Bavarian'; 
$official = "F"; 
$percent = 11.2; 

/* execute prepared statement */ 
$stmt->execute(); 

printf("%d Row inserted.\n", $stmt->affected_rows); 

/* close statement and connection */ 
$stmt->close(); 

/* Clean up table CountryLanguage */ 
$mysqli->query("DELETE FROM CountryLanguage WHERE
Language='Bavarian'"); 
printf("%d Row deleted.\n", $mysqli->affected_rows); 

/* close connection */ 
$mysqli->close(); 
?>






___ALL-NEW Yahoo! Messenger - 
all new features - even more fun!  http://uk.messenger.yahoo.com

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

[PHP-DB] [mysqli] how can i define the charset for connection

[Unreal HSHH]

i want to use utf8 charset to connect the mysql4.1.x,and use the mysql
charset for tables,columns.

but i cant found the charset setting or function in php.
mysqli_character_set_name still return iso charset

how can i do it?

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

RE: [PHP-DB] mysqli/Statement isn't valid anymore in x.php line #

[Gilmore, Corey (DPC)]

> John Holmes wrote:
>
> Gilmore, Corey (DPC) wrote:
> 
> > Is anyone familiar with what would cause an error like this:
> > Warning: Statement isn't valid anymore in includes\import.inc.php on
> > line 810
> 
> 1. Which line is 810? That's going to really help someone determine why 
> you may be getting this warning.

if( !$stmt->bind_param("sssd", $addr->PhoneType, $addr->Phone, $addr->Extension, 
$ContactID ) ) {

> 
> 2. This is a warning, not an error. While annoying, it really can be 
> ignored if the program actually works correctly. The warning can be 
> hidden by using an appropriate error_reporing() level.
> 
Unfortunately it doesn't work, somewhere between my successful call to 
mysqli->prepare() and bind_param my statement object is (apparantly) destroyed, and 
nothing is actually inserted.

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

Re: [PHP-DB] mysqli/Statement isn't valid anymore in x.php line #

[John W. Holmes]

Gilmore, Corey (DPC) wrote:
Is anyone familiar with what would cause an error like this:
Warning: Statement isn't valid anymore in includes\import.inc.php on
line 810
1. Which line is 810? That's going to really help someone determine why 
you may be getting this warning.

2. This is a warning, not an error. While annoying, it really can be 
ignored if the program actually works correctly. The warning can be 
hidden by using an appropriate error_reporing() level.

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] mysqli/Statement isn't valid anymore in x.php line #

[Gilmore, Corey (DPC)]

Hello,

Is anyone familiar with what would cause an error like this:
Warning: Statement isn't valid anymore in includes\import.inc.php on
line 810

I'm using MySQL 4.1.2a, PHP 5.0.0 with Apache 2.0.50.  All tables are
InnoDB format.

The calling function is:
function insert_phone($ContactID) {
global $kTABLE_PREFIX;

foreach( $this->PhoneList as $addr ) {
$db = DoMySQLConnect(); // connect
$sql = "INSERT INTO {$kTABLE_PREFIX}phone (PhoneType, Phone,
Extension, ContactID) VALUES (?, ?, ?, ?)";
if( !$stmt = $db->prepare($sql) ) { // prepare it
printf("Error with %s:%s",$sql, $db->error );
} else {
if( !$stmt->bind_param("sssd", $addr->PhoneType,
$addr->Phone, $addr->Extension, $ContactID ) ) {
printf("Error (#%d): %s;%s",
$stmt->errno, $sql, $stmt->error );
} else {
$stmt->execute(); // execute
$stmt->free_result();
$stmt->close();
}
}
$db->close();
}
}

bind_param fails and I end up with:
Error (#0): INSERT INTO ap2004phone (PhoneType, Phone, Extension,
ContactID) VALUES (?, ?, ?, ?);

insert_phone is part of a class, Phone.

import.inc.php contains class definitions for a bunch of different
objects, contacts, email addresses, addresses etc that are being
massaged from a single flat table into relational tables.  There are
around 2000 inserts performed in a single import.  At the beginning of
the import I disable all foreign key checks etc 
...
$sql =  "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; "
.   "SET
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; "
.   "SET
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; "
.   "SET NAMES utf8; "
.   "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,
UNIQUE_CHECKS=0; "
.   "SET
@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; "
.   "SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"; ";

$db = DoMySQLConnect();
$db->multi_query($sql);
$db->close();
...

And at the end of the import I re-enable them.

As far as I can tell, there is nothing wrong with the INSERT statement,
or the variables it is inserting.  The errors appear randomly, and for
different objects at different times.  Occasionally there are no errors,
and then other times there are hundreds.  There are no mysql errors in
the mysql error log either.

Anyone have any ideas?  I'm going crazy :(

Thanks,
Corey

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

[PHP-DB] MySQLi set_ssl method arguments

[TJ Root]

I was wondering if anyone could shed some light on what an appropriate set
of arguments would look like for the ssl_set method on a MySQLi object.  I
am trying to connect to an SSL-enabled MySQL server without much luck.  I am
confident that I am passing the certificate paths correctly, but once I get
to the capath and and cipher list I am not sure what I need to be passing
exactly.

Here is what I have so far (works fine non-ssl)

$mysqlconn = mysqli_init();

//I put all the keys and certificates in the same folder as the script for
simplicity while testing.
//As I stated, I am unsure about the last 2 arguments.

$mysqlconn->ssl_set( 'client_key.pem', 'client_cert.pem', 'cacert.pem',
'./', 'X509' );

$mysqlconn->real_connect( 'databasehost.domainname.com', 'user', 'pass',
'dbaasename' );

Any help is greatly appreciated

-TJ

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

Re: [PHP-DB] mysqli - using PREPARED STATEMENTS - worth it?

[John W. Holmes]

PHP freak wrote:

Looking at this page:
http://us2.php.net/manual/en/function.mysqli-prepare.php
Read this, too: http://www.zend.com/php5/articles/php5-mysqli.php

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

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

Re: [PHP-DB] mysqli - using PREPARED STATEMENTS - worth it?

[John W. Holmes]

PHP freak wrote:

Looking at this page:
http://us2.php.net/manual/en/function.mysqli-prepare.php
Also the new book "ADVANCED PHP PROGRAMMING" uses 
the new MySQLi Prepared Statements for all queries.

Wondering if that extra code, trouble, and lack 
of flexibility is worth it? Will it be a HUGE 
performance increase that will be worth those 
added lines of code for every single query?
I don't know if the queries are compiled when you prepare them or not, 
but if they are, that could be a large performance increase.

Also, I believe the point to prepared statements is for security. The 
parameters you "bind" to the query will be escaped and validated (?) to 
be the correct type.

Can anyone back this up?

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

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

[PHP-DB] mysqli - using PREPARED STATEMENTS - worth it?

[PHP freak]

Looking at this page:
http://us2.php.net/manual/en/function.mysqli-prepare.php

Also the new book "ADVANCED PHP PROGRAMMING" uses the new MySQLi Prepared Statements 
for all queries.

Wondering if that extra code, trouble, and lack of flexibility is worth it? Will it be 
a HUGE performance increase that will be worth those added lines of code for every 
single query?

Any insight appreciated.

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

[PHP-DB] Mysqli setting database connection character set

[Jaanus Heeringson]

Is there any way to set the default database connection character set 
when using myslqi extension?

When connecting to a database it does not seem to pick up the default 
charset of the database (utf8) but instead reverts to the mysql server 
default chrset (latin_1...).

What i want to do is connect to my mysql db in utf-8 without changing 
the default connection charset of the server.

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