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

[PHP-DB] MySQL connection

[Karl DeSaulniers]

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
http://designdrumm.com 

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

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

[Karl DeSaulniers]

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 

[PHP-DB] mysql and auth plugins

[Roberto Spadim]

Hi guys, i'm with a doubt about mysql and connect with a mariadb
server using a dialog auth plugin (server side)

i didn't found functions to execute dialog with server

a full example of what i want but using heidisql can be found here:
http://www.heidisql.com/forum.php?t=9752

any help is wellcome

-- 
Roberto Spadim

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

Re: [PHP-DB] mysql query

[Michael Oki]

Try the insertion like this:
$sql2 = mysql_query(insert into Inventory (`UPC`
, `quant`, `manuf`, `item`, `orderpt`, `ordrpt_flag`, `stock`)
.values ('$upc', $qnt,'$mnf','$itm',
'$odrpt', '0', '$stk')  ) or die(mysql_error());

On 22 August 2013 05:10, Daniel Krook kr...@us.ibm.com wrote:

 Ethan,

 What about:

 $result2 = mysqli_query(cxn, $sql2);

 Doesn't look like you're sending it a connection link as a variable ($cxn)
 and that's passed through as a literal?




 Thanks,


 Daniel Krook
 Software Engineer, Advanced Cloud Solutions, GTS

 IBM Senior Certified IT Specialist - L3 Thought Leader
 The Open Group Certified IT Specialist - L3 Distinguished
 Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified






 Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/2013
 11:59:19 PM:

  From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
  To: Daniel Krook/White Plains/IBM@IBMUS
  Cc: PHP Database List php-db@lists.php.net
  Date: 08/21/2013 11:59 PM
  Subject: Re: [PHP-DB] mysql query
 
  On 08/21/2013 11:30 PM, Daniel Krook wrote:
  Ethan,
 
  It's hard to tell from the code formatting in your email what the
  exact problem might be, but a few reasons that this might fail in
  PHP rather than when sent to MySQL with hardcoded values:
 
  1.  var_dump/print_r $_POST to see what you're getting as input is
  what you expect (and sanitize!).
 
  2.  Check that the SQL statement concatenation in PHP is building
  the string you're expecting. It looks like you're joining 2 strings
  when defining $sql2 that doesn't leave a space between the close
  parentheses and values. Compare this against what you're sending
  on the command line.
 
  3.  Get rid of all single quotes... escape your double quotes where
  needed. This will avoid any variable-in-string interpolation errors
  and may help you find the issue with input data. Same with your echo
  $sql2 statement... that's not going to give you the same thing as
  the print_r below it.
 
 
 
  Thanks,
 
 
  Daniel Krook
  Software Engineer, Advanced Cloud Solutions, GTS
 
  IBM Senior Certified IT Specialist - L3 Thought Leader
  The Open Group Certified IT Specialist - L3 Distinguished
  Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified
 
 
 
 
  Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/
  2013 07:48:12 PM:
 
   From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
   To: PHP Database List php-db@lists.php.net
   Date: 08/21/2013 07:48 PM
   Subject: [PHP-DB] mysql query
  
   Dear List -
  
   I can't figure this out
  
   mysql describe Inventory;
   +-+-+--+-+-+---+
   | Field   | Type| Null | Key | Default | Extra |
   +-+-+--+-+-+---+
   | UPC | varchar(14) | YES  | | NULL |   |
   | quant   | int(5)  | NO   | | NULL |   |
   | manuf   | varchar(20) | YES  | | NULL |   |
   | item| varchar(50) | YES  | | NULL |   |
   | orderpt | tinyint(4)  | NO   | | NULL |   |
   | ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
   | stock   | int(3)  | YES  | | NULL |   |
   +-+-+--+-+-+---+
  
   Here are code snippets -
  
  $upc   = $_SESSION['UPC'];
  $qnt   = $_POST['quant'];
  $mnf   = $_POST['manuf'];
  $itm   = $_POST['item'];
  $odrpt = $_POST['oderpt'];
  $opf   = $_POST['ordrpt_flag'];
  $stk= $_POST['stock'];
  
  $sql2 = insert into Inventory (UPC, quant,

   manuf, item, orderpt, ordrpt_flag, stock)
.values ('$upc', $qnt,'$mnf','$itm',

   odrpt, 0, $stk);
  $result2 = mysqli_query(cxn, $sql2);
  echo '$sql2br /';
  print_r($sql2);
  echo br /$upc $qnt $mnf $itm $odrpt $opf

   $stkkbr /;
  if (!$result2)
die('Could not enter data: ' .
   mysqli_error());
  
   The mysql query fails.  I cannot figure out why.  It works from the
   command line.
  
   TIA
  
   Ethan
  
  Daniel -
 
  Thanks.
 
  Tried all  your suggestions.
 
  Sorry, no luck.
 
  Ethan

Re: [PHP-DB] mysql query

[Vinay Kannan]

 $sql2 = insert into Inventory (UPC, quant, manuf, item, orderpt,
ordrpt_flag, stock)  values ('$upc', '$qnt','$mnf','$itm', '$odrpt', 0,
$stk);

Looks like, you have the ' ' missing for $qnt and odrpt had the $ and the
'' missing.
The above query should work, I haven't tested it though.
Also for no error, have you turned off the PHP error reporting?

It probably is working on the command line, since you posted actual values
there and in the PHP code, you are using the variables which I think were
not wrapped in the query correctly?


On Thu, Aug 22, 2013 at 12:51 PM, Michael Oki mikeoki@gmail.com wrote:

 Try the insertion like this:
 $sql2 = mysql_query(insert into Inventory (`UPC`
 , `quant`, `manuf`, `item`, `orderpt`, `ordrpt_flag`, `stock`)
 .values ('$upc', $qnt,'$mnf','$itm',
 '$odrpt', '0', '$stk')  ) or die(mysql_error());

 On 22 August 2013 05:10, Daniel Krook kr...@us.ibm.com wrote:

  Ethan,
 
  What about:
 
  $result2 = mysqli_query(cxn, $sql2);
 
  Doesn't look like you're sending it a connection link as a variable
 ($cxn)
  and that's passed through as a literal?
 
 
 
 
  Thanks,
 
 
  Daniel Krook
  Software Engineer, Advanced Cloud Solutions, GTS
 
  IBM Senior Certified IT Specialist - L3 Thought Leader
  The Open Group Certified IT Specialist - L3 Distinguished
  Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified
 
 
 
 
 
 
  Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/2013
  11:59:19 PM:
 
   From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
   To: Daniel Krook/White Plains/IBM@IBMUS
   Cc: PHP Database List php-db@lists.php.net
   Date: 08/21/2013 11:59 PM
   Subject: Re: [PHP-DB] mysql query
  
   On 08/21/2013 11:30 PM, Daniel Krook wrote:
   Ethan,
  
   It's hard to tell from the code formatting in your email what the
   exact problem might be, but a few reasons that this might fail in
   PHP rather than when sent to MySQL with hardcoded values:
  
   1.  var_dump/print_r $_POST to see what you're getting as input is
   what you expect (and sanitize!).
  
   2.  Check that the SQL statement concatenation in PHP is building
   the string you're expecting. It looks like you're joining 2 strings
   when defining $sql2 that doesn't leave a space between the close
   parentheses and values. Compare this against what you're sending
   on the command line.
  
   3.  Get rid of all single quotes... escape your double quotes where
   needed. This will avoid any variable-in-string interpolation errors
   and may help you find the issue with input data. Same with your echo
   $sql2 statement... that's not going to give you the same thing as
   the print_r below it.
  
  
  
   Thanks,
  
  
   Daniel Krook
   Software Engineer, Advanced Cloud Solutions, GTS
  
   IBM Senior Certified IT Specialist - L3 Thought Leader
   The Open Group Certified IT Specialist - L3 Distinguished
   Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified
  
  
  
  
   Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/
   2013 07:48:12 PM:
  
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: PHP Database List php-db@lists.php.net
Date: 08/21/2013 07:48 PM
Subject: [PHP-DB] mysql query
   
Dear List -
   
I can't figure this out
   
mysql describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+
   
Here are code snippets -
   
   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];
   
   $sql2 = insert into Inventory (UPC,
 quant,
 
manuf, item, orderpt, ordrpt_flag, stock)
 .values ('$upc',
 $qnt,'$mnf','$itm',
 
odrpt, 0, $stk);
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2br /';
   print_r($sql2);
   echo br /$upc $qnt $mnf $itm $odrpt
 $opf
 
$stkkbr /;
   if (!$result2

[PHP-DB] mysql query

[Ethan Rosenberg]

Dear List -

I can't figure this out

mysql describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

  $upc   = $_SESSION['UPC'];
  $qnt   = $_POST['quant'];
  $mnf   = $_POST['manuf'];
  $itm   = $_POST['item'];
  $odrpt = $_POST['oderpt'];
  $opf   = $_POST['ordrpt_flag'];
  $stk= $_POST['stock'];

  $sql2 = insert into Inventory (UPC, quant, 
manuf, item, orderpt, ordrpt_flag, stock)
.values ('$upc', $qnt,'$mnf','$itm', 
odrpt, 0, $stk);

  $result2 = mysqli_query(cxn, $sql2);
  echo '$sql2br /';
  print_r($sql2);
  echo br /$upc $qnt $mnf $itm $odrpt $opf 
$stkkbr /;

  if (!$result2)
die('Could not enter data: ' . 
mysqli_error());


The mysql query fails.  I cannot figure out why.  It works from the 
command line.


TIA

Ethan




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

Re: [PHP-DB] mysql query

[Toby Hart Dyke]

1) What is the error message?

2) This has an error:

values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk)

Missing '$' in front of 'odrpt'.

  Toby


On 8/22/2013 12:48 AM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

  $upc   = $_SESSION['UPC'];
  $qnt   = $_POST['quant'];
  $mnf   = $_POST['manuf'];
  $itm   = $_POST['item'];
  $odrpt = $_POST['oderpt'];
  $opf   = $_POST['ordrpt_flag'];
  $stk= $_POST['stock'];

  $sql2 = insert into Inventory (UPC, quant, 
manuf, item, orderpt, ordrpt_flag, stock)
.values ('$upc', $qnt,'$mnf','$itm', 
odrpt, 0, $stk);

  $result2 = mysqli_query(cxn, $sql2);
  echo '$sql2br /';
  print_r($sql2);
  echo br /$upc $qnt $mnf $itm $odrpt $opf 
$stkkbr /;

  if (!$result2)
die('Could not enter data: ' . 
mysqli_error());


The mysql query fails.  I cannot figure out why.  It works from the 
command line.


TIA

Ethan







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

Re: [PHP-DB] mysql query

[Ethan Rosenberg]

On 08/21/2013 07:52 PM, Toby Hart Dyke wrote:


1) What is the error message?

2) This has an error:

values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk)

Missing '$' in front of 'odrpt'.

  Toby


On 8/22/2013 12:48 AM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

  $upc   = $_SESSION['UPC'];
  $qnt   = $_POST['quant'];
  $mnf   = $_POST['manuf'];
  $itm   = $_POST['item'];
  $odrpt = $_POST['oderpt'];
  $opf   = $_POST['ordrpt_flag'];
  $stk= $_POST['stock'];

  $sql2 = insert into Inventory (UPC, quant, 
manuf, item, orderpt, ordrpt_flag, stock)
.values ('$upc', $qnt,'$mnf','$itm', 
odrpt, 0, $stk);

  $result2 = mysqli_query(cxn, $sql2);
  echo '$sql2br /';
  print_r($sql2);
  echo br /$upc $qnt $mnf $itm $odrpt $opf 
$stkkbr /;

  if (!$result2)
die('Could not enter data: ' . 
mysqli_error());


The mysql query fails.  I cannot figure out why.  It works from the 
command line.


TIA

Ethan


Toby -

The problem is that I do not get any error messages.

From this

  if (!$result2)
die('Could not enter data: ' . mysqli_error());

I only get the 'Could not enter data: and no error message.

Ethan


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

Re: [PHP-DB] mysql query

[Daniel Krook]

Ethan,

It's hard to tell from the code formatting in your email what the exact 
problem might be, but a few reasons that this might fail in PHP rather 
than when sent to MySQL with hardcoded values:

1.  var_dump/print_r $_POST to see what you're getting as input is what 
you expect (and sanitize!).

2.  Check that the SQL statement concatenation in PHP is building the 
string you're expecting. It looks like you're joining 2 strings when 
defining $sql2 that doesn't leave a space between the close parentheses 
and values. Compare this against what you're sending on the command 
line.

3.  Get rid of all single quotes... escape your double quotes where 
needed. This will avoid any variable-in-string interpolation errors and 
may help you find the issue with input data. Same with your echo $sql2 
statement... that's not going to give you the same thing as the print_r 
below it.



Thanks,


Daniel Krook
Software Engineer, Advanced Cloud Solutions, GTS

IBM Senior Certified IT Specialist - L3 Thought Leader
The Open Group Certified IT Specialist - L3 Distinguished
Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified





Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/2013 
07:48:12 PM:

 From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
 To: PHP Database List php-db@lists.php.net
 Date: 08/21/2013 07:48 PM
 Subject: [PHP-DB] mysql query
 
 Dear List -
 
 I can't figure this out
 
 mysql describe Inventory;
 +-+-+--+-+-+---+
 | Field   | Type| Null | Key | Default | Extra |
 +-+-+--+-+-+---+
 | UPC | varchar(14) | YES  | | NULL |   |
 | quant   | int(5)  | NO   | | NULL |   |
 | manuf   | varchar(20) | YES  | | NULL |   |
 | item| varchar(50) | YES  | | NULL |   |
 | orderpt | tinyint(4)  | NO   | | NULL |   |
 | ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
 | stock   | int(3)  | YES  | | NULL |   |
 +-+-+--+-+-+---+
 
 Here are code snippets -
 
$upc   = $_SESSION['UPC'];
$qnt   = $_POST['quant'];
$mnf   = $_POST['manuf'];
$itm   = $_POST['item'];
$odrpt = $_POST['oderpt'];
$opf   = $_POST['ordrpt_flag'];
$stk= $_POST['stock'];
 
$sql2 = insert into Inventory (UPC, quant, 
 manuf, item, orderpt, ordrpt_flag, stock)
  .values ('$upc', $qnt,'$mnf','$itm', 
 odrpt, 0, $stk);
$result2 = mysqli_query(cxn, $sql2);
echo '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf 
 $stkkbr /;
if (!$result2)
  die('Could not enter data: ' . 
 mysqli_error());
 
 The mysql query fails.  I cannot figure out why.  It works from the 
 command line.
 
 TIA
 
 Ethan
 
 
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 

Re: [PHP-DB] mysql query

