CODEWITHSUNDEEP
phpmysql
Filip Krstic
Filip Krstic

Reputation: 713

Select from one table, and count from another table in same query

I have quite a bit of knowledge about SQL queries.

I'm trying to make gallery, and I need to select categories from table "cat_photos", which contain rows (id,name,cover,photo) and count number of photos from table "photos" which contain rows (id,thumb,photo,category).

Here is code which i use:

1) Selecting categories

$query = mysql_query("SELECT * FROM cat_photos ORDER BY ID DESC");
               while($data = mysql_fetch_array($query)) {       
        echo "<li><a href='photos.php?cat=$data[id]'><img src='galleries/categories/$row[image]' alt='$row[name]' /></a>
              <div class='photodesc'><div class='catname'><a href='photos.php?cat=$row[id]'>$row[name]</a></div>
              <div class='catcount'>Number of photos in category</div></div></li>"; }

2) Counting number of photos in category

$query = mysql_query("SELECT category, COUNT(photo) FROM photos GROUP BY category") or die(mysql_error());
               while($row = mysql_fetch_array($query)){
        echo "Number of photos is ". $row['COUNT(photo)'] ." in cateogry ". $row['category'] .".";
        echo "<br />"; }

Separated all works, but I can't find a way to merge them into one query. I have googleing for "UNION", "JOIN", "LEFT JOIN" options in MySql query but I could't together the pieces.

I wonder if this is in general possible? How in order that query look like?

Upvotes: 2

Views: 4041

Answers (3)

krtek
krtek

Reputation: 26617

Try this, it should work :

SELECT cat_photos.*, count(photos.id) as number_photos
FROM cat_photos
LEFT JOIN photos ON photos.category = cat_photos.id
GROUP BY cat_photos.id, cat_photos.name, cat_photos.image
ORDER BY cat_photos.id

The number of photos will be accessible trough $row['number_photos'].

Upvotes: 5

Michael Berkowski
Michael Berkowski

Reputation: 270767

Don't SELECT *. Instead select individual columns and then join:

SELECT 
  cat_photos_main.id, cat_photos_main.category, cat_photos_main.photodesc, cat_photos_counts.num_photos
FROM cat_photos cat_photos_main
LEFT OUTER JOIN (SELECT category, count(*) AS num_photos FROM photos GROUP BY category) cat_photos_counts 
  ON cat_photos_main.category = cat_photos_counts.category

Upvotes: 2

Cadoc
Cadoc

Reputation: 249

Just use your second query and join the wanted category elements.

Something quick and dirty would be:

SELECT c.category, COALESCE(COUNT(p.photo),0) as photos FROM photos p, cat_photos c
WHERE c.category = p.category
GROUP BY category

Since I don't know your exact database setup just change the selected elements to the ones you really need.

//edit: Put in Coalesce to get categories with 0 photos.

Upvotes: 2

Related Questions