I have a problem with one task. I need to display various information through two diferent queries.(or one if it could be possible)
The table has these fields. Location is set to x

+------------+------------+-------------+
| blocked    | type       | location    |
+------------+------------+-------------+
|          0 |          a |           x |
|          0 |          b |           x |
|          0 |          c |           x |
|          0 |          a |           x |
|          0 |          d |           x |
|          0 |          e |           x |
|          0 |          b |           x |
|          0 |          a |           x |
+------------+------------+-------------+

My first query is to get all entries from certain types

SELECT COUNT( * ) AS  `how much` ,  `type`
FROM  table
WHERE  (`type`='a' OR `type`='b' or `type`='d')
AND `blocked`='0'
GROUP BY   `type`
ORDER BY   `type` 

which gives me

+------------+------------+
| how much   | type       |
+------------+------------+
|          3 |          a |
|          2 |          b |
|          2 |          d |
+------------+------------+

Next thing i need to get entries that have a certain location

SELECT COUNT( * ) AS  `how much` ,  `type`
FROM  table
WHERE  (`type`='a' OR `type`='b' or `type`='d')
AND `blocked`='0'
AND `location`='y'
GROUP BY   `type`
ORDER BY   `type` 

this would return nothing since everything is set to x.

I need the query to return zero or the right value. So if i set the location on one d to y, the result of the original query would be:

+------------+------------+
| how much   | type       |
+------------+------------+
|          1 |          d |
+------------+------------+

I need it to be:

+------------+------------+
| how much   | type       |
+------------+------------+
|          0 |          a |
|          0 |          b |
|          1 |          d |
+------------+------------+

That is because i display the results using php. The values from the first query are stored to two different arrays in a while loop and the second query to a third array in another while loop. Than they are displayed through a for loop

Hence the current queries would result in this

+------------+-------------------+--------------------------+
| type       | how much records  | how much in location y   |
+------------+-------------------+--------------------------+
|          a |                 3 |                        1 |
|          b |                 2 |                          |
|          d |                 2 |                          |
+------------+-------------------+--------------------------+

AND NOT THIS

+------------+-------------------+--------------------------+
| type       | how much records  | how much in location y   |
+------------+-------------------+--------------------------+
|          a |                 3 |                        0 |
|          b |                 2 |                        0 |
|          d |                 2 |                        1 |
+------------+-------------------+--------------------------+

Thank you very much for any help.

EDIT:
QUERIES:

$query_1 = $conn->query("SELECT COUNT( * ) AS how_much, type FROM table WHERE (type='a' or type='b' or type='d') AND blocked='0' GROUP BY type ORDER by type") or trigger_error($conn->error);
$query_2 = $conn->query("SELECT COUNT( * ) AS how_much, type FROM table WHERE (type='a' or type='b' or type='d') AND blocked='0' AND location='y' GROUP BY type ORDER by type") or trigger_error($conn->error);
$query_max = $conn->query("SELECT COUNT( * ) AS maximum FROM (SELECT COUNT( * ) AS how_much, type FROM table WHERE (type='a' or type='b' or type='d') AND blocked='0' GROUP BY type ORDER by type) AS max") or trigger_error($conn->error);
$max_entries = mysqli_fetch_array($query_max);

ARRAYS:

while($row_1 = mysqli_fetch_array($query_1))
{       
 $types[]= $row_1["type"];
 $how_many_entries[]= $row_1["how_much"];
}

while($row_2 = mysqli_fetch_array($query_2))
{       
 $how_many_in_location[]= $row_2["how_much"];
}

Printing out:

echo ("<table border='1' align='center'>
         <tr><td colspan='3'>Types a,b,c</td></tr>
         <tr><td>Types</td><td>How many entries</td><td>How Many in Location</td></tr>");

for($i=0;$i<$max_entries[0];$i++)
      {echo("<tr>
               <td><a href=some_url.php?menu=".$types[$i].">".$types[$i]."</a></td>
               <td>".$how_many_entries[$i]."</td>
               <td>".$how_many_in_location[$i]."</td>
              </tr>");

So i think the best thing without rewriting most of it would be to edit $query_2 to include 0 values

The way I’m understanding your question is that since the arrays for a and b don’t have a value for ‘in location y’, the loop ‘skips over’ it and displays the value 1 for d in the first open row.

You could try pulling out locations for all the arrays (even the non-y values), and then using an if statement to display either 0 or the result you want.

You can also try putting everything into one for loop, and the arrays that don’t have a location of y output 0.

Additionally, try looking into the isset(); function in php for those arrays that didn’t get the location pulled.

If you post the code that you’re using to pull and display the results, we might be able to give better/more accurate advice.

You don’t need any looping in PHP

Just use a single aggregation query

SELECT COUNT(B.type) "how much",A.type
FROM
(SELECT 'a' type UNION SELECT 'b' UNION SELECT 'c') A
LEFT JOIN
(SELECT type FROM table WHERE `blocked`='0' AND `location`='y') B
USING (type)
GROUP BY A.type
ORDER BY A.type;

Subquery A is essentially an array of types

I would also index the table by blocked, location, and type to speed up Subquery B

ALTER TABLE table ADD INDEX blocked_location_type_ndx (blocked,location,type);

Give it a Try !!!

you should use IN() instead of OR. Because if 1 condition satisfy in OR it will give true flag. That is why it gives only one record.