[Daniel Krook]

Ethan,

What about:

$result2 = mysqli_query(cxn, $sql2);

Doesn't look like you're sending it a connection link as a variable ($cxn) 
and that's passed through as a literal?




Thanks,


Daniel Krook
Software Engineer, Advanced Cloud Solutions, GTS

IBM Senior Certified IT Specialist - L3 Thought Leader
The Open Group Certified IT Specialist - L3 Distinguished
Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified






Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/2013 
11:59:19 PM:

 From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
 To: Daniel Krook/White Plains/IBM@IBMUS
 Cc: PHP Database List php-db@lists.php.net
 Date: 08/21/2013 11:59 PM
 Subject: Re: [PHP-DB] mysql query
 
 On 08/21/2013 11:30 PM, Daniel Krook wrote:
 Ethan, 
 
 It's hard to tell from the code formatting in your email what the 
 exact problem might be, but a few reasons that this might fail in 
 PHP rather than when sent to MySQL with hardcoded values: 
 
 1.  var_dump/print_r $_POST to see what you're getting as input is 
 what you expect (and sanitize!).
 
 2.  Check that the SQL statement concatenation in PHP is building 
 the string you're expecting. It looks like you're joining 2 strings 
 when defining $sql2 that doesn't leave a space between the close 
 parentheses and values. Compare this against what you're sending 
 on the command line.
 
 3.  Get rid of all single quotes... escape your double quotes where 
 needed. This will avoid any variable-in-string interpolation errors 
 and may help you find the issue with input data. Same with your echo
 $sql2 statement... that's not going to give you the same thing as 
 the print_r below it.
 
 
 
 Thanks, 
 
 
 Daniel Krook
 Software Engineer, Advanced Cloud Solutions, GTS
 
 IBM Senior Certified IT Specialist - L3 Thought Leader
 The Open Group Certified IT Specialist - L3 Distinguished
 Cloud, Java, PHP, BlackBerry, DB2  Solaris Certified 
 
 
 
 
 Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote on 08/21/
 2013 07:48:12 PM:
 
  From: Ethan Rosenberg erosenb...@hygeiabiomedical.com 
  To: PHP Database List php-db@lists.php.net 
  Date: 08/21/2013 07:48 PM 
  Subject: [PHP-DB] mysql query 
  
  Dear List -
  
  I can't figure this out
  
  mysql describe Inventory;
  +-+-+--+-+-+---+
  | Field   | Type| Null | Key | Default | Extra |
  +-+-+--+-+-+---+
  | UPC | varchar(14) | YES  | | NULL |   |
  | quant   | int(5)  | NO   | | NULL |   |
  | manuf   | varchar(20) | YES  | | NULL |   |
  | item| varchar(50) | YES  | | NULL |   |
  | orderpt | tinyint(4)  | NO   | | NULL |   |
  | ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
  | stock   | int(3)  | YES  | | NULL |   |
  +-+-+--+-+-+---+
  
  Here are code snippets -
  
 $upc   = $_SESSION['UPC'];
 $qnt   = $_POST['quant'];
 $mnf   = $_POST['manuf'];
 $itm   = $_POST['item'];
 $odrpt = $_POST['oderpt'];
 $opf   = $_POST['ordrpt_flag'];
 $stk= $_POST['stock'];
  
 $sql2 = insert into Inventory (UPC, quant, 

  manuf, item, orderpt, ordrpt_flag, stock)
   .values ('$upc', $qnt,'$mnf','$itm', 

  odrpt, 0, $stk);
 $result2 = mysqli_query(cxn, $sql2);
 echo '$sql2br /';
 print_r($sql2);
 echo br /$upc $qnt $mnf $itm $odrpt $opf 

  $stkkbr /;
 if (!$result2)
   die('Could not enter data: ' . 
  mysqli_error());
  
  The mysql query fails.  I cannot figure out why.  It works from the 
  command line.
  
  TIA
  
  Ethan
  
 Daniel -
 
 Thanks.
 
 Tried all  your suggestions.
 
 Sorry, no luck.
 
 Ethan

Re: [PHP-DB] Mysql PDO statement with params in HAVING problem

[Tamara Temple]

Alexander Pletnev pletnev.rusa...@gmail.com wrote:
 Hi everyone, im new here, so please correct me if i created or formated
 topic incorrectly.
 
 I found a problem. I have a simple query for my needs:
 
 $stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like '%john%');
 $stmt-execute();
 while($row = $stmt-fetch())
 {
echo 'pre'; var_dump($row); echo '/pre';
 }
 
 It works fine, until i add a param to my query:
 
 $stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like '%:query%');
 $stmt-execute();
 $query = 'londo';
 $stmt-bindParam(':query',$query);
 while($row = $stmt-fetch())
 {
echo 'pre'; var_dump($row); echo '/pre';
 }
 
 Now there is nothing in fetch(). Is it a bug ?

Yes. You put the execute before you bound the
parameters. prepare-bind-execute.

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

[PHP-DB] Mysql PDO statement with params in HAVING problem

[Alexander Pletnev]

Hi everyone, im new here, so please correct me if i created or formated
topic incorrectly.

I found a problem. I have a simple query for my needs:

$stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
full_name,t.* FROM `specialists` `t` HAVING full_name like '%john%');
$stmt-execute();
while($row = $stmt-fetch())
{
   echo 'pre'; var_dump($row); echo '/pre';
}

It works fine, until i add a param to my query:

$stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
full_name,t.* FROM `specialists` `t` HAVING full_name like '%:query%');
$stmt-execute();
$query = 'londo';
$stmt-bindParam(':query',$query);
while($row = $stmt-fetch())
{
   echo 'pre'; var_dump($row); echo '/pre';
}

Now there is nothing in fetch(). Is it a bug ?

Thanks.

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

Re: [PHP-DB] Mysql PDO statement with params in HAVING problem

[Vinay Kannan]

$stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
full_name,t.* FROM `specialists` `t` HAVING full_name like '%:query%');

How about '%:$query%' instead of '%:query%' ? Does that solve the problem?

Thanks,



On Thu, Jun 27, 2013 at 5:21 PM, Alexander Pletnev 
pletnev.rusa...@gmail.com wrote:

 Hi everyone, im new here, so please correct me if i created or formated
 topic incorrectly.

 I found a problem. I have a simple query for my needs:

 $stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like '%john%');
 $stmt-execute();
 while($row = $stmt-fetch())
 {
echo 'pre'; var_dump($row); echo '/pre';
 }

 It works fine, until i add a param to my query:

 $stmt = $dbh-prepare(SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like
 '%:query%');
 $stmt-execute();
 $query = 'londo';
 $stmt-bindParam(':query',$query);
 while($row = $stmt-fetch())
 {
echo 'pre'; var_dump($row); echo '/pre';
 }

 Now there is nothing in fetch(). Is it a bug ?

 Thanks.

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

[PHP-DB] MySQL, stored procedures, SIGNAL/RESIGNAL

[Marco Baumgartl]

Hi!

I'm currently developing an application which makes heave use of stored 
procedures, stored functions and MySQL's SIGNAL/RESIGNAL feature.


Everything works great if I call only one stored procedure.

But if I call a stored procedure which calls another stored procedure or 
function, SIGNALS from stored procedures are not available as exceptions 
in PHP.


 doSth stored procedure 
DELIMITER $$
USE signaltest$$
DROP PROCEDURE IF EXISTS doSth$$

CREATE PROCEDURE doSth()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RESIGNAL;
END;

SELECT signalSth();
SELECT 'something';
END$$

DELIMITER ;
-

--- signalSth stored function ---
DELIMITER $$
USE signaltest$$
DROP FUNCTION IF EXISTS signalSth$$

CREATE FUNCTION signalSth() RETURNS BOOLEAN
BEGIN
SIGNAL SQLSTATE '45001';
RETURN TRUE;
END$$

DELIMITER ;
-

?php
$host   = '';
$db = '';
$user   = '';
$password   = '';

$dsn = sprintf('mysql:host=%s;dbname=%s', $host, $db);
$conn = new \PDO($dsn, $user, $password);
$conn-setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$stmt = $conn-prepare('CALL doSth()');
try {
if (! $stmt-execute()) {
echo 'error: ' . PHP_EOL;
print_r($stmt-errorInfo());
print_r($stmt-errorCode());
}
echo 'ok: ' . PHP_EOL;
var_dump($stmt-fetchAll(\PDO::FETCH_ASSOC));
} catch (\PDOException $e) {
echo $e-getMessage() . PHP_EOL;
}

I expect the exception message to be printed to console: Unhandled 
user-defined exception condition instead

ok:
array(0) {
}
is printed.

If I call the stored procedure through MySQL client, the SIGNAL is
handled correctly:

--
mysql call doSth();
Empty set (0.00 sec)

ERROR 1644 (45001): Unhandled user-defined exception condition
--

Some version information:

PHP: PHP 5.4.7-1~dotdeb.0 (cli) (built: Sep 15 2012 09:53:20)
MySQL: 5.5.22-ndb-7.2.6-log

mysqlnd = enabled
Version = mysqlnd 5.0.10 - 20111026 - $Id: 
b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $

Compression = supported
SSL = supported
Command buffer size = 4096
Read buffer size = 32768
Read timeout = 31536000
Collecting statistics = Yes
Collecting memory statistics = No
Tracing = n/a
Loaded plugins = 
mysqlnd,example,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password

API Extensions = mysql,mysqli,pdo_mysql

Am I doing anything wrong, is this feature not supported by the PHP 
driver or is it a bug?



Regards,
Marco

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

[PHP-DB] mysql COUNT row results

[Ron Piggott]

Is there a way that 

SELECT COUNT(auto_increment)  as total_subscribers , `email` FROM `table` 

may exist within the same query and provide more than 1 row of search results?  
When I run a query like this the COUNT portion of the result is allowing only 1 
to be selected.  My desire is to have the the COUNT result appended to each row.

Thoughts anyone?  Ron


The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info  

[PHP-DB] MySQL Unbuffered Query Behavior Change

[Nicholas Williams]

I was previously on PHP 5.1.6 and was using the following code:


  $dbr = mysql_unbuffered_query($query, $this-con);

while($row = mysql_fetch_array($dbr, $assoc ? MYSQL_ASSOC : MYSQL_NUM))

   $this-result[] = $row;

$this-rows = mysql_num_rows($dbr);


It worked properly. The documentation at
http://www.php.net/manual/en/function.mysql-num-rows.php indicates that
mysql_num_rows won't return the correct result on unbuffered queries until
all of the rows had been fetched, but since I was looping through and
fetching all rows with mysql_fetch_array, it worked properly and wasn't a
problem.


I've upgraded to PHP 5.3.5 and now mysql_num_rows always returns 0. I am
fetching all rows before calling mysql_num_rows, just like the documentation
says to do, so mysql_num_rows should return the correct result. Why is it
not? Is the documentation wrong now (should it have been updated to say that
mysql_num_rows now NEVER works with unbuffered queries instead of saying you
have to fetch all rows first)? Is there a bug in PHP? Or am I doing
something wrong and it just coincidentally worked before?


Thanks,


Nick

[PHP-DB] Mysql 5.5 and PHP Mysql API Version 5.1.41

[Matthias Laug]

Hey there,

I've just migrated to Mysql 5.5 from source and it works like a charm. Still 
every now and then (in intervals of approximatly an hour) I get the following 
error:

