Re: [PHP-DB] use php variable within postgresql query

[Karl DeSaulniers]

Oh I see now I am late to the conversation. Slow email load. Carry on.

> On Jun 12, 2023, at 1:42 PM, Karl DeSaulniers  wrote:
> 
> Hi,
> I am assuming this is just a typo in your post and not your code, correct? 
> (see quoted text below)
> If not, then try the other bracket?
> 
> $query=$_GET['databasecolumn'];
> 
> 
>> On Jun 9, 2023, at 4:05 AM, e-letter  wrote:
>> 
>> $query=$_GET['databasecolumn'[;
> 

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

Re: [PHP-DB] use php variable within postgresql query

[Karl DeSaulniers]

Hi,
I am assuming this is just a typo in your post and not your code, correct? (see 
quoted text below)
If not, then try the other bracket?

$query=$_GET['databasecolumn'];


> On Jun 9, 2023, at 4:05 AM, e-letter  wrote:
> 
> $query=$_GET['databasecolumn'[;

Re: [PHP-DB] use php variable within postgresql query

[Michael Oki]

Meanwhile, you should probably use prepared statements to avoid SQL
injection.

On Mon, 12 Jun 2023 at 16:03, e-letter  wrote:

> First, sorry for the mistake to type a sanitised version of code.
> Should have been:
> "$query=$_GET['databasecolumn'];"
>
> After some rtfm, confused as a non-computer-programmer why it is
> necessary to set the $_GET parameter.
>
> The overall simple scenario is to view a postgresql database in a
> series of html web pages of more details of data ("drill down"?).
>
> php code within html file 1:
> "
> $databasequery=pg_query($databaseconnection,'SELECT
> databasecolumn1,
> databasecolumn2 FROM databasetable');
> if (!$databasequery) {
> echo 'rubbish code';
> exit;
> }
> while
> ($databasequery1=pg_fetch_assoc($databasequery)) {
> echo '
>  href="localfile.php?databasecolumn1='.$databasequery1['databasecolumn1'].'">'.$databasequery1['databasecolumn1'].'';
> echo ' '
> .$databasequery1['databasecolumn2'].'';
> ';
> }
> "
>
> The first html file shows successfully the a list of hyperlinks from
> the database, for tuples in 'databasecolumn1'. The desired behaviour
> is that the second html file shows another database query result for
> each tuple in the first html file, i.e. more detail from the database
> for each tuple in 'databasecolumn1'.
>
>
> php code within html file 2:
> "
> $databasequery2=pg_query($databasequery1);
> $databasequery3=pg_query("SELECT * FROM databasetable WHERE
> databasecolumn1='{$databasequery2['databasecolumn']}'");
> echo $databasequery3
> "
>
> The html file 2 shows:
> "
> resource id#3
> "
>
> The expected result was to show all columns for the row constraint
> (...WHERE ...)
>
> It seems that the use of a php variable within a postgresql query is
> not understood. What relevant terminology to read next please?
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

-- 
*Thanks In Advance,*

*Michael OKI*

*CBSA*
*Technologist*
*+34663549276*
*"Impossible Is Nothing"*

*Scan and share QR codes on the mycodescanner
app*


BTA Certified Blockchain Solution Architect & IEEE member
https://michaeloki.blogspot.com

Re: [PHP-DB] use php variable within postgresql query

[e-letter]

First, sorry for the mistake to type a sanitised version of code.
Should have been:
"$query=$_GET['databasecolumn'];"

After some rtfm, confused as a non-computer-programmer why it is
necessary to set the $_GET parameter.

The overall simple scenario is to view a postgresql database in a
series of html web pages of more details of data ("drill down"?).

php code within html file 1:
"
$databasequery=pg_query($databaseconnection,'SELECT databasecolumn1,
databasecolumn2 FROM databasetable');
if (!$databasequery) {
echo 'rubbish code';
exit;
}
while ($databasequery1=pg_fetch_assoc($databasequery)) {
echo '
'.$databasequery1['databasecolumn1'].'';
echo ' ' 
.$databasequery1['databasecolumn2'].'';
';
}
"

The first html file shows successfully the a list of hyperlinks from
the database, for tuples in 'databasecolumn1'. The desired behaviour
is that the second html file shows another database query result for
each tuple in the first html file, i.e. more detail from the database
for each tuple in 'databasecolumn1'.


php code within html file 2:
"
$databasequery2=pg_query($databasequery1);
$databasequery3=pg_query("SELECT * FROM databasetable WHERE
databasecolumn1='{$databasequery2['databasecolumn']}'");
echo $databasequery3
"

The html file 2 shows:
"
resource id#3
"

The expected result was to show all columns for the row constraint
(...WHERE ...)

It seems that the use of a php variable within a postgresql query is
not understood. What relevant terminology to read next please?

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

Re: [PHP-DB] use php variable within postgresql query

[Aziz Saleh]

That was a bad copy/paste on his part. Syntax errors will not allow PHP to
even run the code.

On Fri, Jun 9, 2023 at 11:14 AM G. Franklin  wrote:

> My friends,
>
> Would not the syntax of "$query=$_GET['databasecolumn'[;" be part of the
> issue?
>
> I believe the brackets should be closed like this, no?
> "$query=$_GET['databasecolumn'];"
>
> ~G.
>
>
>
> On Fri, 9 Jun 2023 at 10:53, Aziz Saleh  wrote:
>
> > Read and understand these, should help you:
> >
> > https://www.php.net/manual/en/language.types.array.php
> >
> > Basically its databasecolumnname get param is not defined (set) causing
> the
> > query to fail also.
> >
> > On Fri, Jun 9, 2023 at 5:06 AM e-letter  wrote:
> >
> > > Readers,
> > >
> > > Suppose:
> > >
> > > $query=$_GET['databasecolumn'[;
> > >
> > > $anotherquery=pg_query($databaseconnection, 'SELECT * FROM
> > > databasename WHERE databasecolumnname="'.$query.';"');
> > >
> > > Two errors are reported:
> > >
> > > PHP Notice:  Undefined index: databasecolumnname
> > >
> > > PHP Warning:  pg_query(): Query failed: ERROR:  column ";" does not
> exist
> > >
> > > Any advice please?
> > >
> > > --
> > > PHP Database Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> > >
> >
>

Re: [PHP-DB] use php variable within postgresql query

[G. Franklin]

My friends,

Would not the syntax of "$query=$_GET['databasecolumn'[;" be part of the
issue?

I believe the brackets should be closed like this, no?
"$query=$_GET['databasecolumn'];"

~G.



On Fri, 9 Jun 2023 at 10:53, Aziz Saleh  wrote:

> Read and understand these, should help you:
>
> https://www.php.net/manual/en/language.types.array.php
>
> Basically its databasecolumnname get param is not defined (set) causing the
> query to fail also.
>
> On Fri, Jun 9, 2023 at 5:06 AM e-letter  wrote:
>
> > Readers,
> >
> > Suppose:
> >
> > $query=$_GET['databasecolumn'[;
> >
> > $anotherquery=pg_query($databaseconnection, 'SELECT * FROM
> > databasename WHERE databasecolumnname="'.$query.';"');
> >
> > Two errors are reported:
> >
> > PHP Notice:  Undefined index: databasecolumnname
> >
> > PHP Warning:  pg_query(): Query failed: ERROR:  column ";" does not exist
> >
> > Any advice please?
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>

Re: [PHP-DB] use php variable within postgresql query

[Aziz Saleh]

Read and understand these, should help you:

https://www.php.net/manual/en/language.types.array.php

Basically its databasecolumnname get param is not defined (set) causing the
query to fail also.

On Fri, Jun 9, 2023 at 5:06 AM e-letter  wrote:

> Readers,
>
> Suppose:
>
> $query=$_GET['databasecolumn'[;
>
> $anotherquery=pg_query($databaseconnection, 'SELECT * FROM
> databasename WHERE databasecolumnname="'.$query.';"');
>
> Two errors are reported:
>
> PHP Notice:  Undefined index: databasecolumnname
>
> PHP Warning:  pg_query(): Query failed: ERROR:  column ";" does not exist
>
> Any advice please?
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

RE: [PHP-DB] what does the mysqli real connect MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag do? How to disable only CN validation?

[John Wythe]

Thanks, I already saw that from the documentation. I wanted to know what is 
validation in this context?  Complete verification of the cert, or only CN 
verification.
Looking at the source it appears this flag disables both, in the underlying 
php/openssl, plus does something with ALLOW_SELF_SIGNED.

My problem now is:

  1.  Modify the C source code for mysqli to allow setting only the underlying 
php/openssl VERIFY_NAME only
  2.  Find a way to call the underlying PHP code outside mysqli  using the 
return value from DB:connect, or the passed in $dsn.  The functions are 
accessible, but I need to pass a context, and I don’t think $dsn gives me 
access to the context.  It maybe too late in the sequence of events to change 
any of that.  I was thinking of doing a context get, and a context set, i

John Wythe
Software & Systems Engineer
Epicor Software Corporation
www.epicor.com<http://www.epicor.com/>
Ph: 250-260-6495
E-Mail: jwy...@epicor.com

From: Aziz Saleh 
Sent: Wednesday, March 22, 2023 7:36 PM
To: John Wythe 
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] what does the mysqli real connect 
MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag do? How to disable only CN 
validation?

ATTENTION
This email originated outside of Epicor. Please exercise caution when clicking 
links, opening attachments, or responding to this email.
That flag uses SSL encryption but disables validation of the provided SSL 
certificate. This is only for installations using MySQL Native Driver and MySQL 
5.6 or later.

On Wed, Mar 22, 2023 at 4:50 PM John Wythe 
mailto:jwy...@epicor.com>> wrote:
mysqli_real_connect has a parameter called flags than can be passed to it.  One 
of the flags in MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT.

What does this flag do?  Does it only disable the CN validation, or does it 
also disable the certificate validation against a CA authority.

SSL_CONEXT talks about options

peer_name
peer_verify
peer_verify_name

I would like to use these to at least disable only the verify_name part, or 
maybe even set the peer_name and have both validations turned on.

Seems this context function is only available for streams and not mysqli.

Openssl seems to have the ability to do this, however it does not appear to be 
exposed via mysqli.  I am guessing the flag above equates to peer verify and 
not peer_verify_name, or equates to both.

I suppose I could download the source for php_pmysqli, and have a look, would 
that be the correct package to look at?


John Wythe
Software & Systems Engineer
Epicor Software Corporation
www.epicor.com<http://www.epicor.com/>
Ph: 250-260-6495
TF Support: 800-678-7423
E-Mail: jwy...@epicor.com<mailto:jwy...@epicor.com>
Support E-Mail: silksupp...@epicor.com<mailto:silksupp...@epicor.com>
Support portal: https://epicorcs.service-now.com/epiccare/
<http://www.epicor.com/services/default.aspx>
 <http://www.epicor.com/services/default.aspx>

Re: [PHP-DB] what does the mysqli real connect MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag do? How to disable only CN validation?

[Aziz Saleh]

That flag uses SSL encryption but disables validation of the provided SSL
certificate. This is only for installations using MySQL Native Driver and
MySQL 5.6 or later.

On Wed, Mar 22, 2023 at 4:50 PM John Wythe  wrote:

> mysqli_real_connect has a parameter called flags than can be passed to
> it.  One of the flags in *MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT.*
>
>
>
> What does this flag do?  Does it only disable the CN validation, or does
> it also disable the certificate validation against a CA authority.
>
>
>
> SSL_CONEXT talks about options
>
>
>
> peer_name
>
> peer_verify
>
> peer_verify_name
>
>
>
> I would like to use these to at least disable only the verify_name part,
> or maybe even set the peer_name and have both validations turned on.
>
>
>
> Seems this context function is only available for streams and not mysqli.
>
>
>
> Openssl seems to have the ability to do this, however it does not appear
> to be exposed via mysqli.  I am guessing the flag above equates to peer
> verify and not peer_verify_name, or equates to both.
>
>
>
> I suppose I could download the source for php_pmysqli, and have a look,
> would that be the correct package to look at?
>
>
>
>
>
> John Wythe
> Software & Systems Engineer
> Epicor Software Corporation
> www.epicor.com
>
> Ph: 250-260-6495
> TF Support: 800-678-7423
> E-Mail: jwy...@epicor.com
> Support E-Mail: *silksupp...@epicor.com *
>
> *Support portal:* *https://epicorcs.service-now.com/epiccare/
> *
>
> 
>
>
>

Re: [PHP-DB] MySQLnd support for ed25519

[dimi_php]

Hi Ruprecht,

yes, if a provider not changed the default authentication 
(mysql_nativ_password) there is
nothing customers have to do, because the default authentication on mysql 5.7 
and 10.5 is
the same (SHA-1 based).
Mysql changed this starting from version 8, were the default authentication now 
is the plugin caching_sha2_password.
Mariadb not changed the default, but recommends to use a safer authentication 
method like the ed25519-plugin.

I would like to use it, but then i have the problem that PHP (the Mysql nativ 
driver) cames with an
error like "The server requested authentication method unknown to the client".

So i would be glad if this authentication method would be implemented.

Regards,
Dimi

RE: [PHP-DB] MySQLnd support for ed25519

[Jonathan Aquilina via php-db]

Mysql 8 is already the stable version and has been for a while. I for 
development use it on windows and its v8



-Original Message-
From: Ruprecht Helms  
Sent: 08 September 2021 16:02
To: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQLnd support for ed25519

Hi Dimi,

I think mysql 8 is the new version of mysql. I don't know if there a exists a 
stable release or only a beta.
My thoughts are that the last php-version maybe can handle the mentioned 
authentication method or you have to try to compile the handling for the 
mysql-plugin in the php-mysqldriver.

Maybe the question about enhancing the php is relation on the 
Linuxdistribution.  Here I mean an additional rpm if you use Redhat, Fedora, 
Centos, Suse...
or .deb if you use Debian, Ubuntu or such of these.

To mix a tarball with one of these packages I think it is to difficult in this 
case.

Regards,
Ruprecht

Am 08.09.21 um 15:20 schrieb dimi_php:
> Hi Ruprecht,
>
> yes, if a provider not changed the default authentication 
> (mysql_nativ_password) there is nothing customers have to do, because 
> the default authentication on mysql 5.7 and 10.5 is the same (SHA-1 based).
> Mysql changed this starting from version 8, were the default authentication 
> now is the plugin caching_sha2_password.
> Mariadb not changed the default, but recommends to use a safer authentication 
> method like the ed25519-plugin.
>
> I would like to use it, but then i have the problem that PHP (the 
> Mysql nativ driver) cames with an error like "The server requested 
> authentication method unknown to the client".
>
> So i would be glad if this authentication method would be implemented.
>
> Regards,
> Dimi

--
PHP Database Mailing List 
(https://link.edgepilot.com/s/bc6bf762/v5jGKG7Xd0e-fT3zNkB0xg?u=http://www.php.net/)
To unsubscribe, visit: 
https://link.edgepilot.com/s/cb035ec6/Rl3bBHstt0_Aj5u5ZnK0vg?u=http://www.php.net/unsub.php

Re: [PHP-DB] MySQLnd support for ed25519

[Ruprecht Helms]

Hi Dimi,

I think mysql 8 is the new version of mysql. I don't know if there a 
exists a stable release or only a beta.
My thoughts are that the last php-version maybe can handle the mentioned 
authentication method or you have

to try to compile the handling for the mysql-plugin in the php-mysqldriver.

Maybe the question about enhancing the php is relation on the 
Linuxdistribution.  Here I mean an additional rpm if you use Redhat, 
Fedora, Centos, Suse...

or .deb if you use Debian, Ubuntu or such of these.

To mix a tarball with one of these packages I think it is to difficult 
in this case.


Regards,
Ruprecht

Am 08.09.21 um 15:20 schrieb dimi_php:

Hi Ruprecht,

yes, if a provider not changed the default authentication 
(mysql_nativ_password) there is
nothing customers have to do, because the default authentication on mysql 5.7 
and 10.5 is
the same (SHA-1 based).
Mysql changed this starting from version 8, were the default authentication now 
is the plugin caching_sha2_password.
Mariadb not changed the default, but recommends to use a safer authentication 
method like the ed25519-plugin.

I would like to use it, but then i have the problem that PHP (the Mysql nativ 
driver) cames with an
error like "The server requested authentication method unknown to the client".

So i would be glad if this authentication method would be implemented.

Regards,
Dimi


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

Re: [PHP-DB] MySQLnd support for ed25519

[Ruprecht Helms]

Hi Dimi,
hi everyone,

I don't know about this algorithm. On one running server and on my local 
linuxbox there is Mysql running.
On another server the provider changed from mysql to mariadb. As reason 
for the change from mysql 5.7 to mariadb 10.5
the provider only told his customers that mariadb is an enlargement of 
the database and based on mysql. Nothing told about
better security or any more, only that the provider looked forward 
without problems in the changingprocess.


Regards,
Ruprecht

Am 07.09.21 um 17:04 schrieb dimi_php:

Hi everyone,

i just wanted to ask if the authentication with ed25519 algorithm with the 
MySQLnd is maybe possible in the future?
I use mariadb which currently not support either the sha256_password or the 
caching_sha2_password,
therefore i have to use the standard mysql_native_password authentication, 
although i actually want to use
the ed25519 authentication-plugin in mariadb.

Best regards,

Dimi


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

Re: [PHP-DB] Realtime connection to postgres database

[Karl DeSaulniers]

Is this what you are looking for?

https://www.postgresql.org/docs/9.6/monitoring-stats.html 


or maybe this?

https://github.com/supabase/realtime 


Google Foo:  real-time connection to postgresql database (v9.6)

HTH,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 




> On Nov 9, 2020, at 11:41 PM, Rodrigo Sánchez  wrote:
> 
> connected to  postgresql
> database (v9.6)

Re: [PHP-DB] Realtime connection to postgres database

[Karl DeSaulniers]

Sounds like a good cron job candidate. 

Best,
Karl

Sent from losPhone

> On Oct 25, 2020, at 4:43 PM, Aziz Saleh  wrote:
> 
> Another way to do this is to have an ajax call on timeout to an update
> endpoint, if there is an update inject that update on the page (or refresh
> it).
> 
> Some js examples (both short/long polling):
> 
> https://stackoverflow.com/questions/42825972/update-html-when-change-is-noticed-in-mysql-database
> https://stackoverflow.com/questions/22577457/update-data-on-a-page-without-refreshing
> https://stackoverflow.com/questions/8100594/refresh-content-automatically-if-the-database-changes
> 
> 
>> On Sun, Oct 25, 2020 at 5:18 PM Rodrigo Sánchez 
>> wrote:
>> 
>> Hi everyone,
>> 
>>  I made a table in html that reads a table from a postgres database
>> (easy part).  The postgres database grows every time an earthquake
>> occurs. What is the best way to keep the table updated automatically,
>> each time a new earthquake is added to the database? (I mean, not having
>> to update by clicking on the browser). The ugly way to do it is by using
>> something like header ("refresh: 3"); for example ... but isn't there
>> something better?
>> 
>>  Excuse the question if it is very obvious, but I am not a "full" time
>> php programmer.
>> 
>>  (PHP 7.4.11, postgresql 9.6.19)
>> 
>> 
>> --
>> 
>>  Rodrigo Sánchez-Olavarría
>>  CSN, Centro Sismologico Nacional
>>  Universidad de Chile
>>  Celular : +56 9 42137243
>> 
>> 
>> --
>> 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] Realtime connection to postgres database

[Aziz Saleh]

Another way to do this is to have an ajax call on timeout to an update
endpoint, if there is an update inject that update on the page (or refresh
it).

Some js examples (both short/long polling):

https://stackoverflow.com/questions/42825972/update-html-when-change-is-noticed-in-mysql-database
https://stackoverflow.com/questions/22577457/update-data-on-a-page-without-refreshing
https://stackoverflow.com/questions/8100594/refresh-content-automatically-if-the-database-changes


On Sun, Oct 25, 2020 at 5:18 PM Rodrigo Sánchez 
wrote:

> Hi everyone,
>
>   I made a table in html that reads a table from a postgres database
> (easy part).  The postgres database grows every time an earthquake
> occurs. What is the best way to keep the table updated automatically,
> each time a new earthquake is added to the database? (I mean, not having
> to update by clicking on the browser). The ugly way to do it is by using
> something like header ("refresh: 3"); for example ... but isn't there
> something better?
>
>   Excuse the question if it is very obvious, but I am not a "full" time
> php programmer.
>
>   (PHP 7.4.11, postgresql 9.6.19)
>
>
> --
> 
>   Rodrigo Sánchez-Olavarría
>   CSN, Centro Sismologico Nacional
>   Universidad de Chile
>   Celular : +56 9 42137243
> 
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] Can connect to Mysql via command line but not via browser

[Roberto Carlos Garcia Luis]

Please try with this answer...

https://stackoverflow.com/questions/41178774/connect-database-error-type-2002-permission-denied


El jue., 3 sept. 2020 a las 17:23, Tai Larson ()
escribió:

> The are connected to the same network.  I’m setting up a development
> environment.
>
> From: Aziz Saleh 
> Sent: Thursday, September 3, 2020 3:09 PM
> To: Tai Larson 
> Cc: php-db@lists.php.net
> Subject: Re: [PHP-DB] Can connect to Mysql via command line but not via
> browser
>
> Are both connected to the same network? Just asking because I see a local
> ip for server ip/name. Maybe try with public IP instead see if it helps.
>
> On Thu, Sep 3, 2020 at 5:51 PM Tai Larson  tai.lar...@weierlaw.com>> wrote:
> I have a MySQL database server and a separate web sever running Apache.
> Both are running Centos 8.
>
> I can connect to my database server from my web server via the command
> line, but I get permission denied whenever I try to connect to the server
> via a browser.
>
> Here is the code I'm running:
>  $servername = "192.168.10.XXX";
> $username = "root";
> $password = "123456789";
> $conn = new mysqli($servername, $username, $password); if
> ($conn->connect_error) {
>   die("Connection failed: " . $conn->connect_error); } echo "Connected
> successfully"; ?>
>
> I SSH into my web server and run this command "php TestMySQL.php".  I
> receive the output of "Connected successfully".
>
> When I attempt through my browser or curl, I receive the output of
> "Connection failed: Permission denied".
>
> I can also connect to the remote database with the local mysql client.
>
> Thank you all in advance for your help.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

RE: [PHP-DB] Can connect to Mysql via command line but not via browser

[Tai Larson]

The are connected to the same network.  I’m setting up a development 
environment.

From: Aziz Saleh 
Sent: Thursday, September 3, 2020 3:09 PM
To: Tai Larson 
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Can connect to Mysql via command line but not via browser

Are both connected to the same network? Just asking because I see a local ip 
for server ip/name. Maybe try with public IP instead see if it helps.

On Thu, Sep 3, 2020 at 5:51 PM Tai Larson 
mailto:tai.lar...@weierlaw.com>> wrote:
I have a MySQL database server and a separate web sever running Apache.  Both 
are running Centos 8.

I can connect to my database server from my web server via the command line, 
but I get permission denied whenever I try to connect to the server via a 
browser.

Here is the code I'm running:
connect_error) {
  die("Connection failed: " . $conn->connect_error); } echo "Connected 
successfully"; ?>

I SSH into my web server and run this command "php TestMySQL.php".  I receive 
the output of "Connected successfully".

When I attempt through my browser or curl, I receive the output of "Connection 
failed: Permission denied".

I can also connect to the remote database with the local mysql client.

Thank you all in advance for your help.

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

Re: [PHP-DB] Can connect to Mysql via command line but not via browser

[Aziz Saleh]

Are both connected to the same network? Just asking because I see a local
ip for server ip/name. Maybe try with public IP instead see if it helps.

On Thu, Sep 3, 2020 at 5:51 PM Tai Larson  wrote:

> I have a MySQL database server and a separate web sever running Apache.
> Both are running Centos 8.
>
> I can connect to my database server from my web server via the command
> line, but I get permission denied whenever I try to connect to the server
> via a browser.
>
> Here is the code I'm running:
>  $servername = "192.168.10.XXX";
> $username = "root";
> $password = "123456789";
> $conn = new mysqli($servername, $username, $password); if
> ($conn->connect_error) {
>   die("Connection failed: " . $conn->connect_error); } echo "Connected
> successfully"; ?>
>
> I SSH into my web server and run this command "php TestMySQL.php".  I
> receive the output of "Connected successfully".
>
> When I attempt through my browser or curl, I receive the output of
> "Connection failed: Permission denied".
>
> I can also connect to the remote database with the local mysql client.
>
> Thank you all in advance for your help.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] Getting PDO-OCI to work in PHP 7

[christopher . jones]

Possibly try a two stage build:

- build your main PHP without PDO_OCI

- build PDO_OCI as a shared library with a simplified configure command that 
doesn't have ldap.

Then move the resulting PDO_OCI library to your first PHP install and update 
php.ini.

Then test thoroughly.

I have had to do the same with PHP OCI8 in the past.  I don't recall trying it 
with PDO_OCI.

The ldap / Oracle Client header clash isn't new in PHP 7.

Chris

On 29/5/19 6:31 am, Stokes, John M via php-db wrote:

I have made multiple attempts to get PDO-OCI to work in PHP 7. Sometimes it
fails to compile at all, other times appears to compile correctly, but I
can't connect to any Oracle databases. Thus far, this has kept me stuck on
PHP 5.x.

The relevant part of my configure statement is:
./configure \
...
--with-mysqli=/usr/bin/mysql_config \
--with-pdo-mysql=/usr \
--with-pdo-odbc=unixODBC,/usr \
--with-pdo-oci=instantclient,/usr/lib/oracle/18.3/client64/lib/ \
--with-unixODBC=/usr \
...

The latest error is when I run "make":
...snip...
In file included from /root/php-7.2.4/ext/ldap/php_ldap.h:30:0,
  from main/internal_functions.c:50:
/usr/include/oracle/18.3/client64/ldap.h:988:18: error: conflicting types
for ‘ber_scanf’
  LDAPFUNCDECL int ber_scanf ( BerElement *ber, char * fmt, ... );
   ^
In file included from /root/php-7.2.4/ext/ldap/php_ldap.h:27:0,
  from main/internal_functions.c:50:
/usr/include/lber.h:335:1: note: previous declaration of ‘ber_scanf’ was
here
  ber_scanf LDAP_P((
  ^
make: *** [main/internal_functions.lo] Error 1

Can anyone offer me some ideas?
Thanks in advance.

-John

--

John Stokes – Certified Oracle of Security seeking Zend wearing a Red Hat.
(CISSP, ZCE, RHCSA)
Software Engineer
Verizon Wireless - West Territory Data Tools & Reporting

*Three Pillars: Humility, Communication, Balance*



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

Re: [PHP-DB] PHP mysqli is NOT trapping some errors when calling stored procedure

[Venkat Hariharan]

Yes, I do have all reqd privs.

In fact, as long as a duplicate key situation isnt encountered, I am able
to invoke the stored proc from PHP alright and the record gets saved in the
database.

But when I try to re-do it using the same value (thereby I am expecting
that 1062-DUPLICATE KEY error will be thrown and caught by PHP), I dont get
any error from PHP!

In mysql client or MySQL Workbench, I do see the error. when I invoke the
stored proc.

Thanks,
Venkat


On Tue, Feb 12, 2019 at 9:56 PM Aziz Saleh  wrote:

> Do you have sufficient privileges to execute stored procedures
> (procs_priv) on PHP's end?
>
> On Tue, Feb 12, 2019 at 11:07 AM Venkat Hariharan 
> wrote:
>
>> Can you take a look at the issue that I've described at
>>
>> https://stackoverflow.com/questions/54643704/php-mysqli-is-not-trapping-some-errors-when-calling-stored-procedure
>> and tell me what I am missing in my PHP code ?
>>
>> To summarize: I am calling a MySQL stored procedure from PHP that does
>> some
>> DMLs (using mysqli). But the problem is that PHP is not able to get
>> intimated of some database error conditions raised by the stored proc
>> (which I know are raised, bcoz they do get flagged when invoked from a
>> mysql client)
>>
>> I've listed the relevant code fragments there. Am I not using mysqli
>> correctly?
>>
>> Thanks,
>> Venkat
>>
>

Re: [PHP-DB] PHP mysqli is NOT trapping some errors when calling stored procedure

[Aziz Saleh]

Do you have sufficient privileges to execute stored procedures (procs_priv)
on PHP's end?

On Tue, Feb 12, 2019 at 11:07 AM Venkat Hariharan 
wrote:

> Can you take a look at the issue that I've described at
>
> https://stackoverflow.com/questions/54643704/php-mysqli-is-not-trapping-some-errors-when-calling-stored-procedure
> and tell me what I am missing in my PHP code ?
>
> To summarize: I am calling a MySQL stored procedure from PHP that does some
> DMLs (using mysqli). But the problem is that PHP is not able to get
> intimated of some database error conditions raised by the stored proc
> (which I know are raised, bcoz they do get flagged when invoked from a
> mysql client)
>
> I've listed the relevant code fragments there. Am I not using mysqli
> correctly?
>
> Thanks,
> Venkat
>

Re: [PHP-DB] MySQL connection

[Karl DeSaulniers]

That makes complete sense. 
Thank you Joshua.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Mar 5, 2017, at 6:44 PM, Arneson, Joshua  wrote:
> 
> If you have multiple calls to the database that are grouped, definitely 
> perform them during the same connection session. The bottom line is unless 
> the overhead associated with opening and closing the connection is going to 
> be an issue programmatically, keep connections brief and group your 
> transactions where possible. A good example would be you have a group of 
> calls (A, B, C) that need to happen. Calls A and B can run one after the 
> other, but C has to wait on a user input (no matter how small/simple), you 
> should open the connection, run calls A and B, close the connection waiting 
> on user input, then reopen, run call C, then close the connection. A good 
> exception to this rule would be if you have calls that have to run every 'x' 
> milliseconds and you anticipate that you MIGHT at ANY point run into 
> concurrency issues, you would hold a single open connection for that specific 
> case and adhere to the 'open'-'run calls'-'close' standard for all other 
> cases. Remember, we rarely know what new tasks our programs will be doing in 
> the future so building for scalability is always a good bet. 
> 
> Respectfully,
> 
> Joshua D. Arneson
> Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
> 130 W Kingsbridge Rd, Rm 5F-04
> Bronx, NY 10468
> Email: joshua.arne...@mssm.edu
> Office: 718-584-9000 ext 6094
> Mobile: 347-915-8911
> Fax: 718-741-4746
> 
>> On Mar 5, 2017, at 7:29 PM, Karl DeSaulniers  wrote:
>> 
>> Ah, thanks for the reply Joshua.
>> 
>> Duly noted. So when is it bad to make multiple open and close connections 
>> then?
>> I am guessing that could have some impact with lots of users too. Yes?
>> 
>> If the website in question does not have a lot of users (less than 1,000), 
>> is this still a bad call to keep an open connection?
>> If so, should I be closing the connection after each page load that has 
>> multiple calls on the database?
>> Or after each call to the database? 
>> 
>> I am wanting to make sure I am not doing something bad or dangerous when 
>> holding these sessions open.
>> If it is a matter of taste, I'll leave it, but if it is a matter of best 
>> practices to open and close, I will change the method I am using.
>> 
>> Again, TIA!
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=w7VxdOhlxCY27A_sfaFCxntsWMG8ZDA3nukN6fBstsA=JzvStr8fa_OyNaHOUe0Xp8_o6aDzZSgZ6OXyEk6WyIM=
>> 
>> 
>> 
>> 
>>> On Mar 5, 2017, at 6:19 PM, Arneson, Joshua  wrote:
>>> 
>>> Right off the bat, you need to consider concurrency issues. Depending on 
>>> the size of your user base and level of activity this could become a major 
>>> issue. In the end, why hold an open connection for 15 minutes just to 
>>> process 20-30 transactions that take 20-30ms each? Just better (under most 
>>> circumstances) to open the connection, process your transaction , then 
>>> close the connection. 
>>> 
>>> Respectfully,
>>> 
>>> Joshua D. Arneson
>>> Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
>>> 130 W Kingsbridge Rd, Rm 5F-04
>>> Bronx, NY 10468
>>> Email: joshua.arne...@mssm.edu
>>> Office: 718-584-9000 ext 6094
>>> Mobile: 347-915-8911
>>> Fax: 718-741-4746
>>> 
 On Mar 5, 2017, at 6:54 PM, Karl DeSaulniers  wrote:
 
 Hello everyone,
 Long time. Hope all are well.
 
 Quick question. How should MySQL connections be treated?
 Is it ok to leave them open or is it better to close them after 
 transactions?
 I have a website that uses sessions and was wondering if there was any 
 situations where I should be closing the connection.
 Either for performance reasons or security or best practices. 
 
 Just wondering your professional.
 
 TIA
 
 Best,
 
 Karl DeSaulniers
 Design Drumm
 https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=09oI7Bn2rpePGXSl8CmHMTUqhm3Rjh676OnMYed0L_4=JBoK9bslzQegAxQDG_NbsuvcCBLw5_LIQkjMpEj4kE8=
  
 
 
 
 
 
>>> 
>>> --
>>> PHP Database Mailing List 
>>> (https://urldefense.proofpoint.com/v2/url?u=http-3A__www.php.net_=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=w7VxdOhlxCY27A_sfaFCxntsWMG8ZDA3nukN6fBstsA=wWlQEGEfTaOWZtdTA776DTMosB5WtSX6K5iaiWM7J3A=
>>>  )
>>> To unsubscribe, visit: 
>>> 

Re: [PHP-DB] MySQL connection

[Arneson, Joshua]

If you have multiple calls to the database that are grouped, definitely perform 
them during the same connection session. The bottom line is unless the overhead 
associated with opening and closing the connection is going to be an issue 
programmatically, keep connections brief and group your transactions where 
possible. A good example would be you have a group of calls (A, B, C) that need 
to happen. Calls A and B can run one after the other, but C has to wait on a 
user input (no matter how small/simple), you should open the connection, run 
calls A and B, close the connection waiting on user input, then reopen, run 
call C, then close the connection. A good exception to this rule would be if 
you have calls that have to run every 'x' milliseconds and you anticipate that 
you MIGHT at ANY point run into concurrency issues, you would hold a single 
open connection for that specific case and adhere to the 'open'-'run 
calls'-'close' standard for all other cases. Remember, we rarely know what new 
tasks our programs will be doing in the future so building for scalability is 
always a good bet. 

Respectfully,

Joshua D. Arneson
Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
130 W Kingsbridge Rd, Rm 5F-04
Bronx, NY 10468
Email: joshua.arne...@mssm.edu
Office: 718-584-9000 ext 6094
Mobile: 347-915-8911
Fax: 718-741-4746

> On Mar 5, 2017, at 7:29 PM, Karl DeSaulniers  wrote:
> 
> Ah, thanks for the reply Joshua.
> 
> Duly noted. So when is it bad to make multiple open and close connections 
> then?
> I am guessing that could have some impact with lots of users too. Yes?
> 
> If the website in question does not have a lot of users (less than 1,000), is 
> this still a bad call to keep an open connection?
> If so, should I be closing the connection after each page load that has 
> multiple calls on the database?
> Or after each call to the database? 
> 
> I am wanting to make sure I am not doing something bad or dangerous when 
> holding these sessions open.
> If it is a matter of taste, I'll leave it, but if it is a matter of best 
> practices to open and close, I will change the method I am using.
> 
> Again, TIA!
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=w7VxdOhlxCY27A_sfaFCxntsWMG8ZDA3nukN6fBstsA=JzvStr8fa_OyNaHOUe0Xp8_o6aDzZSgZ6OXyEk6WyIM=
> 
> 
> 
> 
>> On Mar 5, 2017, at 6:19 PM, Arneson, Joshua  wrote:
>> 
>> Right off the bat, you need to consider concurrency issues. Depending on the 
>> size of your user base and level of activity this could become a major 
>> issue. In the end, why hold an open connection for 15 minutes just to 
>> process 20-30 transactions that take 20-30ms each? Just better (under most 
>> circumstances) to open the connection, process your transaction , then close 
>> the connection. 
>> 
>> Respectfully,
>> 
>> Joshua D. Arneson
>> Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
>> 130 W Kingsbridge Rd, Rm 5F-04
>> Bronx, NY 10468
>> Email: joshua.arne...@mssm.edu
>> Office: 718-584-9000 ext 6094
>> Mobile: 347-915-8911
>> Fax: 718-741-4746
>> 
>>> On Mar 5, 2017, at 6:54 PM, Karl DeSaulniers  wrote:
>>> 
>>> Hello everyone,
>>> Long time. Hope all are well.
>>> 
>>> Quick question. How should MySQL connections be treated?
>>> Is it ok to leave them open or is it better to close them after 
>>> transactions?
>>> I have a website that uses sessions and was wondering if there was any 
>>> situations where I should be closing the connection.
>>> Either for performance reasons or security or best practices. 
>>> 
>>> Just wondering your professional.
>>> 
>>> TIA
>>> 
>>> Best,
>>> 
>>> Karl DeSaulniers
>>> Design Drumm
>>> https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=09oI7Bn2rpePGXSl8CmHMTUqhm3Rjh676OnMYed0L_4=JBoK9bslzQegAxQDG_NbsuvcCBLw5_LIQkjMpEj4kE8=
>>>  
>>> 
>>> 
>>> 
>>> 
>>> 
>> 
>> --
>> PHP Database Mailing List 
>> (https://urldefense.proofpoint.com/v2/url?u=http-3A__www.php.net_=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=w7VxdOhlxCY27A_sfaFCxntsWMG8ZDA3nukN6fBstsA=wWlQEGEfTaOWZtdTA776DTMosB5WtSX6K5iaiWM7J3A=
>>  )
>> To unsubscribe, visit: 
>> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.php.net_unsub.php=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=w7VxdOhlxCY27A_sfaFCxntsWMG8ZDA3nukN6fBstsA=qSyyKaFDMntOGvbZ-jgs6sfh-DyfkxT3tTOLa-uFDDw=
>>  
> 
> 
> --
> PHP Database Mailing List 
> 

Re: [PHP-DB] MySQL connection

[Karl DeSaulniers]

Ah, thanks for the reply Joshua.

Duly noted. So when is it bad to make multiple open and close connections then?
I am guessing that could have some impact with lots of users too. Yes?

If the website in question does not have a lot of users (less than 1,000), is 
this still a bad call to keep an open connection?
If so, should I be closing the connection after each page load that has 
multiple calls on the database?
Or after each call to the database? 

I am wanting to make sure I am not doing something bad or dangerous when 
holding these sessions open.
If it is a matter of taste, I'll leave it, but if it is a matter of best 
practices to open and close, I will change the method I am using.

Again, TIA!

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Mar 5, 2017, at 6:19 PM, Arneson, Joshua  wrote:
> 
> Right off the bat, you need to consider concurrency issues. Depending on the 
> size of your user base and level of activity this could become a major issue. 
> In the end, why hold an open connection for 15 minutes just to process 20-30 
> transactions that take 20-30ms each? Just better (under most circumstances) 
> to open the connection, process your transaction , then close the connection. 
> 
> Respectfully,
> 
> Joshua D. Arneson
> Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
> 130 W Kingsbridge Rd, Rm 5F-04
> Bronx, NY 10468
> Email: joshua.arne...@mssm.edu
> Office: 718-584-9000 ext 6094
> Mobile: 347-915-8911
> Fax: 718-741-4746
> 
>> On Mar 5, 2017, at 6:54 PM, Karl DeSaulniers  wrote:
>> 
>> Hello everyone,
>> Long time. Hope all are well.
>> 
>> Quick question. How should MySQL connections be treated?
>> Is it ok to leave them open or is it better to close them after transactions?
>> I have a website that uses sessions and was wondering if there was any 
>> situations where I should be closing the connection.
>> Either for performance reasons or security or best practices. 
>> 
>> Just wondering your professional.
>> 
>> TIA
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=09oI7Bn2rpePGXSl8CmHMTUqhm3Rjh676OnMYed0L_4=JBoK9bslzQegAxQDG_NbsuvcCBLw5_LIQkjMpEj4kE8=
>>  
>> >  >
>> 
>> 
>> 
>> 
> 
> --
> 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] MySQL connection

[Arneson, Joshua]

Right off the bat, you need to consider concurrency issues. Depending on the 
size of your user base and level of activity this could become a major issue. 
In the end, why hold an open connection for 15 minutes just to process 20-30 
transactions that take 20-30ms each? Just better (under most circumstances) to 
open the connection, process your transaction , then close the connection. 

Respectfully,

Joshua D. Arneson
Data Manager, Mount Sinai NIH Brain & Tissue Repository (NBTR)
130 W Kingsbridge Rd, Rm 5F-04
Bronx, NY 10468
Email: joshua.arne...@mssm.edu
Office: 718-584-9000 ext 6094
Mobile: 347-915-8911
Fax: 718-741-4746

> On Mar 5, 2017, at 6:54 PM, Karl DeSaulniers  wrote:
> 
> Hello everyone,
> Long time. Hope all are well.
> 
> Quick question. How should MySQL connections be treated?
> Is it ok to leave them open or is it better to close them after transactions?
> I have a website that uses sessions and was wondering if there was any 
> situations where I should be closing the connection.
> Either for performance reasons or security or best practices. 
> 
> Just wondering your professional.
> 
> TIA
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> https://urldefense.proofpoint.com/v2/url?u=http-3A__designdrumm.com=DwIFAg=shNJtf5dKgNcPZ6Yh64b-A=HSbgyt8GFSyWQ53Nxworjip-dgIKnMlPBkQ0VGj7tYk=09oI7Bn2rpePGXSl8CmHMTUqhm3Rjh676OnMYed0L_4=JBoK9bslzQegAxQDG_NbsuvcCBLw5_LIQkjMpEj4kE8=
>  
>   >
> 
> 
> 
> 

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

Re: [PHP-DB] Re: CHAR field with charset UTF8 and COLLATION UNICODE_CI_AI or UTF8PHP is loading white spaces

[Lester Caine]

On 22/11/16 18:01, Delmar Wichnieski wrote:
> 2016-11-22 12:42 GMT-02:00 Lester Caine :
> 
>> >  needs help to move
>> > the string to a variable that it can check if the UTF8 data is a single
>> > character or multiple characters.
>> >
>> >
> I believe it is a single byte, the goal is to simulate a boolean field,
> where I only use S for yes and N for no. (Idem/the same Y and N in English).
> 
> There is no operations like 'upper' and 'lower'. The script is very simple,
> according to pastebin links in the previous message.
> 
> S and N are in the range between 0 and 127 of the ASCII table and UTF-8
> says that only one byte is required to encode the first 128 ASCII
> characters (Unicode U +  to U + 007F).
> 
> But even if it consumed 2, 3 or 4 bytes, UNICODE should predict the end of
> the character, so it would be enough to find the end, apply the inverse
> algorithm to the encoding of the code point, and we would have the
> character back. This is just a dream.
> 
> 
> If the situation presented along the thread is a problem, then more people
> should report it. Let's wait. I'll use trim per hour, or cast
> 
> example
> 
> $q = $pdoconn->prepare("SELECT CODIGO, CAST(ACESSOSISTEMA AS VARCHAR(1)) AS
> ACESSOSISTEMA FROM USUARIO");
> 
> And the problem is solved. Or yet another solution not thought out.

That is perhaps the point. PHP on it's own can't decide if you need to
convert to an ASCII single byte, allow space for a multiple byte single
character or something else. All PHP sees is a buffer with a number of
bytes in, and what comes over the wire from Firebird even strips any
trailing space characters requiring the client end to untangle things.
If you want a unicode string you have to copy it to a mbstring variable
since the simple single byte buffer does not know that it is not just
256 bit data. Now a CHAR(1) could be treated as a special case, but
CHAR(2+) can not be so easily handled. This is one reason why the normal
'hack' to add a binary domain is to use a SMALLINT rather than a CHAR
and store NULL/0/1 ...

I'm not saying that the current results are correct, just that without a
native handling of unicode one has some edge cases which could be
resolved different ways. Returning a unicode CHAR field as a fixed
number of 32bit characters has an attraction when one needs to work with
particular fixed character positions in the string but while UNICODE_FSS
was designed with that in mind, UTF8 *IS* the right way forward once
everybody actually supports it ;)

One of my pet grips is that simple PHP variables do not play well with
database fields, and rather than having to pull in mbstring, extending
'string' so that it can be flagged as utf8 and handle a utf8 field
natively is what is needed. The fact that Firebird is capable of using a
different collation for each field is not something that PHP understands
and another reason I don't use PDO at all in production. With ADOdb one
has a bit more access to the metadata for the query.

-- 
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] Re: CHAR field with charset UTF8 and COLLATION UNICODE_CI_AI or UTF8PHP is loading white spaces

[Delmar Wichnieski]

2016-11-22 12:42 GMT-02:00 Lester Caine :

>  needs help to move
> the string to a variable that it can check if the UTF8 data is a single
> character or multiple characters.
>
>
I believe it is a single byte, the goal is to simulate a boolean field,
where I only use S for yes and N for no. (Idem/the same Y and N in English).

There is no operations like 'upper' and 'lower'. The script is very simple,
according to pastebin links in the previous message.

S and N are in the range between 0 and 127 of the ASCII table and UTF-8
says that only one byte is required to encode the first 128 ASCII
characters (Unicode U +  to U + 007F).

But even if it consumed 2, 3 or 4 bytes, UNICODE should predict the end of
the character, so it would be enough to find the end, apply the inverse
algorithm to the encoding of the code point, and we would have the
character back. This is just a dream.


If the situation presented along the thread is a problem, then more people
should report it. Let's wait. I'll use trim per hour, or cast

example

$q = $pdoconn->prepare("SELECT CODIGO, CAST(ACESSOSISTEMA AS VARCHAR(1)) AS
ACESSOSISTEMA FROM USUARIO");

And the problem is solved. Or yet another solution not thought out.

Thank you for the precious information.

Delmar Wichnieski

Re: [PHP-DB] Re: CHAR field with charset UTF8 and COLLATION UNICODE_CI_AI or UTF8PHP is loading white spaces

[Lester Caine]

On 22/11/16 13:56, Delmar Wichnieski wrote:
> But VARCHAR fields work correctly. Problem only in CHAR.

VARCHAR is trimmed to the number of bytes used ... not the number of
'characters'! CHAR is only designed for single byte characters IN PHP so
providing multi byte characters to a CHAR(1) field does not know how
many actual characters are displayed. I'm not saying what is currently
happening is right, but it is 'safe' since PHP then needs help to move
the string to a variable that it can check if the UTF8 data is a single
character or multiple characters.

> And it should not be gambol, because
> "Each UTF is reversible, thus every UTF supports lossless round tripping:
> mapping from any Unicode coded character sequence S to a sequence of bytes
> and back will produce S again."
> Source
> http://unicode.org/faq/utf_bom.html

Provided that there is no processing of the data then that is correct,
but operations like 'upper' and 'lower' can result in a change in number
of characters, and the addition of accent characters can also result in
differences. It is this area that basically stopped the development of a
UTF8 native PHP6. Normalization in http://www.unicode.org/reports/tr15/
is a minefield even for the Firebird collation process ... Just how long
is the normalized string?

> 2016-11-22 11:21 GMT-02:00 Lester Caine :
> 
>> > On 22/11/16 12:58, Delmar Wichnieski wrote:
>>> > > Since there was no answer here on the list, I was feeling alone and
>> > afraid
>>> > > and wondering why no one else has this problem.
>> >
>> > Delmar I must apologise as I HAD posted a reply, but it did not actually
>> > go through ... list in bounce emails mode which I missed ...
>> >
>> > The simple answer is that strings in PHP are not UTF8 so the 'bug' you
>> > are listing is actually that we need to make sure that the single byte
>> > buffer for a string is long enough. To ensure UTF8 strings to be handled
>> > properly since PHP6 is not going to happen, we have to transfer the
>> > simple php strings to mbstring objects. UTF8 is a gambol in PHP if it is
>> > going to be transferred properly as a simple string variable and will
>> > give string length as bytes rather than characters ...


-- 
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] Re: CHAR field with charset UTF8 and COLLATION UNICODE_CI_AI or UTF8PHP is loading white spaces

[Delmar Wichnieski]

But VARCHAR fields work correctly. Problem only in CHAR.

And it should not be gambol, because
"Each UTF is reversible, thus every UTF supports lossless round tripping:
mapping from any Unicode coded character sequence S to a sequence of bytes
and back will produce S again."
Source
http://unicode.org/faq/utf_bom.html

2016-11-22 11:21 GMT-02:00 Lester Caine :

> On 22/11/16 12:58, Delmar Wichnieski wrote:
> > Since there was no answer here on the list, I was feeling alone and
> afraid
> > and wondering why no one else has this problem.
>
> Delmar I must apologise as I HAD posted a reply, but it did not actually
> go through ... list in bounce emails mode which I missed ...
>
> The simple answer is that strings in PHP are not UTF8 so the 'bug' you
> are listing is actually that we need to make sure that the single byte
> buffer for a string is long enough. To ensure UTF8 strings to be handled
> properly since PHP6 is not going to happen, we have to transfer the
> simple php strings to mbstring objects. UTF8 is a gambol in PHP if it is
> going to be transferred properly as a simple string variable and will
> give string length as bytes rather than characters ...
>
> --
> 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] Re: CHAR field with charset UTF8 and COLLATION UNICODE_CI_AI or UTF8PHP is loading white spaces

[Lester Caine]

On 22/11/16 12:58, Delmar Wichnieski wrote:
> Since there was no answer here on the list, I was feeling alone and afraid
> and wondering why no one else has this problem.

Delmar I must apologise as I HAD posted a reply, but it did not actually
go through ... list in bounce emails mode which I missed ...

The simple answer is that strings in PHP are not UTF8 so the 'bug' you
are listing is actually that we need to make sure that the single byte
buffer for a string is long enough. To ensure UTF8 strings to be handled
properly since PHP6 is not going to happen, we have to transfer the
simple php strings to mbstring objects. UTF8 is a gambol in PHP if it is
going to be transferred properly as a simple string variable and will
give string length as bytes rather than characters ...

-- 
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] Any method to get primary key matching a given value ?

[B. Aerts]

Hi Ratin,

check out this FAQ : https://sqlite.org/faq.html#q7

SQlite has a read-only table that holds the creation query for each table.

By doing a text analysis of this query, you can find out which field is 
declared as primary key.


Regards,

Bert

On 11/10/16 02:12, Ratin wrote:

Sorry about my late reply but this was my function - a bit of a hack but
works properly on my version of php-sqlite3:


  function get_primary_key_name($table)
  {
   $primary_key='';
   $db = new MyDB();
   if(!$db)
   {
   echo $db->lastErrorMsg();
   }
   else
   {
   $qstr = "PRAGMA table_info(" . $table . ");" ;
   $query = $db->query($qstr);
   while ($result = $query->fetchArray())
   {
  if ($result['pk'] == 1)
  {
$primary_key=$result['name'];
   }
}
   }
   $db->close();
   return $primary_key;
   }


On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers 
wrote:


This may also shed some light for you.
The accepted answer and possibly the one below it if you are on .NET

http://stackoverflow.com/questions/763516/information-
schema-columns-on-sqlite

HTH,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com





On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers 

wrote:


Hey Ratin,
Have you looked into the table column named 'pk' inside table_info?
That is where a column is indicated to be a primary key or not.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 





On Aug 18, 2016, at 6:51 PM, Ratin  wrote:

Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is

not available for sqlite database. I had to built up the whole query with
php using PRAGMA table_info(tablename), looking at the pk entry, when its
1, get the column name, and then update the sql statement based on that. A
bit of work, wouldve been much simpler if a method was provided, but oh
well ..


Thanks again

Ratin

On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers > wrote:

Hi Ratin,
Going to take a stab at this one.
Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
Might be where you want to look for what you are trying.
Sorry can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 





On Aug 18, 2016, at 1:27 PM, Ratin > wrote:


I'm writing the generic get that works on different tables having

different

primary keys but the argument of get is always the primary key , i.e.

get

request is -

get (column name, value)

the value is always the primary key value.

It looks like it would be a pretty standard method but I cant find a

method

like that. Anybody have any clue?

Thanks

Ratin



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

http://www.php.net/unsub.php>








--
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] Any method to get primary key matching a given value ?

[Karl DeSaulniers]

You may be correct. Admittedly, I am stabbing in the dark here. I work on 
MySQL, not SQLite.
Just good at key word searches. :)  This link seems to be promising.

http://www.sqlite.org/c3ref/table_column_metadata.html

Sorry I can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Oct 10, 2016, at 7:48 PM, Ratin  wrote:
> 
> Hi Karl, Isnt it the same ? I am doing the check  if ($result['pk'] == 1),
> and you are doing  if ($result['pk'] !== null). I wanted to make sure I
> dont get false positives, what if $result['pk'] is set to zero, in your
> case it will be a match but thats probably not what we want, cuz the pk
> field is not set to 1..
> 
> Regards,
> Ratin
> 
> 
> On Mon, Oct 10, 2016 at 5:38 PM, Karl DeSaulniers 
> wrote:
> 
>> Hi Ratin,
>> I "think" you can just do this and not have to store the table name.
>> However, I have no way of testing this. You will have to test on your end.
>> 
>> while ($result = $query->fetchArray()) {
>>if ($result['pk'] !== null) {
>>$primary_key=$result['pk'];
>>}
>> }
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com 
>> 
>> 
>> 
>> 
>>> On Oct 10, 2016, at 7:12 PM, Ratin  wrote:
>>> 
>>> Sorry about my late reply but this was my function - a bit of a hack but
>> works properly on my version of php-sqlite3:
>>> 
>>> 
>>>  function get_primary_key_name($table)
>>>  {
>>>   $primary_key='';
>>>   $db = new MyDB();
>>>   if(!$db)
>>>   {
>>>   echo $db->lastErrorMsg();
>>>   }
>>>   else
>>>   {
>>>   $qstr = "PRAGMA table_info(" . $table . ");" ;
>>>   $query = $db->query($qstr);
>>>   while ($result = $query->fetchArray())
>>>   {
>>>  if ($result['pk'] == 1)
>>>  {
>>>$primary_key=$result['name'];
>>>   }
>>>}
>>>   }
>>>   $db->close();
>>>   return $primary_key;
>>>   }
>>> 
>>> 
>>> On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers > > wrote:
>>> This may also shed some light for you.
>>> The accepted answer and possibly the one below it if you are on .NET
>>> 
>>> http://stackoverflow.com/questions/763516/information-
>> schema-columns-on-sqlite > questions/763516/information-schema-columns-on-sqlite>
>>> 
>>> HTH,
>>> 
>>> Best,
>>> 
>>> Karl DeSaulniers
>>> Design Drumm
>>> http://designdrumm.com 
>>> 
>>> 
>>> 
>>> 
 On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers > > wrote:
 
 Hey Ratin,
 Have you looked into the table column named 'pk' inside table_info?
 That is where a column is indicated to be a primary key or not.
 
 Best,
 
 Karl DeSaulniers
 Design Drumm
 http://designdrumm.com  <
>> http://designdrumm.com/ >
 
 
 
 
> On Aug 18, 2016, at 6:51 PM, Ratin  rat...@gmail.com>> wrote:
> 
> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA
>> is not available for sqlite database. I had to built up the whole query
>> with php using PRAGMA table_info(tablename), looking at the pk entry, when
>> its 1, get the column name, and then update the sql statement based on
>> that. A bit of work, wouldve been much simpler if a method was provided,
>> but oh well ..
> 
> Thanks again
> 
> Ratin
> 
> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers <
>> k...@designdrumm.com  > k...@designdrumm.com >> wrote:
> Hi Ratin,
> Going to take a stab at this one.
> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> Might be where you want to look for what you are trying.
> Sorry can't help more.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com  <
>> http://designdrumm.com/ >
> 
> 
> 
> 
>> On Aug 18, 2016, at 1:27 PM, Ratin  rat...@gmail.com> >>
>> wrote:
>> 
>> I'm writing the generic get that works on different tables having
>> different
>> primary keys but the argument of get is always the primary key ,
>> i.e. get
>> request is -
>> 
>> get (column name, value)
>> 
>> the value is always the primary key value.
>> 
>> It looks like it would be a pretty standard method but I cant find a
>> method
>> like that. Anybody have any clue?
>> 
>> Thanks
>> 
>> Ratin
> 
> 
> --
> PHP 

Re: [PHP-DB] Any method to get primary key matching a given value ?

[Ratin]

Hi Karl, Isnt it the same ? I am doing the check  if ($result['pk'] == 1),
and you are doing  if ($result['pk'] !== null). I wanted to make sure I
dont get false positives, what if $result['pk'] is set to zero, in your
case it will be a match but thats probably not what we want, cuz the pk
field is not set to 1..

Regards,
Ratin


On Mon, Oct 10, 2016 at 5:38 PM, Karl DeSaulniers 
wrote:

> Hi Ratin,
> I "think" you can just do this and not have to store the table name.
> However, I have no way of testing this. You will have to test on your end.
>
> while ($result = $query->fetchArray()) {
> if ($result['pk'] !== null) {
> $primary_key=$result['pk'];
> }
> }
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com 
>
>
>
>
> > On Oct 10, 2016, at 7:12 PM, Ratin  wrote:
> >
> > Sorry about my late reply but this was my function - a bit of a hack but
> works properly on my version of php-sqlite3:
> >
> >
> >   function get_primary_key_name($table)
> >   {
> >$primary_key='';
> >$db = new MyDB();
> >if(!$db)
> >{
> >echo $db->lastErrorMsg();
> >}
> >else
> >{
> >$qstr = "PRAGMA table_info(" . $table . ");" ;
> >$query = $db->query($qstr);
> >while ($result = $query->fetchArray())
> >{
> >   if ($result['pk'] == 1)
> >   {
> > $primary_key=$result['name'];
> >}
> > }
> >}
> >$db->close();
> >return $primary_key;
> >}
> >
> >
> > On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers  > wrote:
> > This may also shed some light for you.
> > The accepted answer and possibly the one below it if you are on .NET
> >
> > http://stackoverflow.com/questions/763516/information-
> schema-columns-on-sqlite  questions/763516/information-schema-columns-on-sqlite>
> >
> > HTH,
> >
> > Best,
> >
> > Karl DeSaulniers
> > Design Drumm
> > http://designdrumm.com 
> >
> >
> >
> >
> > > On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers  > wrote:
> > >
> > > Hey Ratin,
> > > Have you looked into the table column named 'pk' inside table_info?
> > > That is where a column is indicated to be a primary key or not.
> > >
> > > Best,
> > >
> > > Karl DeSaulniers
> > > Design Drumm
> > > http://designdrumm.com  <
> http://designdrumm.com/ >
> > >
> > >
> > >
> > >
> > >> On Aug 18, 2016, at 6:51 PM, Ratin > wrote:
> > >>
> > >> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA
> is not available for sqlite database. I had to built up the whole query
> with php using PRAGMA table_info(tablename), looking at the pk entry, when
> its 1, get the column name, and then update the sql statement based on
> that. A bit of work, wouldve been much simpler if a method was provided,
> but oh well ..
> > >>
> > >> Thanks again
> > >>
> > >> Ratin
> > >>
> > >> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers <
> k...@designdrumm.com   k...@designdrumm.com >> wrote:
> > >> Hi Ratin,
> > >> Going to take a stab at this one.
> > >> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> > >> Might be where you want to look for what you are trying.
> > >> Sorry can't help more.
> > >>
> > >> Best,
> > >>
> > >> Karl DeSaulniers
> > >> Design Drumm
> > >> http://designdrumm.com  <
> http://designdrumm.com/ >
> > >>
> > >>
> > >>
> > >>
> > >>> On Aug 18, 2016, at 1:27 PM, Ratin  >>
> wrote:
> > >>>
> > >>> I'm writing the generic get that works on different tables having
> different
> > >>> primary keys but the argument of get is always the primary key ,
> i.e. get
> > >>> request is -
> > >>>
> > >>> get (column name, value)
> > >>>
> > >>> the value is always the primary key value.
> > >>>
> > >>> It looks like it would be a pretty standard method but I cant find a
> method
> > >>> like that. Anybody have any clue?
> > >>>
> > >>> Thanks
> > >>>
> > >>> Ratin
> > >>
> > >>
> > >> --
> > >> PHP Database Mailing List (http://www.php.net/ 
> >)
> > >> To unsubscribe, visit: http://www.php.net/unsub.php <
> http://www.php.net/unsub.php>  http://www.php.net/unsub.php>>
> > >>
> > >>
> > >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/ )
> > To unsubscribe, visit: 

Re: [PHP-DB] Any method to get primary key matching a given value ?

[Karl DeSaulniers]

Hi Ratin,
I "think" you can just do this and not have to store the table name.
However, I have no way of testing this. You will have to test on your end. 

while ($result = $query->fetchArray()) {
if ($result['pk'] !== null) {
$primary_key=$result['pk'];
}
}

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 




> On Oct 10, 2016, at 7:12 PM, Ratin  wrote:
> 
> Sorry about my late reply but this was my function - a bit of a hack but 
> works properly on my version of php-sqlite3:
> 
>  
>   function get_primary_key_name($table)
>   {
>$primary_key='';
>$db = new MyDB();
>if(!$db)
>{
>echo $db->lastErrorMsg();
>}
>else
>{
>$qstr = "PRAGMA table_info(" . $table . ");" ;
>$query = $db->query($qstr);
>while ($result = $query->fetchArray())
>{
>   if ($result['pk'] == 1)
>   {
> $primary_key=$result['name'];
>}
> }
>}
>$db->close();
>return $primary_key;
>}
> 
> 
> On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers  > wrote:
> This may also shed some light for you.
> The accepted answer and possibly the one below it if you are on .NET
> 
> http://stackoverflow.com/questions/763516/information-schema-columns-on-sqlite
>  
> 
> 
> HTH,
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com 
> 
> 
> 
> 
> > On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers  > > wrote:
> >
> > Hey Ratin,
> > Have you looked into the table column named 'pk' inside table_info?
> > That is where a column is indicated to be a primary key or not.
> >
> > Best,
> >
> > Karl DeSaulniers
> > Design Drumm
> > http://designdrumm.com   > >
> >
> >
> >
> >
> >> On Aug 18, 2016, at 6:51 PM, Ratin  >> > wrote:
> >>
> >> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is 
> >> not available for sqlite database. I had to built up the whole query with 
> >> php using PRAGMA table_info(tablename), looking at the pk entry, when its 
> >> 1, get the column name, and then update the sql statement based on that. A 
> >> bit of work, wouldve been much simpler if a method was provided, but oh 
> >> well ..
> >>
> >> Thanks again
> >>
> >> Ratin
> >>
> >> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers  >>   >> >> wrote:
> >> Hi Ratin,
> >> Going to take a stab at this one.
> >> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> >> Might be where you want to look for what you are trying.
> >> Sorry can't help more.
> >>
> >> Best,
> >>
> >> Karl DeSaulniers
> >> Design Drumm
> >> http://designdrumm.com   >> >
> >>
> >>
> >>
> >>
> >>> On Aug 18, 2016, at 1:27 PM, Ratin  >>>   >>> >> wrote:
> >>>
> >>> I'm writing the generic get that works on different tables having 
> >>> different
> >>> primary keys but the argument of get is always the primary key , i.e. get
> >>> request is -
> >>>
> >>> get (column name, value)
> >>>
> >>> the value is always the primary key value.
> >>>
> >>> It looks like it would be a pretty standard method but I cant find a 
> >>> method
> >>> like that. Anybody have any clue?
> >>>
> >>> Thanks
> >>>
> >>> Ratin
> >>
> >>
> >> --
> >> 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] Any method to get primary key matching a given value ?

[Ratin]

Sorry about my late reply but this was my function - a bit of a hack but
works properly on my version of php-sqlite3:


  function get_primary_key_name($table)
  {
   $primary_key='';
   $db = new MyDB();
   if(!$db)
   {
   echo $db->lastErrorMsg();
   }
   else
   {
   $qstr = "PRAGMA table_info(" . $table . ");" ;
   $query = $db->query($qstr);
   while ($result = $query->fetchArray())
   {
  if ($result['pk'] == 1)
  {
$primary_key=$result['name'];
   }
}
   }
   $db->close();
   return $primary_key;
   }


On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers 
wrote:

> This may also shed some light for you.
> The accepted answer and possibly the one below it if you are on .NET
>
> http://stackoverflow.com/questions/763516/information-
> schema-columns-on-sqlite
>
> HTH,
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
>
> > On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers 
> wrote:
> >
> > Hey Ratin,
> > Have you looked into the table column named 'pk' inside table_info?
> > That is where a column is indicated to be a primary key or not.
> >
> > Best,
> >
> > Karl DeSaulniers
> > Design Drumm
> > http://designdrumm.com 
> >
> >
> >
> >
> >> On Aug 18, 2016, at 6:51 PM, Ratin  wrote:
> >>
> >> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is
> not available for sqlite database. I had to built up the whole query with
> php using PRAGMA table_info(tablename), looking at the pk entry, when its
> 1, get the column name, and then update the sql statement based on that. A
> bit of work, wouldve been much simpler if a method was provided, but oh
> well ..
> >>
> >> Thanks again
> >>
> >> Ratin
> >>
> >> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers  > wrote:
> >> Hi Ratin,
> >> Going to take a stab at this one.
> >> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> >> Might be where you want to look for what you are trying.
> >> Sorry can't help more.
> >>
> >> Best,
> >>
> >> Karl DeSaulniers
> >> Design Drumm
> >> http://designdrumm.com 
> >>
> >>
> >>
> >>
> >>> On Aug 18, 2016, at 1:27 PM, Ratin > wrote:
> >>>
> >>> I'm writing the generic get that works on different tables having
> different
> >>> primary keys but the argument of get is always the primary key , i.e.
> get
> >>> request is -
> >>>
> >>> get (column name, value)
> >>>
> >>> the value is always the primary key value.
> >>>
> >>> It looks like it would be a pretty standard method but I cant find a
> method
> >>> like that. Anybody have any clue?
> >>>
> >>> Thanks
> >>>
> >>> Ratin
> >>
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/ )
> >> To unsubscribe, visit: http://www.php.net/unsub.php <
> 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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 27, 2016, at 2:24 AM, Ford, Mike <m.f...@leedsbeckett.ac.uk> wrote:
> 
>> -Original Message-
>> From: Karl DeSaulniers [mailto:k...@designdrumm.com]
>> Sent: 25 September 2016 09:59
>> To: PHP List Database <php-db@lists.php.net>
>> Subject: Re: [PHP-DB] Corn job anomaly
>> 
>> Now I am getting an error with mysql syntax.
>> 
>> "SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN ".FIELDS_TABLE." cf
>> ON cf.Order_ID = otn.Order_ID WHERE cf.Earliest_Pickup >= DATE(NOW()) AND
>> cf.Earliest_Pickup <= DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND
>> otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"
>> 
>> is giving me this error:
>>  You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use near
>> '\"Shipping\" AND otn.Order_Status != \"Completed\"' at line 1
> 
> I don't think INTERVAL works like that - you probably need something like:
> 
>... cf.Earliest_Pickup <= DATE_ADD(CURDATE(), INTERVAL 
> ".($Num_Days_Away+1)." DAY) AND ...
> 
> Incidentally, as I understand it CURDATE() does exactly the same as 
> DATE(NOW()), and is probably more readable.
> 
> Cheers!
> 
> Mike
> 
> --
> Mike Ford,


Hi Mike,
I figured out what was wrong with my script on that error. 
I had mysqli_real_escape_string() wrapping the whole select statement. 
The INTERVAL part I got from PHP.net and it is working just fine. Just fyi.
Once I wrapped just ($Num_Days_Away+1) part it started working.

Thanks for responding!

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] Corn job anomaly

[Ford, Mike]

> -Original Message-
> From: Karl DeSaulniers [mailto:k...@designdrumm.com]
> Sent: 25 September 2016 09:59
> To: PHP List Database <php-db@lists.php.net>
> Subject: Re: [PHP-DB] Corn job anomaly
>
> Now I am getting an error with mysql syntax.
>
> "SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN ".FIELDS_TABLE." cf
> ON cf.Order_ID = otn.Order_ID WHERE cf.Earliest_Pickup >= DATE(NOW()) AND
> cf.Earliest_Pickup <= DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND
> otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"
>
> is giving me this error:
>   You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> '\"Shipping\" AND otn.Order_Status != \"Completed\"' at line 1

I don't think INTERVAL works like that - you probably need something like:

... cf.Earliest_Pickup <= DATE_ADD(CURDATE(), INTERVAL 
".($Num_Days_Away+1)." DAY) AND ...

Incidentally, as I understand it CURDATE() does exactly the same as 
DATE(NOW()), and is probably more readable.

Cheers!

Mike

--
Mike Ford,
Electronic Information Developer, Libraries and Learning Innovation,
110 Queen Square House, 80 Woodhouse Lane,
Leeds Beckett University, Leeds LS2 8NU,  United Kingdom
E & S4B: m.f...@leedsbeckett.ac.uk T: +44 113 812 4730

To view the terms under which this email is distributed, please go to:-
http://disclaimer.leedsbeckett.ac.uk/disclaimer/disclaimer.html

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

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

Oops. I actually ran it with single and double quotes and got the same error 
both times. I am using single quotes originally. 

I think the query is getting the \ (backslash) read literally. I did not add 
those manually. 

Best,
Karl

Sent from losPhone

> On Sep 25, 2016, at 9:17 AM, Richard 
> <inbound-lists-...@listmail.innovate.net> wrote:
> 
> 
> 
>> Date: Sunday, September 25, 2016 08:37:14 -0400
>> From: Aziz Saleh <azizsa...@gmail.com>
>> Subject: Re: [PHP-DB] Corn job anomaly
>> 
>> On Sun, Sep 25, 2016 at 4:59 AM, Karl DeSaulniers
>> <k...@designdrumm.com> wrote:
>> 
>>>> On Sep 23, 2016, at 5:38 AM, Richard wrote:
>>>> 
>>>> -- Try the mysql access and commands directly from the command
>>>>   line (using the mysql cli).
>>>> 
>>>> -- Put debug/print statements in your code so you can clearly
>>>>   see where you are getting.
>>>> 
>>>>   -- This includes printing out your mysql statements so that
>>>>  you know exactly what is being passed to mysql (both when
>>>>  you run the script by hand and via cron).
>>>> 
>>> 
>>> Thanks Richard,
>>> That got me thinking and I figured out my access issue...
>>> HALELUYA!
>>> 
>>> Now I am getting an error with mysql syntax.
>>> 
>>> "SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN
>>> ".FIELDS_TABLE." cf ON cf.Order_ID = otn.Order_ID WHERE
>>> cf.Earliest_Pickup >= DATE(NOW()) AND cf.Earliest_Pickup <=
>>> DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND
>>> otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"
>>> 
>>> is giving me this error:
>>>You have an error in your SQL syntax; check the manual that
>>> corresponds to your MySQL server version for the right syntax to
>>> use near '\"Shipping\" AND otn.Order_Status != \"Completed\"' at
>>> line 1
>>> 
>>> 
>> 
>> Echo out the query, then manually run it.
>> 
> 
> Based on the quoting, it doesn't appear that the error you are
> reporting is actually based on the command syntax you are showing.
> 
> If after echoing your mysql query, so that you see all substitutions,
> you are still having trouble, then take your query apart and simplify
> it. When you get a trimmed down version to work, then add complexity.
> 
> 
> 
> 
> -- 
> 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] Corn job anomaly

[Richard]

> Date: Sunday, September 25, 2016 08:37:14 -0400
> From: Aziz Saleh <azizsa...@gmail.com>
> Subject: Re: [PHP-DB] Corn job anomaly
>
> On Sun, Sep 25, 2016 at 4:59 AM, Karl DeSaulniers
> <k...@designdrumm.com> wrote:
> 
>> > On Sep 23, 2016, at 5:38 AM, Richard wrote:
>> > 
>> > -- Try the mysql access and commands directly from the command
>> >line (using the mysql cli).
>> > 
>> > -- Put debug/print statements in your code so you can clearly
>> >see where you are getting.
>> > 
>> >-- This includes printing out your mysql statements so that
>> >   you know exactly what is being passed to mysql (both when
>> >   you run the script by hand and via cron).
>> > 
>> 
>> Thanks Richard,
>> That got me thinking and I figured out my access issue...
>> HALELUYA!
>> 
>> Now I am getting an error with mysql syntax.
>> 
>> "SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN
>> ".FIELDS_TABLE." cf ON cf.Order_ID = otn.Order_ID WHERE
>> cf.Earliest_Pickup >= DATE(NOW()) AND cf.Earliest_Pickup <=
>> DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND
>> otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"
>> 
>> is giving me this error:
>> You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to
>> use near '\"Shipping\" AND otn.Order_Status != \"Completed\"' at
>> line 1
>> 
>> 
>
> Echo out the query, then manually run it.
>

Based on the quoting, it doesn't appear that the error you are
reporting is actually based on the command syntax you are showing.

If after echoing your mysql query, so that you see all substitutions,
you are still having trouble, then take your query apart and simplify
it. When you get a trimmed down version to work, then add complexity.




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

Re: [PHP-DB] Corn job anomaly

[Aziz Saleh]

On Sun, Sep 25, 2016 at 4:59 AM, Karl DeSaulniers 
wrote:

> > On Sep 23, 2016, at 5:38 AM, Richard  innovate.net> wrote:
> >
> >
> >
> >> Date: Friday, September 23, 2016 03:28:47 -0500
> >> From: Karl DeSaulniers 
> >>
> >> Ok, so I have run down all the possible things (that I know of)
> >> that could be keeping this from working. I have checked with my
> >> hosting service that:
> >>
> >> 1. MySQL is accessible with the original user I started with (for
> >> this case were using db123456) via ssh. 2. The cron is accessing my
> >> php script fine.
> >> 3. The cron is running the script and is attempting to access MySQL
> >> but not allowing access still.
> >>
> >> I tried running the script via ssh and it is timing out. No error
> >> of not accessing MySQL, just telling me it is timing out.  This
> >> tells me (I could be wrong) that it is in fact accessing MySQL and
> >> that my calls to MySQL are making the script time out? What bothers
> >> me is that the cron errors out saying access denied but ssh says
> >> timeout error. Does anyone have any ideas for me to try at this
> >> point? If I don't get this to work in the next couple of days, I am
> >> toast!
> >>
> >
> > -- Try the mysql access and commands directly from the command
> >line (using the mysql cli).
> >
> > -- Put debug/print statements in your code so you can clearly
> >see where you are getting.
> >
> >-- This includes printing out your mysql statements so that
> >   you know exactly what is being passed to mysql (both when
> >   you run the script by hand and via cron).
> >
>
> Thanks Richard,
> That got me thinking and I figured out my access issue... HALELUYA!
>
> Now I am getting an error with mysql syntax.
>
> "SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN ".FIELDS_TABLE." cf
> ON cf.Order_ID = otn.Order_ID WHERE cf.Earliest_Pickup >= DATE(NOW()) AND
> cf.Earliest_Pickup <= DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND
> otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"
>
> is giving me this error:
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> '\"Shipping\" AND otn.Order_Status != \"Completed\"' at line 1
>
> I think at this point my brain is fried and I am not seeing what is wrong
> with this.
> Can anyone point out my folly? My guess is it is an obvious one, but I am
> not seeing it.
>
> Thanks again for your help everyone. I think I am almost there!
>
> 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
>
>
Echo out the query, then manually run it.

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

> On Sep 23, 2016, at 5:38 AM, Richard 
>  wrote:
> 
> 
> 
>> Date: Friday, September 23, 2016 03:28:47 -0500
>> From: Karl DeSaulniers 
>> 
>> Ok, so I have run down all the possible things (that I know of)
>> that could be keeping this from working. I have checked with my
>> hosting service that:
>> 
>> 1. MySQL is accessible with the original user I started with (for
>> this case were using db123456) via ssh. 2. The cron is accessing my
>> php script fine.
>> 3. The cron is running the script and is attempting to access MySQL
>> but not allowing access still.
>> 
>> I tried running the script via ssh and it is timing out. No error
>> of not accessing MySQL, just telling me it is timing out.  This
>> tells me (I could be wrong) that it is in fact accessing MySQL and
>> that my calls to MySQL are making the script time out? What bothers
>> me is that the cron errors out saying access denied but ssh says
>> timeout error. Does anyone have any ideas for me to try at this
>> point? If I don't get this to work in the next couple of days, I am
>> toast!
>> 
> 
> -- Try the mysql access and commands directly from the command
>line (using the mysql cli).
> 
> -- Put debug/print statements in your code so you can clearly
>see where you are getting. 
> 
>-- This includes printing out your mysql statements so that
>   you know exactly what is being passed to mysql (both when
>   you run the script by hand and via cron).
> 

Thanks Richard,
That got me thinking and I figured out my access issue... HALELUYA!

Now I am getting an error with mysql syntax.

"SELECT otn.*, cf.* FROM ".ORDER_TABLE." otn LEFT JOIN ".FIELDS_TABLE." cf ON 
cf.Order_ID = otn.Order_ID WHERE cf.Earliest_Pickup >= DATE(NOW()) AND 
cf.Earliest_Pickup <= DATE(NOW() + INTERVAL ".($Num_Days_Away+1)." DAY) AND 
otn.Order_Status != 'Shipping' AND otn.Order_Status != 'Completed'"

is giving me this error:
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '\"Shipping\" AND 
otn.Order_Status != \"Completed\"' at line 1

I think at this point my brain is fried and I am not seeing what is wrong with 
this. 
Can anyone point out my folly? My guess is it is an obvious one, but I am not 
seeing it.

Thanks again for your help everyone. I think I am almost there!

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] Corn job anomaly

[Richard]

> Date: Friday, September 23, 2016 03:28:47 -0500
> From: Karl DeSaulniers 
>
> Ok, so I have run down all the possible things (that I know of)
> that could be keeping this from working. I have checked with my
> hosting service that:
> 
> 1. MySQL is accessible with the original user I started with (for
> this case were using db123456) via ssh. 2. The cron is accessing my
> php script fine.
> 3. The cron is running the script and is attempting to access MySQL
> but not allowing access still.
> 
> I tried running the script via ssh and it is timing out. No error
> of not accessing MySQL, just telling me it is timing out.  This
> tells me (I could be wrong) that it is in fact accessing MySQL and
> that my calls to MySQL are making the script time out? What bothers
> me is that the cron errors out saying access denied but ssh says
> timeout error. Does anyone have any ideas for me to try at this
> point? If I don't get this to work in the next couple of days, I am
> toast!
> 

 -- Try the mysql access and commands directly from the command
line (using the mysql cli).

 -- Put debug/print statements in your code so you can clearly
see where you are getting. 

-- This includes printing out your mysql statements so that
   you know exactly what is being passed to mysql (both when
   you run the script by hand and via cron).




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

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

Ok, so I have run down all the possible things (that I know of) that could be 
keeping this from working.
I have checked with my hosting service that:

1. MySQL is accessible with the original user I started with (for this case 
were using db123456) via ssh.
2. The cron is accessing my php script fine.
3. The cron is running the script and is attempting to access MySQL but not 
allowing access still.

I tried running the script via ssh and it is timing out. No error of not 
accessing MySQL, just telling me it is timing out. 
This tells me (I could be wrong) that it is in fact accessing MySQL and that my 
calls to MySQL are making the script time out?
What bothers me is that the cron errors out saying access denied but ssh says 
timeout error.
Does anyone have any ideas for me to try at this point? If I don't get this to 
work in the next couple of days, I am toast!

TIA,

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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 10:09 PM, Peter Beckman  wrote:
> 
> I suspect you are taking the pipe or semicolon literally.
> 
> Drop the pipe and semicolon entirely. This should work:
> 
>/usr/bin/php5 -c /home/123456/etc/php.ini -f 
> /home/123456/data/auto_reminder.php
> 
> The "pipe" (|) in unix (and I assume this is unix) means to pipe the output
> of php5 -c config to the command "-f /home...php". Which (-f) isn't a command.
> 
> The "semicolon" (;) in unix means "do this command first; then do this 
> command"
> so in your second part it is running /home/123456/data/auto_reminder.php 
> -udb123456  after it runs php5.
> 
> Usually php5 -c php.ini will run PHP in interactive mode, and sits and
> waits for input. When run from cron, there is no input, so it dies, unable
> to pipe the output (there is none) or just run the next command.
> 
> So -- lose the pipe, lose the semicolon, just run the command above in cron
> (and on the command line) and it should "just work."
> 
> Beckman
> 
> 


Hey thanks Peter for clearing that up. Yes, I was taking the pipe and semicolon 
literally.
I have since found this out on my own that my original code works. It is as 
Richard and Bert had said.
My cron runs under the server admin user and so I have to contact my client to 
find out what that password is or change it.

Much thanks to everyone for your time and responses with my issue. You guys are 
invaluable!

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] Corn job anomaly

[Peter Beckman]

I suspect you are taking the pipe or semicolon literally.

Drop the pipe and semicolon entirely. This should work:

/usr/bin/php5 -c /home/123456/etc/php.ini -f 
/home/123456/data/auto_reminder.php

The "pipe" (|) in unix (and I assume this is unix) means to pipe the output
of php5 -c config to the command "-f /home...php". Which (-f) isn't a command.

The "semicolon" (;) in unix means "do this command first; then do this command"
so in your second part it is running 
/home/123456/data/auto_reminder.php -udb123456  after it runs php5.


Usually php5 -c php.ini will run PHP in interactive mode, and sits and
waits for input. When run from cron, there is no input, so it dies, unable
to pipe the output (there is none) or just run the next command.

So -- lose the pipe, lose the semicolon, just run the command above in cron
(and on the command line) and it should "just work."

Beckman

On Tue, 20 Sep 2016, Karl DeSaulniers wrote:


Hi Richard & Bert,
Thanks for the responses. I think I understand what you are both saying.

Not sure where my database is housed as I am using a hosting provider 
(Mediatemple)
and they allow limited access to such things or I am unaware how to access them.

For my current issue, lets just say my username is  db123456
db123456 is the root user who has access to all databases
However I am not sure if the cron job runs under this user.

This is the command line I have currently that is not working

/usr/bin/php5 -c /home/123456/etc/php.ini | -f 
/home/123456/data/auto_reminder.php

My hosting company says I can force my php.ini with -c |
but this errors out saying -f command can not be found.

I tried this:

/usr/bin/php5 -c /home/123456/etc/php.ini | /home/123456/data/auto_reminder.php

but then I get the same error I originally had saying php doesn't exist.
I tried a semicolon like I had seen on some message boards and the double &&
but still no dice.

I even tried to set the user in the args like so

/usr/bin/php5 -c /home/123456/etc/php.ini ; /home/123456/data/auto_reminder.php 
-udb123456

again, no dice.

@Bert
I think it may be that the cron is under another user and that my original CL 
works, just not sure how to find out.
I would call my hosting provider to ask, but I always get the same response ...

We don't troubleshoot peoples code it is out of our scope of support.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com





On Sep 20, 2016, at 1:07 PM, Richard  
wrote:



 Original Message 

Date: Tuesday, September 20, 2016 12:54:38 -0500
From: Karl DeSaulniers 

On Sep 20, 2016, at 7:21 AM, Richard
 wrote:





Date: Tuesday, September 20, 2016 03:26:39 -0500
From: Karl DeSaulniers 


On Sep 20, 2016, at 3:22 AM, Lester Caine 
wrote:


On 20/09/16 09:14, Karl DeSaulniers wrote:
Pardon my ignorance, but what do you mean full path?

Full path to php application.
Cron jobs run as 'root' and so need and user account settings
added manually if they do not match the 'root' environment.



OH, ok, I did read something about root earlier.
I think it was something like:

root /usr/bin/php5 /home/(directory name
removed)/auto_reminder.php

But not positive. I will have to research that more.
Thank you.




Whether cron jobs run as root or a standard user depends on how
they are set up. If set up under a user -- which is the better
approach for general use -- they will run as that user. Really only
system-type cron jobs (that require root access) should be set up
to run as root.

If you use the command:

 crontab -e

from a user account, then the crontab is set up as that user. The
first 5 fields/values are time/date related, followed by the
command to be invoked. See: man -s5 crontab  for more details.

In that case, the user that mysql sees is the user that is running
the job. So, if your mysql authentication is set to accept
connections/give access to say the user your web server is running
as (which is how php will show by default), then you will either
need to pass those credentials in your cron job php script or,
give the user that's running the cron job access.



Again, thank you for the response Richard.

Would you possibly have a link to how to set that up? I mean the
actual commands. My trouble is I don't know what commands and how
to write them. Like including a user, forcing the PHP.ini and
executing the script. All in one line. That is what I am trying to
accomplish and not sure how. Need to read up on the proper way to
do this.



The php.ini that is used will be based on the php rules for the
path(s) (and order) it searches for that file.

The user that is running the script will be the user that the cron
job is set up under. If you set up a cron job as a standard user you
can't assign it to run as a different user. If the issue is mysql
access, you do that just the way you would in a 

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

Hi Richard & Bert,
Thanks for the responses. I think I understand what you are both saying. 

Not sure where my database is housed as I am using a hosting provider 
(Mediatemple)
and they allow limited access to such things or I am unaware how to access them.

For my current issue, lets just say my username is  db123456
db123456 is the root user who has access to all databases
However I am not sure if the cron job runs under this user.

This is the command line I have currently that is not working

/usr/bin/php5 -c /home/123456/etc/php.ini | -f 
/home/123456/data/auto_reminder.php

My hosting company says I can force my php.ini with -c |
but this errors out saying -f command can not be found.

I tried this:

/usr/bin/php5 -c /home/123456/etc/php.ini | /home/123456/data/auto_reminder.php

but then I get the same error I originally had saying php doesn't exist.
I tried a semicolon like I had seen on some message boards and the double &&
but still no dice.

I even tried to set the user in the args like so

/usr/bin/php5 -c /home/123456/etc/php.ini ; /home/123456/data/auto_reminder.php 
-udb123456

again, no dice.

@Bert  
I think it may be that the cron is under another user and that my original CL 
works, just not sure how to find out.
I would call my hosting provider to ask, but I always get the same response ... 

We don't troubleshoot peoples code it is out of our scope of support.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Sep 20, 2016, at 1:07 PM, Richard 
>  wrote:
> 
> 
> 
>  Original Message 
>> Date: Tuesday, September 20, 2016 12:54:38 -0500
>> From: Karl DeSaulniers 
>> 
>> On Sep 20, 2016, at 7:21 AM, Richard
>>  wrote:
>>> 
>>> 
>>> 
 Date: Tuesday, September 20, 2016 03:26:39 -0500
 From: Karl DeSaulniers 
 
> On Sep 20, 2016, at 3:22 AM, Lester Caine 
> wrote:
> 
>> On 20/09/16 09:14, Karl DeSaulniers wrote:
>> Pardon my ignorance, but what do you mean full path?
> Full path to php application.
> Cron jobs run as 'root' and so need and user account settings
> added manually if they do not match the 'root' environment.
> 
 
 OH, ok, I did read something about root earlier.
 I think it was something like:
 
 root /usr/bin/php5 /home/(directory name
 removed)/auto_reminder.php
 
 But not positive. I will have to research that more.
 Thank you.
 
 
>>> 
>>> Whether cron jobs run as root or a standard user depends on how
>>> they are set up. If set up under a user -- which is the better
>>> approach for general use -- they will run as that user. Really only
>>> system-type cron jobs (that require root access) should be set up
>>> to run as root.
>>> 
>>> If you use the command: 
>>> 
>>>  crontab -e
>>> 
>>> from a user account, then the crontab is set up as that user. The
>>> first 5 fields/values are time/date related, followed by the
>>> command to be invoked. See: man -s5 crontab  for more details.
>>> 
>>> In that case, the user that mysql sees is the user that is running
>>> the job. So, if your mysql authentication is set to accept
>>> connections/give access to say the user your web server is running
>>> as (which is how php will show by default), then you will either
>>> need to pass those credentials in your cron job php script or,
>>> give the user that's running the cron job access.
>>> 
>> 
>> Again, thank you for the response Richard.
>> 
>> Would you possibly have a link to how to set that up? I mean the
>> actual commands. My trouble is I don't know what commands and how
>> to write them. Like including a user, forcing the PHP.ini and
>> executing the script. All in one line. That is what I am trying to
>> accomplish and not sure how. Need to read up on the proper way to
>> do this. 
>> 
> 
> The php.ini that is used will be based on the php rules for the
> path(s) (and order) it searches for that file.
> 
> The user that is running the script will be the user that the cron
> job is set up under. If you set up a cron job as a standard user you
> can't assign it to run as a different user. If the issue is mysql
> access, you do that just the way you would in a [web]server parsed
> php file.
> 
> The script is whatever you put on the crontab line.
> 
> The script you reference from the crontab entry can be a shell script
> that does some setup, and then calls your php script.  Your php
> script can have include files (e.g., for mysql connection setup) just
> as you might have in a [web]server parsed php file.
> 
> 
> 
> 
> 
> -- 
> 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] Corn job anomaly

[B. Aerts]

On 20/09/16 10:06, Lester Caine wrote:

On 20/09/16 06:16, Karl DeSaulniers wrote:

Was probably a newb question, however, now it is saying that my database user 
is not allowed access.
I have a mysql connection inside my script that reads the database to get user 
email addresses to send a reminder email to.

Is there supposed to be a call or directive to load mysql in my command line as 
well?
Man this is frustrating.


The user name and password you are using from the web scripts SHOULD
work in the script you are using on the cron job. Although mysql does
seem to have a few extra security features that cut in. Are they listed
in the script, or loaded from the environment ... which is of cause
different for the cron jobs ... and why you needed the full path.



Karl,

is your MySQL database located on the machine itsself where you run the 
Cron job ?
If so, does your Cron user have read/write/execute permissions on either 
the DB directory, and the DB itsself ?


Regards, Bert

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

Re: [PHP-DB] Corn job anomaly

[Richard]

 Original Message 
> Date: Tuesday, September 20, 2016 12:54:38 -0500
> From: Karl DeSaulniers 
>
> On Sep 20, 2016, at 7:21 AM, Richard
>  wrote:
>> 
>> 
>> 
>>> Date: Tuesday, September 20, 2016 03:26:39 -0500
>>> From: Karl DeSaulniers 
>>> 
 On Sep 20, 2016, at 3:22 AM, Lester Caine 
 wrote:
 
> On 20/09/16 09:14, Karl DeSaulniers wrote:
> Pardon my ignorance, but what do you mean full path?
 Full path to php application.
 Cron jobs run as 'root' and so need and user account settings
 added manually if they do not match the 'root' environment.
 
>>> 
>>> OH, ok, I did read something about root earlier.
>>> I think it was something like:
>>> 
>>> root /usr/bin/php5 /home/(directory name
>>> removed)/auto_reminder.php
>>> 
>>> But not positive. I will have to research that more.
>>> Thank you.
>>> 
>>> 
>> 
>> Whether cron jobs run as root or a standard user depends on how
>> they are set up. If set up under a user -- which is the better
>> approach for general use -- they will run as that user. Really only
>> system-type cron jobs (that require root access) should be set up
>> to run as root.
>> 
>> If you use the command: 
>> 
>>   crontab -e
>> 
>> from a user account, then the crontab is set up as that user. The
>> first 5 fields/values are time/date related, followed by the
>> command to be invoked. See: man -s5 crontab  for more details.
>> 
>> In that case, the user that mysql sees is the user that is running
>> the job. So, if your mysql authentication is set to accept
>> connections/give access to say the user your web server is running
>> as (which is how php will show by default), then you will either
>> need to pass those credentials in your cron job php script or,
>> give the user that's running the cron job access.
>> 
> 
> Again, thank you for the response Richard.
> 
> Would you possibly have a link to how to set that up? I mean the
> actual commands. My trouble is I don't know what commands and how
> to write them. Like including a user, forcing the PHP.ini and
> executing the script. All in one line. That is what I am trying to
> accomplish and not sure how. Need to read up on the proper way to
> do this. 
> 

The php.ini that is used will be based on the php rules for the
path(s) (and order) it searches for that file.

The user that is running the script will be the user that the cron
job is set up under. If you set up a cron job as a standard user you
can't assign it to run as a different user. If the issue is mysql
access, you do that just the way you would in a [web]server parsed
php file.

The script is whatever you put on the crontab line.

The script you reference from the crontab entry can be a shell script
that does some setup, and then calls your php script.  Your php
script can have include files (e.g., for mysql connection setup) just
as you might have in a [web]server parsed php file.





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

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

On Sep 20, 2016, at 7:21 AM, Richard  
wrote:
> 
> 
> 
>> Date: Tuesday, September 20, 2016 03:26:39 -0500
>> From: Karl DeSaulniers 
>> 
>>> On Sep 20, 2016, at 3:22 AM, Lester Caine 
>>> wrote:
>>> 
 On 20/09/16 09:14, Karl DeSaulniers wrote:
 Pardon my ignorance, but what do you mean full path?
>>> Full path to php application.
>>> Cron jobs run as 'root' and so need and user account settings added
>>> manually if they do not match the 'root' environment.
>>> 
>> 
>> OH, ok, I did read something about root earlier.
>> I think it was something like:
>> 
>> root /usr/bin/php5 /home/(directory name removed)/auto_reminder.php
>> 
>> But not positive. I will have to research that more.
>> Thank you.
>> 
>> 
> 
> Whether cron jobs run as root or a standard user depends on how they
> are set up. If set up under a user -- which is the better approach
> for general use -- they will run as that user. Really only
> system-type cron jobs (that require root access) should be set up to
> run as root.
> 
> If you use the command: 
> 
>   crontab -e
> 
> from a user account, then the crontab is set up as that user. The
> first 5 fields/values are time/date related, followed by the command
> to be invoked. See: man -s5 crontab  for more details.
> 
> In that case, the user that mysql sees is the user that is running
> the job. So, if your mysql authentication is set to accept
> connections/give access to say the user your web server is running as
> (which is how php will show by default), then you will either need to
> pass those credentials in your cron job php script or, give the user
> that's running the cron job access.
> 

Again, thank you for the response Richard.

Would you possibly have a link to how to set that up? I mean the actual 
commands. My trouble is I don't know what commands and how to write them. Like 
including a user, forcing the PHP.ini and executing the script. All in one 
line. That is what I am trying to accomplish and not sure how. Need to read up 
on the proper way to do this. 

TIA,

Best,
Karl

Re: [PHP-DB] Corn job anomaly

[Richard]

> Date: Tuesday, September 20, 2016 03:26:39 -0500
> From: Karl DeSaulniers 
>
>> On Sep 20, 2016, at 3:22 AM, Lester Caine 
>> wrote:
>> 
>> On 20/09/16 09:14, Karl DeSaulniers wrote:
>>> Pardon my ignorance, but what do you mean full path?
>> Full path to php application.
>> Cron jobs run as 'root' and so need and user account settings added
>> manually if they do not match the 'root' environment.
>> 
> 
> OH, ok, I did read something about root earlier.
> I think it was something like:
> 
> root /usr/bin/php5 /home/(directory name removed)/auto_reminder.php
> 
> But not positive. I will have to research that more.
> Thank you.
> 
>

Whether cron jobs run as root or a standard user depends on how they
are set up. If set up under a user -- which is the better approach
for general use -- they will run as that user. Really only
system-type cron jobs (that require root access) should be set up to
run as root.

If you use the command: 

   crontab -e

from a user account, then the crontab is set up as that user. The
first 5 fields/values are time/date related, followed by the command
to be invoked. See: man -s5 crontab  for more details.

In that case, the user that mysql sees is the user that is running
the job. So, if your mysql authentication is set to accept
connections/give access to say the user your web server is running as
(which is how php will show by default), then you will either need to
pass those credentials in your cron job php script or, give the user
that's running the cron job access.



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

Re: [PHP-DB] Corn job anomaly

[Lester Caine]

On 20/09/16 09:19, Karl DeSaulniers wrote:
> The command line I am using is as follows.
> 
> /usr/bin/php5 /home/(directory name removed)/auto_reminder.php

Which runs when entered on YOUR command line, but fails in the cron job?
Something in auto_reminder.php needs replacing with the 'full' text as
it is getting information from the local user settings.

> The time stamps (minute, hour, day, etc) that precede the /usr are loaded by 
> my host. just fyi.

-- 
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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 3:22 AM, Lester Caine  wrote:
> 
> On 20/09/16 09:14, Karl DeSaulniers wrote:
>> Pardon my ignorance, but what do you mean full path?
> Full path to php application.
> Cron jobs run as 'root' and so need and user account settings added
> manually if they do not match the 'root' environment.
> 
> -- 
> Lester Caine - G8HFL
> -


OH, ok, I did read something about root earlier.
I think it was something like:

root /usr/bin/php5 /home/(directory name removed)/auto_reminder.php

But not positive. I will have to research that more.
Thank you.

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] Cron job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 3:20 AM, Lester Caine  wrote:
> 
> On 20/09/16 09:10, Karl DeSaulniers wrote:
>> Hi Lester,
>> They are listed inside the php file.
>> 
>> The error message I get is:
>> 
>> Access denied for user '(user name removed)'@'%' to database '(database name 
>> removed)'
>> 
>> Thanks for your response.
> 
> Need a mysql guy to deal with that ;)
> I have seen it myself when trying to use mysql and it's to do with the
> 'network path' but I'm on firebird which simply uses the
> '(user name removed)'
> 
> -- 
> Lester Caine - G8HFL


