Re: PHP and JOIN... (I know.. it's easy!)

Tristan Pretty wrote: > I'll then make a page that looks like this: > > file name: User(s) > 12.doc user@website.com (12 times) > bob@bill.com (1 times) > bush@dc.gov (2 times) > etc...... > > I'll make the email addresses/file name links to detailed info, but I'm > fine with doing that... > > Once I see a few Joins in action based on what I'm trying to do, I'll get > it.. I'm self taught (as most of us are right?) and learn better from real > life example than books... > > Please find my table structure below... > > =============================== > > // stores only file id, user id and date etc... > CREATE TABLE captures ( > id int(11) NOT NULL auto_increment, > user_id int(11) NOT NULL default '0', > date date NOT NULL default '0000-00-00', > file_id int(8) NOT NULL default '0', > page_id int(11) NOT NULL default '0', > ip varchar(255) NOT NULL default '', > PRIMARY KEY (id) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ; > > // all data pertainign to the file > CREATE TABLE files ( > file_master_bu varchar(255) NOT NULL default '', > id int(8) NOT NULL auto_increment, > uploaded_by varchar(255) NOT NULL default '', > uploaded_date date NOT NULL default '0000-00-00', > edited_by varchar(255) NOT NULL default '', > edited_date date NOT NULL default '0000-00-00', > file_title varchar(255) NOT NULL default '', > file_name varchar(255) NOT NULL default '', > file_cat varchar(255) NOT NULL default '', > file_type varchar(255) NOT NULL default '', > file_desc text NOT NULL, > PRIMARY KEY (id) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ; > > // the users who fill out forms, are put here. I do a search each download > to see if they're in the database already, based on email address... but > that's kinda irrelevent here... ;-) > CREATE TABLE users ( > id int(11) NOT NULL auto_increment, > salutation varchar(255) NOT NULL default '', > forename varchar(255) NOT NULL default '', > surname varchar(255) NOT NULL default '', > email varchar(255) NOT NULL default '', > tel varchar(255) NOT NULL default '', > fax varchar(255) NOT NULL default '', > company varchar(255) NOT NULL default '', > job_title varchar(255) NOT NULL default '', > street varchar(255) NOT NULL default '', > street2 varchar(255) NOT NULL default '', > city varchar(255) NOT NULL default '', > zip varchar(255) NOT NULL default '', > state varchar(255) NOT NULL default '', > country varchar(255) NOT NULL default '', > hear varchar(255) NOT NULL default '', > us_opt varchar(255) NOT NULL default '', > eu_opt varchar(255) NOT NULL default '', > PRIMARY KEY (id) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; select count(captures.id)file_name,email from capture,files,users where users.id=captures.user_id and files.id=captures.file_id group by file_name; That /may/ do it. -- kae verens http://verens.com/