Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Thu, Sep 15, 2011 at 00:56, Alex Nikitin niks...@gmail.com wrote: 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. I agree with this point, except that MySQL does not parse any other (unicode) character as the single quote. 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. This has not been my experience. As for turning off the escape line, that is another argument for using the array that I demonstrated previously. 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. I just googled prepared statements in PHP and I see that they don't need to be pre-prepared in the database. I must have been conflating them with stored procedures. Thanks, I'll play around and possibly adopt the use of 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... This is not good for searching the data afterwards. If I have a specific non-searchable field meant for code or such, then I do base64 encode. 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
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] 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: [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: [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] Querying a database for 50 users' information: 50 queries or a WHERE array?
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm using now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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?
Dotan Cohen dotanco...@gmail.com wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm using
now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
Much smaller than what you proposed in #3, and easier to make if your user is
list is already an array.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
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?
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 ) }
On Tue, Sep 13, 2011 at 7:24 AM, Dotan Cohen dotanco...@gmail.com wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm using
now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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?
On Tue, 2011-09-13 at 17:24 +0300, Dotan Cohen wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm using now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
Thanks!
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
what criteria are you using to get the stats for these 50 users?
also, wouldn't this be much better suited for the mysql mailing list?
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.
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.
--
Steve Staples
Web Application Developer
--
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?
select * from table where userID in(1,2,3,etc)
From: a...@ashleysheridan.co.uk
Date: Tue, 13 Sep 2011 15:29:26 +0100
To: dotanco...@gmail.com; php-general@lists.php.net
Subject: Re: [PHP] Querying a database for 50 users' information: 50 queries
or a WHERE array?
Dotan Cohen dotanco...@gmail.com wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm using
now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
Much smaller than what you proposed in #3, and easier to make if your user is
list is already an array.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
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?
yeer tai yeer...@hotmail.com wrote:
select * from table where userID in(1,2,3,etc)
From: a...@ashleysheridan.co.uk
Date: Tue, 13 Sep 2011 15:29:26 +0100
To: dotanco...@gmail.com; php-general@lists.php.net
Subject: Re: [PHP] Querying a database for 50 users' information: 50
queries or a WHERE array?
Dotan Cohen dotanco...@gmail.com wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to
have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm
using
now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
Much smaller than what you proposed in #3, and easier to make if your
user is list is already an array.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Erm, that's funny that you replied with the exact same answer as I gave, just
top-posted on my reply...
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
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?
Oh no, he stole your internet points!
On Tue, Sep 13, 2011 at 10:44 AM, Ashley Sheridan
a...@ashleysheridan.co.uk wrote:
yeer tai yeer...@hotmail.com wrote:
select * from table where userID in(1,2,3,etc)
From: a...@ashleysheridan.co.uk
Date: Tue, 13 Sep 2011 15:29:26 +0100
To: dotanco...@gmail.com; php-general@lists.php.net
Subject: Re: [PHP] Querying a database for 50 users' information: 50
queries or a WHERE array?
Dotan Cohen dotanco...@gmail.com wrote:
I have a MySQL database table with about 10,000 rows. If I want to
query for 50 specific users (so no LIMIT ORDER BY) then I seem to
have
these choices:
1) SELECT * FROM table
This will pull in all 10,000 rows, not nice!
2) foreach ($user as $u) { mysql_query(SELECT * FROM table WHERE
userID=.$u); }
This will lead to 50 queries, again not nice! (maybe worse)
3) foreach ($user as $u) { $whereClause+= OR userID=.$u; }
This makes a huge SQL query. However, this is the method that I'm
using
now.
Is there some sort of array that can be passed in the WHERE clause,
containing all the userID's that I am interested in?
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
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
Much smaller than what you proposed in #3, and easier to make if your
user is list is already an array.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Erm, that's funny that you replied with the exact same answer as I gave, just
top-posted on my reply...
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
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] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Tue, Sep 13, 2011 at 7:29 AM, Ashley Sheridan
a...@ashleysheridan.co.ukwrote:
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
+1. And this is a great place to use implode():
$sql = 'select ... where userID in (' . implode(',', $ids) . ')';
David
Re: [PHP] Querying a database for 50 users' information: 50 queries or a WHERE array?
On Tue, 2011-09-13 at 09:48 -0700, David Harkness wrote:
On Tue, Sep 13, 2011 at 7:29 AM, Ashley Sheridan
a...@ashleysheridan.co.ukwrote:
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
+1. And this is a great place to use implode():
$sql = 'select ... where userID in (' . implode(',', $ids) . ')';
David
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
good luck!
--
Steve Staples
Web Application Developer
--
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?
Another theoretical approach, given the grey areas, would be to add a field to your table to indicate these special users. I would call the field is_awesome and have it default to zero, because that's just the way it is. Then you can make your query SELECT * FROM users WHERE is_awesome=1. This method might make the code a bit more manageable as you could modify the users through a CMS rather than updating a hardcoded array. Marc -- 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 2:06 PM, Steve Staples sstap...@mnsi.net wrote:
On Tue, 2011-09-13 at 09:48 -0700, David Harkness wrote:
On Tue, Sep 13, 2011 at 7:29 AM, Ashley Sheridan
a...@ashleysheridan.co.ukwrote:
SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc)
+1. And this is a great place to use implode():
$sql = 'select ... where userID in (' . implode(',', $ids) . ')';
David
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
good luck!
--
Steve Staples
Web Application Developer
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
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 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?
On Tue, Sep 13, 2011 at 17:29, Ashley Sheridan a...@ashleysheridan.co.uk wrote: SELECT * FROM table WHERE userID IN (1,2,3,4,5,etc) Much smaller than what you proposed in #3, and easier to make if your user is list is already an array. Thank you Ash, that is exactly what I was looking for! -- 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 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 -- 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 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
--
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 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 -- 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 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 -- 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 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 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. 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. -- 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?
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 On 14 Sep 2011 01:35, Alex Nikitin niks...@gmail.com wrote: 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 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. 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. -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray
