Re: [PHP-DB] MySQL connection

[Karl DeSaulniers]

That makes complete sense. 
Thank you Joshua.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




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

Re: [PHP-DB] MySQL connection

[Arneson, Joshua]

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

Respectfully,

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

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

Re: [PHP-DB] MySQL connection

[Karl DeSaulniers]

Ah, thanks for the reply Joshua.

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

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

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

Again, TIA!

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




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


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

Re: [PHP-DB] MySQL connection

[Arneson, Joshua]

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

Respectfully,

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

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

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

Re: [PHP-DB] 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

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

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

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

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

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

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

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

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
 
 

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

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

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

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

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

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

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

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

Re: [PHP-DB] mySQL SELECT query

[Evert Lammerts]

In the SQL standard this would be

AND NOT ministry_directory.listing_approved=0 AND NOT
ministry_directory.listing_approved=4

MySQL supports (and there are probably more RDBs that do):

AND ministry_directory.listing_approved!=0 AND
ministry_directory.listing_approved!=4

On Fri, Aug 29, 2008 at 7:09 PM, Ron Piggott [EMAIL PROTECTED] wrote:
 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



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

Re: [PHP-DB] mySQL SELECT query

[Matt Anderton]

or NOT IN (0,4)

-- matt



On Fri, Aug 29, 2008 at 12:19 PM, Evert Lammerts
[EMAIL PROTECTED]wrote:

 In the SQL standard this would be

 AND NOT ministry_directory.listing_approved=0 AND NOT
 ministry_directory.listing_approved=4

 MySQL supports (and there are probably more RDBs that do):

 AND ministry_directory.listing_approved!=0 AND
 ministry_directory.listing_approved!=4

 On Fri, Aug 29, 2008 at 7:09 PM, Ron Piggott [EMAIL PROTECTED]
 wrote:
  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
 
 

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

Re: [PHP-DB] Mysql logs

[Micah Gersten]

You have to edit your my.cnf file.  Here's a link to the docs:
http://dev.mysql.com/doc/refman/5.0/en/log-files.html

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



Manoj Singh wrote:
 Hi All,

 Please help me to set the logging on in mysql. I am using the following
 command when starting mysql:

 service mysqld start --log=/usr/local/


 But logging is not maintained through this command.

 Best Regards,
 Manoj Kumar Singh

   

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

Re: [PHP-DB] Mysql logs

[Manoj Singh]

Hi Micah,

Thanks for help.

Best Regards,
Manoj Kumar Singh

On Thu, Jul 24, 2008 at 11:02 AM, Micah Gersten [EMAIL PROTECTED] wrote:

 You have to edit your my.cnf file.  Here's a link to the docs:
 http://dev.mysql.com/doc/refman/5.0/en/log-files.html

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



 Manoj Singh wrote:
  Hi All,
 
  Please help me to set the logging on in mysql. I am using the following
  command when starting mysql:
 
  service mysqld start --log=/usr/local/
 
 
  But logging is not maintained through this command.
 
  Best Regards,
  Manoj Kumar Singh
 
 

Re: [PHP-DB] MySQL circular buffer

[OKi98]

 (i.e. stack_id  500  stack_id  601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id  500  stack_id  601) is much 
better




What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.
  

tables:
process_table:
   IDProcess PK

mail_table
   IDMail PK
   IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
   Mail TEXT
   From VCH(255)
   TO VCH(255)
   DateModified DATE ON UPDATE CURRENT_TIMESTAMP
   Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);

INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();

label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
   SET IDProcess = $lnIdProcess
   WHERE
  Mailed = 0 AND
  (
   IDProcess IS NULL OR
  (
   IDProcess IS NOT NULL AND
   DateModified = $ldDateExpired
  );
  )
   LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE 
IDProcess = $lnIDProcess)

