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

All good... I've beenm chattingto the people who will be using the system, and think I'm gonna do a phase 1, then 2 etc.. with phase 1, being just a simple search option, and add mor elater... Keeps the masses happy, and gives me more time... still need to sort out my prob though :-( CHeers for trying!!! Jochem Maas <jochem@iamjochem.com> 08/03/2005 17:05 To cc php-db@lists.php.net Subject Re: [PHP-DB] PHP and JOIN... (I know.. it's easy!) Tristan.Pretty@risk.sungard.com wrote: > Cool. cheers for the quick responce... > Well, my flatmate is one of two, I live with a lesbian couple in London... > One is a falsh guru, sho recently got into PHP (she's a REAL brain box, > and picsk stuff up sooo much quick than us mere mortals!) > Hard life init ;-) shucks. :-) > > Anyhoo, my prob, in more detail, with table structures (at the bottom!)... > > An admin user should be able to: > Select one (or more, though I'm fine with looping, once I get this first > one done) file ID number to see who's downloaded it, and see detailed info > on them. > > So I'm listing a list of all file id No's on page 1. > when the user selects the ID they wanna report on, the MYSQL query should > read something like: > > 1. Get, and count user_id from table captures. > 2. get all user info from table users, based on above user_id. > > 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... some books are worth their weight in gold. some not. > > Please find my table structure below... very good :-). I so *(*^*& busy that I haven't got time to get my head into mySQL mode. so to other listers with some JOIN foo: I've extracted sufficient info from the OP, any care to finalize? :-) (sorry tristan) > > =============================== > > // 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 ; > | > > > > ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php