Page 1 of 1

album names with photo count sql query!!!

Posted: Thu Aug 23, 2007 2:44 pm
by kunalm
pickle | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.  I'll let you edit your other posts.[/color]


Hello Gurus
                  I am trying to develop a photo gallery website with php4 and mysql 4(xaampp). Now I have a album table(id, name, desc, created by) and then photos table(id, name, desc,album_id...). Now I want to write a sql query so that i get the following fields:

album id
album name
photo count
where created by a particulaer user id

for that i have used the following query
[syntax="sql"]SELECT pg_albm_id , pg_albm_title,count(pg_photo_id) 
FROM pg_photo_albums
LEFT OUTER JOIN pg_photos ON pg_photo_albums.`pg_albm_id` = pg_photos.`pg_photo_albumid`
WHERE t1.`pg_albm_createdby`='kunalm'
GROUP BY pg_photos.`pg_photo_id`
But its not returning all sets created by this user. This user has total 4 albums in my samle records, but returning only one. Can you tell me what I am missing or is there any mistake?

Pls help me out. Thanks in advance for your help.


pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.  I'll let you edit your other posts.[/color]

albums table export text

Posted: Thu Aug 23, 2007 2:45 pm
by kunalm
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]-- 
-- Table structure for table `pg_photo_albums`
-- 

CREATE TABLE `pg_photo_albums` (
  `pg_albm_id` int(11) NOT NULL auto_increment,
  `pg_albm_title` varchar(100) default NULL,
  `pg_albm_desc` varchar(250) default NULL,
  `pg_albm_createdon` datetime default NULL,
  `pg_albm_createdby` varchar(15) default NULL,
  `pg_albm_isprivate` tinyint(1) default NULL,
  `pg_albm_hits` int(11) default NULL,
  `pg_cat_id` int(11) default NULL,
  PRIMARY KEY  (`pg_albm_id`)
) TYPE=MyISAM  AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `pg_photo_albums`
-- 

INSERT INTO `pg_photo_albums` (`pg_albm_id`, `pg_albm_title`, `pg_albm_desc`, `pg_albm_createdon`, `pg_albm_createdby`, `pg_albm_isprivate`, `pg_albm_hits`, `pg_cat_id`) VALUES 
(1, 'First Test Album 12', 'This is just a test album', '2007-08-21 02:08:37', 'kunalm', 0, 23, 1),
(2, 'Nature Pics 2323', 'This category holds all albums for nature images', '2007-08-15 02:09:16', 'chitla123', 0, 12, 1),
(3, 'First Test Album sfdff', 'This is just a test album', '2007-08-21 02:08:37', 'kunalm', 0, 23, 1),
(4, 'Nature Pics 23', 'This category holds all albums for nature images', '2007-08-15 02:09:16', 'chitla123', 0, 12, 1),
(5, 'First Test Album 56', 'This is just a test album', '2007-08-21 02:08:37', 'kunalm', 0, 23, 1),
(6, 'Nature Pics 4', 'This category holds all albums for nature images', '2007-08-15 02:09:16', 'chitla123', 0, 12, 1),
(7, 'First Test Album 1aed3', 'This is just a test album', '2007-08-21 02:08:37', 'kunalm', 0, 23, 1),
(8, 'Nature Pics 45gg', 'This category holds all albums for nature images', '2007-08-15 02:09:16', 'chitla123', 0, 12, 1);

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

export text for photos table

Posted: Thu Aug 23, 2007 2:46 pm
by kunalm
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]-- 
-- Table structure for table `pg_photos`
-- 

CREATE TABLE `pg_photos` (
  `pg_photo_id` int(11) NOT NULL auto_increment,
  `pg_photo_title` varchar(100) default NULL,
  `pg_photo_desc` varchar(250) default NULL,
  `pg_photo_createdon` datetime default NULL,
  `pg_photo_lastmodifiedon` datetime default NULL,
  `pg_photo_isprivate` tinyint(1) default NULL,
  `pg_photo_albumid` int(11) default NULL,
  `pg_photo_hits` int(11) default NULL,
  `pg_photo_keywords` varchar(250) default NULL,
  `pg_photo_uploaded_by` varchar(15) default NULL,
  `pg_photo_size` varchar(10) default NULL,
  `pg_photo_type` varchar(10) default NULL,
  `pg_equip_id` int(11) default NULL,
  PRIMARY KEY  (`pg_photo_id`)
) TYPE=MyISAM  AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `pg_photos`
-- 

INSERT INTO `pg_photos` (`pg_photo_id`, `pg_photo_title`, `pg_photo_desc`, `pg_photo_createdon`, `pg_photo_lastmodifiedon`, `pg_photo_isprivate`, `pg_photo_albumid`, `pg_photo_hits`, `pg_photo_keywords`, `pg_photo_uploaded_by`, `pg_photo_size`, `pg_photo_type`, `pg_equip_id`) VALUES 
(1, 'Our_World.jpg', 'This is our world. We need to take care of it. Don''t waste energy', '2007-08-22 02:12:25', '2007-08-22 02:12:25', 0, 2, 10, 'world us care', 'chitla123', '32.0 KB ', 'JPEG', 10),
(2, 'Bella_46.jpg', 'Come kiss me baby. Have you seen lips like this before?', '2007-08-22 02:16:45', '2007-08-22 02:16:45', 0, 2, 21, 'lips kiss love', 'kunalm', '32.0 KB ', 'JPEG', 10),
(3, 'Blue_Butterfly.jpg', 'Nature is full of colours. see it and enjoy it. But don''t destroy it.', '2007-08-22 02:19:34', '2007-08-22 02:19:34', 0, 2, 34, 'butterfly nature beutiful', 'chitla123', '48.0 KB', 'JPEG', 10);

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Aug 23, 2007 3:26 pm
by xpgeek
Try to remove OUTER from you query.