G, ok, thanks for the input. (: ))

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] Corn job anomaly

[Lester Caine]

On 20/09/16 09:14, Karl DeSaulniers wrote:
> Pardon my ignorance, but what do you mean full path?
Full path to php application.
Cron jobs run as 'root' and so need and user account settings added
manually if they do not match the 'root' environment.

-- 
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] Cron job anomaly

[Lester Caine]

On 20/09/16 09:10, Karl DeSaulniers wrote:
> Hi Lester,
> They are listed inside the php file.
> 
> The error message I get is:
> 
> Access denied for user '(user name removed)'@'%' to database '(database name 
> removed)'
> 
> Thanks for your response.

Need a mysql guy to deal with that ;)
I have seen it myself when trying to use mysql and it's to do with the
'network path' but I'm on firebird which simply uses the
'(user name removed)'

-- 
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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 3:14 AM, Karl DeSaulniers  wrote:
> 
>> On Sep 20, 2016, at 3:06 AM, Lester Caine  wrote:
>> 
>> On 20/09/16 06:16, Karl DeSaulniers wrote:
>>> Was probably a newb question, however, now it is saying that my database 
>>> user is not allowed access.
>>> I have a mysql connection inside my script that reads the database to get 
>>> user email addresses to send a reminder email to.
>>> 
>>> Is there supposed to be a call or directive to load mysql in my command 
>>> line as well?
>>> Man this is frustrating.
>> 
>> The user name and password you are using from the web scripts SHOULD
>> work in the script you are using on the cron job. Although mysql does
>> seem to have a few extra security features that cut in. Are they listed
>> in the script, or loaded from the environment ... which is of cause
>> different for the cron jobs ... and why you needed the full path.
>> 
>> -- 
>> Lester Caine - G8HFL
>> -
> 
> 
> Pardon my ignorance, but what do you mean full path?
> Full path of the host name?
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com