{
   if (send_mail())
   {
   UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
   }
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;

this way more than one process can send mails, also if one process exits 
prematurely the other can send his emails later.

Time to time run cron:
   DELETE FROM mail_table WHERE Mailed = 1;
   rebuild indexes on mail_table;

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

Re: [PHP-DB] MySQL replication delaying issue

[Daniel Brown]

On Tue, Feb 26, 2008 at 10:49 AM, Lasitha Alawatta [EMAIL PROTECTED] wrote:
 Hello Everybody,

 We have 6 multi-master MySql instances within a LAN , that are replicating
 is a sequential manner.

 Server Environment :  Six identical Linux Enterprise version 4 running,
 servers with having MySQL version 6.

 we are using MySQL multi-master replication method for database
 replication.

 There is a delay (5-7 minutes) of that data replication process. We notice 
 that it's because of MySQL table locking.

 Your comments are highly appreciated.

Ask on a MySQL list.  It has nothing to do with PHP.

http://lists.mysql.com/

Specifically: Replication - http://lists.mysql.com/replication

-- 
/Dan

Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?

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

RE: [PHP-DB] MySQL replication delaying issue

[Lasitha Alawatta]

 

Oky, Sorry for that.. J

 

 

From: Thiago Pojda [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 26, 2008 8:49 PM
To: Lasitha Alawatta
Cc: php-db@lists.php.net
Subject: RES: [PHP-DB] MySQL replication delaying issue

 

I did not say: do not ask on this list.

 

I said: you would get BETTER answers if you asked on a MySQL list.

 



De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] 
Enviada em: terça-feira, 26 de fevereiro de 2008 13:47
Para: Thiago Pojda
Assunto: RE: [PHP-DB] MySQL replication delaying issue

 

Hay, 

If U don't know about that then u can ignore that mail. U need not to answer 
each and every mails in this list.

MOST of the PHP developer are using MYSQL as backend. 

 

That's Y I sent that message to phpresources  list also.

 

 

 

 

 

From: Thiago Pojda [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 26, 2008 8:39 PM
To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RES: [PHP-DB] MySQL replication delaying issue

 

Not trying to be an ass, but wouldn't you get better answers if you asked in a 
MySQL list?

 

I work with PHP, but have near to 0 experience with MySQL.

 



De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] 
Enviada em: terça-feira, 26 de fevereiro de 2008 12:50
Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Assunto: [PHP-DB] MySQL replication delaying issue
Prioridade: Alta

 

Hello Everybody,

 

We have 6 multi-master MySql instances within a LAN , that are replicating is a 
sequential manner.

Server Environment :  Six identical Linux Enterprise version 4 running, servers 
with having MySQL version 6. 

we are using MySQL multi-master replication method for database replication.

 

There is a delay (5-7 minutes) of that data replication process. 

We notice that it's because of MySQL table locking.

 

Your comments are highly appreciated.

 

 

Many thanks in advance and best regards,

 

Lasitha Alawatta

Application Developer

Destinations of the World Holding Establishment

P O Box: 19950

Dubai, United Arab Emirates

( Ph +971 4 295 8510 (Board) / 1464 (Ext.)

7 Fax +971 4 295 8910
+ [EMAIL PROTECTED] 

cid:image002.jpg@01C7A7A4.0AC70A00 http://www.dotw.com/ 

 

 


DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended for the 
addressee only. If you are not the named addressee you must not disclose, copy 
or take
any action in reliance of this transmission and you should notify us as soon as 
possible. If you have received it in error, please contact the message sender 
immediately.
This e-mail and any attachments are believed to be free from viruses but it is 
your responsibility to carry out all necessary virus checks and DOTW accepts no 
liability
in connection therewith. 

This e-mail and all other electronic (including voice) communications from the 
sender's company are for informational purposes only.  No such communication is 
intended
by the sender to constitute either an electronic record or an electronic 
signature or to constitute any agreement by the sender to conduct a transaction 
by electronic means.

 

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

RE: [PHP-DB] MySQL replication delaying issue

[Lasitha Alawatta]

Hello,

 

Thanks a LOT...

 

Best Regards,

Lasitha

 

From: Thiago Pojda [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 26, 2008 9:51 PM
To: Lasitha Alawatta
Cc: php-db@lists.php.net
Subject: RES: [PHP-DB] MySQL replication delaying issue

 

That's ok, good luck with your problem.

 

Perhaps this could help :)

http://forums.mysql.com/read.php?26,197734,197734#msg-197734

 



De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] 
Enviada em: terça-feira, 26 de fevereiro de 2008 14:44
Para: Thiago Pojda
Cc: php-db@lists.php.net
Assunto: RE: [PHP-DB] MySQL replication delaying issue

 

Oky, Sorry for that.. J

 

 

From: Thiago Pojda [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 26, 2008 8:49 PM
To: Lasitha Alawatta
Cc: php-db@lists.php.net
Subject: RES: [PHP-DB] MySQL replication delaying issue

 

I did not say: do not ask on this list.

 

I said: you would get BETTER answers if you asked on a MySQL list.

 



De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] 
Enviada em: terça-feira, 26 de fevereiro de 2008 13:47
Para: Thiago Pojda
Assunto: RE: [PHP-DB] MySQL replication delaying issue

 

Hay, 

If U don't know about that then u can ignore that mail. U need not to answer 
each and every mails in this list.

MOST of the PHP developer are using MYSQL as backend. 

 

That's Y I sent that message to phpresources  list also.

 

 

 

 

 

From: Thiago Pojda [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 26, 2008 8:39 PM
To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RES: [PHP-DB] MySQL replication delaying issue

 

Not trying to be an ass, but wouldn't you get better answers if you asked in a 
MySQL list?

 

I work with PHP, but have near to 0 experience with MySQL.

 



De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] 
Enviada em: terça-feira, 26 de fevereiro de 2008 12:50
Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Assunto: [PHP-DB] MySQL replication delaying issue
Prioridade: Alta

 

Hello Everybody,

 

We have 6 multi-master MySql instances within a LAN , that are replicating is a 
sequential manner.

Server Environment :  Six identical Linux Enterprise version 4 running, servers 
with having MySQL version 6. 

we are using MySQL multi-master replication method for database replication.

 

There is a delay (5-7 minutes) of that data replication process. 

We notice that it's because of MySQL table locking.

 

Your comments are highly appreciated.

 

 

Many thanks in advance and best regards,

 

Lasitha Alawatta

Application Developer

Destinations of the World Holding Establishment

P O Box: 19950

Dubai, United Arab Emirates

( Ph +971 4 295 8510 (Board) / 1464 (Ext.)

7 Fax +971 4 295 8910
+ [EMAIL PROTECTED] 

cid:image002.jpg@01C7A7A4.0AC70A00 http://www.dotw.com/ 

 

 


DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended for the 
addressee only. If you are not the named addressee you must not disclose, copy 
or take
any action in reliance of this transmission and you should notify us as soon as 
possible. If you have received it in error, please contact the message sender 
immediately.
This e-mail and any attachments are believed to be free from viruses but it is 
your responsibility to carry out all necessary virus checks and DOTW accepts no 
liability
in connection therewith. 

This e-mail and all other electronic (including voice) communications from the 
sender's company are for informational purposes only.  No such communication is 
intended
by the sender to constitute either an electronic record or an electronic 
signature or to constitute any agreement by the sender to conduct a transaction 
by electronic means.

 

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

RE: [PHP-DB] mysql data truncation does not cause an error to be thrown

[Vandegrift, Ken]

You may want to check the my.ini setting for the table type you are
using and see if there is a setting in there that needs to be enabled.  

I thought I read once that truncation may happen silently depending on
the my.ini setting.

Just a thought.

Ken Vandegrift
Shari's Management Corporation
Web Developer/Administrator
(Direct) 503-605-4132
[EMAIL PROTECTED]
-Original Message-
From: Andrew Blake [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 7:51 AM
To: Instruct ICC
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
be thrown

Hiya
I could check the length of the field against the entry data and 
javascript myself out of trouble but i was more worried that there is no

error or message when mysql clearly returns one saying i've truncated 
this yet php ignores it completely. It should fail or know about the 
truncation at least !
Cheers for your reply though :-)

Andy

Instruct ICC wrote:
 Using mysql_query if i try to force more data than a field can have
the 
 data is truncated yet no error is throw at all.
 Is there a way round this ?
 Cheers

 Andy
 

 This isn't exactly what you want to hear, but how about validating
your input before submitting a query?

 _
 Boo! Scare away worms, viruses and so much more! Try Windows Live
OneCare!

http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotm
ailnews
   

-- 
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 data truncation does not cause an error to be thrown

[Instruct ICC]

I agree.  And maybe there is an error reporting level that can be set in mysql.

But you should also start sanitizing user input.  And do it on the server side 
(even if you do some on the client side).
Maybe your form's action script could be sent to directly and your javascript 
validation sidestepped.

 Date: Thu, 8 Nov 2007 15:50:38 +
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 CC: php-db@lists.php.net
 Subject: Re: [PHP-DB] mysql data truncation does not cause an error to be 
 thrown
 
 Hiya
 I could check the length of the field against the entry data and 
 javascript myself out of trouble but i was more worried that there is no 
 error or message when mysql clearly returns one saying i've truncated 
 this yet php ignores it completely. It should fail or know about the 
 truncation at least !
 Cheers for your reply though :-)
 
 Andy
 
 Instruct ICC wrote:
  Using mysql_query if i try to force more data than a field can have the 
  data is truncated yet no error is throw at all.
  Is there a way round this ?
  Cheers
 
  Andy
  
 
  This isn't exactly what you want to hear, but how about validating your 
  input before submitting a query?
 
  _
  Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare!
  http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews

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

_
Help yourself to FREE treats served up daily at the Messenger Café. Stop by 
today.
http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline

RE: [PHP-DB] mysql data truncation does not cause an error to be thrown

[Instruct ICC]

 Using mysql_query if i try to force more data than a field can have the 
 data is truncated yet no error is throw at all.
 Is there a way round this ?
 Cheers
 
 Andy

This isn't exactly what you want to hear, but how about validating your input 
before submitting a query?

_
Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews

Re: [PHP-DB] mysql data truncation does not cause an error to be thrown

[Andrew Blake]

I figured it out

it was the mysql install not php :-)
cheers for your help though :-)

