Question on Database Structure

Discussion in 'Tips/Tricks/Mods' started by jonwatson, Jan 22, 2009.

  1. jonwatson

    jonwatson New Member

    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?


  2. falko

    falko Super Moderator ISPConfig Developer

    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
  3. jonwatson

    jonwatson New Member

    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 if the doc_id of in the isp_isp_web table was 1.


  4. falko

    falko Super Moderator ISPConfig Developer

    Try this:

    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'
  5. jonwatson

    jonwatson New Member

    Works like a charm, thanks.

    For anyone else that is reading this, the doc_id for 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'....
  6. jonwatson

    jonwatson New Member

    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.

  7. falko

    falko Super Moderator ISPConfig Developer

    Yes, that's right.
  8. jonwatson

    jonwatson New Member

    OK, thanks.

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


Share This Page