The command line I am using is as follows.

/usr/bin/php5 /home/(directory name removed)/auto_reminder.php

The time stamps (minute, hour, day, etc) that precede the /usr are loaded by my 
host. just fyi.

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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 3:06 AM, Lester Caine  wrote:
> 
> On 20/09/16 06:16, Karl DeSaulniers wrote:
>> Was probably a newb question, however, now it is saying that my database 
>> user is not allowed access.
>> I have a mysql connection inside my script that reads the database to get 
>> user email addresses to send a reminder email to.
>> 
>> Is there supposed to be a call or directive to load mysql in my command line 
>> as well?
>> Man this is frustrating.
> 
> The user name and password you are using from the web scripts SHOULD
> work in the script you are using on the cron job. Although mysql does
> seem to have a few extra security features that cut in. Are they listed
> in the script, or loaded from the environment ... which is of cause
> different for the cron jobs ... and why you needed the full path.
> 
> -- 
> Lester Caine - G8HFL
> -


Pardon my ignorance, but what do you mean full path?
Full path of the host name?

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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 3:06 AM, Lester Caine  wrote:
> 
> On 20/09/16 06:16, Karl DeSaulniers wrote:
>> Was probably a newb question, however, now it is saying that my database 
>> user is not allowed access.
>> I have a mysql connection inside my script that reads the database to get 
>> user email addresses to send a reminder email to.
>> 
>> Is there supposed to be a call or directive to load mysql in my command line 
>> as well?
>> Man this is frustrating.
> 
> The user name and password you are using from the web scripts SHOULD
> work in the script you are using on the cron job. Although mysql does
> seem to have a few extra security features that cut in. Are they listed
> in the script, or loaded from the environment ... which is of cause
> different for the cron jobs ... and why you needed the full path.
> 
> -- 
> Lester Caine - G8HFL
> -