Error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2013] 
Lost connection to MySQL server at 'reading initial communication packet', 
system error: 110' in 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php:129
 Stack trace: #0 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(129):
 PDO-__construct('mysql:port=6664...', '#', '#', Array) #1 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Mysql.php(96):
 Zend_Db_Adapter_Pdo_Abstract-_connect() #2 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Abstract.php(448):
 Zend_Db_Adapter_Pdo_Mysql-_connect() #3 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(238):
 Zend_Db_Adapter_Abstract-query('SET NAMES 'utf8...', Array) #4 
/home/ydadmin/build/lieferando.de/11720/application/Bootstrap.php(99): 
Zend_Db_Adapter_Pdo_Abstract-query('SET NAMES 'utf8...') #5 
/home/ydadmin/build/lieferando.de/11720/library/Zend/App in 
/home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php
 on line 144

It is like any queue is running full and this is the result, but actually no 
clue. My first guess is the API Version of the mysql client I am using with the 
precompiled php binaries (Ubuntu 10.10 Server, PHP 5.3.2-1ubuntu4.7 with 
Suhosin-Patch (cli) (built: Jan 12 2011 18:36:55))

Does anyone else have the same problems?

Thanks for any help,
Matthias
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] MySQL: Creating a database with

[Alexander Schunk]

Hello,

i want to create a database with php.

A look in the php manual says that you need a special 4.x MySQL
version for using
mysql_create_db().

I am getting error message: Call to undefined function mysql_create_db().

When is this function defined and in what version of MySQL?

yours sincerly

Alexander Schunk

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

Re: [PHP-DB] MySQL: Creating a database with

[Artur Ejsmont]

You probably miss the mysql extension or have different one than you are
calling.

Please call
?php
php_info();
?

in script to see what extensions are loaded. if there is some other module
supporting mysql just use different way to run sql.

otherwise you need to look into php.ini and see if module is available and
activate it. If you dont have one and you are on debian like system you
might be able to install it from a system package.

Art

On 18 May 2010 09:18, Alexander Schunk asch...@gmail.com wrote:

 Hello,

 i want to create a database with php.

 A look in the php manual says that you need a special 4.x MySQL
 version for using
 mysql_create_db().

 I am getting error message: Call to undefined function mysql_create_db().

 When is this function defined and in what version of MySQL?

 yours sincerly

 Alexander Schunk

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

Re: [PHP-DB] MySQL Wildcard

[Onur Yerlikaya]

function getOptGrps($ID=null){
 if ($ID == All) {
$ID = %; // % = MySQL multi character wildcard
 }
 $extraClause = ;
 if(!is_null($ID))
 {
 $extraClause =  WHERE OptGrpID LIKE '$ID' ;
 }
 $q = SELECT * FROM .OPT_GRPS_TABLE.$extraClause;
 $result = $this-query($q);
 /* Error occurred, return given name by default */
 if(!$result || (mysql_numrows($result)  1)){
return NULL;
 }
 /* Return result array */
 $array_results = mysql_fetch_array($result);
 return $array_results;
  }



On May 7, 2010, at 11:46 AM, Karl DeSaulniers wrote:

 ...

--
Onur Yerlikaya




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

Re: [PHP-DB] MySQL Wildcard

[Karl DeSaulniers]

On May 7, 2010, at 4:16 AM, Onur Yerlikaya wrote:


function getOptGrps($ID=null){
 if ($ID == All) {
$ID = %; // % = MySQL multi character wildcard
 }
 $extraClause = ;
 if(!is_null($ID))
 {
 $extraClause =  WHERE OptGrpID LIKE '$ID' ;
 }
 $q = SELECT * FROM .OPT_GRPS_TABLE.$extraClause;
 $result = $this-query($q);
 /* Error occurred, return given name by default */
 if(!$result || (mysql_numrows($result)  1)){
return NULL;
 }
 /* Return result array */
 $array_results = mysql_fetch_array($result);
 return $array_results;
  }



On May 7, 2010, at 11:46 AM, Karl DeSaulniers wrote:


...


--
Onur Yerlikaya


Thanks Onur, but same error message.
It is not setting the % as a wild card.
Not sure the way around this.


Karl






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



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 Wildcard

[Onur Yerlikaya]

so sorry

function getOptGrps($ID=null){
$extraClause = ;
if($ID != All)
{
$extraClause =  WHERE OptGrpID LIKE '$ID' ;
}
$q = SELECT * FROM .OPT_GRPS_TABLE.$extraClause;
$result = $this-query($q);
/* Error occurred, return given name by default */
if(!$result || (mysql_numrows($result)  1)){
   return NULL;
}
/* Return result array */
$array_results = mysql_fetch_array($result);
return $array_results;
 }


On May 7, 2010, at 12:36 PM, Karl DeSaulniers wrote:

 
 On May 7, 2010, at 4:16 AM, Onur Yerlikaya wrote:
 
 function getOptGrps($ID=null){
 if ($ID == All) {
$ID = %; // % = MySQL multi character wildcard
 }
 $extraClause = ;
 if(!is_null($ID))
 {
 $extraClause =  WHERE OptGrpID LIKE '$ID' ;
 }
 $q = SELECT * FROM .OPT_GRPS_TABLE.$extraClause;
 $result = $this-query($q);
 /* Error occurred, return given name by default */
 if(!$result || (mysql_numrows($result)  1)){
return NULL;
 }
 /* Return result array */
 $array_results = mysql_fetch_array($result);
 return $array_results;
  }
 
 
 
 On May 7, 2010, at 11:46 AM, Karl DeSaulniers wrote:
 
 ...
 
 --
 Onur Yerlikaya
 
 Thanks Onur, but same error message.
 It is not setting the % as a wild card.
 Not sure the way around this.
 
 
 Karl
 
 
 
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 Karl DeSaulniers
 Design Drumm
 http://designdrumm.com
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 

--
Onur Yerlikaya




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

Re: [PHP-DB] mySQL date query

[Chaitanya Yanamadala]

what is the format of the date u are storing ?

Chaitanya



On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott 
ron.pigg...@actsministries.org wrote:

 I am trying to write a mySQL query on my stats table.  I am trying to
 determine the number of records (users) during a 7 day period ending
 yesterday.  I always to keep it current ... Yesterday will keep changing.
 In other words I want to know the number of users who accessed the web
 site during seven full days.

 This is the beginning of the query.  The date column is date.

 SELECT count(`visits`) as users FROM `stats` WHERE `date`

 Thanks, Ron


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

Re: [PHP-DB] mySQL date query

[Chris]

Ron Piggott wrote:

I am trying to write a mySQL query on my stats table.  I am trying to
determine the number of records (users) during a 7 day period ending
yesterday.  I always to keep it current ... Yesterday will keep changing. 
In other words I want to know the number of users who accessed the web

site during seven full days.

This is the beginning of the query.  The date column is date.

SELECT count(`visits`) as users FROM `stats` WHERE `date`


The mysql manual is a good place to start.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub

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


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

Re: [PHP-DB] mySQL date query

[Ron Piggott]

It is in a 'date' column type -MM-DD

 what is the format of the date u are storing ?

 Chaitanya



 On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott 
 ron.pigg...@actsministries.org wrote:

 I am trying to write a mySQL query on my stats table.  I am trying to
 determine the number of records (users) during a 7 day period ending
 yesterday.  I always to keep it current ... Yesterday will keep
 changing.
 In other words I want to know the number of users who accessed the web
 site during seven full days.

 This is the beginning of the query.  The date column is date.

 SELECT count(`visits`) as users FROM `stats` WHERE `date`

 Thanks, Ron


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

[Chaitanya Yanamadala]

try this

SELECT DATE_ADD('2008-01-02', INTERVAL -7 DAY);


Chaitanya




On Tue, Apr 13, 2010 at 12:01 PM, Ron Piggott 
ron.pigg...@actsministries.org wrote:

 It is in a 'date' column type -MM-DD

  what is the format of the date u are storing ?
 
  Chaitanya
 
 
 
  On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott 
  ron.pigg...@actsministries.org wrote:
 
  I am trying to write a mySQL query on my stats table.  I am trying to
  determine the number of records (users) during a 7 day period ending
  yesterday.  I always to keep it current ... Yesterday will keep
  changing.
  In other words I want to know the number of users who accessed the web
  site during seven full days.
 
  This is the beginning of the query.  The date column is date.
 
  SELECT count(`visits`) as users FROM `stats` WHERE `date`
 
  Thanks, Ron
 
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 

RE: [PHP-DB] Mysql completing Query fast but mysql-query() takes long time to return even single selects/updates

[David Murphy]

I see this occurring randomly on different quries with different
indexes/tables.

It appears like php is taking a lot longer than mysql's transmission time to
cache  the result be it  bool TRUE/FALSE ora select of 1 - 50 records. 
While it does not happen all the time, since  from the MySQL side I can see
how long each step was and how long it  took to even transmit the results to
the php server. I am thinking it a small memory hole we do not normaly see
for some reason but  there are no  sqlng parameters even that seem to be
able to help in this situation. 

Also since this is is not really repeatable  as is occurs only sometimes and
not with a  predictable  frequency it would be very hard to do the roll
back.
Also since its  in the mysql class of functions I cant even debug into it to
try to see much of anything (since its c++ code not userland functions).

David

-Original Message-
From: Chris [mailto:dmag...@gmail.com] 
Sent: Sunday, April 11, 2010 6:26 PM
To: David Murphy
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Mysql completing Query fast but mysql-query() takes
long time to return even single selects/updates

David Murphy wrote:
 As you can see  PHP claims  it took 20 seconds for mysql-query() to
return
 but   mysql think is took around 1.0s
 
  
 This is from our application 
 I enabled profile in mysql to determine why an update took 20seconds.  As
 you can see  MySQL reported no where near that amount of duration took
 place. 
 Is there any way I can dig into php and determine why  mysql client libs
are
 so slow (this is not using mysqlnd but  mysql-client-libs on CentOS using
 5.3.2)

Is this a one-off thing or is it happening all the time?

If it's a one-off thing it could be a spurious result (maybe someone 
else was doing a mysqldump when your query ran, the dump blocks your 
query)..

What sort of mysql table is it? if it's innodb you can try it in a 
transaction and roll it back:

begin;
update blah;
rollback;

see how long it takes.

if it takes a short time in the mysql client, then try it in a php 
script from your other server.

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


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

Re: [PHP-DB] Mysql completing Query fast but mysql-query() takes long time to return even single selects/updates

[Chris]

David Murphy wrote:

As you can see  PHP claims  it took 20 seconds for mysql-query() to return
but   mysql think is took around 1.0s

 
This is from our application 
I enabled profile in mysql to determine why an update took 20seconds.  As

you can see  MySQL reported no where near that amount of duration took
place. 
Is there any way I can dig into php and determine why  mysql client libs are

so slow (this is not using mysqlnd but  mysql-client-libs on CentOS using
5.3.2)


Is this a one-off thing or is it happening all the time?

If it's a one-off thing it could be a spurious result (maybe someone 
else was doing a mysqldump when your query ran, the dump blocks your 
query)..


What sort of mysql table is it? if it's innodb you can try it in a 
transaction and roll it back:


begin;
update blah;
rollback;

see how long it takes.

if it takes a short time in the mysql client, then try it in a php 
script from your other server.


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


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

Re: [PHP-DB] Mysql completing Query fast but mysql-query() takes long time to return even single selects/updates

[kranthi]

just a pointer.. have you enabled php profiling to see if it actually
mysql-query() that takes 20 secs ?

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

[PHP-DB] mysql/php time out issue

[Stephen Sunderlin]

I'm running php 5.1.6 with mysql  5.0.45 on CentOS 5.3  box trying to 
loop through a while statement to send about 3000 email using 
phpmailer.  It's worked well in the past but after an upgrade it seems 
to be timing out now  after 200-300 emails  over 1 minute or two.   I've 
added set_time_limit(30) within the while loop to reset the time out.  
I've added a sleep(1) statement to throttle the program (I've tried 
sending with and without these additions).  I've altered several config 
files to see if it would send to completion to no avail:

mysql.connect_timeout = -1
memory_limit = 100M
max_input_time = 60
max_input_time = 600
max_input_time = 0

and the following mysql config:

key_buffer = 256M
max_allowed_packet = 50M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
tmp_table_size = 40M
join_buffer_size = 1M
query_cache_limit = 12M
query_cache_size= 32M
query_cache_type = 1
max_connections = 60
thread_stack = 128K
thread_concurrency = 4

Any where else I should be looking.  Thanks!


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

Re: [PHP-DB] mysql/php time out issue

[Chaitanya Yanamadala]

have u checked max_execution_time


Chaitanya



On Fri, Apr 9, 2010 at 7:17 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net wrote:

 I'm running php 5.1.6 with mysql  5.0.45 on CentOS 5.3  box trying to loop
 through a while statement to send about 3000 email using phpmailer.  It's
 worked well in the past but after an upgrade it seems to be timing out now
  after 200-300 emails  over 1 minute or two.   I've added set_time_limit(30)
 within the while loop to reset the time out.  I've added a sleep(1)
 statement to throttle the program (I've tried sending with and without these
 additions).  I've altered several config files to see if it would send to
 completion to no avail:
 mysql.connect_timeout = -1
 memory_limit = 100M
 max_input_time = 60
 max_input_time = 600
 max_input_time = 0

 and the following mysql config:

 key_buffer = 256M
 max_allowed_packet = 50M
 table_cache = 1024
 sort_buffer_size = 1M
 read_buffer_size = 1M
 read_rnd_buffer_size = 4M
 myisam_sort_buffer_size = 64M
 thread_cache_size = 64
 tmp_table_size = 40M
 join_buffer_size = 1M
 query_cache_limit = 12M
 query_cache_size= 32M
 query_cache_type = 1
 max_connections = 60
 thread_stack = 128K
 thread_concurrency = 4

 Any where else I should be looking.  Thanks!


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

[PHP-DB] Mysql completing Query fast but mysql-query() takes long time to return even single selects/updates

[David Murphy]

As you can see  PHP claims  it took 20 seconds for mysql-query() to return
but   mysql think is took around 1.0s

 
This is from our application 
I enabled profile in mysql to determine why an update took 20seconds.  As
you can see  MySQL reported no where near that amount of duration took
place. 
Is there any way I can dig into php and determine why  mysql client libs are
so slow (this is not using mysqlnd but  mysql-client-libs on CentOS using
5.3.2)
 
 
04/06/2010 14:54:54 20.6899s  UPDATE `calls` SET `Result`='Busy' WHERE
`CallID`='144786'
 | Status   | Duration | CPU_user | CPU_system |
Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out |
Messages_sent | Messages_received | Page_faults_major | Page_faults_minor |
Swaps | 
 


--
 | starting | 0.39 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | checking permissions | 0.08 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | Opening tables   | 0.10 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | System lock  | 0.05 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | Table lock   | 0.06 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | init | 0.36 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | Updating | 0.99 | 0.001000 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | end  | 0.23 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | query end| 0.04 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | freeing items| 0.007410 | 0.00 | 0.00   | 4
| 1   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | logging slow query   | 0.04 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 | 
 | cleaning up  | 0.04 | 0.00 | 0.00   | 0
| 0   | 0| 0 | 0 | 0
| 0 | 0 | 0 |

 

 

This is to a  remote system ( but on same  GigE switch), however mysql
profiling   would log transit type if this was a select . 

 

Thanks

David Murphy

Re: [PHP-DB] mysql/php time out issue

[Chaitanya Yanamadala]

these are the entire core values of mine
try making a replica of the required..
i am able to send for 3 mails. are u using an smtp sever to send mails..


allow_call_time_pass_referenceOnOn allow_url_fopenOnOn allow_url_includeOnOn
always_populate_raw_post_dataOffOff arg_separator.input
arg_separator.outputamp;amp; asp_tagsOffOff auto_append_file*no value**no
value* auto_globals_jitOnOn auto_prepend_file*no value**no value* browscap
C:\xampp\php\extras\browscap.iniC:\xampp\php\extras\browscap.ini
default_charset*no value**no value* default_mimetypetext/htmltext/html
define_syslog_variablesOffOff disable_classes*no value**no value*
disable_functions*no value**no value* display_errorsOnOn
display_startup_errorsOnOn doc_root*no value**no value* docref_ext*no value*
*no value* docref_root*no value**no value* enable_dlOnOn error_append_string
*no value**no value* error_log*no value**no value* error_prepend_string*no
value**no value* error_reporting2251922519 exit_on_timeoutOffOff expose_php
OnOn extension_dirC:\xampp\php\extC:\xampp\php\ext file_uploadsOnOn
highlight.bg#FF#FF highlight.comment#FF8000#FF8000 highlight.default
#BB#BB highlight.html#00#00 highlight.keyword#007700#007700
highlight.string#DD#DD html_errorsOnOn ignore_repeated_errorsOffOff
ignore_repeated_sourceOffOff ignore_user_abortOffOff implicit_flushOffOff
include_path.;C:\xampp\php\PEAR.;C:\xampp\php\PEAR log_errorsOffOff
log_errors_max_len10241024 magic_quotes_gpcOnOn magic_quotes_runtimeOffOff
magic_quotes_sybaseOffOff mail.add_x_headerOffOff
mail.force_extra_parameters*no value**no value* mail.log*no value**no value*
max_execution_time60006000 max_input_nesting_level6464 max_input_time600600
memory_limit128M128M open_basedir*no value**no value* output_buffering*no
value**no value* output_handler*no value**no value* post_max_size128M128M
precision1414 realpath_cache_size16K16K realpath_cache_ttl120120
register_argc_argvOnOn register_globalsOffOff register_long_arraysOffOff
report_memleaksOnOn report_zend_debugOnOn request_order*no value**no value*
safe_modeOffOff safe_mode_exec_dir*no value**no value* safe_mode_gidOffOff
safe_mode_include_dir*no value**no value* sendmail_from*no value**no value*
sendmail_path*no value**no value* serialize_precision100100 short_open_tagOn
On