Vandegrift, Ken wrote:

You may want to check the my.ini setting for the table type you are
using and see if there is a setting in there that needs to be enabled.  


I thought I read once that truncation may happen silently depending on
the my.ini setting.

Just a thought.

Ken Vandegrift
Shari's Management Corporation
Web Developer/Administrator
(Direct) 503-605-4132
[EMAIL PROTECTED]
-Original Message-
From: Andrew Blake [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 7:51 AM

To: Instruct ICC
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
be thrown

Hiya
I could check the length of the field against the entry data and 
javascript myself out of trouble but i was more worried that there is no


error or message when mysql clearly returns one saying i've truncated 
this yet php ignores it completely. It should fail or know about the 
truncation at least !

Cheers for your reply though :-)

Andy

Instruct ICC wrote:
  

Using mysql_query if i try to force more data than a field can have
  
the 
  

data is truncated yet no error is throw at all.
Is there a way round this ?
Cheers

Andy

  

This isn't exactly what you want to hear, but how about validating


your input before submitting a query?
  

_
Boo! Scare away worms, viruses and so much more! Try Windows Live


OneCare!
  
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotm

ailnews
  
  



  


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

Re: [PHP-DB] mysql data truncation does not cause an error to be thrown

[Andrew Blake]

Hiya
I could check the length of the field against the entry data and 
javascript myself out of trouble but i was more worried that there is no 
error or message when mysql clearly returns one saying i've truncated 
this yet php ignores it completely. It should fail or know about the 
truncation at least !