Hi Lester,
They are listed inside the php file.

The error message I get is:

Access denied for user '(user name removed)'@'%' to database '(database name 
removed)'

Thanks for your response.

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] Corn job anomaly

[Lester Caine]

On 20/09/16 06:16, Karl DeSaulniers wrote:
> Was probably a newb question, however, now it is saying that my database user 
> is not allowed access.
> I have a mysql connection inside my script that reads the database to get 
> user email addresses to send a reminder email to.
> 
> Is there supposed to be a call or directive to load mysql in my command line 
> as well?
> Man this is frustrating.

The user name and password you are using from the web scripts SHOULD
work in the script you are using on the cron job. Although mysql does
seem to have a few extra security features that cut in. Are they listed
in the script, or loaded from the environment ... which is of cause
different for the cron jobs ... and why you needed the full path.

-- 
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] Corn job anomaly

[Karl DeSaulniers]

> On Sep 20, 2016, at 12:16 AM, Karl DeSaulniers  wrote:
> 
>> On Sep 19, 2016, at 6:14 AM, Richard 
>>  wrote:
>> 
>> 
>> 
>>> Date: Monday, September 19, 2016 03:26:54 -0500
>>> From: Karl DeSaulniers 
>>> 
>>> I am hoping someone can enlighten me on this issue I am having.
>>> I am trying to set up a cron job to run a php file.
>>> I am getting a strange error though.
>>> 
>>> /home/(protected directory)/auto_reminder.php: line 1: ?php: No
>>> such file or directory /home/(protected
>>> directory)/auto_reminder.php: line 2: =: command not found
>>> /home/(protected directory)/auto_reminder.php: line 3: /*WordPress:
>>> No such file or directory
>>> 
>>> This is lines 1, 2 and 3 from my code.
>>> 
>>> >> $error_log = "";
>>> /*WordPress Optons Table*/
>>> 
>>> Am I correct in thinking that the error is saying that php doesn't
>>> exist? Has anyone ever experienced this? Is this a server related
>>> issue? Any clue on how to fix?
>> 
>> What do you have on the "command-line" part of your crontab entry?
>> Your php script will need to be called as a php cgi or cli. Because
>> of the way things get set up via cron, it's best to include the full
>> path to the php binary on the cron command-line.
>> 
> 
> Hey thanks Richard. That was the problem.
> Sorry, I am new to command line and cron jobs. 
> Was probably a newb question, however, now it is saying that my database user 
> is not allowed access.
> I have a mysql connection inside my script that reads the database to get 
> user email addresses to send a reminder email to.
> 
> Is there supposed to be a call or directive to load mysql in my command line 
> as well?
> Man this is frustrating.
> 
> Much thanks for your input!
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 

Heh, I just noticed the subject line. Got to love auto-correct!
I feel like a corn job at this point...  (:  /)

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] MySQL two tables and an uneven number of rows

[Karl DeSaulniers]

Thanks Bert,
Sorry for late response, but I had to step away from this for a moment to work 
on other things.
Will most likely be back though as things are not working still.

Thank you for your responses.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Sep 13, 2016, at 2:15 AM, B. Aerts  wrote:
> 
> On 13/09/16 08:42, Karl DeSaulniers wrote:
>>> On Sep 12, 2016, at 2:53 PM, B. Aerts  wrote:
>>> 
>>> On 12/09/16 05:24, Karl DeSaulniers wrote:
 Hello All,
 Hoping you can help clear my head on this. I have two MySQL tables for 
 custom fields data to be stored.
 
custom_fields   custom_fields_meta
 
 custom_fields is the info for the actual field displayed in the html and 
 custom_fields_meta is the data stored from entering a value on said field 
 in the form.
 
 Custom fields can be added and removed at will by the user and so when for 
 instance, adding a field,
 it currently creates an uneven number of rows in the custom_fields_meta if 
 there were any entries with fields create prior to this new one.
 
 Currently I have this code:
 
 $SQL = "SELECT ft.*, mt.Meta_Value
FROM `CUSTOM_FIELDS` ft
LEFT JOIN `CUSTOM_FIELDS_META` mt
ON mt.Field_ID = ft.Field_ID
WHERE mt.Order_ID=%d
ORDER BY ft.Field_ID ASC";
 
 I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
 If I manually put in the missing rows in the meta table, left join works.
 However, manually updating prior entries is not going to happen.
 
 So my question is how do I get all the table rows in both tables even if 
 there is not a row to match on the meta table?
 or
 How would I update the prior entries to include this new field in the meta 
 table and keep things orderly?
 The meta is stored per order id and so there is groups of meta data per 
 order id. I would like to avoid scattered data.
 Is there a way to push the index down to fit them in or is this just going 
 to be too costly on server resources?
 
 TIA,
 
 Best,
 
 Karl DeSaulniers
 Design Drumm
 http://designdrumm.com 
 
 
 
 
 
