Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 2 > Tips/Tricks/Mods

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 22nd January 2009, 19:06
jonwatson jonwatson is offline
Senior Member
 
Join Date: Feb 2007
Posts: 176
Thanks: 15
Thanked 3 Times in 3 Posts
Default 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
Reply With Quote
Sponsored Links
  #2  
Old 23rd January 2009, 14:07
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,735 Times in 2,571 Posts
Default

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
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #3  
Old 24th March 2009, 16:17
jonwatson jonwatson is offline
Senior Member
 
Join Date: Feb 2007
Posts: 176
Thanks: 15
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #4  
Old 25th March 2009, 14:11
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,735 Times in 2,571 Posts
Default

Quote:
Originally Posted by jonwatson View Post
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.
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'
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
The Following User Says Thank You to falko For This Useful Post:
jonwatson (25th March 2009)
  #5  
Old 25th March 2009, 14:28
jonwatson jonwatson is offline
Senior Member
 
Join Date: Feb 2007
Posts: 176
Thanks: 15
Thanked 3 Times in 3 Posts
Default

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'....
Reply With Quote
  #6  
Old 25th March 2009, 16:24
jonwatson jonwatson is offline
Senior Member
 
Join Date: Feb 2007
Posts: 176
Thanks: 15
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #7  
Old 26th March 2009, 17:29
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,735 Times in 2,571 Posts
Default

Quote:
Originally Posted by jonwatson View Post
Table isp_isp_traffic
Fields: bytes_web, bytes_ftp, bytes_mail are all stored in bytes, correct?
Yes, that's right.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
The Following User Says Thank You to falko For This Useful Post:
jonwatson (2nd April 2009)
  #8  
Old 2nd April 2009, 19:32
jonwatson jonwatson is offline
Senior Member
 
Join Date: Feb 2007
Posts: 176
Thanks: 15
Thanked 3 Times in 3 Posts
 
Default

OK, thanks.

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

Jon
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database missing after upgrade autonic General 1 7th March 2008 15:33
Mail System doesnt work! crichton Installation/Configuration 3 17th September 2007 23:49
Image gallery question: Better to save the thumbnails in the database? padmx82 Programming/Scripts 2 4th January 2007 00:52
web database question nightcrawler72 General 1 8th September 2006 06:27
Recreating ISPConfig database retran Installation/Configuration 2 15th April 2006 23:22


All times are GMT +2. The time now is 04:37.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.