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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tristan Pretty wrote:
> 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 ;


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/

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux