Join SELECT FROMworking

hi i am trying to display a list with data coming from two tables but its not wrking something not wrking help please

TABLE photos album_id photo, name
TABLE albums album_id, album_name

$query = "SELECT `album_id`, `album_name`, COUNT(`photo_name`) AS `count` FROM `photos`, `albums` GROUP BY `album_id` ON albums.album_name = photos.album_id";

i manage to get to wrk but it only display the album if thers an item on it how do i get to display album even if no items been inside

this how is looking now

$query = "SELECT `albums`. `album_name`, `photos`. `album_id`, COUNT(`photo_name`) AS `count` FROM photos

INNER JOIN albums ON albums.album_id=photos.album_id GROUP BY `album_id`";

     
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "<a href='portfolio.php?albums=".$row['album_id']."'><img src='photos/albumcover.jpg' width='60'  height='60'></a>";echo "<br />";
    echo "". $row['album_name'] . $row['count'] ;
    echo "<br />";
}

yes

i fixed a bunch of stuff, and i wanted you to find and fix at least one

:slight_smile:

did u look at the other one the delete album???

In that case make a backup first and try it then :slight_smile:

when i tried what change Inner to left i didnt try i’m scared:sick::sick: to messed up
if u mean my new problem the album delete problem gives me error
Delete photo failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 5
which one do u mean???

What happened when you tried? :cool:

this is another way i tried but now same error but on line 5

<?php
define('ROOT_DIR', './');
define('PROPER', TRUE);
/**
* include common files
*/
include_once(ROOT_DIR. 'includes/common.inc.php');


// No album id has been selected
if (isset($_GET['albums'])) 
 {
   // get the image file name so we
   // can delete it from the server
   $sql = "SELECT album_id, album_name, album_owner, sub_album

           FROM albums

           WHERE album_id = {$_GET['albums']}";

   $result = mysql_query($sql)

             or die('Delete photo failed. ' . mysql_error());

   if (mysql_num_rows($result) == 1) {

      $row = mysql_fetch_assoc($result);

      // get the image filenames first so we can delete them
      // from the server
     $sql = "SELECT photo_id, photo_proper

           FROM photos

           WHERE photo_id = {$_GET['photos']}";

   $result = mysql_query($sql)

             or die('Delete photo failed. ' . mysql_error());

   if (mysql_num_rows($result) == 1) {

      $row = mysql_fetch_assoc($result);
      
define("GALLERY_IMG_DIR", "./photos/");

      // remove the image and the thumbnail from the server
      unlink(GALLERY_IMG_DIR . $row['photo_proper']);

      unlink(GALLERY_IMG_DIR . 'thumbs/' . $row['photo_proper']);
      
      // and then remove the database entry

      $sql = "DELETE FROM photos

              WHERE photo_id = {$_GET['photos']}";
      $result = mysql_query("DELETE FROM album
                             WHERE album_id = $album_id")
                or die('Delete album failed. ' . mysql_error());

      // album deleted successfully, let the user know about it
      echo "<p align=center>Album '$album_name' deleted.</p>";
   } else {
      echo "<p align=center>Cannot delete a non-existent album.</p>";
   }

}
}

?>

and here I thought you made a mistake.

well Inner join kind wrk do left join if i just change inner to left will it wrk??? if too complicated i think i will just leave as it is as i have another problem i was about to post here different topic bt thought might ask hopefully u get where i am going wrong??

i do think is a small mistake i making can u please have a look if u can
ok here’s the problem i am trying to delete an album within the album should also delete the photos related to that album this what i tried gives this error

Delete image failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 3

my tables are
table albums fields album_id, album_name, album_owner, sub_album
table photos fields, photo_id, photo_name, photo_extension, photo_proper photo_owner, photo_date, photo_comments, photo_size, album_id

photo_proper is the name image stored in folder

<?php
define('ROOT_DIR', './');
define('PROPER', TRUE);
/**
* include common files
*/
include_once(ROOT_DIR. 'includes/common.inc.php');


// No album id has been selected
if (isset($_GET['albums'])) 
   // get the album name since we need to display
   // a message that album 'foo' is deleted
   $result = mysql_query("SELECT album_id, album_name, album_owner, sub_album
                          FROM albums
                          WHERE album_id = $album_id")
             or die('Delete image failed. ' . mysql_error());
   if (mysql_num_rows($result) == 1) {
      $row        = mysql_fetch_assoc($result);
      $album_id  = $row['album_id'];
      $album_name = $row['album_name'];

      // get the image filenames first so we can delete them
      // from the server
      $result = mysql_query("SELECT photo_id, photo_id
                             FROM photos
                             WHERE album_id = $album_id")
                or die(mysql_error());
      while ($row = mysql_fetch_assoc($result)) {
      
    define("GALLERY_IMG_DIR", "./photos/");

      
      unlink(GALLERY_IMG_DIR . $row['photo_proper']);
      unlink(GALLERY_IMG_DIR . 'thumbs/' . $row['photo_proper']);

      }

      $result = mysql_query("DELETE FROM photos
                             WHERE album_id = $album_id")
                or die('Delete image failed. ' . mysql_error());
      $result = mysql_query("DELETE FROM album
                             WHERE album_id = $album_id")
                or die('Delete album failed. ' . mysql_error());

      // album deleted successfully, let the user know about it
      echo "<p align=center>Album '$album_name' deleted.</p>";
   } else {
      echo "<p align=center>Cannot delete a non-existent album.</p>";
   }



?>


If I understand your request correctly sounds like you want to use a left join instead of a inner join.

that couldn’t have worked, you have an ambiguous column in the GROUP BY clause

plus, you put the wrong table as the outer table

SELECT albums.album_id
     , albums.album_name
     , COUNT(photos.album_id) AS count 
  FROM albums 
LEFT OUTER
  JOIN photos
    ON photos.album_id = albums.album_id
GROUP 
    BY albums.album_id DESC

wow A BIG THANK YOU VERY MUCH wow :):blush:

it gives error
Column ‘album_id’ in field list is ambiguous

Left join did same thing here’s how it looks like
$query = "SELECT albums. album_name, photos. album_id, COUNT(photo_name) AS count FROM photos

LEFT JOIN albums ON albums.album_id=photos.album_id GROUP BY album_id DESC";
doesnt display album if no item inside of it

New topic started on delete question here: http://www.sitepoint.com/forums/showthread.php?t=701885

Since everything discussed in this thread now works this is thread is hereby closed.

  1. the GROUP BY clause goes at the end
  2. the ON clause requires JOIN syntax
  3. you were connecting the wrong columns
  4. please learn indentation and line breaks to make your SQL more readable
SELECT album_id
     , album_name
     , COUNT(photo_name) AS count 
  FROM photos
INNER
  JOIN albums 
    ON albums.album_id = photos.album_id
GROUP 
    BY album_id