Question on Database Structure

Hi All,

I have written a little PHP script that grabs each domain's bandwidth limit and the total monthly usage and calculates what percent of use they are at. I'd like to be able to email the user flagged as the admin user for each site, but I can't figure out how to get that information.

I see that the isp_isp_user table has a field user_admin which is set to 1 when a user is an admin and set to 0 when a user is not. I also see that the isp_isp_web table has a field named web_domain. What I can't see is how to know which admin user is admin of what domain.

Can someone help me on this?

Thanks

Jon

 

Try this MySQL query:

SELECT * FROM isp_isp_web, isp_dep WHERE isp_isp_web.doc_id = isp_dep.parent_doc_id AND isp_isp_web.doctype_id = isp_dep.parent_doctype_id AND isp_dep.child_doctype_id = $user_doctype_id (1014) AND isp_dep.child_doc_id = $user_doc_id

 

HI Falko,

I am finally getting back to this project after being derailed for the last little while.

I'm not having a lot of luck with this query. Probably mostly because I don't really understand it so I am not able to trouble shoot it effectively.

Can you clarify it a bit for me?

I guess if you can explain to me the relationship between the tables for me that would be the most helpful as I would then be able to figure out the query. Or, perhaps an example of how I would find the admin user of www.somedomain.com if the doc_id of www.somedomain.com in the isp_isp_web table was 1.

Thanks

Jon

 

Try this:

Code:

SELECT * FROM isp_nodes, isp_dep, isp_isp_user WHERE isp_dep.parent_doc_id = '1' AND isp_dep.parent_doctype_id = '1013' AND isp_dep.child_doc_id = isp_isp_user.doc_id AND isp_dep.child_doctype_id = '1014' AND isp_isp_user.user_admin = '1' AND isp_nodes.doc_id = isp_isp_user.doc_id AND isp_nodes.doctype_id = '1014' AND isp_nodes.status = '1'

 

Works like a charm, thanks.

For anyone else that is reading this, the doc_id for somedomain.com is the first instance of '1' in the query:

SELECT * FROM isp_nodes, isp_dep, isp_isp_user WHERE isp_dep.parent_doc_id = '1'....

 

OK, so one last question (I hope).

Can someone confirm the format in which the following values are stored in the database:

Table isp_isp_traffic
Fields: bytes_web, bytes_ftp, bytes_mail are all stored in bytes, correct?

Table isp_isp_web
Field web_traffic is stored in MBs, correct?

These are my assumptions but I am having a heck of a time trying to get a proper usage percentage calculated. I have verified that the web_traffic field is in MBs, but I may be assuming incorrectly on the other fields based on their names.

At the moment, I am trying to do something like:

((bytes_web + bytes_ftp + bytes_mail) *1024 *1024 ) / web_traffic

But the results are totally out of whack.

Thanks

 

Yes, that's right.

 

OK, thanks.

It turned out that my problem was in a different area. It all makes sense now.

Jon