Cheers for your reply though :-)

Andy

Instruct ICC wrote:
Using mysql_query if i try to force more data than a field can have the 
data is truncated yet no error is throw at all.

Is there a way round this ?
Cheers

Andy



This isn't exactly what you want to hear, but how about validating your input 
before submitting a query?

_
Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews
  


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

RE: [PHP-DB] mysql data truncation does not cause an error to be thrown

[Instruct ICC]

Maybe tell the list the exact solution?

File/variable/setting?

Cheers.


 I figured it out
 
 it was the mysql install not php :-)
 cheers for your help though :-)
 
 Vandegrift, Ken wrote:
  You may want to check the my.ini setting for the table type you are
  using and see if there is a setting in there that needs to be enabled.  
 
  I thought I read once that truncation may happen silently depending on
  the my.ini setting.


_
Peek-a-boo FREE Tricks  Treats for You!
http://www.reallivemoms.com?ocid=TXT_TAGHMloc=us

RE: [PHP-DB] mysql error...

[Bastien Koert]

Condition is a reserved word in mysql...you will need to change the field name
 
bastien Date: Tue, 28 Aug 2007 18:11:20 -0700 From: [EMAIL PROTECTED] To: 
php-db@lists.php.net Subject: [PHP-DB] mysql error...  this is bugging me to 
no end (no pun intended)... I am getting the error:  invalid query: 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 'Condition, ConstructType, 
BldgDimensions, Stories,  CurrentParking, MaxParking, P' at line 1  I cannot 
find a thing wrong with this... any ideas what may be the  problem? All the 
data types are either int(11) or varchar(50).  JohnINSERT INTO 
tblBuildings (SiteID, Name, Available, MfgWHSqFt,  OfficeSqFt, TotalSqFt, 
MinSqFtAvailable, MaxSqFtAvailable, Condition,  ConstructType, BldgDimensions, 
Stories, CurrentParking, MaxParking,  ParkingSurface, PctOccupied, ClearSpan, 
CeilingMax, PctAC, PctHeated,  FloorType, Sprinkled, DHDoors, DHSize, GLDoors, 
GLSize, RRDoors, RRSize,  CraneNum, CraneSize, RefrigSize) VALUES ('', '[enter 
building name]',  'Y', '', '', '', '', '', '- Select -', '- Select -', '', '', 
'', '', '-  Select -', '', '', '', '', '', '- Select -', 'Y', '', '', '', '', 
'',  '', '', '', '')  --  PHP Database Mailing List (http://www.php.net/) 
To unsubscribe, visit: http://www.php.net/unsub.php 
_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+worldmkt=en-USform=QBRE

Re: [PHP-DB] mysql error...

[Chris]

John Pillion wrote:

this is bugging me to no end (no pun intended)...  I am getting the error:

invalid query: 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 'Condition, ConstructType, BldgDimensions, Stories, 
CurrentParking, MaxParking, P' at line 1


I cannot find a thing wrong with this...  any ideas what may be the 
problem? All the data types are either int(11) or varchar(50).


condition is a reserved word in mysql 5.

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.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 different server different EXPLAIN result

[Niel]

Hi

 I'm running 3 servers with mysql database
 - Local : 5.0.18-log, MySQL Community Edition (GPL)
 - Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard 
 (GPL)
 - Backup Server : 4.1.20, Source Distribution
 
 I copied a database from the Main Server to Local and Backup Server and 
 run a SQL
 explain command on the database but i get different EXPLAIN result. I 
 think because
 i copied it from one source shouldn't it show the same EXPLAIN result 
 cause i didn't
 change any of the index, keys or records

Not if that's what has changed between versions. Check the change log
for details of what was altered, to see if it's relevant.

--
Niel Archer

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

Re: [PHP-DB] mysql different server different EXPLAIN result

[Chris]

Chenri wrote:

Dear All

I'm running 3 servers with mysql database
- Local : 5.0.18-log, MySQL Community Edition (GPL)
- Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard 
(GPL)

- Backup Server : 4.1.20, Source Distribution


Not sure why you're surprised why they are different.

As with all software the new version will have stuff the old version 
didn't have. In this case v5 will have improvements in the engine.


--
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 user anHTACCESSusername not found?

[Instruct ICC]

From: Instruct ICC [EMAIL PROTECTED]
Why is this second log entry present?  I don't see any place where I ask 
MySQL to authenticate an HTACCESS username.


It came to me.  The very fact that we are using mod_auth_mysql in the Apache 
web server means that Apache is trying to authenticate anHTACCESSusername in 
the MySQL database.  I suppose since the web server could not connect to the 
database, anHTACCESSusername was not found after some timeout.


_
http://imagine-windowslive.com/hotmail/?locale=en-usocid=TXT_TAGHM_migration_HM_mini_2G_0507

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

Re: [PHP-DB] MySQL Error 1366

[Chris]

elk dolk wrote:

Hi All,

I want to load data from dump file to MySQL table using LOAD DATA INFILE 
but there is Error 1366 :


mysql LOAD DATA
- INFILE 'D:/SITE/SOMETABLE.SQL'
- INTO TABLE SOMETABLE
- FIELDS TERMINATED BY ','
- OPTIONALLY ENCLOSED BY ''
- LINES TERMINATED BY ')';
ERROR 1366 (HY000): Incorrect integer value:  '--MySQL dump 10.10
--
--S' for column 'ID' at row 1


LOAD DATA INFILE imports a CSV like file, it can't contain create table 
statements or insert statements.


See the documentation: http://dev.mysql.com/doc/refman/4.1/en/load-data.html


If you have a script with create table  insert statements, use source:

source  (\.)Execute a SQL script file. Takes a file name as an argument.

So

\. filename

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

[Chetanji]

Chetanji says,

This may be a typo of yours.
Look at the header...
 
CREATE TABLE `sometableo`

The 'o' is added by mistake?
Blessings,
Chetanji



elk dolk wrote:
 
Hi All,
 
I want to load data from dump file to MySQL table using LOAD DATA INFILE 
but there is Error 1366 :
 
mysql LOAD DATA
- INFILE 'D:/SITE/SOMETABLE.SQL'
- INTO TABLE SOMETABLE
- FIELDS TERMINATED BY ','
- OPTIONALLY ENCLOSED BY ''
- LINES TERMINATED BY ')';
ERROR 1366 (HY000): Incorrect integer value:  '--MySQL dump 10.10
--
--S' for column 'ID' at row 1
 
 
 
this is the header of my dump file:
 
 
DROP TABLE IF EXISTS `sometable`;
CREATE TABLE `sometableo` (
  `ID` smallint(6) NOT NULL auto_increment,
  `Name` varchar(30) NOT NULL,
  `title` tinytext,
  `description` tinytext,
  `cat` tinytext,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
LOCK TABLES `sometable` WRITE;
/*!4 ALTER TABLE `sometable` DISABLE KEYS */;
INSERT INTO `sometable` VALUES
(79,'110_1099','AAA','AA','AAA'),(80,'110_1100','AAA','DFGDFGF','AAA'),
 
 
 
 
any idea for  solving the problem?
 
 

-
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's
on, when. 


-- 
View this message in context: 
http://www.nabble.com/MySQL-Error-1366-tf3830472.html#a10846547
Sent from the Php - Database mailing list archive at Nabble.com.

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

Re: [PHP-DB] MySQL BETWEEN Problems

[Brad Bonkoski]

I think you need between 'A' and 'F%'

Aa is between A and F
but 'Fa' is not between 'A' and 'F'
(but 'Ez' would be between 'A' and 'F')
-B


Tony Grimes wrote:

I'm using BETWEEN to return a list all people who's last names fall between
A and F:

 WHERE last_name BETWEEN 'A' AND 'F' ...

but it's only returning names between A and E. The same thing happens when I
use:

 WHERE last_name = 'A' AND last_name = 'F' ...

Shouldn't this work the way I expect it? What am I doing wrong?

Tony

  


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

Re: [PHP-DB] MySQL BETWEEN Problems

[Brad Bonkoski]

Try using the substr, since you are only comparing the first letters...
(sorry I did not reply all on the other email, Dan)

i.e.

select name from users where substr(name, 1, 1) = 'A' and substr(name, 
1, 1) = 'B';


-B
Dan Shirah wrote:
So just change it to WHERE last_name BETWEEN 'A' AND 'G' .  That'll 
get you

Aa-Fz :)


On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote:


We tried that and it didn't work. We've also tried every combination of
upper and lower case too.

Tony


On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote:

 I think you need between 'A' and 'F%'

 Aa is between A and F
 but 'Fa' is not between 'A' and 'F'
 (but 'Ez' would be between 'A' and 'F')
 -B


 Tony Grimes wrote:
 I'm using BETWEEN to return a list all people who's last names fall
between
 A and F:

  WHERE last_name BETWEEN 'A' AND 'F' ...

 but it's only returning names between A and E. The same thing happens
when I
 use:

  WHERE last_name = 'A' AND last_name = 'F' ...

 Shouldn't this work the way I expect it? What am I doing wrong?

 Tony




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

[Tony Grimes]

Yeah, but what do I do for Q-Z? I guess I¹ll have to write a condition to
use last_name = ŒQ¹ if it finds a Z. I¹ll have to write conditions to look
for other exceptions too. Aaarrgh!

*looks up at the Gods*

Damn you BETWEEN! Why must you torture me so!?!



Have a good weekend.


On 5/18/07 2:51 PM, Dan Shirah [EMAIL PROTECTED] wrote:

 So just change it to WHERE last_name BETWEEN 'A' AND 'G' .  That'll get you
 Aa-Fz :)
 
  
 On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote:
 We tried that and it didn't work. We've also tried every combination of
 upper and lower case too.
 
 Tony
 
 
 On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote:
 
  I think you need between 'A' and 'F%'
 
  Aa is between A and F
  but 'Fa' is not between 'A' and 'F'
  (but 'Ez' would be between 'A' and 'F')
  -B
 
 
  Tony Grimes wrote:
  I'm using BETWEEN to return a list all people who's last names fall
 between
  A and F:
 
   WHERE last_name BETWEEN 'A' AND 'F' ...
 
  but it's only returning names between A and E. The same thing happens
 when I 
  use:
 
   WHERE last_name = 'A' AND last_name = 'F' ...
 
  Shouldn't this work the way I expect it? What am I doing wrong?
 
  Tony
 
 
 
 
 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 http://www.php.net/unsub.php
 
 
 

