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 ;-)
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...
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 ;
|
Jochem Maas <jochem@xxxxxxxxxxxxx>
08/03/2005 10:21
To
cc
php-db@xxxxxxxxxxxxx
Subject
Re: PHP and JOIN... (I know.. it's easy!)
Tristan.Pretty@xxxxxxxxxxxxxxxx wrote:
> Hi all, long time no post...
> I've rebuilt a download tracking system my company uses.
> I built the original, and thought that a restructure would be a doddle!
> Ooops! ;-)
>
> Anyhoo...
> I used to capture each download in it's own row, replicating user data
> each time.
> (Eg: if a user downloaded 5 files, I'd capture their address, email etc,
5
>
> times)
>
> For the redesign, I've got several tables.
> a file list table, a user list table, and a capture list table.
>
> I'm built 90% of the front end, and it works great!
> However, I'm having trouble getting my head round the reporting side of
> things. (probably need to stop thinking about it for a while, but I
don't
> have that luxury)
>
> My admin users, will come in, and select a file ID to view who's
> downloaded it.
> So with a file ID No, I need to also get the following:
> 1. Get the file info from the file table
> 2. go to the captures table, get all the fields where the file id is the
> same as theonce requested
the admin user can specify multiple files simultaneously to inspect?
> 3. count each user that has downloaded it
> 4. get thier data from the users table
>
> Now I could do this with 4 seperate queries, and a few for loops etc...
> But I'm trying to figure out JOIN... as I'm told I can do it all in one
> query, and make a temp fake table?
can't see why you need a temp table at this junture - maybe you could
explain _exactly_ what you want to report to the user (and not so much
how/where
you intend to get the data from)
>
> I've googled for a decent tutorial, but perhaps I'm just panicing, but
> they all seem over my head...
> (My flatmate is a PHP developer too, and she says that it's easy!.. so I
six women in the world that grok php... and you live with one, b'std :-)
> hope I'm just stressed)
>
> Anyhoo, does anyone know of a decent site, that'll really dumb it down
for
>
> me, or could talk me through what I'm after?
whaaaaa you want more??? the female flatmate who groks php is not enough
:-)
only kidding.
>
> Sorry for the long winded E-mail.. but I'm pulling my hair out here..
:-(
post your table structures. that way we can see which fields to join on...
btw this is a pure SQL problem - no php knowledge technically require, not
to
worry!
>
> Tris...
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
