Total Space Left

homer09001

ok so im writing a file management system, that allows members to upload files, each member is assigned a user level which has a specific Quota of space on the server, what i need my script to do is show the total amount of space currently used.

my databases are constructed as follows:

MF_Members:

| ID | UName | PWord | ULevel | Regcode |

| 01 | tester | MD5() | 1 | unique md5() |

MF_Lvls

| ID | Quota |

| 01 | xBytes|

| 02 | xBytes|

MF_Files

| ID | Filename | Size | Usercode | Access |

| 01 | test.zip | 1024 | Regcode | P |

Usercode is what ties a file to a user, Usercode == user Regcode
Access is what defines a public file to a private file.

so bassicaly i need my script to show the logged in user their quota used and their quota left.

so it would be a case of getting the userlevel checking teh quota for that user level, then adding the total of all the users files sizes and subtracting them from the user level quota, i could right a long list of queries to do this but they probly would be all that efficient so what would the most efficient way iof doing this?

nemonoman

SELECT MAX(Quota), SUM(Size)
FROM MF_Members, MF_Lvls, MF_Files
WHERE MF_Files.Usercode=MF_Members.Regcode
AND MF_Members.ULevel=MF_Lvls.ID
AND MF_Members.Regcode=$someSearchValue
GROUP BY MF_Members.Regcode

Should work.

homer09001

nemonoman wrote:
SELECT MAX(Quota), SUM(Size)
FROM MF_Members, MF_Lvls, MF_Files
WHERE MF_Files.Usercode=MF_Members.Regcode
AND MF_Members.ULevel=MF_Lvls.ID
AND MF_Members.Regcode=$someSearchValue
GROUP BY MF_Members.Regcode

Should work.

yes that works kind of.

as is yes does what i want it to do

no as if there arent any files listed in the database then the script returns no rows what so ever and doesnt allow a member to upload the file.

so would it be a case of making another query to run IF the above one return no rows

nemonoman

SELECT MAX(Quota), SUM(Size)
FROM MF_Members, MF_Lvls
LEFT JOIN MF_Files ON MF_Files.Usercode=MF_Members.Regcode
WHERE MF_Members.ULevel=MF_Lvls.ID
AND MF_Members.Regcode=$someSearchValue
GROUP BY MF_Members.Regcode