php-general Digest 14 Sep 2011 07:39:27 -0000 Issue 7477
php-general Digest 14 Sep 2011 07:39:27 - Issue 7477
Topics (messages 314812 through 314834):
Re: Querying a database for 50 users' information: 50 queries or a WHERE array?
314812 by: Dotan Cohen
314813 by: Dotan Cohen
314814 by: Dotan Cohen
314815 by: Dotan Cohen
314816 by: Alex Nikitin
314832 by: chetan rane
What would you like to see in most in a text editor?
314817 by: Brad Huskins
314819 by: Brad Huskins
314820 by: Robert Cummings
314823 by: Alex Nikitin
314824 by: Igor Escobar
314825 by: Brad Huskins
314826 by: Daniel Brown
314827 by: tamouse mailing lists
314828 by: Jim Lucas
314829 by: James Yerge
314830 by: Brad Huskins
314831 by: Brad Huskins
314833 by: Jim Lucas
What would you like to see in a text editor?
314818 by: Brad Huskins
Sorry!
314821 by: Brad Huskins
Re: htmlentities
314822 by: Shawn McKenzie
314834 by: Johan Lidström
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
--
---BeginMessage---
On Tue, Sep 13, 2011 at 17:32, muad shibani muad.shib...@gmail.com wrote:
Yes there is but all the IDs in one string like this
$ids = $id1.', '.$id2.', ' ;
note : remove the last comma from the string
the make the query like this:
mysql_query(SELECT * FROM table WHERE
userID= in($ids ) }
Thank you Muad!
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
---End Message---
---BeginMessage---
On Tue, Sep 13, 2011 at 17:34, Steve Staples sstap...@mnsi.net wrote:
what criteria are you using to get the stats for these 50 users?
They are passed as an array into a function I'm cleaning up.
also, wouldn't this be much better suited for the mysql mailing list?
Yes.
if you know all the userids, then you could just do it as:
$sql = SELECT * FROM table WHERE userid IN (. implode(', ',
$usersids) .);
not very elegant, and I am not sure that the IN is any better than doing
50 mysql calls, but this is only 1 call, and gets you the data.
This is exactly what I need, thanks!
Are you querying the database to get the id's in the frist place? if
so, you could look at doing an inner join on the 2 tables.
Actually, I do suspect that is where it's coming from. But the calling
function is not in my hands.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
---End Message---
---BeginMessage---
On Tue, Sep 13, 2011 at 21:06, Steve Staples sstap...@mnsi.net wrote:
I mentioned that implode earlier, but there is also the underlying
question (which I also asked earlier)... how is he getting the 50 id's
to populate?
here are 2 other ways of skinning the cat:
using an inner join:
select table.* from table inner join othertable on (table.userid =
othertable.userid) where (use the way your getting the 50 id's here);
OR by using a subselect,
select * from table where userid IN (select group_concat(userid,
separator ', ') FROM othertable where (using logic here));
guess it all depends on how you want to do it... but that would make it
1 db query
I personally would stick with the inner join as I know that syntax,
but thanks for introducing me to the subselect. I have never seen
that.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
---End Message---
---BeginMessage---
On Tue, Sep 13, 2011 at 21:34, Alex Nikitin niks...@gmail.com wrote:
And this will be faster or at least more efficient with a limit (e.g. limit
50) this way when you have found the 50 users in the in statement, you
don't continue iterating through the rest of your data set...
The number is never exactly 50 but rather some arbitrary large number.
But there is no need for LIMIT, that is the purpose of the _INNER_
JOIN. INNER means to only return the matching rows.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
---End Message---
---BeginMessage---
On Tue, Sep 13, 2011 at 3:45 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Tue, Sep 13, 2011 at 21:34, Alex Nikitin niks...@gmail.com wrote:
And this will be faster or at least more efficient with a limit (e.g.
limit
50) this way when you have found the 50 users in the in statement, you
don't continue iterating through the rest of your data set...
The number is never exactly 50 but rather some arbitrary large number.
But there is no need for LIMIT, that is the purpose of the _INNER_
JOIN. INNER means to only return the matching rows.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
Dotan,
IN (the function used in all of the queries above) is not the same as an
INNER_JOIN, inner join joins 2 tables, as
Re: [PHP] Re: htmlentities
On 13 September 2011 23:01, Shawn McKenzie nos...@mckenzies.net wrote: On 09/13/2011 01:38 PM, Ron Piggott wrote: Is there a way to only change accented characters and not HTML (Example: p /p a href =”” /a ) The syntax echo htmlentities( stripslashes(mysql_result($whats_new_result,0,message)) ) . \r\n; is doing everything (as I expect). I store breaking news within the database as HTML formatted text. I am trying to see if a work around is available? Do I need to do a variety of search / replace to convert the noted characters above back after htmlentities ? (I am just starting to get use to accented letters.) Thanks a lot for your help. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info If it is meant to be HTML then why run htmlentities(), especially before storing it in the DB? -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Perhaps something like this might help you $content = htmlspecialchars_decode(htmlentities($content,ENT_NOQUOTES,ISO-8859-1),ENT_NOQUOTES); or perhaps $table_all = get_html_translation_table(HTML_ENTITIES,ENT_NOQUOTES,ISO-8859-1); $table_html = get_html_translation_table(HTML_SPECIALCHARS,ENT_NOQUOTES); $table_nonhtml = array_diff_key($table_all,$table_html); $content1 = strtr($content1,$table_nonhtml); $content2 = strtr($content2,$table_nonhtml); if using it multiple times. -- It is not possible to simultaneously understand and appreciate the Intel architecture --Ben Scott
Re: [PHP] What would you like to see in most in a text editor?
On 13 September 2011 21:56, Brad Huskins brad.husk...@gmail.com wrote: Hello all you php coders out there, I'm doing an Open Source text editor (just a hobby) that's designed for PHP developers and is accessible through the web. This has been stewing for a while, and has gotten to the point where I can use it for my own work. I would like any feedback on things that people really like/dislike about their current editors, as I believe some of these things could be resolved in mine. I currently have username/password protection (with Salted-Hash passwords), a file-system browser, file loading/saving, and syntax highlighting -- and these things seem to work reasonably well. As well, most things about the editor are scriptable with JavaScript. This would seem to imply that in a few weeks I would have something useful. So I would like to get some feedback on what features people would most want, since I am still at a very flexible stage in development. If you would like to see what I have, you can go to un1tware.wordpress.com. You can also peruse the code at github.com/bhus/scriptr. In particular, the README on github gives a little bit better rationality for why something like this might be useful, and how things are currently structured. --Brad [ Yes, this is based on the layout of Linus' original post to comp.os.minix. ] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Refactoring (that is, changing the name or arguments of variables or functions and have all references to that variable or function changed accordingly) would be nice to see in an online editor. ^_^ -- It is not possible to simultaneously understand and appreciate the Intel architecture --Ben Scott
Re: [PHP] Stop PHP execution on client connection closed
Il 13/09/2011 20:58, Alex Nikitin ha scritto: Correction on Marco's post. You can absolutely stop a mysql query I know I can stop a query, but I don't know how to realize HTTP client has closed connection during query execution. My query count how many records match selected fields in a 50M records table. Any query field is indexed and innodb uses 20GB of RAM to store data and indexes, but some queries take about 30 seconds to run. When user changes filters and asks for a new count, the old queries continue to run using DB resurces unnecessarily. Bye, Marco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Tue, Sep 13, 2011 at 23:04, Alex Nikitin niks...@gmail.com wrote: Dotan, IN (the function used in all of the queries above) is not the same as an INNER_JOIN, inner join joins 2 tables, as you have already described, IN however is a function that return 1 if the value being searched for is in the array of its values or 0 if it is not, thus IN is not an inner join, but a comparator function, thus if you are using IN, limit will indeed be more efficient than it's omission for exactly the reason i have stated in my previous post. Because your user array seems to be in php, and implode has been a topic of discussion above as well, setting an adequate limit is a simple task with the php's count function. Yes, I did realize that after seeing the syntax of IN, which I have not been exposed to before. My response that you quoted was in response to a suggestion that a LIMIT clause be used with an INNER JOIN query, which is wrong on two principles. This is all ofcourse void if the user array being pulled from mysql, in which case you could simply join the two tables to get your resulting data set. The trick there is to use the USING clause which seems to run a lot faster than any ON clause, or work on an optimized subselect, especially if you are running a cluster. Agreed. In fact I don't know from where the array is coming, that's not my part of the code! But I agree that if it is coming from mysql then a join would be preferable. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Wed, Sep 14, 2011 at 06:05, chetan rane chetan.d.r...@gmail.com wrote: Hi, There are 2 peoblems with subselect 1. You cant use a limit on the nested select 2. Id the number of elements in the in clause exceeds the subselect buffer you will run into performance issues ans eventually you query will be doomed. Inner joins in,this is the best option for this . You can use a temp table for this Thanks Chetan. I will keep that in mind if I ever get around to learning about subselects. Have a great day! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] What would you like to see in most in a text editor?
On 14 September 2011 01:23, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummings rob...@interjinn.com wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] What would you like to see in most in a text editor?
On 14 Sep 2011 at 12:40, Richard Quadling rquadl...@gmail.com wrote: On 14 September 2011 01:23, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummings rob...@interjinn.com wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. Is that more or less terse than TECO? Back in 1989 when I was at SLAC, they were just getting into unix, and debates were raging about which editor to standardise on and teach people (emacs, vi, jove, etc). Because this wasn't settled, I started using notepad (and later, dxnotepad) and got on with coding. Six months later, the debates were still raging. I then had an epiphany: I'd been using notepad for six moths got work done. It took me 5 minutes to find out how to use it. I didn't need teaching about it or to have a manual. So IMO, emacs, vi, and all their ilk belong in the dustbin of history. -- Cheers -- Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] What would you like to see in most in a text editor?
Thanks Tim. That is some very useful feedback. I am aiming to build something that is almost as easy to use as Notepad. Don't know if I'll be successful or not, but nice to know people value simplicity. --Brad. On 09/14/2011 08:18 AM, Tim Streater wrote: On 14 Sep 2011 at 12:40, Richard Quadlingrquadl...@gmail.com wrote: On 14 September 2011 01:23, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummingsrob...@interjinn.com wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. Is that more or less terse than TECO? Back in 1989 when I was at SLAC, they were just getting into unix, and debates were raging about which editor to standardise on and teach people (emacs, vi, jove, etc). Because this wasn't settled, I started using notepad (and later, dxnotepad) and got on with coding. Six months later, the debates were still raging. I then had an epiphany: I'd been using notepad for six moths got work done. It took me 5 minutes to find out how to use it. I didn't need teaching about it or to have a manual. So IMO, emacs, vi, and all their ilk belong in the dustbin of history. -- Cheers -- Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Wed, Sep 14, 2011 at 4:12 AM, Dotan Cohen dotanco...@gmail.com wrote: On Wed, Sep 14, 2011 at 06:05, chetan rane chetan.d.r...@gmail.com wrote: Hi, There are 2 peoblems with subselect 1. You cant use a limit on the nested select 2. Id the number of elements in the in clause exceeds the subselect buffer you will run into performance issues ans eventually you query will be doomed. Inner joins in,this is the best option for this . You can use a temp table for this Thanks Chetan. I will keep that in mind if I ever get around to learning about subselects. Have a great day! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Just out of curiosity, where are these ids coming from? Doing a raw implode on them like that is a sql injection vuln. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] What would you like to see in most in a text editor?
On 14 September 2011 13:18, Tim Streater t...@clothears.org.uk wrote: On 14 Sep 2011 at 12:40, Richard Quadling rquadl...@gmail.com wrote: On 14 September 2011 01:23, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummings rob...@interjinn.com wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. Is that more or less terse than TECO? Back in 1989 when I was at SLAC, they were just getting into unix, and debates were raging about which editor to standardise on and teach people (emacs, vi, jove, etc). Because this wasn't settled, I started using notepad (and later, dxnotepad) and got on with coding. Six months later, the debates were still raging. I then had an epiphany: I'd been using notepad for six moths got work done. It took me 5 minutes to find out how to use it. I didn't need teaching about it or to have a manual. So IMO, emacs, vi, and all their ilk belong in the dustbin of history. -- Cheers -- Tim TECO - OUCH. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Sort problem
Hi Folks! Anyone know a smart way to order file names? An example to you guys picture what im saying is: ?php $serie[] = Two And Half Man Season 1; $serie[] = Two And Half Man Season 4; $serie[] = Two And Half Man Season 2; $serie[] = Two And Half Man Season 3; $serie[] = Two And Half Man Season 10; $serie[] = Two And Half Man Season 9; sort($serie); print_r($serie); ? The result of this snippet is: Array ( [0] = Two And Half Man Season 1[1] = Two And Half Man Season 10[2] = Two And Half Man Season 2 [3] = Two And Half Man Season 3 [4] = Two And Half Man Season 4 [5] = Two And Half Man Season 9 ) Anyone knows how to solve this problem? Regards, Igor Escobar *Software Engineer * + http://blog.igorescobar.com + http://www.igorescobar.com + @igorescobar http://www.twitter.com/igorescobar
Re: [PHP] Sort problem
Anyone know a smart way to order file names? Nope, but I know a natural way: http://ca.php.net/manual/en/function.natsort.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sort problem
Wow! Thank you! I completely forgot this method! Regards, Igor Escobar *Software Engineer * + http://blog.igorescobar.com + http://www.igorescobar.com + @igorescobar http://www.twitter.com/igorescobar On Wed, Sep 14, 2011 at 12:02 PM, Marc Guay marc.g...@gmail.com wrote: Anyone know a smart way to order file names? Nope, but I know a natural way: http://ca.php.net/manual/en/function.natsort.php
Re: [PHP] What would you like to see in most in a text editor?
But why? Brad Huskins brad.husk...@gmail.com wrote in message news:66.b1.08893.200a0...@pb1.pair.com... I am aiming to build something that is almost as easy to use as Notepad. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
On 9/14/2011 1:04 AM, Marco Lanzotti wrote: Il 13/09/2011 20:58, Alex Nikitin ha scritto: Correction on Marco's post. You can absolutely stop a mysql query I know I can stop a query, but I don't know how to realize HTTP client has closed connection during query execution. My query count how many records match selected fields in a 50M records table. Any query field is indexed and innodb uses 20GB of RAM to store data and indexes, but some queries take about 30 seconds to run. When user changes filters and asks for a new count, the old queries continue to run using DB resurces unnecessarily. Bye, Marco Well, from the sounds of that, you really do not have an easy option. Here is my suggestion. In your initial script, you could add a unique value to your SQL statement. You SQL would be something like... SELECT ... FROM ... WHERE ... AND (1=1 OR 'unique value'); add 'unique value' to your session data and then, when the person changes the selected fields and starts to execute another query, first, you could search to see if an SQL statement is running that has your unique value in it. if it cannot find a matching statement, simply execute the SQL query. If it does find an SQL statement that matches the unique value, kill it, then issue your SQL statement. Read the following to figure out how to find your unique process: http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html Read the following to find out how to kill your processes: http://dev.mysql.com/doc/refman/5.0/en/kill.html But, it does seem like it would be possible. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
Il 14/09/2011 17:35, Jim Lucas ha scritto: SELECT ... FROM ... WHERE ... AND (1=1 OR 'unique value'); add 'unique value' to your session data and then, when the person changes the selected fields and starts to execute another query, first, you could search to see if an SQL statement is running that has your unique value in it. Not so clean, but it could work! Thank you, Marco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] What would you like to see in most in a text editor?
On Wed, Sep 14, 2011 at 01:18:00PM +0100, Tim Streater wrote: On 14 Sep 2011 at 12:40, Richard Quadling rquadl...@gmail.com wrote: On 14 September 2011 01:23, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummings rob...@interjinn.com wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. Is that more or less terse than TECO? Back in 1989 when I was at SLAC, they were just getting into unix, and debates were raging about which editor to standardise on and teach people (emacs, vi, jove, etc). Because this wasn't settled, I started using notepad (and later, dxnotepad) and got on with coding. Six months later, the debates were still raging. I then had an epiphany: I'd been using notepad for six moths got work done. It took me 5 minutes to find out how to use it. I didn't need teaching about it or to have a manual. So IMO, emacs, vi, and all their ilk belong in the dustbin of history. -- Cheers -- Tim I agree with you for the most part. I used to use Nano for this reason, which tends to be available on any given system. But sometimes Nano isn't available and/or is difficult to find/install. It offers very little flexibility and, as far as I know, no capability to do add-ons. It also doesn't do syntax highlighting, as far as I know. I resisted Emacs because I'd have arthritis in short order from having to deal with the plethora of control and alt keystrokes which don't make mnemonic sense to me. Plus, it can be a massive. Eventually I switched to Vim (counter-intuitively) because 1) there's no *unix variant on which it's not available; 2) at some point, you're probably going to *have* to know how to operate Vi if you move around among foreign machines and networks; 3) there are many other applications which use many of the same keystroke patterns which are fundamental to Vi; 4) most keystroke combinations do not require leaving the home row, etc.; 5) Vi easily does syntax hilighting and a variety of other things, depending on add-ons. The modal model of Vi/Vim is sometimes a pain in the ass. And yes, it can take a long time to know all the features of Vim. But there are a number of things I can do faster in Vim, than anyone else can do in other editors, with less effort. No attempt here to dissuade Emacers or others. Whatever floats your boat and you're happy with, continue using. Why should you or I care what someone else uses for an editor? BTW, my big beef with online editors is latency, and it's a *huge* problem, as far as I'm concerned. Ultimately this is why I wrote blog software for myself which requires you to compose and edit your posts locally, and then *upload* them to the blog. That, and the silly idea that one should store huge masses of text in relation databases; large masses of text should be stored as what they are-- flat files. Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] What would you like to see in most in a text editor?
On Tue, Sep 13, 2011 at 7:56 PM, James Yerge ja...@nixsecurity.org wrote: I'd have to go agree with the exception of s/emacs/vi/ :P invoke(EditorChoiceReligiousArgument); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] What would you like to see in most in a text editor?
On Wed, Sep 14, 2011 at 11:52 AM, Paul M Foster pa...@quillandmouse.com wrote: BTW, my big beef with online editors is latency, and it's a *huge* problem, as far as I'm concerned. Ultimately this is why I wrote blog software for myself which requires you to compose and edit your posts locally, and then *upload* them to the blog. That, and the silly idea that one should store huge masses of text in relation databases; large masses of text should be stored as what they are-- flat files. ^^This. This is my hugest complaint about using Google Docs. I seem to suffer from lag a lot, despite having a high speed cable connection. Concerns about losing work, losing control, losing access, etc. I don't think I'd like it very much if didn't have the possibility of working on code and text files while I was not connected to a network. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
On Wed, Sep 14, 2011 at 4:04 AM, Marco Lanzotti ma...@lanzotti.com wrote: Il 13/09/2011 20:58, Alex Nikitin ha scritto: Correction on Marco's post. You can absolutely stop a mysql query I know I can stop a query, but I don't know how to realize HTTP client has closed connection during query execution. My query count how many records match selected fields in a 50M records table. Any query field is indexed and innodb uses 20GB of RAM to store data and indexes, but some queries take about 30 seconds to run. When user changes filters and asks for a new count, the old queries continue to run using DB resurces unnecessarily. Bye, Marco Marco, I ran queries on a table that had 12M rows added to it each month with a year+ worth of data going back, pulling 80-90 thousand records with over a dozen columns on an older dual dual core box with 8gb ram (so 6 for MySQL) joining multiple tables for various criteria, matching on various values with query execution in a second range (depending on load, from under a second, to under 2 seconds). I think, and i am not trying to sound like pompous buffoon or to put anyone down or say that you or anyone here don't know what they are talking about or anything like that, but i think that you should first look into how you can optimize your database and your query, as well as maybe the access to this information (volume of information that you are presenting vs getting, also how you filter it, etc). Sometimes it's a very simple thing that can make or brake query execution time, and it's not immediately apparent. I was once tasked to fix a process in which about 2-300 queries were ran against the database in periodic ajax calls, they took about a 1/4 second to execute for each query. This ofcourse means that the refresh took almost a minute to run, which was getting very annoying, so i glimpsed over the queries and the tables at hand and 5 minutes later issued 2 queries, one to delete a useless index that was created for the main table, and another to create a new index on the database that reduced the execution time of those queries from 1/4 sec for each to 1.4 or 1.6 sec for all 2-300. And most of that time was actually caused by the network lag for the 2-300 queries, since they were individually executed from php, i wanted to reduce that whole thing to one query, but wasn't allowed to. Other times its a lot more complex, and sometimes blowing a query up from something simple or straight forward to something more complex can wield similar increases in performance, this ofcourse has to be with thorough understanding of how the database works. Perhaps if I, or we can understand your application a little better, we could suggest better solutions, just remember that you are not the first person to have to solve these similar issues. I can help you if you want, glimpse over your database design and queries for a fresh look, i have fairly extensive php (and many other languages) programming experience, as well as database design and administration, system development and administration, optimization, security, caching (many other things, that don't directly pertain to this) though we should probably keep it off the list. - Alex -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
You can use a limit with a nested select, you just can't use it in some cases, like inside an IN statement, but something like this should work: SELECT id, data, etc FROM table JOIN (SELECT special_id as id FROM special_table ORDER BY special_id LIMIT 0, 1000) AS table2 USING (id) Note: syntax may not be valid, but should be fairly straight forward to fix, have no time to play with it though... -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray On Wed, Sep 14, 2011 at 4:12 AM, Dotan Cohen dotanco...@gmail.com wrote: On Wed, Sep 14, 2011 at 06:05, chetan rane chetan.d.r...@gmail.com wrote: Hi, There are 2 peoblems with subselect 1. You cant use a limit on the nested select 2. Id the number of elements in the in clause exceeds the subselect buffer you will run into performance issues ans eventually you query will be doomed. Inner joins in,this is the best option for this . You can use a temp table for this Thanks Chetan. I will keep that in mind if I ever get around to learning about subselects. Have a great day! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: Re: [PHP] What would you like to see in most in a text editor?
On 14 Sep 2011 at 17:52, Paul M Foster pa...@quillandmouse.com wrote: Eventually I switched to Vim (counter-intuitively) because 1) there's no *unix variant on which it's not available; 2) at some point, you're probably going to *have* to know how to operate Vi if you move around among foreign machines and networks Yes, this is entirely valid IMO. I still have my ultrix vi summary card for such occasions. -- Cheers -- Tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Wed, Sep 14, 2011 at 16:02, Eric Butera eric.but...@gmail.com wrote: Just out of curiosity, where are these ids coming from? Doing a raw implode on them like that is a sql injection vuln. They are in an array. I do of course is_int() them first, plus some other sanitation including mysql_real_escape_string(). -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Wed, Sep 14, 2011 at 21:01, Alex Nikitin niks...@gmail.com wrote: You can use a limit with a nested select, you just can't use it in some cases, like inside an IN statement, but something like this should work: SELECT id, data, etc FROM table JOIN (SELECT special_id as id FROM special_table ORDER BY special_id LIMIT 0, 1000) AS table2 USING (id) Note: syntax may not be valid, but should be fairly straight forward to fix, have no time to play with it though... Thanks. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
rant from=tired of constantly having to explain it, developer MySQL real escape string doesn't work, it's a bad solution to the problem that has been with the internets since the very beginning, and if people program like they are taught to by books, doesn't look like it's going away any time soon. The problem of course is that various programming languages don't know how to talk to other languages, and we as devs see no better way to do this then concatenate strings. Basically this is the core reason why XSS and SQL injection is rampant on the interwebs. Escaping only seems like it's a good idea to you, but if you analyze what it does and compare it to today's technology, you quickly realize how wrong of a concept it actually is. Escaping looks for certain characters, and if found escapes them in some form. The problem here is that rather then say defining all safe characters, it defines what the developers believe to be bad characters, and the affect that you get is not dissimilar to creating a firewall rule set where the bottom rule is accept all, as long as my character doesn't match what they thought was a bad character, it is allowed. This was fine in the days of ASCII, but the tubes are hardly ASCII anymore, with Unicode, UTF-16, i have 1,112,064 code points, they are not even called characters anymore, because they really aren't. And if you are familiar with best-fit mapping, you would know that there are now dozens of characters that can represent any single symbol in ASCII, meaning that using the above type of blocking mechanisms is silly and technically insecure. Another problem with it is the fact that security-wise this again is a bad solution from another perspective. A programmer comes in, and starts debugging code, the first thing they always seem to do is to turn off the security and comment out the escape line, and you know what happens, the bug gets found and fixed completely else-where, but the security never gets re-enabled. This is called failing open, and it again goes with the concept above where the escape in itself fails open as well. So if you look into the problem at the core, what you have are two types of code, code that you know is good, and crap data that you have to somehow make safe. So you know how you do it in the same language? Right, you assign that data to a storage container called a variable, and the interpreter knows that this data here, i execute, and that data there i use as data and don't execute. Well what happens when you add another language into the mix? Well language a passes known good code that it string concatenates to bad code, and what you get as a result is the second language parser thinking hey, all of this stuff is good code, let me execute it!... This is why a stringent delimiter between known good and not good data needs to be portrayed to the second language. How do we do it with SQL? There are a few ways, one of the more common ones is to use a prepared statement, this clearly separates the code from the data for the SQL interpreter on the other side. This works really well, with one HUGE down-side, it can be a REAL pain in the butt to use, the more complex your query gets, the more pain in the butt it is to use prepared statements. Another way, and this works for mostly any language is to use an in-common function that jumbles the known-bad data on one end, and unjumbles it as data on the other. For example base64. It works extremely well, you take any data on the PHP side, base 64 encode it, and send it to SQL or JS or whatever. you can string concatenate the b64'd data, because you know what b64'd data looks like? Yep, data, its not JS, it's not SQL, bunch of garbled junk. You can then use b64decode on that data, and by the design of the function the result will be just that, data. So with this you keep the code/data separation even with string concatenation... Base 64 performs really well, and is well worth the few extra cycles for the above-mentioned guaranteed code/data separation barrier, it's easy to implement. More importantly, this by default fails closed. You would have to disable at least 4 security points and change 2 queries to disable this (and if you are using a stored procedure this is even harder), and that's beyond what you want to do during troubleshooting usually, and if you disable one point, your application fails to work all together and it fails closed. More over you can make this completely transparent to your devs by changing your data access libraries (for SQL, or Ajax functions for JS for example). They can pass in crap data, and the first thing your data access library does before doing anything else is it encodes the data into a bunch of gibberish... And when they pull the data back, your library gets the data and unencodes it. the devs don't have to worry about SQL injection, you don't have to worry about their competence, you win ;) /rant sources: Dan Kaminsky - HOPE keynote -
[PHP] Re: What would you like to see in most in a text editor?
On Wed, 14 Sep 2011 14:08:09 +0100, Richard Quadling wrote: On 14 September 2011 13:18, Tim Streater t...@clothears.org.uk wrote: On 14 Sep 2011 at 12:40, Richard Quadling rquadl...@gmail.com wrote: On 14 September 2011 01:23, tamouse wrote: On Tue, Sep 13, 2011 at 3:35 PM, Robert Cummings wrote: I'm a big fan of editors that work in the terminal. You'll get my emacs when you pry it out of my cold dead hands. Pah! You and your full screen editor. EDLIN is the way to go. Is that more or less terse than TECO? TECO - OUCH. heh. I built my first website with WordStar 6.0 under OS/2. Jonesy -- yup, I've used TECO, too -- on an ASR-35 TTY -- Marvin L Jones| jonz | W3DHJ | linux 38.24N 104.55W | @ config.com | Jonesy | OS/2 * Killfiling google banter.com: jonz.net/ng.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Dereferencing an array.
Hi.
Based upon ...
?php
$name = Null;
$age = Null;
$boundParams = array('name' = $name, 'age' = $age);
$records = array();
$name = 'Richard';
$age = 43;
$records[] = $boundParams;
$name = 'Sally';
$age = 37;
$records[] = $boundParams;
print_r($records);
?
outputs Sally twice.
Whilst that is the correct output based upon the code, it is undesired.
I want the boundParams to have the references (the actual data from my
mysqli_stmt::fetch() with bound results), but I want to be able to
copy the values and not maintain the references.
The best I've come up with is ...
?php
$name = Null;
$age = Null;
$boundParams = array('name' = $name, 'age' = $age);
$records = array();
$columns = array_keys($boundParams);
$name = 'Richard';
$age = 43;
//$records[] = $boundParams;
$records[] = array_combine($columns,
array_map(function($m_Value){return $m_Value;}, $boundParams));
$name = 'Sally';
$age = 37;
//$records[] = $boundParams;
$records[] = array_combine($columns,
array_map(function($m_Value){return $m_Value;}, $boundParams));
print_r($records);
?
Is there a more efficient way?
--
Richard Quadling
Twitter : EE : Zend : PHPDoc
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Dereferencing an array.
it's only marginally faster, but it does look a bit cleaner, and is a
bit more memory efficient:
$records[] = unserialize(serialize($boundParams));
--
The trouble with programmers is that you can never tell what a
programmer is doing until it’s too late. ~Seymour Cray
On Wed, Sep 14, 2011 at 6:36 PM, Richard Quadling rquadl...@gmail.com wrote:
Hi.
Based upon ...
?php
$name = Null;
$age = Null;
$boundParams = array('name' = $name, 'age' = $age);
$records = array();
$name = 'Richard';
$age = 43;
$records[] = $boundParams;
$name = 'Sally';
$age = 37;
$records[] = $boundParams;
print_r($records);
?
outputs Sally twice.
Whilst that is the correct output based upon the code, it is undesired.
I want the boundParams to have the references (the actual data from my
mysqli_stmt::fetch() with bound results), but I want to be able to
copy the values and not maintain the references.
The best I've come up with is ...
?php
$name = Null;
$age = Null;
$boundParams = array('name' = $name, 'age' = $age);
$records = array();
$columns = array_keys($boundParams);
$name = 'Richard';
$age = 43;
//$records[] = $boundParams;
$records[] = array_combine($columns,
array_map(function($m_Value){return $m_Value;}, $boundParams));
$name = 'Sally';
$age = 37;
//$records[] = $boundParams;
$records[] = array_combine($columns,
array_map(function($m_Value){return $m_Value;}, $boundParams));
print_r($records);
?
Is there a more efficient way?
--
Richard Quadling
Twitter : EE : Zend : PHPDoc
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Repetitive answers . . .
On Wed, Sep 14, 2011 at 11:59 AM, Govinda govinda.webdnat...@gmail.com wrote: As for duplicate answers..., [snip] Also newbies may tend to like the multiples answers.. for the different perspectives, as Dan said, but also when they are exact dupe answers - because then the newbie knows the answer is definitive.. and then stops asking the list.. and starts doing what work is called for. -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php That's a good point. The absence of objection to a provided answer doesn't necessarily make it definitive since it could just be the masses passed over the conversation. Therefore, yes, duplicate answers are a good thing. Thanks everybody for your patience in helping this mailing list newcomer understand how things work. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