Re: [PHP-DB] MySQL BETWEEN Problems

[Tony Grimes]

Nice! That did the trick. Thanks Brad. I live to fight another day.

Tony


On 5/18/07 3:00 PM, Brad Bonkoski [EMAIL PROTECTED] wrote:

 Try using the substr, since you are only comparing the first letters...
 (sorry I did not reply all on the other email, Dan)
 
 i.e.
 
 select name from users where substr(name, 1, 1) = 'A' and substr(name,
 1, 1) = 'B';
 
 -B
 Dan Shirah wrote:
 So just change it to WHERE last_name BETWEEN 'A' AND 'G' .  That'll
 get you
 Aa-Fz :)
 
 
 On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote:
 
 We tried that and it didn't work. We've also tried every combination of
 upper and lower case too.
 
 Tony
 
 
 On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote:
 
 I think you need between 'A' and 'F%'
 
 Aa is between A and F
 but 'Fa' is not between 'A' and 'F'
 (but 'Ez' would be between 'A' and 'F')
 -B
 
 
 Tony Grimes wrote:
 I'm using BETWEEN to return a list all people who's last names fall
 between
 A and F:
 
  WHERE last_name BETWEEN 'A' AND 'F' ...
 
 but it's only returning names between A and E. The same thing happens
 when I
 use:
 
  WHERE last_name = 'A' AND last_name = 'F' ...
 
 Shouldn't this work the way I expect it? What am I doing wrong?
 
 Tony
 
 
 
 
 -- 
 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 Query on the Fly

