Query help (Sql and PHP)

Question

I have the following query:

$lsel_lijst = mysql_query("SELECT * FROM users WHERE (owner = $id AND extern = 0 ) OR ( extern = $id )");

This gives me a whole list of users where owner = $id or where extern = $id. After this query i want to specify a lot of groups out of this list. With level = 10, with level = 20, with level = 30 til 2000.
And put them in the right div box (box 10, box 20, box 30 etc.).

In stead of doing this:

$lsel_lijst = mysql_query("SELECT * FROM users WHERE level = 10 AND (owner = $id AND extern = 0 ) OR ( extern = $id )");
$lsel_lijst = mysql_query("SELECT * FROM users WHERE level = 20 AND (owner = $id AND extern = 0 ) OR ( extern = $id )");
$lsel_lijst = mysql_query("SELECT * FROM users WHERE level = 30 AND (owner = $id AND extern = 0 ) OR ( extern = $id )");

etc. My question is can i do the query one more time, like above, and select afterwards?

Were you able to find an answer to this? I'd be interested what it was. — crthompson, Oct 02 '14 at 19:34

score 0 · Answer 1 · answered Mar 12 '14 at 13:57

0

Use an "in" clause:

$lsel_lijst = mysql_query("SELECT * FROM users WHERE level in(10,20,30) AND (owner = $id AND extern = 0 ) OR ( extern = $id )");

answered Mar 12 '14 at 13:57

LewisT

74
2

And what's the code for getting the list with only the users with level 10? – user2519424 Mar 12 '14 at 13:59

score 0 · Answer 2 · answered Mar 12 '14 at 14:01

0

$lsel_lijst = mysql_query("SELECT * FROM users WHERE (level > 9 OR level < 31)  AND (owner = $id AND extern = 0 ) OR ( extern = $id )");

answered Mar 12 '14 at 14:01

Rossco

3,563
3
24
37

THen i have the users with level 9 up and below 31. But i need a list with users with level 9. Another list with users with level 10. Another list with users with level 11. – user2519424 Mar 12 '14 at 14:03
I think you will have to use for loops for this then as you are going to be wanting to put the output in separate div's not so? Like based on the query where level=10 then you will want to put the output into a div with an id of 10? – Rossco Mar 12 '14 at 14:07
yeah i already use loops but that is to sloow, website is lagging with more then 100 people online. – user2519424 Mar 12 '14 at 14:09

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

You can try to use

$levels = array(10,20,30,40,50,60); //and goes one
$lsel_lijst = mysql_query("SELECT * FROM users WHERE level IN (".implode(',', $levels).") AND (owner = $id AND extern = 0 ) OR ( extern = $id )")

Every SQL squery has to fit in the Batch Size Limit which is 65,536 * Network Packet Size (around 250MB). More info about max query size you will find here Limit on the WHERE col IN (...) condition

edited May 23 '17 at 11:57

Community

1
1

answered Mar 12 '14 at 14:02

PolishDeveloper

930
5
17

Ok and then what is the code for getting only the players with level 10? – user2519424 Mar 12 '14 at 14:08
to get players only with level of 10 you just put "in (10)" in SQL statement. The only thing that you have take care of is situation when array is empty. When you send a statement " level in () AND ..." you will get a SQL Error - invalid syntax. – PolishDeveloper Mar 12 '14 at 14:35

score 0 · Answer 4 · answered Mar 12 '14 at 14:03

Select everything as you do in the first then use the level param to create your div's.

if ($lsel_lijst) {
  while($row = mysql_fetch_array($lsel_lijst)) {
     <!-- write your div code here <div className="divLevelnumber" -->
  }

}
else {
  echo mysql_error();
}

Kei · Answer 5 · 2014-03-12T14:18:51.343

while ($row = mysql_fetch_assoc($lsel_lijst))
    $list[$row['level']][] = $row;

In this way you'll have an array ($list) which will contain many sub-arrays indentified by their level:

$list = array(
    1 => array(level1 entries...),
    2 => array(level2 entries...)
);

Beware, if there is an empty group (for example no level 1), the $list array will have its value at index 1 null ($list[1] = null)

And, stop using "mysql" set of functions, instead (if possible) use mysqli or PDO. Using a deprecated extension is bad, really bad.

EDIT: looking at your comment

yeah i already use loops but that is to sloow, website is lagging with more then 100 people online.

Remind that relational databases will always return a "table" (set of results) as result. There is no practical way to make it return an array or a different structure. That's why fetch functions are there, they convert your result (set of results, rows) in a language structure (like an array: mysql_fetch_array(), mysql_fetch_assoc(). Or as an object mysql_fetch_object()).

Query help (Sql and PHP)

5 Answers5