smtp_port2525 sql.safe_modeOffOff track_errorsOffOff
unserialize_callback_func*no value**no value* upload_max_filesize128M128M
upload_tmp_dirC:\xampp\tmpC:\xampp\tmp user_dir*no value**no value*
user_ini.cache_ttl300300 user_ini.filename.user.ini.user.ini variables_order
GPCSGPCS xmlrpc_error_number00 xmlrpc_errorsOffOff y2k_complianceOnOn
zend.enable_gcOnOn

Chaitanya



On Fri, Apr 9, 2010 at 8:20 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net wrote:

 Set that to 6oo. Stopped after 125 loops in under a minute.

 _
 Stephen Sunderlin
 2162 Broadway, 4th Fl.
 New  York, NY 10024
 (212) 799-3753

 -Original Message-
 From: Chaitanya Yanamadala dr.virus.in...@gmail.com
 Date: Fri, 09 Apr 2010 19:26:22
 To: Stephen Sunderlinstephen.sunder...@verizon.net
 Cc: php-db@lists.php.net
 Subject: Re: [PHP-DB] mysql/php time out issue

 have u checked max_execution_time


 Chaitanya



 On Fri, Apr 9, 2010 at 7:17 PM, Stephen Sunderlin 
 stephen.sunder...@verizon.net wrote:

  I'm running php 5.1.6 with mysql  5.0.45 on CentOS 5.3  box trying to
 loop
  through a while statement to send about 3000 email using phpmailer.  It's
  worked well in the past but after an upgrade it seems to be timing out
 now
   after 200-300 emails  over 1 minute or two.   I've added
 set_time_limit(30)
  within the while loop to reset the time out.  I've added a sleep(1)
  statement to throttle the program (I've tried sending with and without
 these
  additions).  I've altered several config files to see if it would send to
  completion to no avail:
  mysql.connect_timeout = -1
  memory_limit = 100M
  max_input_time = 60
  max_input_time = 600
  max_input_time = 0
 
  and the following mysql config:
 
  key_buffer = 256M
  max_allowed_packet = 50M
  table_cache = 1024
  sort_buffer_size = 1M
  read_buffer_size = 1M
  read_rnd_buffer_size = 4M
  myisam_sort_buffer_size = 64M
  thread_cache_size = 64
  tmp_table_size = 40M
  join_buffer_size = 1M
  query_cache_limit = 12M
  query_cache_size= 32M
  query_cache_type = 1
  max_connections = 60
  thread_stack = 128K
  thread_concurrency = 4
 
  Any where else I should be looking.  Thanks!
 
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 

Re: [PHP-DB] mysql/php time out issue

[ad]

Thanks Chaitanya.

I've tried both Sendmail and SMTP.

I'll let you know how this works out.

On 4/9/2010 10:57 AM, Chaitanya Yanamadala wrote:

these are the entire core values of mine
try making a replica of the required..
i am able to send for 3 mails. are u using an smtp sever to send 
mails..



allow_call_time_pass_reference  On  On
allow_url_fopen On  On
allow_url_include   On  On
always_populate_raw_post_data   Off Off
arg_separator.input
arg_separator.outputamp;   amp;
asp_tagsOff Off
auto_append_file/no value/  /no value/
auto_globals_jitOn  On
auto_prepend_file   /no value/  /no value/
browscap 	C:\xampp\php\extras\browscap.ini 
C:\xampp\php\extras\browscap.ini

default_charset /no value/  /no value/
default_mimetypetext/html   text/html
define_syslog_variables Off Off
disable_classes /no value/  /no value/
disable_functions   /no value/  /no value/
display_errors  On  On
display_startup_errors  On  On
doc_root/no value/  /no value/
docref_ext  /no value/  /no value/
docref_root /no value/  /no value/
enable_dl   On  On
error_append_string /no value/  /no value/
error_log   /no value/  /no value/
error_prepend_string/no value/  /no value/
error_reporting 22519   22519
exit_on_timeout Off Off
expose_php  On  On
extension_dir   C:\xampp\php\extC:\xampp\php\ext
file_uploadsOn  On
highlight.bg http://highlight.bg#FF #FF
highlight.comment   #FF8000 #FF8000
highlight.default   #BB #BB
highlight.html  #00 #00
highlight.keyword   #007700 #007700
highlight.string#DD #DD
html_errors On  On
ignore_repeated_errors  Off Off
ignore_repeated_source  Off Off
ignore_user_abort   Off Off
implicit_flush  Off Off
include_path.;C:\xampp\php\PEAR .;C:\xampp\php\PEAR
log_errors  Off Off
log_errors_max_len  10241024
magic_quotes_gpcOn  On
magic_quotes_runtimeOff Off
magic_quotes_sybase Off Off
mail.add_x_header   Off Off
mail.force_extra_parameters /no value/  /no value/
mail.log/no value/  /no value/
max_execution_time  60006000
max_input_nesting_level 64  64
max_input_time  600 600
memory_limit128M128M
open_basedir/no value/  /no value/
output_buffering/no value/  /no value/
output_handler  /no value/  /no value/
post_max_size   128M128M
precision   14  14
realpath_cache_size 16K 16K
realpath_cache_ttl  120 120
register_argc_argv  On  On
register_globalsOff Off
register_long_arraysOff Off
report_memleaks On  On
report_zend_debug   On  On
request_order   /no value/  /no value/
safe_mode   Off Off
safe_mode_exec_dir  /no value/  /no value/
safe_mode_gid   Off Off
safe_mode_include_dir   /no value/  /no value/
sendmail_from   /no value/  /no value/
sendmail_path   /no value/  /no value/
serialize_precision 100 100
short_open_tag  On  On



smtp_port   25  25
sql.safe_mode   Off Off
track_errorsOff Off
unserialize_callback_func   /no value/  /no value/
upload_max_filesize 128M128M
upload_tmp_dir  C:\xampp\tmpC:\xampp\tmp
user_dir/no value/  /no value/
user_ini.cache_ttl  300 300
user_ini.filename   .user.ini   .user.ini
variables_order GPCSGPCS
xmlrpc_error_number 0   0
xmlrpc_errors   Off Off
y2k_compliance  On  On
zend.enable_gc  On  On



Chaitanya



On Fri, Apr 9, 2010 at 8:20 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net mailto:stephen.sunder...@verizon.net 
wrote:


Set that to 6oo. Stopped after 125 loops in under a minute.

_
Stephen Sunderlin
2162 Broadway, 4th Fl.
New  York, NY 10024
(212) 799-3753

-Original Message-
From: Chaitanya Yanamadala dr.virus.in...@gmail.com
mailto:dr.virus.in...@gmail.com
Date: Fri, 09 Apr 2010 19:26:22
To: Stephen Sunderlinstephen.sunder...@verizon.net
mailto:stephen.sunder...@verizon.net
Cc: php-db@lists.php.net mailto:php-db@lists.php.net
Subject: Re: [PHP-DB] mysql/php time out issue

have u checked max_execution_time


Chaitanya



On Fri, Apr 9, 2010 at 7:17 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net
mailto:stephen.sunder...@verizon.net wrote:

 I'm running php 5.1.6 with mysql  5.0.45 on CentOS 5.3  box
trying to loop
 through a while statement to send about 3000 email using
phpmailer.  It's
 worked well in the past but after an upgrade it seems to be
timing out now

Re: [PHP-DB] mysql/php time out issue

[ad]

expose_php  On  On
extension_dir   C:\xampp\php\extC:\xampp\php\ext
file_uploadsOn  On
highlight.bg http://highlight.bg#FF #FF
highlight.comment   #FF8000 #FF8000
highlight.default   #BB #BB
highlight.html  #00 #00
highlight.keyword   #007700 #007700
highlight.string#DD #DD
html_errors On  On
ignore_repeated_errors  Off Off
ignore_repeated_source  Off Off
ignore_user_abort   Off Off
implicit_flush  Off Off
include_path.;C:\xampp\php\PEAR .;C:\xampp\php\PEAR
log_errors  Off Off
log_errors_max_len  10241024
magic_quotes_gpcOn  On
magic_quotes_runtimeOff Off
magic_quotes_sybase Off Off
mail.add_x_header   Off Off
mail.force_extra_parameters /no value/  /no value/
mail.log/no value/  /no value/
max_execution_time  60006000
max_input_nesting_level 64  64
max_input_time  600 600
memory_limit128M128M
open_basedir/no value/  /no value/
output_buffering/no value/  /no value/
output_handler  /no value/  /no value/
post_max_size   128M128M
precision   14  14
realpath_cache_size 16K 16K
realpath_cache_ttl  120 120
register_argc_argv  On  On
register_globalsOff Off
register_long_arraysOff Off
report_memleaks On  On
report_zend_debug   On  On
request_order   /no value/  /no value/
safe_mode   Off Off
safe_mode_exec_dir  /no value/  /no value/
safe_mode_gid   Off Off
safe_mode_include_dir   /no value/  /no value/
sendmail_from   /no value/  /no value/
sendmail_path   /no value/  /no value/
serialize_precision 100 100
short_open_tag  On  On



smtp_port   25  25
sql.safe_mode   Off Off
track_errorsOff Off
unserialize_callback_func   /no value/  /no value/
upload_max_filesize 128M128M
upload_tmp_dir  C:\xampp\tmpC:\xampp\tmp
user_dir/no value/  /no value/
user_ini.cache_ttl  300 300
user_ini.filename   .user.ini   .user.ini
variables_order GPCSGPCS
xmlrpc_error_number 0   0
xmlrpc_errors   Off Off
y2k_compliance  On  On
zend.enable_gc  On  On



Chaitanya



On Fri, Apr 9, 2010 at 8:20 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net mailto:stephen.sunder...@verizon.net 
wrote:


Set that to 6oo. Stopped after 125 loops in under a minute.

_
Stephen Sunderlin
2162 Broadway, 4th Fl.
New  York, NY 10024
(212) 799-3753

-Original Message-
From: Chaitanya Yanamadala dr.virus.in...@gmail.com
mailto:dr.virus.in...@gmail.com
Date: Fri, 09 Apr 2010 19:26:22
Cc: php-db@lists.php.net mailto:php-db@lists.php.net
Subject: Re: [PHP-DB] mysql/php time out issue

have u checked max_execution_time


Chaitanya



On Fri, Apr 9, 2010 at 7:17 PM, Stephen Sunderlin 
stephen.sunder...@verizon.net
mailto:stephen.sunder...@verizon.net wrote:

 I'm running php 5.1.6 with mysql  5.0.45 on CentOS 5.3  box
trying to loop
 through a while statement to send about 3000 email using
phpmailer.  It's
 worked well in the past but after an upgrade it seems to be
timing out now
  after 200-300 emails  over 1 minute or two.   I've added
set_time_limit(30)
 within the while loop to reset the time out.  I've added a sleep(1)
 statement to throttle the program (I've tried sending with and
without these
 additions).  I've altered several config files to see if it
would send to
 completion to no avail:
 mysql.connect_timeout = -1
 memory_limit = 100M
 max_input_time = 60
 max_input_time = 600
 max_input_time = 0

 and the following mysql config:

 key_buffer = 256M
 max_allowed_packet = 50M
 table_cache = 1024
 sort_buffer_size = 1M
 read_buffer_size = 1M
 read_rnd_buffer_size = 4M
 myisam_sort_buffer_size = 64M
 thread_cache_size = 64
 tmp_table_size = 40M
 join_buffer_size = 1M
 query_cache_limit = 12M
 query_cache_size= 32M
 query_cache_type = 1
 max_connections = 60
 thread_stack = 128K
 thread_concurrency = 4

 Any where else I should be looking.  Thanks!


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

[PHP-DB] MySQL vs. Session array

[3dgtech]

Quick question. I have an AJAX component that fires a like query for  
each key up event (since it's a like % query there is no indexing or  
caching possible). While everything works fine, I was contemplaining  
downloading the ~1500 rows into a session var as array, offloading the  
(seperate) mysql server and utilizing the (underutilized) php server.  
Any thoughts?

PS: maybe even offload it to a JavaScript function??

Thank you,
Eli

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

Re: [PHP-DB] MySQL vs. Session array

[Peter Lind]

On 8 April 2010 00:13, 3dgtech syst...@3dgtech.com wrote:
 Quick question. I have an AJAX component that fires a like query for each
 key up event (since it's a like % query there is no indexing or caching
 possible). While everything works fine, I was contemplaining downloading the
 ~1500 rows into a session var as array, offloading the (seperate) mysql
 server and utilizing the (underutilized) php server. Any thoughts?

You'd be better off storing stuff in memcache than session, would be
my opinion - session is per user, so you wouldn't get much cache reuse
out of it.

-- 
hype
WWW: http://plphp.dk / http://plind.dk
LinkedIn: http://www.linkedin.com/in/plind
Flickr: http://www.flickr.com/photos/fake51
BeWelcome: Fake51
Couchsurfing: Fake51
/hype

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

Re: [PHP-DB] Mysql query

[Chris]

ron.pigg...@actsministries.org wrote:

The query from my previous post was only part of a larger query.  This is
the entire query:

SELECT GREATEST( IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(
1239508800 ) ) , INTERVAL LEAST( 14, (

SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =1 ) )
DAY )
AND CURDATE( ) = DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (

SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2 ) )
DAY )
AND CURDATE( ) = DATE( 2009 -12 -25 ) , 2, 0
)
) AS verse_application


It took me a while to work out what this was trying to do, that's 
complicated.


Reformatted a little:

SELECT
  GREATEST(
IF
(
  CURDATE() =
DATE_SUB(
DATE(FROM_UNIXTIME(1239508800)),
INTERVAL LEAST(14, (SELECT 1)) DAY)
  AND CURDATE() = DATE(FROM_UNIXTIME(1239508800)),
  1,
  0
),
IF
(
  CURDATE() =
DATE_SUB(
  DATE('2009-12-25'),
  INTERVAL LEAST(14, (SELECT 2)) DAY)
  AND CURDATE() = DATE('2009-12-25'),
  2,
  0
)
  ) AS verse_application;

