php mysql merge multiple same queries into one

Question

So i have some range from 1 to 40,

which i have to use in order execute different queries from the same table and column but from a different range like:

mysql_query("SELECT * FROM table WHERE column > 1 && column <= 15");
mysql_query("SELECT * FROM table WHERE column >=18 && column <= 30");
mysql_query("SELECT * FROM table WHERE column >= 35 && column <= 38");

i get the total rows from these queries one by one for different actions... but how can i combine these 3 for example queries into 1 and get the same result?

Niko Efimov · Accepted Answer · 2011-06-15 19:39:25Z

5

SELECT * FROM table 
WHERE (column > 1 AND column <= 15) 
OR (column >= 18 AND column <= 30) 
OR (column >= 35 AND column <= 38)

// EDIT: OP Wants to Count number of rows for each condition:

SELECT 
(SELECT COUNT(*) FROM table WHERE column > 1 AND column <= 15) AS count_1, 
(SELECT COUNT(*) FROM table WHERE column >= 18 AND column <= 30) AS count_2, 
(SELECT COUNT(*) FROM table WHERE column >= 35 AND column <= 38) AS count_3 
FROM table

count_1, count_2, and count_3 are the numbers of rows for each condition.

edited Jun 15, 2011 at 19:39

answered Jun 15, 2011 at 19:16

Niko Efimov

2,2233 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stergosz Over a year ago

i want to get the number of rows from table where column is from range 1 to 15 , 18 30 and 35 38 seperately but in one query

Andrew Curioso Over a year ago

@fxuser Can you elaborate what you mean by "separately but in one query"?

stergosz Over a year ago

when i execute the 3 queries on my question i get 3 numbers for example 143, 4343 and 938 ... how can i make these 3 queries one and get the same result?

Andrew Curioso · Accepted Answer · 2011-06-15 20:01:23Z

2

Heavily edited because I misread the question

If you want all the rows you could just do:

SELECT * FROM table WHERE (column > 1 AND column <= 15) OR
  (column >=18 AND column <= 30) OR
  (column >= 35 AND column <= 38)

However, if you just want the count for each, that is not the right way to go about it. The count can be calculated directly in MySQL using count(*). Even if you were to do three separate queries it would be better to use count(*) over selecting all the rows (it used much less memory).

With that said, you can get the count for all the rows like this:

$query = "SELECT ".
      " (SELECT COUNT(*) FROM table WHERE column > 1 AND column <= 15) AS count_1,".
      " (SELECT COUNT(*) FROM table WHERE column >= 18 AND column <= 30) AS count_2,". 
      " (SELECT COUNT(*) FROM table WHERE column >= 35 AND column <= 38) AS count_3";

$res = mysql_query($query);
extract( mysql_fetch_assoc($res) );

echo "The counts are $count_1, $count_2, and $count_3";

The extract function will take an associative array and set local variables for each item in that array. I just figured it would be easier to use than dealing with the array returned from mysql_fetch_assoc.

Note: @fn-n posted the SQL first, I just formatted it into PHP. I'd write it a different way except (s?)he already did it the right way :)

edited Jun 15, 2011 at 20:01

answered Jun 15, 2011 at 19:18

Andrew Curioso

2,19113 silver badges25 bronze badges

5 Comments

stergosz Over a year ago

probably i didnt explain it as much as possible, currently i get all rows where column is from 1 to 15 etc... i do this 3 times and get different number of rows from each query.. i dont want to combine the query and get the total rows, i want to get the same different count of rows but in one query

Andrew Curioso Over a year ago

So what is the end result that you want? Multiple PHP arrays each with the data set? MySQL can only return a single dimension of results but there are ways to separate the data out by doing some post-processing of the returned rows... is that what you need?

stergosz Over a year ago

quote from my above comment:when i execute the 3 queries on my question i get 3 numbers for example 143, 4343 and 938 ... how can i make these 3 queries one and get the same result?

Andrew Curioso Over a year ago

@fxuser Ah, so you need to get the same value as if you had done three queries and called mysql_num_rows? Do you need the data too or just the count?

Andrew Curioso Over a year ago

@fxuser Got it. The reason we were so confused is that it is not typical to get all the rows in order to just get the count. That is what count(*) is for and it uses much less memory and is much more efficient. I updated my answer with an example.

Collectives™ on Stack Overflow

php mysql merge multiple same queries into one

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related