Hi Tris,
> 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
> 3. count each user that has downloaded it
> 4. get thier data from the users table
Yeah, you need a join.
Assuming you've got a "Files" table with these columns:
- FileID
- Filename
A "Captures" table with these:
- CaptureID
- UserID
- FileID
- Timestamp
And a "Users" table with these:
- UserID
- Username
I'd start off with this query:
SELECT CaptureID
, Filename
, c.UserID
, Username
, Timestamp
FROM Captures c
INNER JOIN Files f
ON c.FileID = f.FileID
INNER JOIN Users u
ON c.UserID = u.UserID
WHERE c.FileID = <your file ID here>
ORDER BY Timestamp DESC
(Typed out in email, not tested, but should work in MSSQL)
With a bit of luck you'll get results like this:
CaptureID Filename UserID Username Timestamp
1 whatever 1 Anne 1st Jan 10:00
2 whatever 1 Anne 1st Jan 11:15
3 whatever 5 Bob 2nd Jan 15:30
4 whatever 8 Charlie 3rd Jan 09:45
Take out the WHERE clause and you get the same thing for all files instead
of just one.
Note that if any rows in Captures have an empty UserID or FileID this query
won't pick them up unless you change the INNER JOINs to LEFT JOINs.
> 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?
Zend.com has good PHP articles, but this is more a database thing - which
one are you using?
Cheers
Jon
--
This message has been scanned for viruses and
dangerous content by Swift Internet, and is
believed to be clean.
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