(which isn't much better in email).

You're not getting '2' because the second part is returning 0.

I substituted dummy variables for your subqueries (select 1 and select 2).

SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2;

What does that return by itself?

that is what your query will run instead of my 'select 2'.

That in turn goes into the

select least(14, result_from_above_query);

and takes that away from date('2009-12-25');

If the current date is not in that range, it will return 0.

Here's the second part of your query isolated for you to test:

SELECT
  IF
(
  CURDATE() =
DATE_SUB(
  DATE('2009-12-25'),
  INTERVAL LEAST(14, (SELECT COUNT(*) FROM 
verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY)

AND CURDATE() = DATE('2009-12-25'),
2,
0
  )
;


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


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

Re: [PHP-DB] Mysql query

[ron . piggott]

Chris I spent 3 hours debugging this query myself.  I got as far as
putting '' around 2009-12-25 to get the desired results.  I just added the
word DATE.  It works, thanks.

Chris I run a verse of the day e-mail list.  This query determines the
logic of the content (year round, Easter and Christmas).  It is quite the
query to say the least.

Thanks for your help.

Sincerely,

Ron

www.TheVerseOfTheDay.info

 ron.pigg...@actsministries.org wrote:
 The query from my previous post was only part of a larger query.  This
 is
 the entire query:

 SELECT GREATEST( IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(
 1239508800 ) ) , INTERVAL LEAST( 14, (

 SELECT COUNT( * )
 FROM `verse_of_the_day_Bible_verses`
 WHERE seasonal_use =1 ) )
 DAY )
 AND CURDATE( ) = DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
 CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (

 SELECT COUNT( * )
 FROM `verse_of_the_day_Bible_verses`
 WHERE seasonal_use =2 ) )
 DAY )
 AND CURDATE( ) = DATE( 2009 -12 -25 ) , 2, 0
 )
 ) AS verse_application

 It took me a while to work out what this was trying to do, that's
 complicated.

 Reformatted a little:

 SELECT
GREATEST(
  IF
   (
CURDATE() =
   DATE_SUB(
   DATE(FROM_UNIXTIME(1239508800)),
   INTERVAL LEAST(14, (SELECT 1)) DAY)
 AND CURDATE() = DATE(FROM_UNIXTIME(1239508800)),
1,
0
   ),
  IF
   (
 CURDATE() =
   DATE_SUB(
 DATE('2009-12-25'),
 INTERVAL LEAST(14, (SELECT 2)) DAY)
AND CURDATE() = DATE('2009-12-25'),
 2,
 0
  )
) AS verse_application;

 (which isn't much better in email).

 You're not getting '2' because the second part is returning 0.

 I substituted dummy variables for your subqueries (select 1 and select 2).

 SELECT COUNT( * )
 FROM `verse_of_the_day_Bible_verses`
 WHERE seasonal_use =2;

 What does that return by itself?

 that is what your query will run instead of my 'select 2'.

 That in turn goes into the

 select least(14, result_from_above_query);

 and takes that away from date('2009-12-25');

 If the current date is not in that range, it will return 0.

 Here's the second part of your query isolated for you to test:

 SELECT
IF
  (
CURDATE() =
  DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT COUNT(*) FROM
 verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY)
  AND CURDATE() = DATE('2009-12-25'),
  2,
  0
)
 ;


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





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

[PHP-DB] Mysql query

[ron . piggott]

Does anyone see anything wrong with this query?  Do I have one of the , =
or  mixed up?  The purpose is to figure out if it is within 14 days of
Christmas AND if there is content for Christmas available.  2 is symbolic
in the database being Christmas.  Ron


IF( CURDATE( ) = DATE_SUB( DATE(2009-12-25) , INTERVAL LEAST( 14, (
SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use
=2 ) ) DAY ) AND CURDATE( ) = DATE(2009-12-25) , 2, 0 )


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

[PHP-DB] Mysql query

[ron . piggott]

The query from my previous post was only part of a larger query.  This is
the entire query:

SELECT GREATEST( IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(
1239508800 ) ) , INTERVAL LEAST( 14, (

SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =1 ) )
DAY )
AND CURDATE( ) = DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (

SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2 ) )
DAY )
AND CURDATE( ) = DATE( 2009 -12 -25 ) , 2, 0
)
) AS verse_application

The result should be a 2.  I am getting a 0.

When I try the first subquery / IF statement the error message is:

#1064 - 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
'IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(1239508800) ) , INTERVAL
LEAST( '

The error message for the Christmas check which should be giving me a 2
result is:

#1064 - 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
'IF( CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (

SELE' at line 1

Any help out there please?

Ron


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

[PHP-DB] mySQL query syntax error

[Ron Piggott]

I am getting the following error message:

#1054 - Unknown column 'customers.email' in 'order clause'

from the query below --- I don't understand why.  Would someone help me
please?  Ron


SELECT 'first_name', 'last_name', 'email'
FROM (

(

SELECT `firstname` , `lastname` , `buyer_email` 
FROM `paypal_payment_info` 
WHERE `datecreation` = '$two_weeks_ago'
GROUP BY `buyer_email` 
)
UNION ALL (

SELECT `mail_order_address`.`first_name` ,
`mail_order_address`.`last_name` , `mail_order_address`.`email` 
FROM `mail_order_address` 
INNER JOIN `mail_order_payment` ON `mail_order_address`.`reference` =
`mail_order_payment`.`mail_order_address_reference` 
WHERE `mail_order_payment`.`payment_received` = '$two_weeks_ago'
GROUP BY `mail_order_address`.`email` 
)
) AS customers
ORDER BY `customers`.`email` ASC 



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

Re: [PHP-DB] mySQL query syntax error

[Ron Piggott]

I found the problem.  The first subquery needed AS aliases.  I am
learning more of what mySQL is capable of and appreciate the help.  Ron

-Original Message-
From: TG tg-...@gryffyndevelopment.com
To: ron.pigg...@actsministries.org
Subject: Re: [PHP-DB] mySQL query syntax error
Date: Sat, 14 Nov 2009 07:17:53 -0500


Only half awake and don't really see the problem with customers.email since 
you alias the two selects and union as 'customers'.  But since you don't 
have any other tables in your primary select, you can drop the 
`customers` part and just say ORDER BY email ASC.

- Original Message -
From: Ron Piggott ron.pigg...@actsministries.org
To: PHP DB php-db@lists.php.net
Date: Sat, 14 Nov 2009 03:23:47 -0500
Subject: [PHP-DB] mySQL query syntax error

 
 I am getting the following error message:
 
 #1054 - Unknown column 'customers.email' in 'order clause'
 
 from the query below --- I don't understand why.  Would someone help me
 please?  Ron
 
 
 SELECT 'first_name', 'last_name', 'email'
 FROM (
 
 (
 
 SELECT `firstname` , `lastname` , `buyer_email` 
 FROM `paypal_payment_info` 
 WHERE `datecreation` = '$two_weeks_ago'
 GROUP BY `buyer_email` 
 )
 UNION ALL (
 
 SELECT `mail_order_address`.`first_name` ,
 `mail_order_address`.`last_name` , `mail_order_address`.`email` 
 FROM `mail_order_address` 
 INNER JOIN `mail_order_payment` ON `mail_order_address`.`reference` =
 `mail_order_payment`.`mail_order_address_reference` 
 WHERE `mail_order_payment`.`payment_received` = '$two_weeks_ago'
 GROUP BY `mail_order_address`.`email` 
 )
 ) AS customers
 ORDER BY `customers`.`email` ASC 
 
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 

[PHP-DB] Mysql multiple queries Vs. Query in multi-dimensional array

[Drew Stokes]

Hello all,
I have an enterprise application that needs to be optimized/re- 
engineered.  In the process of re-designing the framework, i stumbled  
across the idea of storing large mysql result sets in multi- 
dimensional arrays for easy/quick reference.


Basically, my application polls the database looking for 1 of up to 3  
documents for each day in the reporting period (1 to 4 weeks) for  
multiple clients, and follows a complex hierarchy based on the  
document and related information returned.  One of the areas i imagine  
i am losing resources is in the queries sent to the database where no  
result is returned.


My idea involves grabbing all of each document within the reporting  
period, and storing each type in its own multi-dimensional array with  
all other documents of the same type.  Then, throughout the report, i  
would check array keys for results instead of querying the database.   
It sounds like a sensible solution for taking some of the 2+  
queries of each report, but i'm not sure if the gains would be offset  
by the demands on RAM due to the large arrays.


Drew Stokes: Web Development at MPCS
contact | d...@mpcompliance.com | 818.792.4135
The information contained in this email message and its attachments is  
intended only for the private and confidential use of the recipient(s)  
named above, unless the sender expressly agrees otherwise. If the  
reader of this message is not the intended recipient and/or you have  
received this email in error, you must take no action based on the  
information in this email and you are hereby notified that any  
dissemination, misuse, copying, or disclosure of this communication is  
strictly prohibited. If you have received this communication in error,  
please notify us immediately by email and delete the original message.

Re: [PHP-DB] Mysql multiple queries Vs. Query in multi-dimensional array

[Chris]

Drew Stokes wrote:

Hello all,
I have an enterprise application that needs to be 
optimized/re-engineered.  In the process of re-designing the framework, 
i stumbled across the idea of storing large mysql result sets in 
multi-dimensional arrays for easy/quick reference.


Basically, my application polls the database looking for 1 of up to 3 
documents for each day in the reporting period (1 to 4 weeks) for 
multiple clients, and follows a complex hierarchy based on the document 
and related information returned.  One of the areas i imagine i am 
losing resources is in the queries sent to the database where no result 
is returned.


My idea involves grabbing all of each document within the reporting 
period, and storing each type in its own multi-dimensional array with 
all other documents of the same type.  Then, throughout the report, i 
would check array keys for results instead of querying the database.  It 
sounds like a sensible solution for taking some of the 2+ queries of 
each report, but i'm not sure if the gains would be offset by the 
demands on RAM due to the large arrays.


I can't see any report doing that number of queries (unless each query 
is just selecting a different column in the same table *maybe*), so 
looking at reducing that number is a good idea.


The arrays may work - but it depends. Are you fetching the same thing 
from the db each time or are you only using each piece of information once?


eg

$title = select title from table where id='x';
...
$title = select title from table where id='x';

If you are, then using an array of some sort would work nicely.

If you're doing the above for each entry in the db, maybe it would be 
quicker to retrieve everything and process in php.


$all_titles = select title from table;

It really depends on what the report is doing, what you're fetching from 
the database (and how) - so doing a new report using your new ideas 
(even with hardcoded values) is about the only way to find out for sure 
which is going to be better.


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


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

[PHP-DB] MySQL - LIMIT 1 on primary KEY - makes sense?

[Martin Zvarík]

SELECT * FROM xxx WHERE primary_id=123 LIMIT 1

Is it useless (LIMIT 1) ?

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

Re: [PHP-DB] MySQL - LIMIT 1 on primary KEY - makes sense?

[Bastien Koert]

2009/6/3 Martin Zvarík mzva...@gmail.com:
 SELECT * FROM xxx WHERE primary_id=123 LIMIT 1

 Is it useless (LIMIT 1) ?

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




It doesn't do anything...you are already returning just the one row
since there can only be one primary key record
-- 

Bastien

Cat, the other other white meat

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

Re: [PHP-DB] MySQL - LIMIT 1 on primary KEY - makes sense?

[Martin Zvarík]

 Bastien Koert napsal(a):

2009/6/3 Martin Zvarík mzva...@gmail.com:
  

SELECT * FROM xxx WHERE primary_id=123 LIMIT 1

Is it useless (LIMIT 1) ?

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


It doesn't do anything...you are already returning just the one row
since there can only be one primary key record
  
I know, but I thought it might have had a little performance + for the 
MySQL... but I guess not.


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

Re: [PHP-DB] MySQL: did anyone start a transaction?

[Bastien Koert]

On Fri, May 8, 2009 at 6:23 PM, Bogdan Stancescu gu...@moongate.ro wrote:

 Hello all,

 I asked this question on php-general last month
 (http://marc.info/?t=12404923034r=1w=2), and received some answers
 -- but I was directed to this list, so here.

 My question is: can I tell programatically whether the next query I'm
 going to execute is already part of a transaction, using MySQL?

 The context is thus: I'm writing a PHP library which attempts to
 seamlessly merge database objects and PHP objects. Architecture
 discussion aside (which I'm more than open to, if anyone cares enough to
 talk), I need to know whether the code outside the library has already
 started a transaction or not. I need to know that because I need to
 execute both PHP and SQL from a single external call -- if either one of
 my internal PHP or SQL calls fail, I need to revert to the original
 state before the external call. And since transactions can't be nested,
 I can't unconditionally start a new transaction when the external call
 is being initiated -- therefore I need to know whether the programmer
 has already started a transaction or not.

 Thank you,
 Bogdan

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



What about creating a global variable or singleton class to hold the start
of the transaction? This is something worth reading, as it indicates that
the application should manage it (
http://stackoverflow.com/questions/319788/how-do-detect-that-transaction-has-already-been-started)
and that transaction fall outside of OO scope.
-- 

Bastien

Cat, the other other white meat

[PHP-DB] MySQL: did anyone start a transaction?

[Bogdan Stancescu]

Hello all,

I asked this question on php-general last month
(http://marc.info/?t=12404923034r=1w=2), and received some answers
-- but I was directed to this list, so here.

My question is: can I tell programatically whether the next query I'm
going to execute is already part of a transaction, using MySQL?

The context is thus: I'm writing a PHP library which attempts to
seamlessly merge database objects and PHP objects. Architecture
discussion aside (which I'm more than open to, if anyone cares enough to
talk), I need to know whether the code outside the library has already
started a transaction or not. I need to know that because I need to
execute both PHP and SQL from a single external call -- if either one of
my internal PHP or SQL calls fail, I need to revert to the original
state before the external call. And since transactions can't be nested,
I can't unconditionally start a new transaction when the external call
is being initiated -- therefore I need to know whether the programmer
has already started a transaction or not.

Thank you,
Bogdan

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

[PHP-DB] MySQL + PHP on WinXP x64 = ??

[YVES SUCAET]

Hi List,

I'm experiencing the problem as described at
http://forums.mysql.com/read.php?37,39779,136287#msg-136287

I'm using the following script to test:

?php
$p = 0;
$n = 0;
for ($i = 0; $i  120; $i++) {
$conn = mysql_connect(www.myserver.org, user, secret);
if ($conn) {
echo Success!\n;
mysql_close($conn);
$p++;
} else {
echo Failed test $i\n;
$n++;
}
sleep(1);
}
echo \n$p successes, $n failures\n;
?

Results are very unpredictable. E.g. out of 120 connection attempts, I'll have
114 successes and 6 failures.

The solution that's mentioned is to enable connection pooling, yet since I'm
using the x64 edition, that doesn't work for me.

Has anybody else run into this problem? And how did you solve it?

Thanks in advance,

Yves

-- Original Message --
Received: Mon, 09 Mar 2009 05:55:55 AM CDT
From: Daniel Carrera daniel.carr...@theingots.org
To: php-db@lists.php.net
Subject: Re: [PHP-DB] Re : Problem with PDO exceptions

Neil Smith [MVP, Digital media] wrote:
 When you create your DB connection $db, follow the connection line 
 directly after with this :
 $db-setAttribute(PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION);
 
 The default is I believe PDO::ERRMODE_SILENT which is confusing to most 
 people the first time.


Thanks! It works now.

Cheers,
Daniel.

-- 
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 + PHP on WinXP x64 = ??

[Chris]

YVES SUCAET wrote:

Hi List,

I'm experiencing the problem as described at
http://forums.mysql.com/read.php?37,39779,136287#msg-136287

I'm using the following script to test:

?php
$p = 0;
$n = 0;
for ($i = 0; $i  120; $i++) {
$conn = mysql_connect(www.myserver.org, user, secret);
if ($conn) {
echo Success!\n;
mysql_close($conn);
$p++;
} else {
echo Failed test $i\n;
$n++;
}
sleep(1);
}
echo \n$p successes, $n failures\n;
?

Results are very unpredictable. E.g. out of 120 connection attempts, I'll have
114 successes and 6 failures.


Well to be honest my question would be why do you need 120 connections 
in one script?



The solution that's mentioned is to enable connection pooling, yet since I'm
using the x64 edition, that doesn't work for me.


Why doesn't it work for you? What package were you going to use for 
connection pooling? Is there an alternative package to try?


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


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

Re: [PHP-DB] MySQL + PHP on WinXP x64 = ?? - clarification

[YVES SUCAET]

Hi Chris,

I don't need 120 concurrent connections. The problem arises from subsequently
opening connections and the script is a way to simulate the problem: the
first
100 connections e.g. open up fine; then the next 5 mess up; then the next 7
are good to go again etc...

I'm using the basic PHP php_mysql.dll driver. I've used MDB2 from PEAR as
well: same result. I think both of these use the C API, and not the
ODBC-driver.

Yves

-- Original Message --
Received: Thu, 12 Mar 2009 04:30:33 PM CDT
From: Chris dmag...@gmail.com
To: YVES SUCAET yves.suc...@usa.netCc: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQL + PHP on WinXP x64 = ??

YVES SUCAET wrote:
 Hi List,
 
 I'm experiencing the problem as described at
 http://forums.mysql.com/read.php?37,39779,136287#msg-136287
 
 I'm using the following script to test:
 
 ?php
 $p = 0;
 $n = 0;
 for ($i = 0; $i  120; $i++) {
   $conn = mysql_connect(www.myserver.org, user, secret);
   if ($conn) {
   echo Success!\n;
   mysql_close($conn);
   $p++;
   } else {
   echo Failed test $i\n;
   $n++;
   }
   sleep(1);
 }
 echo \n$p successes, $n failures\n;
 ?
 
 Results are very unpredictable. E.g. out of 120 connection attempts, I'll
have
 114 successes and 6 failures.

Well to be honest my question would be why do you need 120 connections 
in one script?

 The solution that's mentioned is to enable connection pooling, yet since
I'm
 using the x64 edition, that doesn't work for me.

Why doesn't it work for you? What package were you going to use for 
connection pooling? Is there an alternative package to try?

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








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

Re: [PHP-DB] MySQL + PHP on WinXP x64 = ?? - clarification

[Chris]

YVES SUCAET wrote:

Hi Chris,

I don't need 120 concurrent connections. The problem arises from subsequently
opening connections and the script is a way to simulate the problem: the
first
100 connections e.g. open up fine; then the next 5 mess up; then the next 7
are good to go again etc...

I'm using the basic PHP php_mysql.dll driver. I've used MDB2 from PEAR as
well: same result. I think both of these use the C API, and not the
ODBC-driver.


Have you changed the max_connections setting?

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connections

If you have, what is mysql_error() reporting when it can't connect?

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


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

RE: [PHP-DB] MySQL query executes outside of PHP, but not in PHP

[Dave.McGovern]

snip

  error_log(Hey, the SQL is: $sql);

  Then look in your php error log (you do have error logging enabled,
  right?)
[McGovern, Dave] Enabled, yes.  Usefully enabled, no (correcting this
helped a lot).

  If that SQL in the error log is fine, then your problem is
  $client2-multi_query($sql) -- what does THAT return?  What SHOULD it
  return?  What are you expecting it to return?  Does it return what
you
  thought it did?
[McGovern, Dave] It was returning ''.  I was expecting 1.

  When you do if ($var) it can sometimes have unexpected results.  If
$var
  is an empty string, it's still true, and executes.  I don't know that
  multi_query SHOULD return, or how to determine if it throws an error,
but
  that's one place to start.

  Next, if multi_query worked, then this line is suspect:

if ($result = $client2-use_result()) {

  This will always result in TRUE, as the assignment will always
succeed.
  Change it to:

if (($result = $client2-use_result())) {

  (added parenthesis)
 What DB library are you using?
[McGovern, Dave] php_mysqli.dll

---

Peter Beckman  Internet
Guy
beck...@angryox.com
http://www.angryox.com/
---


[McGovern, Dave] 
Hi, Peter -
Thanks for the suggestions.  I did have error logging enabled, but I
wasn't writing any of my own errors to the log, so it wasn't doing me
much good. Once I started doing this, I was able to trace the source of
the error to the fact that this particular SQL script was meant to be
aimed at a different schema. The reason it worked in MySQL Query Browser
was that the required schema was already selected in the Query Browser
(ouch).

Sorry to have wasted the list's time with what turned out to be a silly
human error.
Dave


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

[PHP-DB] MySQL query executes outside of PHP, but not in PHP

[Dave.McGovern]

Hi,
I am running: PHP 5.2.8, Apache 2.2.11, MySQL 5.1.30 on Win32/XP.

I have a number of queries on my page which are very similar in
structure, and they all work except for the following one. 

$mysql['process'] = $client2-real_escape_string($clean['process']);

$sql = SELECT f.name, f.description
FROM files f, file_mapping m, processes p
WHERE m.file_id = f.id
AND p.name = '{$mysql['process']}'
AND m.process_id = p.id
AND m.io_flag = 'I';

if ($client2-multi_query($sql)) {
  echo 'h3 class=H3-OTMSMain Input Files/h3';
  do {
if ($result = $client2-use_result()) {
 while ($input = $result-fetch_row()) {
   $filename = $input[0];
   $descr = $input[1];
   echo 'pspan class=FileName'.$filename.'/span'.'
'.$descr.'/p';
 }
$result-close();
}
} while ($client2-next_result());
}


If I echo the $sql, and then run it in MySQL directly, it works fine.  I
have tried replacing the variable in the WHERE clause with a hardcoded
value and and have tried replacing this query with a very basic query
with no variable, but nothing has worked. No error message is returned.

Any suggestions as to what I might check?  Here's an example of an echo
of the following $sql that runs OK in MySQL Query Browser:
SELECT f.name, f.description FROM files f, file_mapping m, processes p
WHERE m.file_id = f.id AND p.name = 'BCOM1AC' AND m.process_id = p.id
AND m.io_flag = 'I'

Thanks,
Dave


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

Re: [PHP-DB] MySQL query executes outside of PHP, but not in PHP

[Peter Beckman]

On Fri, 23 Jan 2009, dave.mcgov...@sungard.com wrote:


Hi,
I am running: PHP 5.2.8, Apache 2.2.11, MySQL 5.1.30 on Win32/XP.

I have a number of queries on my page which are very similar in
structure, and they all work except for the following one.

$mysql['process'] = $client2-real_escape_string($clean['process']);

$sql = SELECT f.name, f.description
   FROM files f, file_mapping m, processes p
   WHERE m.file_id = f.id
   AND p.name = '{$mysql['process']}'
   AND m.process_id = p.id
   AND m.io_flag = 'I';

if ($client2-multi_query($sql)) {
 echo 'h3 class=H3-OTMSMain Input Files/h3';
 do {
   if ($result = $client2-use_result()) {
while ($input = $result-fetch_row()) {
  $filename = $input[0];
  $descr = $input[1];
  echo 'pspan class=FileName'.$filename.'/span'.'
'.$descr.'/p';
}
   $result-close();
   }
   } while ($client2-next_result());
   }


If I echo the $sql, and then run it in MySQL directly, it works fine.  I
have tried replacing the variable in the WHERE clause with a hardcoded
value and and have tried replacing this query with a very basic query
with no variable, but nothing has worked. No error message is returned.

Any suggestions as to what I might check?  Here's an example of an echo
of the following $sql that runs OK in MySQL Query Browser:
SELECT f.name, f.description FROM files f, file_mapping m, processes p
WHERE m.file_id = f.id AND p.name = 'BCOM1AC' AND m.process_id = p.id
AND m.io_flag = 'I'


 error_log(Hey, the SQL is: $sql);

 Then look in your php error log (you do have error logging enabled,
 right?)

 If that SQL in the error log is fine, then your problem is
 $client2-multi_query($sql) -- what does THAT return?  What SHOULD it
 return?  What are you expecting it to return?  Does it return what you
 thought it did?

 When you do if ($var) it can sometimes have unexpected results.  If $var
 is an empty string, it's still true, and executes.  I don't know that
 multi_query SHOULD return, or how to determine if it throws an error, but
 that's one place to start.

 Next, if multi_query worked, then this line is suspect:


   if ($result = $client2-use_result()) {


 This will always result in TRUE, as the assignment will always succeed.
 Change it to:


   if (($result = $client2-use_result())) {


 (added parenthesis)

 What DB library are you using?

---
Peter Beckman  Internet Guy
beck...@angryox.com http://www.angryox.com/
---

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

Re: [PHP-DB] MySQL Conditional Trigger

[Chris]

Aleksandar Vojnovic wrote:

You could try with the  operator.
!=, Not equal operator


Same issue as using '=':

mysql select 2  null;
+---+
| 2  null |
+---+
|  NULL |
+---+
1 row in set (0.00 sec)

Comparing anything with null returns null. (I'm not asking for a 
solution, I'm stating it as fact).



Or you could try it this way - ISNULL(col_or_data_to_check).
Example:
mysql SELECT ISNULL(1+1);
+-+
| ISNULL(1+1) |
+-+
|   0 |
+-+
1 row in set (0.02 sec)


You can also use 'coalesce' to set default value for null columns.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

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


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

Re: [PHP-DB] MySQL Conditional Trigger

[Aleksandar Vojnovic]

You could try with the  operator.
!=, Not equal operator

Or you could try it this way - ISNULL(col_or_data_to_check).
Example:
mysql SELECT ISNULL(1+1);
+-+
| ISNULL(1+1) |
+-+
|   0 |
+-+
1 row in set (0.02 sec)



Aleksander


Chris wrote:

Chris wrote:

unknown compared to anything is unknown.

he was talking about plsql and condition evaluation (ends with true 
or false), your point is absolutely useless.


The context is in the database - my example is in the database. How 
is that useless?


In case you missed it, this is the context:

 Which only works when ShipDate was not NULL to begin with.
 I suppose it evaluates the following to FALSE
 IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
 (not liking the NULL != '2008-10-31' part)

 Please give me the correct syntax.
 TIA


anything compared to NULL is always false


ie - you are wrong.




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

Re: [PHP-DB] MySQL Conditional Trigger

[OKi98]

 Original Message  
Subject: Re: [PHP-DB] MySQL Conditional Trigger
From: Chris dmag...@gmail.com
To: OKi98 ok...@centrum.cz
Date: 8.12.2008 23:09



anything compared to NULL is always false


Actually it's null.

mysql select false = null;
+--+
| false = null |
+--+
| NULL |
+--+
1 row in set (0.01 sec)

mysql select 1 = null;
+--+
| 1 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

mysql select 2 = null;
+--+
| 2 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)


unknown compared to anything is unknown.

he was talking about plsql and condition evaluation (ends with true or 
false), your point is absolutely useless.



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

Re: [PHP-DB] MySQL Conditional Trigger

[Chris]

unknown compared to anything is unknown.

he was talking about plsql and condition evaluation (ends with true or 
false), your point is absolutely useless.


The context is in the database - my example is in the database. How is 
that useless?


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


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

Re: [PHP-DB] MySQL Conditional Trigger

[Chris]

Chris wrote:

unknown compared to anything is unknown.

he was talking about plsql and condition evaluation (ends with true or 
false), your point is absolutely useless.


The context is in the database - my example is in the database. How is 
that useless?


In case you missed it, this is the context:

 Which only works when ShipDate was not NULL to begin with.
 I suppose it evaluates the following to FALSE
 IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
 (not liking the NULL != '2008-10-31' part)

 Please give me the correct syntax.
 TIA


anything compared to NULL is always false


ie - you are wrong.

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


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

Re: [PHP-DB] MySQL Conditional Trigger

[OKi98]

 Original Message  
Subject: [PHP-DB] MySQL Conditional Trigger
From: Dee Ayy [EMAIL PROTECTED]
To: php-db@lists.php.net
Date: 31.10.2008 17:09

I don't think my trigger likes a comparison with a variable which is
NULL.  The docs seem to have a few interesting variations on playing
with NULL and I was hoping someone would just throw me a fish so I
don't have to try every permutation (possibly using CASE, IFNULL,
etc.).

If my ShipDate (which is a date datatype which can be NULL) changes to
a non-null value, I want my IF statement to evaluate to TRUE.
IF NULL changes to aDate : TRUE
IF aDate changes to aDifferentDate : TRUE
IF anyDate changes to NULL : FALSE

In my trigger I have:
...
IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN
...

Which only works when ShipDate was not NULL to begin with.
I suppose it evaluates the following to FALSE
IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
(not liking the NULL != '2008-10-31' part)

Please give me the correct syntax.
TIA

  

anything compared to NULL is always false
NULL = NULL (NULL included) =  false
NULL != anything (NULL included) = false
that's why IS NULL exists

I would go this way:

IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN




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

RE: [PHP-DB] MySQL Conditional Trigger

[Fortuno, Adam]

Dee,

If all you're trying to code is that a value of NULL equates to FALSE
and a date value (whatever date value) equates to true, you can use
something like this:

If ($MyVariable) {
//... true path blah...
} else {
//... false path blah...
}

You can use this because NULL equates to false. If you prefer something
more concise, try the following:

$MyVariable ? //True : //False

I'm not a PHP guy so take this with a grain of salt. If I'm full of it,
don't hesitate to correct me.

A-

-Original Message-
From: OKi98 [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 08, 2008 4:33 PM
To: Dee Ayy
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQL Conditional Trigger

 Original Message  
Subject: [PHP-DB] MySQL Conditional Trigger
From: Dee Ayy [EMAIL PROTECTED]
To: php-db@lists.php.net
Date: 31.10.2008 17:09
 I don't think my trigger likes a comparison with a variable which is
 NULL.  The docs seem to have a few interesting variations on playing
 with NULL and I was hoping someone would just throw me a fish so I
 don't have to try every permutation (possibly using CASE, IFNULL,
 etc.).

 If my ShipDate (which is a date datatype which can be NULL) changes to
 a non-null value, I want my IF statement to evaluate to TRUE.
 IF NULL changes to aDate : TRUE
 IF aDate changes to aDifferentDate : TRUE
 IF anyDate changes to NULL : FALSE

 In my trigger I have:
 ...
 IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN
 ...

 Which only works when ShipDate was not NULL to begin with.
 I suppose it evaluates the following to FALSE
 IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
 (not liking the NULL != '2008-10-31' part)

 Please give me the correct syntax.
 TIA

   
anything compared to NULL is always false
NULL = NULL (NULL included) =  false
NULL != anything (NULL included) = false
that's why IS NULL exists

I would go this way:

IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN




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

[Chris]

anything compared to NULL is always false


Actually it's null.

mysql select false = null;
+--+
| false = null |
+--+
| NULL |
+--+
1 row in set (0.01 sec)

mysql select 1 = null;
+--+
| 1 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

mysql select 2 = null;
+--+
| 2 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)


unknown compared to anything is unknown.

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


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

RE: [PHP-DB] MySQL Conditional Trigger

[Fortuno, Adam]

All,

Chris makes a good point i.e., unknown compared to anything is
unknown. His comment made me want to clarify my earlier note. In my
earlier example, I wasn't suggesting that-that logic be coded in the
database. I was assuming it would be in the page:

?php

$TestNullValue = NULL;

If ($TestNullValue) {
print Evaluates to true!;
} else {
print Evaluates to false!;
}

?

I tested this, and it worked. Here is why, PHP (like pretty much every
other language) silently casts a NULL to false: 

When converting to boolean, the following values are considered FALSE:

 - the boolean FALSE itself 
 - the integer 0 (zero) 
 - the float 0.0 (zero) 
 - the empty string, and the string 0 
 - an array with zero elements 
 - an object with zero member variables (PHP 4 only)
 - the special type NULL (including unset variables) (Booleans,
php.net, 05 Dec. 2008).

If you were going to code it in the database, I'd suggest something like
this:

--Using T-SQL here...
DECLARE @TestNullValue SMALLDATETIME

SET @TestNullValue = NULL

If (@TestNullValue Is Null) 
 PRINT 'Evaluates to true!';
ELSE
 PRINT 'Evaluates to false!';

In either case, this should have the same net result.

Be Well,
A-

-Original Message-
From: Chris [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 08, 2008 5:10 PM
To: OKi98
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQL Conditional Trigger


 anything compared to NULL is always false

Actually it's null.

mysql select false = null;
+--+
| false = null |
+--+
| NULL |
+--+
1 row in set (0.01 sec)

mysql select 1 = null;
+--+
| 1 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

mysql select 2 = null;
+--+
| 2 = null |
+--+
| NULL |
+--+
1 row in set (0.00 sec)


unknown compared to anything is unknown.

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


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


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

Re: [PHP-DB] MySQL Conditional Trigger

[Chris]

Fortuno, Adam wrote:

All,

Chris makes a good point i.e., unknown compared to anything is
unknown. His comment made me want to clarify my earlier note. In my
earlier example, I wasn't suggesting that-that logic be coded in the
database. I was assuming it would be in the page:

?php

$TestNullValue = NULL;

If ($TestNullValue) {
print Evaluates to true!;
} else {
print Evaluates to false!;
}

?

I tested this, and it worked. Here is why, PHP (like pretty much every
other language) silently casts a NULL to false: 


When converting to boolean, the following values are considered FALSE:

 - the boolean FALSE itself 
 - the integer 0 (zero) 
 - the float 0.0 (zero) 
 - the empty string, and the string 0 
 - an array with zero elements 
 - an object with zero member variables (PHP 4 only)

 - the special type NULL (including unset variables) (Booleans,
php.net, 05 Dec. 2008).


If you use the shortcut php check, but you can do it the longer way.

if ($value !== null) {
...
}

Can you turn off email notifications too please? It's rather annoying 
when it's on a public mailing list.


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


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

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Thodoris]

Man- when the query is fired through the web interface- it rans on
mysql server

max_execution_time - wont' help evn I stop apache itself...
  


Yes it will. Apache will be instructed to stop execution of any script 
by the mod_php module (assuming you are using mod_php). But mysql 
process probably won't stop running (not 100% sure for that) although 
php script has timed out.

The query runs on mysql server - so I have to kill the PID on server itself...
  


In order to do this you will need (in the unix world) to have rights to 
kill mysql processes. That means that you must become either root or the 
mysql user which is not that simple since everything that apache runs is 
usually running as the apache or the www user who can't kill those 
processes (at least if he can't su exec).


I will have to note here that this is a bad practice of doing things...

Thanks
Piyush

On Thu, Oct 16, 2008 at 7:57 AM, Jack van Zanen [EMAIL PROTECTED] wrote:
  

Just put the time out in your PHP.INI file

max_execution_time = 30 ; Maximum execution time of each script, in
seconds




This is probably a solution although you will have to know what to 
expect if you query a table with 3M records. It will be slow and that 
can't change. Try using LIMIT when sending queries to stop mass data 
retrieval.



2008/10/16 Piyush Kumar [EMAIL PROTECTED]


I'm using http://myclient.polarlava.com/ as web query interface for mysql
server

Now I want to add Query Timeout functionality to it
  


Every apache process has a timeout limit you can leave the user wait for 
a page a lifetime.



For that I need to get the PID for last ran mysql query and then using
kill
PID - I can kill the process on MySQL server

  

Sorry but I still can't see why.


Please explain how to do that in PHP Thanks!

Similar to what described @ http://bytes.com/forum/thread156058.html


--
Thanks  Regards,
-Piyush
Mo.: 091-9910904233
Mail: [EMAIL PROTECTED]
Web: http://piyush.me/

-In a world without fences, limits, boundaries and walls, Who needs
Windows and Gates?

  


Although some think that limits are wrong I still have walls around my 
house. Windows are not so bad sometimes everything has its use. Besides 
everyone needs to be annoyed from time to time.

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

  


--
J.A. van Zanen






  


--
Thodoris

[PHP-DB] MySQL Conditional Trigger

[Dee Ayy]

I don't think my trigger likes a comparison with a variable which is
NULL.  The docs seem to have a few interesting variations on playing
with NULL and I was hoping someone would just throw me a fish so I
don't have to try every permutation (possibly using CASE, IFNULL,
etc.).

If my ShipDate (which is a date datatype which can be NULL) changes to
a non-null value, I want my IF statement to evaluate to TRUE.
IF NULL changes to aDate : TRUE
IF aDate changes to aDifferentDate : TRUE
IF anyDate changes to NULL : FALSE

In my trigger I have:
...
IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN
...

Which only works when ShipDate was not NULL to begin with.
I suppose it evaluates the following to FALSE
IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
(not liking the NULL != '2008-10-31' part)

Please give me the correct syntax.
TIA

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

[PHP-DB] MySQL Query Timeout program in PHP

[Piyush Kumar]

I'm using http://myclient.polarlava.com/ as web query interface for mysql server

Now I want to add Query Timeout functionality to it

For that I need to get the PID for last ran mysql query and then using kill
PID - I can kill the process on MySQL server

Please explain how to do that in PHP Thanks!

Similar to what described @ http://bytes.com/forum/thread156058.html


--
Thanks  Regards,
-Piyush
Mo.: 091-9910904233
Mail: [EMAIL PROTECTED]
Web: http://piyush.me/

-In a world without fences, limits, boundaries and walls, Who needs
Windows and Gates?

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

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Chris]

Piyush Kumar wrote:

I'm using http://myclient.polarlava.com/ as web query interface for mysql server

Now I want to add Query Timeout functionality to it

For that I need to get the PID for last ran mysql query and then using kill
PID - I can kill the process on MySQL server

Please explain how to do that in PHP Thanks!

Similar to what described @ http://bytes.com/forum/thread156058.html


Learn some php and convert the perl script?

What area are you having issues with exactly?

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


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

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Jack van Zanen]

Just put the time out in your PHP.INI file

max_execution_time = 30 ; Maximum execution time of each script, in
seconds

2008/10/16 Piyush Kumar [EMAIL PROTECTED]

 I'm using http://myclient.polarlava.com/ as web query interface for mysql
 server

 Now I want to add Query Timeout functionality to it

 For that I need to get the PID for last ran mysql query and then using kill
 PID - I can kill the process on MySQL server

 Please explain how to do that in PHP Thanks!

 Similar to what described @ http://bytes.com/forum/thread156058.html


 --
 Thanks  Regards,
 -Piyush
 Mo.: 091-9910904233
 Mail: [EMAIL PROTECTED]
 Web: http://piyush.me/

 -In a world without fences, limits, boundaries and walls, Who needs
 Windows and Gates?

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




-- 
J.A. van Zanen

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Piyush Kumar]

Man- when the query is fired through the web interface- it rans on
mysql server

max_execution_time - wont' help evn I stop apache itself...

The query runs on mysql server - so I have to kill the PID on server itself...

Thanks
Piyush

On Thu, Oct 16, 2008 at 7:57 AM, Jack van Zanen [EMAIL PROTECTED] wrote:
 Just put the time out in your PHP.INI file

 max_execution_time = 30 ; Maximum execution time of each script, in
 seconds

 2008/10/16 Piyush Kumar [EMAIL PROTECTED]

 I'm using http://myclient.polarlava.com/ as web query interface for mysql
 server

 Now I want to add Query Timeout functionality to it

 For that I need to get the PID for last ran mysql query and then using
 kill
 PID - I can kill the process on MySQL server

 Please explain how to do that in PHP Thanks!

 Similar to what described @ http://bytes.com/forum/thread156058.html


 --
 Thanks  Regards,
 -Piyush
 Mo.: 091-9910904233
 Mail: [EMAIL PROTECTED]
 Web: http://piyush.me/

 -In a world without fences, limits, boundaries and walls, Who needs
 Windows and Gates?

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




 --
 J.A. van Zanen




-- 
Thanks  Regards,
-Piyush
Mo.: 091-9910904233
Mail: [EMAIL PROTECTED]
Web: http://piyush.me/

-In a world without fences, limits, boundaries and walls, Who needs
Windows and Gates?

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

Fwd: [PHP-DB] MySQL Query Timeout program in PHP

[Piyush Kumar]

-- Forwarded message --
From: Piyush Kumar [EMAIL PROTECTED]
Date: Thu, Oct 16, 2008 at 9:09 AM
Subject: Re: [PHP-DB] MySQL Query Timeout program in PHP
To: Chris [EMAIL PROTECTED]


Hi Chris,

As I mentioned in my query I want to Kill only the query ran by web
client not all queries running on the server.

The perl script kill all queries - which are been running from for
last 120(or any threshold set)

Issues:
How do I get the PID of my last select query ran from the web query
interface..?? Any php function..?

Thanks
Piyush

On Thu, Oct 16, 2008 at 7:35 AM, Chris [EMAIL PROTECTED] wrote:
 Piyush Kumar wrote:

 I'm using http://myclient.polarlava.com/ as web query interface for mysql
 server

 Now I want to add Query Timeout functionality to it

 For that I need to get the PID for last ran mysql query and then using
 kill
 PID - I can kill the process on MySQL server

 Please explain how to do that in PHP Thanks!

 Similar to what described @ http://bytes.com/forum/thread156058.html

 Learn some php and convert the perl script?

 What area are you having issues with exactly?

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





--
Thanks  Regards,
-Piyush
Mo.: 091-9910904233
Mail: [EMAIL PROTECTED]
Web: http://piyush.me/

-In a world without fences, limits, boundaries and walls, Who needs
Windows and Gates?



-- 
Thanks  Regards,
-Piyush
Mo.: 091-9910904233
Mail: [EMAIL PROTECTED]
Web: http://piyush.me/

-In a world without fences, limits, boundaries and walls, Who needs
Windows and Gates?

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

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Chris]

Piyush Kumar wrote:

Hi Chris,

As I mentioned in my query I want to Kill only the query ran by web
client not all queries running on the server.

The perl script kill all queries - which are been running from for
last 120(or any threshold set)

Issues:
How do I get the PID of my last select query ran from the web query
interface..?? Any php function..?


No, there isn't.

show processlist;

should give you some info.

It won't give you the whole query, only the start of it.

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


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

Re: [PHP-DB] MySQL Query Timeout program in PHP

[Chris]

Piyush Kumar wrote:

show full processlist ; gives me whole query -- but I want some php
function -- like mysql_info() -- to return the PID of last run query


Please do not email me directly. Always send to the mailing list.

If it's not listed under php.net/mysql somewhere, then it's not 
available as a native php function.


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


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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Tim Hawkins]