>>> Hi Karl,
>>> 
>>> I can't really follow your problem ... Any chance to post 2 dummy table 
>>> layouts to show what you want, and what you get ?
>>> 
>>> And it isn't something you could solve with a UNION ?
>>> 
>>> 
>> 
>> 
>> Hello,
>> Thanks fro your reply. I can try. :)
>> 
>> BEFORE:
>> 
>> CUSTOM_FIELDS:
>> 
>> `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
>> `Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
>> `Field_Required`, `Field_Date_Created`
>> ||
>> (1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
>> 'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
>> (2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
>> 'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
>> (3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
>> 'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12')
>> 
>> CUSTOM_FIELDS_META:
>> 
>> `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
>> |—|
>> (1, 1, 1003, '555-123-4567'),
>> (2, 2, 1003, ''),
>> (3, 3, 1003, '')
>> 
>> 
>> Then lets say the user wants to add a cell phone field.
>> 
>> AFTER:
>> 
>> CUSTOM_FIELDS:
>> 
>> `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
>> `Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
>> `Field_Required`, `Field_Date_Created`
>> ||
>> (1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
>> 'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
>> (2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
>> 'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
>> (3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
>> 'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12'),
>> (4, 'Pickup Info', 'Cell', 'Origin_Cell', 'origin-cell', 'phone', 'Pickup 
>> cell phone number', '', 'Yes', 'No', '2015-04-19 08:46:13')
>> 
>> CUSTOM_FIELDS_META:
>> 
>> `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
>> |—|
>> (1, 1, 1003, '555-123-4567'),
>> (2, 2, 1003, ''),
>> (3, 3, 1003, '')
>> 
>> The 4th field id is not in the meta table. So when I read out what is in the 
>> custom fields and matching meta data, cell phone does not show up for order 
>> that were processed before adding the custom field cell 

Re: [PHP-DB] Corn job anomaly

[Karl DeSaulniers]

> On Sep 19, 2016, at 6:14 AM, Richard 
>  wrote:
> 
> 
> 
>> Date: Monday, September 19, 2016 03:26:54 -0500
>> From: Karl DeSaulniers 
>> 
>> I am hoping someone can enlighten me on this issue I am having.
>> I am trying to set up a cron job to run a php file.
>> I am getting a strange error though.
>> 
>> /home/(protected directory)/auto_reminder.php: line 1: ?php: No
>> such file or directory /home/(protected
>> directory)/auto_reminder.php: line 2: =: command not found
>> /home/(protected directory)/auto_reminder.php: line 3: /*WordPress:
>> No such file or directory
>> 
>> This is lines 1, 2 and 3 from my code.
>> 
>> > $error_log = "";
>> /*WordPress Optons Table*/
>> 
>> Am I correct in thinking that the error is saying that php doesn't
>> exist? Has anyone ever experienced this? Is this a server related
>> issue? Any clue on how to fix?
> 
> What do you have on the "command-line" part of your crontab entry?
> Your php script will need to be called as a php cgi or cli. Because
> of the way things get set up via cron, it's best to include the full
> path to the php binary on the cron command-line.
> 

Hey thanks Richard. That was the problem.
Sorry, I am new to command line and cron jobs. 
Was probably a newb question, however, now it is saying that my database user 
is not allowed access.
I have a mysql connection inside my script that reads the database to get user 
email addresses to send a reminder email to.

Is there supposed to be a call or directive to load mysql in my command line as 
well?
Man this is frustrating.

Much thanks for your input!

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] Corn job anomaly

[Richard]

> Date: Monday, September 19, 2016 03:26:54 -0500
> From: Karl DeSaulniers 
>
> I am hoping someone can enlighten me on this issue I am having.
> I am trying to set up a cron job to run a php file.
> I am getting a strange error though.
> 
> /home/(protected directory)/auto_reminder.php: line 1: ?php: No
> such file or directory /home/(protected
> directory)/auto_reminder.php: line 2: =: command not found
> /home/(protected directory)/auto_reminder.php: line 3: /*WordPress:
> No such file or directory
> 
> This is lines 1, 2 and 3 from my code.
> 
>  $error_log = "";
> /*WordPress Optons Table*/
> 
> Am I correct in thinking that the error is saying that php doesn't
> exist? Has anyone ever experienced this? Is this a server related
> issue? Any clue on how to fix?

What do you have on the "command-line" part of your crontab entry?
Your php script will need to be called as a php cgi or cli. Because
of the way things get set up via cron, it's best to include the full
path to the php binary on the cron command-line.

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

Re: [PHP-DB] MySQL two tables and an uneven number of rows

[B. Aerts]

On 13/09/16 08:42, Karl DeSaulniers wrote:

On Sep 12, 2016, at 2:53 PM, B. Aerts  wrote:

On 12/09/16 05:24, Karl DeSaulniers wrote:

Hello All,
Hoping you can help clear my head on this. I have two MySQL tables for custom 
fields data to be stored.

custom_fields   custom_fields_meta

custom_fields is the info for the actual field displayed in the html and 
custom_fields_meta is the data stored from entering a value on said field in 
the form.

Custom fields can be added and removed at will by the user and so when for 
instance, adding a field,
it currently creates an uneven number of rows in the custom_fields_meta if 
there were any entries with fields create prior to this new one.

Currently I have this code:

$SQL = "SELECT ft.*, mt.Meta_Value
FROM `CUSTOM_FIELDS` ft
LEFT JOIN `CUSTOM_FIELDS_META` mt
ON mt.Field_ID = ft.Field_ID
WHERE mt.Order_ID=%d
ORDER BY ft.Field_ID ASC";

I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
If I manually put in the missing rows in the meta table, left join works.
However, manually updating prior entries is not going to happen.

So my question is how do I get all the table rows in both tables even if there 
is not a row to match on the meta table?
or
How would I update the prior entries to include this new field in the meta 
table and keep things orderly?
The meta is stored per order id and so there is groups of meta data per order 
id. I would like to avoid scattered data.
Is there a way to push the index down to fit them in or is this just going to 
be too costly on server resources?

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 






Hi Karl,

I can't really follow your problem ... Any chance to post 2 dummy table layouts 
to show what you want, and what you get ?

And it isn't something you could solve with a UNION ?





Hello,
Thanks fro your reply. I can try. :)