[Dwight Altman]

That's one of the uses of Ajax.

http://www.xajaxproject.org/

http://wiki.xajaxproject.org/Tutorials:Learn_xajax_in_10_Minutes

You don't have to use an iframe.

Regards,
Dwight

 -Original Message-
 From: Todd A. Dorschner [mailto:[EMAIL PROTECTED]
 Sent: Friday, May 11, 2007 8:31 AM
 To: php-db@lists.php.net
 Subject: [PHP-DB] MySQL Query on the Fly
 
 Good Morning,
 
 
 
 I've been searching and toying with this solution for some time but
 can't find that right answer.  Looking for solutions/suggestions to the
 following:
 
 
 
 I created a program that will allow people to track sales and depending
 on what they've sold, they will either get a set bonus, or a bonus based
 on a percentage.  I have no problem creating a drop down box for them to
 select from, and I could probably create a select button that would do
 the action, but I'd rather have it in one step.  So what I'm trying to
 do is somehow when they select from the drop down (via a JavaScript
 onchange or something) I'd like it to pull data from the MySQL database.
 I'd like it to pull the amount and then each or per based on the ID of
 what they've selected from the drop down.  If it was always based on
 each sale or based on a percentage, that would be easy as I could code
 that into the drop box, but I'd like to keep it based on the ID so that
 it can pull out of the database the name, and then when changed pull the
 info.  Any ideas?  Thanks in advance for the help.  If you have any
 questions or it seems like I left something out, please let me know.
 
 
 
 
 
 Thank you for your time,
 
 
 Todd Dorschner

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

Re: [PHP-DB] MySQL ERRORS

[Chris]

Chetan Graham wrote:

Hi to All,

I am having problems with the MySQL DB.  It started with this command from
a call in a PHP script...

INSERT INTO docprouser (id,valid,password)VALUES
('user5','Y',md5('ksobhinyai')); or die(mysql_error());


That doesn't do anything (it will create a parse error).

You need to:

mysql_query(INSERT INTO docprouser (id,valid,password) VALUES( 
'user5','Y',md5('ksobhinyai'))) or die(mysql_error());


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

[Dimiter Ivanov]

On 4/3/07, Me2resh Lists [EMAIL PROTECTED] wrote:

hi
i need help regarding a sql query in my php app.

the query is :
$SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes'
LIMIT $startingID,$items_numbers_list;

i want to sort this query by the number of the repeated EMail counts.
can anyone help me with that please ?



$SQL = SELECT EMail,count(EMail) AS repeated FROM mena_guests WHERE
Voted = 'yes' GROUP BY EMail ORDER BY count(EMail)  LIMIT
$startingID,$items_numbers_list;

I can't remember if in the order clause you can order by the alias of
the field or using the count again, test it to see what's the proper
syntax

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