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@xxxxxxxxxxxxx> 08/03/2005 17:05 To cc php-db@xxxxxxxxxxxxx Subject Re: PHP and JOIN... (I know.. it's easy!) Tristan.Pretty@xxxxxxxxxxxxxxxx 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@xxxxxxxxxxx (12 times) > bob@xxxxxxxx (1 times) > bush@xxxxxx (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