Until TUDBC is available under an accredited FOSS license, nobody in  
their right mind is going to use it in any project that

may need to be ipr encumbrement  free at a future date.

Posting solutions that pertain to a proprietary technology on a list  
predominately dedicated to technologies that do meet that

requirement is bordering on being classified as commercial spam.

On 11 Oct 2008, at 01:52, Post-No-Reply TUDBC wrote:


I kindly disagree. The original post asked How to use OUT or INOUT
parameters of MySQL's stored procedures in PHP? by STF.

To quote STF again in a later post Yes, I've already found that
multi-step way before... I was just wondering if anything got better
since with regard to this. Apparently not.

If you're aware of what developers need to face when dealing with when
trying to get an OUT parameter from a stored procedure, there are
multi-step way workaround which is cumbersome.

My reply is directly offering an alternate way in PHP to solve this
problem faced by the original post.


On 10/10/08, Fergus Gibson [EMAIL PROTECTED] wrote:

2008/10/10 Post-No-Reply TUDBC [EMAIL PROTECTED]:

By using TUDBC (http://www.tudbc.org), you can call stored  
procedures

easily.



Your post was an excellent answer to the question, How do I call
stored procedures easily with TUDBC?  Unfortunately, that is not  
what

the original poster asked.  In fact, no one has ever asked that
question on this list.  Ever.  Posting to the list from a generic
no-reply address seems pretty rude.

But setting aside the irrelevance of your post, the example does not
seem EZ at all.  In fact, it seems quite a bit more complicated  
than

the comparable code for PDO or mysqli, not to mention both
unnecessarily verbose and simultaneously cryptic.


--

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




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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Post TUDBC]

TUDBC has recently moved to open-source GPL, similar to how MySQL
works. (You probably got the impression that it had proprietary
license, maybe because some the obsolete webpages still referred to
the old license, so I have corrected those obsolete web pages.)

I am not familiar with FOSS, but my reading showed that GPL is the
most used open-source license, which was why I chose GPL.