BEFORE:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')


Then lets say the user wants to add a cell phone field.

AFTER:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12'),
(4, 'Pickup Info', 'Cell', 'Origin_Cell', 'origin-cell', 'phone', 'Pickup cell 
phone number', '', 'Yes', 'No', '2015-04-19 08:46:13')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')

The 4th field id is not in the meta table. So when I read out what is in the 
custom fields and matching meta data, cell phone does not show up for order 
that were processed before adding the custom field cell phone. I am trying to 
show the cell phone field on old orders as well even if there is not a row 
representing data in the meta table.

Hope that clears it up and not mud it up.. :P

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



Hi Karl,

indeed this should work with a left JOIN. Did a quick test in SQLite, 
and got this to work (just to indicate that your principle is correct) :


sqlite> select * from table1 ;
1|100
2|200
4|400
sqlite> select * from table2 ;
1|1000
2|2000
3|3000
sqlite> select * from table1 join table2 on table1.ID = table2.ID ;
1|100|1|1000
2|200|2|2000
sqlite> select * from table1 left join table2 on table1.ID = table2.ID ;
1|100|1|1000
2|200|2|2000
4|400||
sqlite> select * from table1 right join table2 on table1.ID = table2.ID;

Re: [PHP-DB] MySQL two tables and an uneven number of rows

[Karl DeSaulniers]

> On Sep 12, 2016, at 2:53 PM, B. Aerts  wrote:
> 
> On 12/09/16 05:24, Karl DeSaulniers wrote:
>> Hello All,
>> Hoping you can help clear my head on this. I have two MySQL tables for 
>> custom fields data to be stored.
>> 
>>  custom_fields   custom_fields_meta
>> 
>> custom_fields is the info for the actual field displayed in the html and 
>> custom_fields_meta is the data stored from entering a value on said field in 
>> the form.
>> 
>> Custom fields can be added and removed at will by the user and so when for 
>> instance, adding a field,
>> it currently creates an uneven number of rows in the custom_fields_meta if 
>> there were any entries with fields create prior to this new one.
>> 
>> Currently I have this code:
>> 
>> $SQL = "SELECT ft.*, mt.Meta_Value
>>  FROM `CUSTOM_FIELDS` ft
>>  LEFT JOIN `CUSTOM_FIELDS_META` mt
>>  ON mt.Field_ID = ft.Field_ID
>>  WHERE mt.Order_ID=%d
>>  ORDER BY ft.Field_ID ASC";
>> 
>> I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
>> If I manually put in the missing rows in the meta table, left join works.
>> However, manually updating prior entries is not going to happen.
>> 
>> So my question is how do I get all the table rows in both tables even if 
>> there is not a row to match on the meta table?
>> or
>> How would I update the prior entries to include this new field in the meta 
>> table and keep things orderly?
>> The meta is stored per order id and so there is groups of meta data per 
>> order id. I would like to avoid scattered data.
>> Is there a way to push the index down to fit them in or is this just going 
>> to be too costly on server resources?
>> 
>> TIA,
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com 
>> 
>> 
>> 
>> 
>> 
> Hi Karl,
> 
> I can't really follow your problem ... Any chance to post 2 dummy table 
> layouts to show what you want, and what you get ?
> 
> And it isn't something you could solve with a UNION ?
> 
> 


Hello,
Thanks fro your reply. I can try. :)

BEFORE:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')


Then lets say the user wants to add a cell phone field.

AFTER:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12'),
(4, 'Pickup Info', 'Cell', 'Origin_Cell', 'origin-cell', 'phone', 'Pickup cell 
phone number', '', 'Yes', 'No', '2015-04-19 08:46:13')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')

The 4th field id is not in the meta table. So when I read out what is in the 
custom fields and matching meta data, cell phone does not show up for order 
that were processed before adding the custom field cell phone. I am trying to 
show the cell phone field on old orders as well even if there is not a row 
representing data in the meta table.

Hope that clears it up and not mud it up.. :P

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] Any method to get primary key matching a given value ?

[B. Aerts]

Hi Ratin,

working with sqlite, are you ?

In that case, take a look at the default table :

SELECT sql from sqlite_master where type= "table" and name = 
""


This query returns the creation query of the table concerned.
By parsing it textually, you can find out the field name that was 
declared PRIMARY (key)



On 19/08/16 01:51, Ratin wrote:

Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is not
available for sqlite database. I had to built up the whole query with php
using PRAGMA table_info(tablename), looking at the pk entry, when its 1,
get the column name, and then update the sql statement based on that. A bit
of work, wouldve been much simpler if a method was provided, but oh well ..

Thanks again

Ratin

On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers 
wrote:


Hi Ratin,
Going to take a stab at this one.
Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
Might be where you want to look for what you are trying.
Sorry can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com





On Aug 18, 2016, at 1:27 PM, Ratin  wrote:

I'm writing the generic get that works on different tables having

different

primary keys but the argument of get is always the primary key , i.e. get
request is -

get (column name, value)

the value is always the primary key value.

It looks like it would be a pretty standard method but I cant find a

method

like that. Anybody have any clue?

Thanks

Ratin



--
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] Any method to get primary key matching a given value ?

[Karl DeSaulniers]

This may also shed some light for you.
The accepted answer and possibly the one below it if you are on .NET

http://stackoverflow.com/questions/763516/information-schema-columns-on-sqlite

HTH,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers  wrote:
> 
> Hey Ratin,
> Have you looked into the table column named 'pk' inside table_info?
> That is where a column is indicated to be a primary key or not.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com 
> 
> 
> 
> 
>> On Aug 18, 2016, at 6:51 PM, Ratin  wrote:
>> 
>> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is not 
>> available for sqlite database. I had to built up the whole query with php 
>> using PRAGMA table_info(tablename), looking at the pk entry, when its 1, get 
>> the column name, and then update the sql statement based on that. A bit of 
>> work, wouldve been much simpler if a method was provided, but oh well ..
>> 
>> Thanks again
>> 
>> Ratin 
>> 
>> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers > > wrote:
>> Hi Ratin,
>> Going to take a stab at this one.
>> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
>> Might be where you want to look for what you are trying.
>> Sorry can't help more.
>> 
>> Best,
>> 
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com 
>> 
>> 
>> 
>> 
>>> On Aug 18, 2016, at 1:27 PM, Ratin >> > wrote:
>>> 
>>> I'm writing the generic get that works on different tables having different
>>> primary keys but the argument of get is always the primary key , i.e. get
>>> request is -
>>> 
>>> get (column name, value)
>>> 
>>> the value is always the primary key value.
>>> 
>>> It looks like it would be a pretty standard method but I cant find a method
>>> like that. Anybody have any clue?
>>> 
>>> Thanks
>>> 
>>> Ratin
>> 
>> 
>> --
>> 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] Any method to get primary key matching a given value ?

[Karl DeSaulniers]

Hey Ratin,
Have you looked into the table column named 'pk' inside table_info?
That is where a column is indicated to be a primary key or not.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 




> On Aug 18, 2016, at 6:51 PM, Ratin  wrote:
> 
> Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is not 
> available for sqlite database. I had to built up the whole query with php 
> using PRAGMA table_info(tablename), looking at the pk entry, when its 1, get 
> the column name, and then update the sql statement based on that. A bit of 
> work, wouldve been much simpler if a method was provided, but oh well ..
> 
> Thanks again
> 
> Ratin 
> 
> On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers  > wrote:
> Hi Ratin,
> Going to take a stab at this one.
> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> Might be where you want to look for what you are trying.
> Sorry can't help more.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com 
> 
> 
> 
> 
> > On Aug 18, 2016, at 1:27 PM, Ratin  > > wrote:
> >
> > I'm writing the generic get that works on different tables having different
> > primary keys but the argument of get is always the primary key , i.e. get
> > request is -
> >
> > get (column name, value)
> >
> > the value is always the primary key value.
> >
> > It looks like it would be a pretty standard method but I cant find a method
> > like that. Anybody have any clue?
> >
> > Thanks
> >
> > Ratin
> 
> 
> --
> PHP Database Mailing List (http://www.php.net/ )
> To unsubscribe, visit: http://www.php.net/unsub.php 
> 
> 
> 

Re: [PHP-DB] Any method to get primary key matching a given value ?

[Ratin]

Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is not
available for sqlite database. I had to built up the whole query with php
using PRAGMA table_info(tablename), looking at the pk entry, when its 1,
get the column name, and then update the sql statement based on that. A bit
of work, wouldve been much simpler if a method was provided, but oh well ..

Thanks again

Ratin

On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers 
wrote:

> Hi Ratin,
> Going to take a stab at this one.
> Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
> Might be where you want to look for what you are trying.
> Sorry can't help more.
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
>
> > On Aug 18, 2016, at 1:27 PM, Ratin  wrote:
> >
> > I'm writing the generic get that works on different tables having
> different
> > primary keys but the argument of get is always the primary key , i.e. get
> > request is -
> >
> > get (column name, value)
> >
> > the value is always the primary key value.
> >
> > It looks like it would be a pretty standard method but I cant find a
> method
> > like that. Anybody have any clue?
> >
> > Thanks
> >
> > Ratin
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] Any method to get primary key matching a given value ?

[Karl DeSaulniers]

Hi Ratin,
Going to take a stab at this one. 
Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
Might be where you want to look for what you are trying. 
Sorry can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




> On Aug 18, 2016, at 1:27 PM, Ratin  wrote:
> 
> I'm writing the generic get that works on different tables having different
> primary keys but the argument of get is always the primary key , i.e. get
> request is -
> 
> get (column name, value)
> 
> the value is always the primary key value.
> 
> It looks like it would be a pretty standard method but I cant find a method
> like that. Anybody have any clue?
> 
> Thanks
> 
> Ratin


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

Re: [PHP-DB] Connecting to database fails

[Rich Shepard]

On Sat, 13 Aug 2016, Lester Caine wrote:


Do you actually have the postgresql PHP driver installed. Database drivers
are not installed by default as you only really need the ones you are
actually using.


  Should be installed:

/usr/lib/php/extensions/pgsql.so
/usr/lib/php/extensions/pdo_pgsql.so

and /etc/httpd/php.ini contains both.

  Still see the error that the connection cannot be established after
re-starting httpd.

  Where else do I look?

Thanks,

Rich

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

Re: [PHP-DB] Connecting to database fails

[Lester Caine]

On 14/08/16 00:00, Rich Shepard wrote:
>   Ah, yes. Pat likes MySQL/MariaDB so he does not build php to support
> postgres. I'll rebuild it and that should solve the problem.

I've been with Firebird/Interbase since before PHP existed ;)

-- 
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] Connecting to database fails

[Rich Shepard]

On Sat, 13 Aug 2016, Rich Shepard wrote:


 Not installed. I'll search the Web to learn how to install it.


  Ah, yes. Pat likes MySQL/MariaDB so he does not build php to support
postgres. I'll rebuild it and that should solve the problem.

Thanks again,

Rich

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

Re: [PHP-DB] Connecting to database fails

[Rich Shepard]

On Sat, 13 Aug 2016, Lester Caine wrote:


Do you actually have the postgresql PHP driver installed. Database drivers
are not installed by default as you only really need the ones you are
actually using.


Lester,

  Well, that would make a difference. I did not know how to check for this.


Should be php5-pgsql


  Not installed. I'll search the Web to learn how to install it.

Thanks very much,

Rich

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

Re: [PHP-DB] Connecting to database fails

[Lester Caine]

On 13/08/16 21:23, Rich Shepard wrote:
> With no other PHP applications installed here I have no experience in
> determining where this stoppage occurs. Pointers on how to isolate the
> source of the problem, than how to fix it, are needed.

Do you actually have the postgresql PHP driver installed. Database
drivers are not installed by default as you only really need the ones
you are actually using.

Should be php5-pgsql

-- 
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] weird string literal related problem

[Ratin]

nvm, I got my answer -- foreach ( $entries as $column => $value ) will give
the column names .. which simplifies my task a lot.

On Mon, Aug 1, 2016 at 12:52 PM, Ratin  wrote:

> Just curious why the values can only be accessed by column names and not
> an array subscript, like :
>
> $i=0;
> $result = $db->query('select * from DecoderUser');
> while ($entries = $result->fetchArray())
> {
> $value=$entries[i];
> $i = $i + 1;
> }
>
> On Mon, Aug 1, 2016 at 12:25 PM, Ratin  wrote:
>
>> Thanks Aziz, that works. Somehow i thought the ' needed to be included
>> just like you quote all strings on an interactive DB shell or even when you
>> get the value directly by column name.
>>
>> On Mon, Aug 1, 2016 at 11:47 AM, Aziz Saleh  wrote:
>>
>>> But if i print $entries['decoderUserGUID'] I get the correct value
>>>
>>> This means the key value is decoderUserGUID NOT 'decoderUserGUID', try
>>> creating $strIndex without the quotes, just the key value.
>>>
>>> On Mon, Aug 1, 2016 at 2:31 PM, Ratin  wrote:
>>>
 Hi I am new to this list, not sure how active this list is but i have a
 question regarding php-sqlite integration. I am using a string variable
 where I put the column name and trying to use that varible to get its
 value, in a loop. So here is what I have (simplified version):


 $strIndex='\'' . $columns[$i] . '\'';  //strIndex has the value :
 'decoderUserGUID'  (including quotes)
 $result = $db->query('select * from DecoderUser');
 while ($entries = $result->fetchArray())
 {
 $value=$entries[$strIndex];
 }



 So if I print $value, I get nothing. But if i print
 $entries['decoderUserGUID'] I get the correct value. If I print
 $strIndex,
 I get 'decoderUserGUID' (including the quotes). How can I solve this
 mistery.. whats hapenning here , Anybody have any idea?


 Thanks

 Ratin

>>>
>>>
>>
>

Re: [PHP-DB] weird string literal related problem

[Ratin]

Just curious why the values can only be accessed by column names and not an
array subscript, like :

$i=0;
$result = $db->query('select * from DecoderUser');
while ($entries = $result->fetchArray())
{
$value=$entries[i];
$i = $i + 1;
}

On Mon, Aug 1, 2016 at 12:25 PM, Ratin  wrote:

> Thanks Aziz, that works. Somehow i thought the ' needed to be included
> just like you quote all strings on an interactive DB shell or even when you
> get the value directly by column name.
>
> On Mon, Aug 1, 2016 at 11:47 AM, Aziz Saleh  wrote:
>
>> But if i print $entries['decoderUserGUID'] I get the correct value
>>
>> This means the key value is decoderUserGUID NOT 'decoderUserGUID', try
>> creating $strIndex without the quotes, just the key value.
>>
>> On Mon, Aug 1, 2016 at 2:31 PM, Ratin  wrote:
>>
>>> Hi I am new to this list, not sure how active this list is but i have a
>>> question regarding php-sqlite integration. I am using a string variable
>>> where I put the column name and trying to use that varible to get its
>>> value, in a loop. So here is what I have (simplified version):
>>>
>>>
>>> $strIndex='\'' . $columns[$i] . '\'';  //strIndex has the value :
>>> 'decoderUserGUID'  (including quotes)
>>> $result = $db->query('select * from DecoderUser');
>>> while ($entries = $result->fetchArray())
>>> {
>>> $value=$entries[$strIndex];
>>> }
>>>
>>>
>>>
>>> So if I print $value, I get nothing. But if i print
>>> $entries['decoderUserGUID'] I get the correct value. If I print
>>> $strIndex,
>>> I get 'decoderUserGUID' (including the quotes). How can I solve this
>>> mistery.. whats hapenning here , Anybody have any idea?
>>>
>>>
>>> Thanks
>>>
>>> Ratin
>>>
>>
>>
>

Re: [PHP-DB] weird string literal related problem

[Ratin]

Thanks Aziz, that works. Somehow i thought the ' needed to be included just
like you quote all strings on an interactive DB shell or even when you get
the value directly by column name.

On Mon, Aug 1, 2016 at 11:47 AM, Aziz Saleh  wrote:

> But if i print $entries['decoderUserGUID'] I get the correct value
>
> This means the key value is decoderUserGUID NOT 'decoderUserGUID', try
> creating $strIndex without the quotes, just the key value.
>
> On Mon, Aug 1, 2016 at 2:31 PM, Ratin  wrote:
>
>> Hi I am new to this list, not sure how active this list is but i have a
>> question regarding php-sqlite integration. I am using a string variable
>> where I put the column name and trying to use that varible to get its
>> value, in a loop. So here is what I have (simplified version):
>>
>>
>> $strIndex='\'' . $columns[$i] . '\'';  //strIndex has the value :
>> 'decoderUserGUID'  (including quotes)
>> $result = $db->query('select * from DecoderUser');
>> while ($entries = $result->fetchArray())
>> {
>> $value=$entries[$strIndex];
>> }
>>
>>
>>
>> So if I print $value, I get nothing. But if i print
>> $entries['decoderUserGUID'] I get the correct value. If I print $strIndex,
>> I get 'decoderUserGUID' (including the quotes). How can I solve this
>> mistery.. whats hapenning here , Anybody have any idea?
>>
>>
>> Thanks
>>
>> Ratin
>>
>
>

