Re: 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 ; |

....