CODEWITHSUNDEEP
phpmysql
Eduardo VSales
Eduardo VSales

Reputation: 39

How to join two mysql querys from two tables

I have two independents querys:

Query 1 (This query get all gallery from my table) :

$showgallerys = mysqli_query($con,"SELECT * FROM canais");
while($row = mysqli_fetch_array($showgallerys)) {
echo '<div class="canal-nome">'.$row['nome'].'</div>;

Query 2 (This query count numbers of photos per category):

$q="SELECT categoria, COUNT(titulo) FROM galerias GROUP BY categoria ";
$res=mysqli_query($con,$q);
while($row = mysqli_fetch_array($res)){
 echo '
('. $row['COUNT(titulo)'] .')';
}

I need to show the name of the gallery (query 1) with the numbers of photos (query 2)

like this Gallery name (30)

This the structure of first table (called canais): 

  id | nome            | htd     | imagem   | thumb    |
   1 | Gallery Nature  | nature  | face.jpg | thumb.jg | 
   2 | Gallery Peoples | people  | face.jpg | thumb.jg | 
   3 | Gallery Animals | animal  | face.jpg | thumb.jg |

This the structure of second table (called galerias)

  id | titulo       | foto    | thumb           | data    | categoria |
  1  | Sun          | sun.jpg | sun-thumb.jpg   | now     | nature    |
  2  | Moon         | mon.jpg | mon-thumb.jpg   | now     | nature    |
  3  | Tree         | tree.jpg| tre-thumb.jpg   | now     | nature    |
  4  | Woman        | wman.jpg| wman-thumb.jpg  | now     | people    |
  5  | Girl         | gran.jpg| gr-thumb.jpg    | now     | people    |
  6  | leaf         | lea.jpg | leaf-thumb.jpg  | now     | nature    |
  7  | dog          | dog.jpg | dog-thumb.jpg   | now     | animal    |

In this case i need show the results like this:

   Gallery name --> Gallery Nature (4) <-- Number of  occurrences 
   Gallery name --> Gallery People (2) <-- Number of  occurrences 
   Gallery name --> Gallery Animal (1) <-- Number of  occurrences

being the name of the gallery must be obtained through the "canais" table and the number of occurrences must come from the "galerias" table based in the "categoria" column. Can anyone help me to solve it ?

Upvotes: 0

Views: 45

Answers (1)

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Below query will help you

SELECT a.nome AS gallery  ,COUNT(b.titulo)  AS photos 
FROM canais a INNER JOIN galerias b ON a.htd    = b.categoria 
GROUP BY a.nome

Upvotes: 2

Related Questions