Re: [PHP-DB] weird string literal related problem

[Aziz Saleh]

But if i print $entries['decoderUserGUID'] I get the correct value

This means the key value is decoderUserGUID NOT 'decoderUserGUID', try
creating $strIndex without the quotes, just the key value.

On Mon, Aug 1, 2016 at 2:31 PM, Ratin  wrote:

> Hi I am new to this list, not sure how active this list is but i have a
> question regarding php-sqlite integration. I am using a string variable
> where I put the column name and trying to use that varible to get its
> value, in a loop. So here is what I have (simplified version):
>
>
> $strIndex='\'' . $columns[$i] . '\'';  //strIndex has the value :
> 'decoderUserGUID'  (including quotes)
> $result = $db->query('select * from DecoderUser');
> while ($entries = $result->fetchArray())
> {
> $value=$entries[$strIndex];
> }
>
>
>
> So if I print $value, I get nothing. But if i print
> $entries['decoderUserGUID'] I get the correct value. If I print $strIndex,
> I get 'decoderUserGUID' (including the quotes). How can I solve this
> mistery.. whats hapenning here , Anybody have any idea?
>
>
> Thanks
>
> Ratin
>

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

[Alko Kotalko]

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

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

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

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

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

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

[Karl DeSaulniers]

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

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


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

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

be

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

or like this

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

?

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

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


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

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

[Jeff]

Greetings,

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

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

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

[...snipped...]

regards,
J


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

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

[Lester Caine]

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

This is the key to your problem ...

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

Does your setup work if the SQL is

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

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

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

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

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

[Alko Kotalko]

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

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

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

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

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

[Alko Kotalko]

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

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

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

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

[Alko Kotalko]

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

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

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

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

[Karl DeSaulniers]

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

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



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

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

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

[Lester Caine]

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

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

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

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

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

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

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

Re: [PHP-DB] Correct Syntax?

[chimurenga chimurenga]

make vehicle licence the unique key

On 14 Oct 2015 00:28, "Karl DeSaulniers"  wrote:
>
> Quick question. Is this viable in MySQL?
>
> UNIQUE KEY `Vehicle_Name`
(`Vehicle_Make`+`Vehicle_Model`+`Vehicle_Style`+`Vehicle_License`)
>
> TIA
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>

Re: [PHP-DB] Correct Syntax?

[Roberto Spadim]

instead of +, use ",'

2015-10-13 20:21 GMT-03:00 Karl DeSaulniers :

> Quick question. Is this viable in MySQL?
>
> UNIQUE KEY `Vehicle_Name`
> (`Vehicle_Make`+`Vehicle_Model`+`Vehicle_Style`+`Vehicle_License`)
>
> TIA
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle

Re: [PHP-DB] Zero Values

[Karl DeSaulniers]

On Sep 1, 2015, at 10:36 PM, Ethan Rosenberg  
wrote:

> Dear List -
> 
> I have a payment/charges table -
> 
> mysql> describe Charges;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | Indx | mediumint(9) | NO   | PRI | 0   |   |
> | Cust_Num | smallint(5) unsigned | NO   | | NULL|   |
> | Balance  | decimal(10,2)| YES  | | NULL|   |
> | Payments | decimal(10,2)| YES  | | NULL|   |
> | Charges  | decimal(10,2)| YES  | | NULL|   |
> | Notes2   | text | YES  | | NULL|   |
> | Date | date | YES  | | NULL|   |
> | PH1  | char(4)  | YES  | | NULL|   |
> | PH2  | char(4)  | YES  | | NULL|   |
> | PH3  | char(5)  | YES  | | NULL|   |
> +--+--+--+-+-+---+
> 10 rows in set (0.11 sec)
> 
> If Balance, Payments and Charges all equal 0, and then
> 
> select * from Charges,
> 
> the rows w/ all zero values will not be displayed.
> 
> Why?
> 
> TIA
> 
> Ethan
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

I would recommend storing the zero value if you plan on using that value for 
calculation later. 
Just makes things easier than having to do a if(NULL) statement.
2¢

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] Zero Values

[Karl DeSaulniers]

On Sep 2, 2015, at 11:02 PM, Ethan Rosenberg  
wrote:

> Dear list -
> 
> I know that I am making a mistake somewhere. but am lost for an answer.
> 
> mysql> select Payments  from Charges where if(Payments,  Payments, 0);
> +--+
> | Payments |
> +--+
> |13.00 |
> |55.00 |
> |65.00 |
> |65.00 |
> |65.00 |
> |65.00 |
> |   123.00 |
> |   150.00 |
> 
> mysql> select Balance, Payments, Charges from Charges where ifnull(Balance,0);
> +-+--+-+
> | Balance | Payments | Charges |
> +-+--+-+
> |  -13.00 |13.00 |0.00 |
> |  123.00 | 0.00 |  123.00 |
> |  325.00 | 0.00 |  325.00 |
> |   10.00 | 0.00 |   23.00 |
> |  270.00 |55.00 |0.00 |
> |   58.00 |65.00 |0.00 |
> |   -7.00 |65.00 |0.00 |
> |  -72.00 |65.00 |0.00 |
> 
> TIA
> 
> Ethan

Something like?

$SQL = "SELECT * FROM `Charges` 
WHERE Cust_Num=".$Cust_Num." 
AND Charges!='NULL' AND Ballance!='NULL' AND Payments!='NULL' 
ORDER BY Date ASC";

Not sure why you would have a column name the same as your database table 
though.
Could get confusing.  

HTH,

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] Zero Values

[Ethan Rosenberg]

On 09/01/2015 11:43 PM, Aziz Saleh wrote:

On Tue, Sep 1, 2015 at 11:36 PM, Ethan Rosenberg <
erosenb...@hygeiabiomedical.com> wrote:


Dear List -

I have a payment/charges table -

mysql> describe Charges;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Indx | mediumint(9) | NO   | PRI | 0   |   |
| Cust_Num | smallint(5) unsigned | NO   | | NULL|   |
| Balance  | decimal(10,2)| YES  | | NULL|   |
| Payments | decimal(10,2)| YES  | | NULL|   |
| Charges  | decimal(10,2)| YES  | | NULL|   |
| Notes2   | text | YES  | | NULL|   |
| Date | date | YES  | | NULL|   |
| PH1  | char(4)  | YES  | | NULL|   |
| PH2  | char(4)  | YES  | | NULL|   |
| PH3  | char(5)  | YES  | | NULL|   |
+--+--+--+-+-+---+
10 rows in set (0.11 sec)

If Balance, Payments and Charges all equal 0, and then

select * from Charges,

the rows w/ all zero values will not be displayed.

Why?

TIA

Ethan


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



The default value field is NULL, which is not the same as zero. When
inserting the records add '0' as a value if you want. Another way to do it
is during the SELECT ad an ifnull use '0'.


Dear list -

I know that I am making a mistake somewhere. but am lost for an answer.

mysql> select Payments  from Charges where if(Payments,  Payments, 0);
+--+
| Payments |
+--+
|13.00 |
|55.00 |
|65.00 |
|65.00 |
|65.00 |
|65.00 |
|   123.00 |
|   150.00 |

mysql> select Balance, Payments, Charges from Charges where ifnull(Balance,0);
+-+--+-+
| Balance | Payments | Charges |
+-+--+-+
|  -13.00 |13.00 |0.00 |
|  123.00 | 0.00 |  123.00 |
|  325.00 | 0.00 |  325.00 |
|   10.00 | 0.00 |   23.00 |
|  270.00 |55.00 |0.00 |
|   58.00 |65.00 |0.00 |
|   -7.00 |65.00 |0.00 |
|  -72.00 |65.00 |0.00 |

TIA

Ethan



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

Re: [PHP-DB] Zero Values

[Aziz Saleh]

On Tue, Sep 1, 2015 at 11:36 PM, Ethan Rosenberg <
erosenb...@hygeiabiomedical.com> wrote:

> Dear List -
>
> I have a payment/charges table -
>
> mysql> describe Charges;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | Indx | mediumint(9) | NO   | PRI | 0   |   |
> | Cust_Num | smallint(5) unsigned | NO   | | NULL|   |
> | Balance  | decimal(10,2)| YES  | | NULL|   |
> | Payments | decimal(10,2)| YES  | | NULL|   |
> | Charges  | decimal(10,2)| YES  | | NULL|   |
> | Notes2   | text | YES  | | NULL|   |
> | Date | date | YES  | | NULL|   |
> | PH1  | char(4)  | YES  | | NULL|   |
> | PH2  | char(4)  | YES  | | NULL|   |
> | PH3  | char(5)  | YES  | | NULL|   |
> +--+--+--+-+-+---+
> 10 rows in set (0.11 sec)
>
> If Balance, Payments and Charges all equal 0, and then
>
> select * from Charges,
>
> the rows w/ all zero values will not be displayed.
>
> Why?
>
> TIA
>
> Ethan
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
The default value field is NULL, which is not the same as zero. When
inserting the records add '0' as a value if you want. Another way to do it
is during the SELECT ad an ifnull use '0'.

Re: [PHP-DB] For my understanding

[Aziz Saleh]

On Sat, Jul 25, 2015 at 9:33 PM, Karl DeSaulniers k...@designdrumm.com
wrote:

 Hello,
 This might be a question better suited for the general php list.
 If it is, please let me know.

 I have inherited some pages that have a code on it I don't recognize.
 Can anyone enlighten me as to what this code is doing?

 [code]
 array_walk($Options, create_function('$val', '$val = trim($val);'));

 Is this creating php variables from the contents of the $Options array?
 TIA

 Best,

 Karl DeSaulniers
 Design Drumm
 http://designdrumm.com




It's trimming all elements within the array.

http://php.net/manual/en/function.array-walk.php
http://php.net/manual/en/function.create-function.php
http://php.net/manual/en/function.trim.php

Re: [PHP-DB] Grouping results

[Karl DeSaulniers]

Well, I figured it out. It took two queries.
Probably a better way I'm not thinking about, but this works in case anyone 
needs/wants.

Was hoping to do it all in the SQL statement I guess.

$FieldGroups = $wpdb-get_results(SELECT DISTINCT Field_Group FROM 
.table_name);

foreach($FieldGroups as $FieldGroup) {
$ReturnString .= 'h3'.$FieldGroup-Field_Group.'/h3';
$Fields = $wpdb-get_results($wpdb-prepare(SELECT * FROM . 
table_name.' WHERE Field_Group=%s', $FieldGroup-Field_Group));
foreach($Fields as $i=$Field) {
$ReturnString .= /*Other code to build html*/
}
}
return $ReturnString;

I am working on a WordPress site. Hence the $wpdb-get_results().
FYI, for those who aren't familiar. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


PS: Thanks teddy bear


On Jul 22, 2015, at 11:19 PM, Karl DeSaulniers k...@designdrumm.com wrote:

 Hello All,
 How can I group database results in a foreach loop?
 I have a field_group column that can have a value or not.
 
 I want to read from the database then display results and if the results have 
 a matching field_group, I want to have them group in the html.
 
 This is my current SQL. I am using MySQL.
 
 $Sql = SELECT * FROM .table_name.' GROUP BY Field_ID, Field_Group';
 
 From this I want to be able to wrap the groups in a div and add a h3 with the 
 group name.
 So if there are 5 entries in the BOB group, then the html would look like 
 this.
 
 input name=Field_Name1 ///empty Field_Group
 div id=BOB//Field_Group = BOB
   input name=Field_Name2 /
   input name=Field_Name3 /
   input name=Field_Name4 /
   input name=Field_Name10 /
 /div
 input name=Field_Name5 ///empty Field_Group
 input name=Field_Name6 ///empty Field_Group
 div id=Charlie//Field_Group = Charlie
   input name=Field_Name8 /
   input name=Field_Name9 /
   input name=Field_Name15 /
 /div
 
 Am I setting this up right?  
 
 TIA
 
 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] How Do I Enter a Defect Against PHP ODBC Extension?

[Chupaka]

https://bugs.php.net/

--
Подпись:
(добавляется в конце всех исходящих писем)

2015-07-08 21:25 GMT+03:00 Bruce Bailey brucebailey...@gmail.com:

 I have discovered a defect in the PHP extension, odbc.so.  Specifically the
 function odbc_fetch_array is not properly handling boolean values from
 postgresql on 64bit systems.

 I'd like to enter a defect, how can I do this?

 Thanks,

 Bruce

Re: [PHP-DB] PHP ODBC odbc_fetch_array returns uninitialized values for Booleans

[Bastien Koert]

Is it a truly a three state field (true, false, null) or just T/F? Perhaps
a default value for the field might be better?

On Mon, Jun 29, 2015 at 11:42 AM Bruce Bailey brucebailey...@gmail.com
wrote:

 *Description*

 The PHP function odbc_fetch_array returns uninitialized values for
 PostgreSQL boolean values.  On older systems, this function returned '1'
 for true and '0' for false values.  On our 64 bit system, the boolean
 values appear to be uninitialized data.

 *Additional information*

 Increasing buffer size in php_odbc.c (odbc.so) in function odbc_bindcols,
 just prior to call to SQLBindCol makes problem stop exhibiting.

 SQLColAttributes(...,SQL_COLUMN_TYPE,...) returns type of SQL_VARCHAR on
 system with problem, SQL_CHAR on system where code works as expected.

 SQLColAttributes is deprecated (shouldn't matter, though)

 *Recreation steps:*

 // Create table/data in PostgreSQL

 DROP TABLE IF EXISTS public.persons;
 create table public.persons (id int, name varchar(255), switch_sw boolean);
 insert into public.persons values (0, 'smith', true);
 insert into public.persons values (1, 'jones', false);
 insert into public.persons values (2, 'bailey', true);
 insert into public.persons values (3, 'johnson', false);

 // Test script
 ?php

if(!($conn = odbc_connect(... , ... , ...)))
   die(odbc_connect failed\n);

if(!($rows = odbc_exec($conn, select * from persons;)))
   die(odbc_exec failed\n);

for(;;)
{
   if (!($row = odbc_fetch_array ($rows)))
  break;

   print_r($row);
}

odbc_close($conn);
 ?

 *Actual output*

 Array
 (
 [id] = 0
 [name] = smith
 [switch_sw] = . // some unreadable binary content
 )
 . . .

 *Expected Output*

 Array
 (
 [id] = 0
 [name] = smith
 [switch_sw] = 1
 )
 . . .

 *Software levels*

 kernel - Linux C921189 3.10.0-123.20.1.el7.x86_64 . . . GNU/Linux
 ODBC - libodbc.so.1 (cannot exactly determine version)
 PostgreSQL - 9.3.0
 PHP - 5.6.7

 This system is little endian

Re: [PHP-DB] Re: سلام دوست من

[Aziz Saleh]

It's spam message, been sent almost every other day.

2015-06-23 11:06 GMT-04:00 Jim Giner jim.gi...@albanyhandball.com:

 On 6/2/2015 2:17 PM, Mahsa Ehsani wrote:

 زمان ثمردهي‌اش گذشته بود و دوره بازنشستگي را طي مي‌كرد. روزگاري طراوت و
 سرسبزي داشت و كودك و بزرگ از قِبَلِ او مرزوق بودند. اما اكنون تمام
 دلخوشي‌اش اين بود كه پيامبر اعظم ـ صلي­الله­عليه­و­آله ـ چون به ايراد سخن
 مي‌ايستاد بر او تكيه مي‌كرد و اين براي او از هر افتخاري بالاتر بود.
 نخل خشكيده‌اي در شهر مدينه كه توفيق هم­جواري با حجّت كبراي حق و برترين
 مخلوق در عرصه هستي را پيدا كرده بود.
 او چوب خشكيده‌اي بود از جنس نخل، نه ادعاي عقلانيّت داشت و نه خود را در حد
 و اندازه ما انسان‌ها مي‌دانست. اما تمام سرمايه‌اش عشقي بود كه به وجود
 نوراني پيامبر خدا ـ صلي­الله­عليه­و­آله ـ در دل پيدا كرده بود.
 چند صباحي گذشت تا اين كه اصحاب براي حضرت منبري سه پله از چوب درست كردند و
 با اجازه ايشان وارد مسجد نمودند، اما تكيه حضرت همچنان به نخل خشكيده بود.
 در اثناء سخن، حضرت به سمت منبر حركت كرد. اما چند قدم دورتر نشده بود كه
 نخل صدايش به ناله بلند شد. ناله‌اي از سوز دل مثل ناله ماده­شتر در فراق
 فرزندش. و همه مردم شنيدند و به ستون حنّانه خيره ماندند كه الله اكبر!
 الله اكبر از اين شور و اشتياق و از اين بي‌تحملي درد فراق!
 اما او نبيّ رحمت بود و كشتي نجات امّت؛ و با همه هستي رفيق شفيق بود و يار
 صميمي. و الله اكبر از اين قلب آكنده از محبّت نبي حتي نسبت به آنچه كه ما
 بي‌جانش مي‌پنداريم.
 او برگشت به سوي ستون و با تمام وجود او را در آغوش گرفت و خوشا به حال ستون
 عاشق. نخل اما صداي ناله‌اش عوض شد و مثل كودكي كه بعد از دوري به آغوش مادر
 رسيده باشد هق هق گريست.
 و رسول او را نوازش كرد. آن قدر كه نخل در آغوش پرمهر مادر هستي آرام گرفت و
 گريه‌اش خاموش شد.
 حضرت رو كرد به مردم و فرمود: به خدا قسم اگر در آغوشش نمي­گرفتم تا قيام
 قيامت ناله‌اش پاياني نداشت!
 و مرحبا بر ستون و هزار آفرين بر اين همه ارادت و استقامت!
 آري مهدي‌جان! او كه چوب بود و بي‌جان، فراق حجت خدا را چند ثانيه بيشتر
 نتوانست تحمل كند اما ما كه انسانيم و ادعا داريم سيزده قرن فراق تو را
 ديده‌ايم و هنوز صدايمان به ناله بلند نشده است! كه اگر شده بود تو آمده بودي!
 چرا كه تو پسر همان پيغمبري با همه شئونات حتي مهرباني‌اش منهاي وحي.
 آري، مشكل از ما است كه لذت با تو بودن را نچشيديم تا در رنج و درد جانسوز
 فراقت ناله زنيم.
 ما بيشتر به نبودنت انس گرفته‌ايم تا به بودنت؛ پس بيا، بيا و لذت با ولي
 بودن و با ولي زيستن را به انسانيت بچشان.
 هرگونه پخش و نشر اين محتوا بلامانع مي باشد

  Uhh,  translation please?

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