On 10/11/08, Tim Hawkins [EMAIL PROTECTED] wrote:
 Until TUDBC is available under an accredited FOSS license, nobody in their
 right mind is going to use it in any project that
  may need to be ipr encumbrement  free at a future date.

  Posting solutions that pertain to a proprietary technology on a list
 predominately dedicated to technologies that do meet that
  requirement is bordering on being classified as commercial spam.

  On 11 Oct 2008, at 01:52, Post-No-Reply TUDBC wrote:


  I kindly disagree. The original post asked How to use OUT or INOUT
  parameters of MySQL's stored procedures in PHP? by STF.
 
  To quote STF again in a later post Yes, I've already found that
  multi-step way before... I was just wondering if anything got better
  since with regard to this. Apparently not.
 
  If you're aware of what developers need to face when dealing with when
  trying to get an OUT parameter from a stored procedure, there are
  multi-step way workaround which is cumbersome.
 
  My reply is directly offering an alternate way in PHP to solve this
  problem faced by the original post.
 
 
  On 10/10/08, Fergus Gibson [EMAIL PROTECTED] wrote:
 
   2008/10/10 Post-No-Reply TUDBC [EMAIL PROTECTED]:
  
  
By using TUDBC (http://www.tudbc.org), you can call stored procedures
easily.
   
  
  
   Your post was an excellent answer to the question, How do I call
   stored procedures easily with TUDBC?  Unfortunately, that is not what
   the original poster asked.  In fact, no one has ever asked that
   question on this list.  Ever.  Posting to the list from a generic
   no-reply address seems pretty rude.
  
   But setting aside the irrelevance of your post, the example does not
   seem EZ at all.  In fact, it seems quite a bit more complicated than
   the comparable code for PDO or mysqli, not to mention both
   unnecessarily verbose and simultaneously cryptic.
  
  
   --
  
   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
 
 



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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Post-No-Reply TUDBC]

By using TUDBC (http://www.tudbc.org), you can call stored procedures
easily. Better yet, it works on any flavors of PHP (original,
Phalanger, or Quercus). Even better, you would use the same style of
coding for any DBMSes or programming languages. Have fun!

For an example in MySQL (just change the connection string for other DBMSes),

 TestSumOut(IN x int, IN y int, OUT total int)

The PHP codes (using EZ Style) are

$vault = new 
TUDBCConnectionVaultSingle(tudbc:mysqli://localhost//tudbcguest,helloworld/tudbcdemo);
$conn = $vault-getConnection();
$ezsql = $conn-getSQL_EZ(
call TestSumOut(*?int:x*, *?int:y*, *??int:total*),
TestSumOutSprocSQL);
$ezsql-getStatement()-parameterSetValues($x, $y);
$result = $ezsql-getStatement()-executeUpdate();
$total = $ezsql-getStatement()-sprocGetOutputParameter(2)-getInt();
$ezsql-close();
$conn-close();
$vault-close();

You will find other examples in the demo codes on the website.

TUDBC


Below is the declaration of the example stored procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS TestSumOut` $$
CREATE PROCEDURE `TestSumOut`(x int, y int, OUT total int )
BEGIN
set total = x + y;
END $$
DELIMITER;


On 9/19/08, Stanisław T. Findeisen [EMAIL PROTECTED] wrote:
 Thodoris wrote:

  I think that the manual is quite clear on this:
 
  http://dev.mysql.com/doc/refman/5.1/en/call.html
 
  You use a way to query the database like PDO or mysqli and you wite sql.
 

  Yes, I've already found that multi-step way before... I was just wondering
 if anything got better since with regard to this.

  Apparently not.

  STF

 ===
  http://eisenbits.homelinux.net/~stf/ . My PGP key
 fingerprint is:
  9D25 3D89 75F1 DF1D F434  25D7 E87F A1B9 B80F 8062
 ===

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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Fergus Gibson]

2008/10/10 Post-No-Reply TUDBC [EMAIL PROTECTED]:
 By using TUDBC (http://www.tudbc.org), you can call stored procedures
 easily.

Your post was an excellent answer to the question, How do I call
stored procedures easily with TUDBC?  Unfortunately, that is not what
the original poster asked.  In fact, no one has ever asked that
question on this list.  Ever.  Posting to the list from a generic
no-reply address seems pretty rude.

But setting aside the irrelevance of your post, the example does not
seem EZ at all.  In fact, it seems quite a bit more complicated than
the comparable code for PDO or mysqli, not to mention both
unnecessarily verbose and simultaneously cryptic.

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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Post-No-Reply TUDBC]

I kindly disagree. The original post asked How to use OUT or INOUT
parameters of MySQL's stored procedures in PHP? by STF.

To quote STF again in a later post Yes, I've already found that
multi-step way before... I was just wondering if anything got better
since with regard to this. Apparently not.

If you're aware of what developers need to face when dealing with when
trying to get an OUT parameter from a stored procedure, there are
multi-step way workaround which is cumbersome.

My reply is directly offering an alternate way in PHP to solve this
problem faced by the original post.


On 10/10/08, Fergus Gibson [EMAIL PROTECTED] wrote:
 2008/10/10 Post-No-Reply TUDBC [EMAIL PROTECTED]:

  By using TUDBC (http://www.tudbc.org), you can call stored procedures
   easily.


 Your post was an excellent answer to the question, How do I call
  stored procedures easily with TUDBC?  Unfortunately, that is not what
  the original poster asked.  In fact, no one has ever asked that
  question on this list.  Ever.  Posting to the list from a generic
  no-reply address seems pretty rude.

  But setting aside the irrelevance of your post, the example does not
  seem EZ at all.  In fact, it seems quite a bit more complicated than
  the comparable code for PDO or mysqli, not to mention both
  unnecessarily verbose and simultaneously cryptic.


  --

 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 stored procedures OUT or INOUT parameters

[Thodoris]

:

How to use OUT or INOUT parameters of MySQL's stored procedures in PHP?

STF

===
http://eisenbits.homelinux.net/~stf/ . My PGP key fingerprint is:
9D25 3D89 75F1 DF1D F434  25D7 E87F A1B9 B80F 8062
===




I think that the manual is quite clear on this:

http://dev.mysql.com/doc/refman/5.1/en/call.html

You use a way to query the database like PDO or mysqli and you wite sql.

Is there something specific that you want to ask?

--
Thodoris


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

Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Stanisław T. Findeisen]

Thodoris wrote:

I think that the manual is quite clear on this:

http://dev.mysql.com/doc/refman/5.1/en/call.html

You use a way to query the database like PDO or mysqli and you wite sql.


Yes, I've already found that multi-step way before... I was just 
wondering if anything got better since with regard to this.


Apparently not.

STF

===
http://eisenbits.homelinux.net/~stf/ . My PGP key fingerprint is:
9D25 3D89 75F1 DF1D F434  25D7 E87F A1B9 B80F 8062
===

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

[PHP-DB] MySQL stored procedures OUT or INOUT parameters

[Stanisław T. Findeisen]

How to use OUT or INOUT parameters of MySQL's stored procedures in PHP?

STF

===
http://eisenbits.homelinux.net/~stf/ . My PGP key fingerprint is:
9D25 3D89 75F1 DF1D F434  25D7 E87F A1B9 B80F 8062
===


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

[PHP-DB] mysql auto_increment

[Chris Hale]

I am writing a catologe application and i have a problem when it comes 
to the edit product part. I have a table with the catogories and a table 
with manufacturers.
Each table has a id column and a name column. The id column is set up in 
the MySQL to auto_increment, which works fine normally, but i am writing 
this script:


function edit_cat_radio($item_cat_id)
{
   connect();
   $sql = SELECT * FROM cat DISTINGT ORDER BY cat_id;
   $result = mysql_query($sql);
   $k = 1;
   while ($row = mysql_fetch_assoc($result))
   {
   extract($row);
   echo 'label for=',$cat_name,'',$cat_name,'input 
type=radio name=fcat value=',$cat_id,' id=',$cat_id,'';

   if($k == $item_cat_id)
   {
   echo 'checked';
   }
   echo ' /';
   $k++;
   }
return;   
}


This should (in theory) automatically check the radio button of the 
existing catogory. It would work fine; but what seems to mess it up is 
the auto_increment.


If i delete a catogory/manufacturer from the id's remain the same. and 
end up like this:

cat_id cat_name
1 Bridlework
2 Clippers
3 Clothing
4 Dressage Tests
5 DVD/Video/Books
9 Footwear

but if the cat_id is 9 the /while /statement doesnt repeat 9 times so 
the counter never reaches 9.


I don't know if you got all that, its hard to explain.

I would appreciate any help on how to sort this out.

Thanks

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

Re: [PHP-DB] mysql auto_increment

[Evert Lammerts]

I guess this code was not copy pasted from your actual source? It
would generate mysql errors.

Anyway, I think I've an idea of what you want, so here's my two cents :

function edit_cat_radio($item_cat_id) {
connect();
$query = mysql_query(SELECT * FROM cat);
while ($row = mysql_fetch_assoc($query)) {
echo label for=\{$row['cat_name']}\{$row['cat_name']}input
type=\radio\ name=\fcat\ value=\{$row['cat_id']}\
id=\{$row['cat_id']}\  . ($item_cat_id == $row[cat_id] ?
checked=\checked\ : ) . /\n;
}
}



On Sun, Sep 7, 2008 at 11:16 PM, Chris Hale [EMAIL PROTECTED] wrote:
 I am writing a catologe application and i have a problem when it comes to
 the edit product part. I have a table with the catogories and a table with
 manufacturers.
 Each table has a id column and a name column. The id column is set up in the
 MySQL to auto_increment, which works fine normally, but i am writing this
 script:

 function edit_cat_radio($item_cat_id)
 {
   connect();
   $sql = SELECT * FROM cat DISTINGT ORDER BY cat_id;
   $result = mysql_query($sql);
   $k = 1;
   while ($row = mysql_fetch_assoc($result))
   {
   extract($row);
   echo 'label for=',$cat_name,'',$cat_name,'input type=radio
 name=fcat value=',$cat_id,' id=',$cat_id,'';
   if($k == $item_cat_id)
   {
   echo 'checked';
   }
   echo ' /';
   $k++;
   }
 return;   }

 This should (in theory) automatically check the radio button of the existing
 catogory. It would work fine; but what seems to mess it up is the
 auto_increment.

 If i delete a catogory/manufacturer from the id's remain the same. and end
 up like this:
 cat_id cat_name
 1 Bridlework
 2 Clippers
 3 Clothing
 4 Dressage Tests
 5 DVD/Video/Books
 9 Footwear

 but if the cat_id is 9 the /while /statement doesnt repeat 9 times so the
 counter never reaches 9.

 I don't know if you got all that, its hard to explain.

 I would appreciate any help on how to sort this out.

 Thanks

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

[Evert Lammerts]

Pastebin is so much nicer when posting code. Find the code i've sent here:

http://pastebin.com/mc5d611a

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

Re: [PHP-DB] mysql auto_increment

[Evert Lammerts]

Little change - the label was not used in the right way:

http://pastebin.com/m2d98e677

On Sun, Sep 7, 2008 at 11:46 PM, Evert Lammerts
[EMAIL PROTECTED] wrote:
 I guess this code was not copy pasted from your actual source? It
 would generate mysql errors.

 Anyway, I think I've an idea of what you want, so here's my two cents :

 function edit_cat_radio($item_cat_id) {
connect();
$query = mysql_query(SELECT * FROM cat);
while ($row = mysql_fetch_assoc($query)) {
echo label 
 for=\{$row['cat_name']}\{$row['cat_name']}input
 type=\radio\ name=\fcat\ value=\{$row['cat_id']}\
 id=\{$row['cat_id']}\  . ($item_cat_id == $row[cat_id] ?
 checked=\checked\ : ) . /\n;
}
 }



 On Sun, Sep 7, 2008 at 11:16 PM, Chris Hale [EMAIL PROTECTED] wrote:
 I am writing a catologe application and i have a problem when it comes to
 the edit product part. I have a table with the catogories and a table with
 manufacturers.
 Each table has a id column and a name column. The id column is set up in the
 MySQL to auto_increment, which works fine normally, but i am writing this
 script:

 function edit_cat_radio($item_cat_id)
 {
   connect();
   $sql = SELECT * FROM cat DISTINGT ORDER BY cat_id;
   $result = mysql_query($sql);
   $k = 1;
   while ($row = mysql_fetch_assoc($result))
   {
   extract($row);
   echo 'label for=',$cat_name,'',$cat_name,'input type=radio
 name=fcat value=',$cat_id,' id=',$cat_id,'';
   if($k == $item_cat_id)
   {
   echo 'checked';
   }
   echo ' /';
   $k++;
   }
 return;   }

 This should (in theory) automatically check the radio button of the existing
 catogory. It would work fine; but what seems to mess it up is the
 auto_increment.

 If i delete a catogory/manufacturer from the id's remain the same. and end
 up like this:
 cat_id cat_name
 1 Bridlework
 2 Clippers
 3 Clothing
 4 Dressage Tests
 5 DVD/Video/Books
 9 Footwear

 but if the cat_id is 9 the /while /statement doesnt repeat 9 times so the
 counter never reaches 9.

 I don't know if you got all that, its hard to explain.

 I would appreciate any help on how to sort this out.

 Thanks

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

[PHP-DB] MySQL Reporting Tool

[Micah Gersten]

Anyone use one, hopefully written in PHP?

-- 


Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com


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

Re: [PHP-DB] MySQL Reporting Tool

[Chris]

Micah Gersten wrote:

Anyone use one, hopefully written in PHP?


To report on what?

Or you mean something like crystal reports where you can make up your 
own stuff?


crystal reports supports odbc so will be able to hook into mysql.

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


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

Re: [PHP-DB] MySQL Reporting Tool

[Micah Gersten]

Anything Open Source?  One of my friends uses SQL Server Reporting
Services and was wondering if there is a similar product for MySQL.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Chris wrote:
 Micah Gersten wrote:
 Anyone use one, hopefully written in PHP?

 To report on what?

 Or you mean something like crystal reports where you can make up your
 own stuff?

 crystal reports supports odbc so will be able to hook into mysql.


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

Re: [PHP-DB] MySQL Reporting Tool

[Chris]

Micah Gersten wrote:

Anything Open Source?  One of my friends uses SQL Server Reporting
Services and was wondering if there is a similar product for MySQL.


Don't know any off hand but check out sourceforge. Here's a couple:

http://sourceforge.net/projects/fourfive/
http://sourceforge.net/projects/mydbaccess/

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


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

[PHP-DB] mySQL SELECT query

[Ron Piggott]

Is there a way to make this part of a SELECT query more concise?  

AND ministry_directory.listing_approved NOT LIKE '0' AND
ministry_directory.listing_approved NOT LIKE '4'

I know of the 

IN ( 10, 12 )

command.  Is there something similar available for NOT LIKE?

Ron


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