Results 1 to 4 of 4

Thread: mysql query help needed

  1. #1
    Elite Member TonyT's Avatar
    Join Date
    Jan 2000
    Location
    Fairfax, VA
    Posts
    10,337

    mysql query help needed

    I have a db & need to query 8 tables to determine net income. I want the php script to make the variable $net_inc by using the result AS net_inc.

    Table1 called 'income' = all monies received = gross income.
    6 other tables are expense tables = all business expenses.
    net income = adjusted gross income = income minus expenses.
    All tables have a date field.
    These are the tables:
    Code:
    TABLE		FIELD
    income		inc_amount
    homedepot	hd_amount
    mosaic		mt_amount
    other		other_amount
    auto		auto_amount
    misc		misc_amount
    commis		commis_amount
    labor		lab_amount
    I need to:
    PHP Code:
      $gi mysql_query("SELECT SUM(inc_amount) AS gi FROM income WHERE date > 2004-12-31");
    $hd_exp mysql_query("SELECT SUM(hd_amount) AS hd_exp FROM homedepot WHERE date > 2004-12-31");
    $mt_exp mysql_query("SELECT SUM(mt_amount) AS mt_exp FROM mosaic WHERE date > 2004-12-31");
    $other_exp mysql_query("SELECT SUM(other_amount) AS other_exp FROM other WHERE date > 2004-12-31");
    $auto_exp mysql_query("SELECT SUM(auto_amount) AS auto_exp FROM auto WHERE date > 2004-12-31");
    $misc_exp mysql_query("misc_exp = mysql_query("SELECT SUM(misc_amount) AS misc_exp FROM misc WHERE date 2004-12-31");
    $commis_exp = mysql_query("SELECT SUM(commis_amount) AS commis_exp FROM commis WHERE date 2004-12-31");
    $lab_exp = mysql_query("SELECT SUM(lab_amount) AS lab_exp FROM labor WHERE date 2004-12-31"); 
    How do I then get the sum of the expense sums and then subtract that total from total income?

    I know I could probably use a temporary table for all the expenses and then get the sum of them, but there has to be a better way.
    Last edited by TonyT; 03-22-05 at 09:56 AM.
    No one has any right to force data on you
    and command you to believe it or else.
    If it is not true for you, it isn't true.

    LRH

  2. #2
    SG Enthusiast
    Join Date
    Jan 2001
    Location
    DC
    Posts
    4,717
    Hmmm.

    I would have probably created just one table for expense data with a column for 'expense type' - maybe with a reference table for expense types so I could use '1', '2', etc in my expense table (querying for integer values is much faster than strings). This would make totalling the expenses very easy; and require only a single, simple query. Make sense?

    Based on your current structure, I guess you would need to query for each type of expense (as your example) and then do math on the variables. Or you could throw everything into a temp table, as you mentioned.

    How do you plan to use the results? Update to a table, display for printing, etc?
    anything is possible - nothing is free


    Quote Originally Posted by Blisster
    It *would* be brokeback bay if I in fact went and hung out with Skye and co (did I mention he is teh hotness?)

  3. #3
    Elite Member TonyT's Avatar
    Join Date
    Jan 2000
    Location
    Fairfax, VA
    Posts
    10,337
    I thought about breaking it into more tables, but it's really just a simple setup with not a lot of data yearly, it's not like I have 10,000 separate expense records. Same goes for income table cause my jobs last anywhere from a half day to 2 weeks, thus at most there will be 60 income records/month but realistic figure is 10-15/month.

    I decided to use separate tables because not all the expense tables are related to the jobs table, which is the main table. For example, homedepot, mosais, other, commis & labor contain jobid fields. Misc & auto do not because misc can include expenses not directly related to a particular job costing, such as office supplies, tools, etc.

    The results will be incorporated onto a stats(yearly) script that currently displays GI, Estimates Pending and separated expenses (by expense tables).

    After the above is solved I will then build a query that can show individual job cost breakdown, e.g. jobid=278, last=Smith date=0000-00-00, gi=$xxx.xx, expenses=$xx.xx, netinc=$xxx.xx. Then I can see if I'm charging enough for certain services. As well I'll have a same script for weekly & monthly stats.

    Also, a few times/yr I will print the financial records, esp just before taxes due.

    I'm not worried about performance, the sql server is ultra fast as it is on my lan and so far all fields used in queries ar indexed.

    It's the math on the variables that is stumping me. Basically I need to get a sum of 7 sums and subtract that from income sum. Puzzling me.....
    No one has any right to force data on you
    and command you to believe it or else.
    If it is not true for you, it isn't true.

    LRH

  4. #4
    SG Enthusiast
    Join Date
    Jan 2001
    Location
    DC
    Posts
    4,717
    I understand your reasoning, but I would still use a single table for expenses - you could always have a jobid=0 to represent expenses not related to a particular job. But that's not what we're working with, so....

    Use you queries listed to assign each SUM(x) to a variable ($SUM1, $SUM2, etc)

    $netIncomeTotal = $gi - ($SUM1 + $SUM2 + $SUM3).

    Because of your architecture, it is easiest to handle the math within php vs. on the mysql side. If the were all in one table, you could do :

    $expenseTotal = ("SELECT SUM(amount) from expenses where date > xxxx-xx-xx AND (expenseType = 1 OR expenseType = 2 OR expenseType = 3)") [you get the idea]

    then $gi = SUM(income)

    then $totalNet = $gi - $expenseTotal
    anything is possible - nothing is free


    Quote Originally Posted by Blisster
    It *would* be brokeback bay if I in fact went and hung out with Skye and co (did I mention he is teh hotness?)

Similar Threads

  1. Freakin out a little here....SP2
    By Subsane in forum Software Forum
    Replies: 1
    Last Post: 09-30-04, 08:18 PM
  2. MySQL Shell inside a browser
    By TonyT in forum Programming Forum
    Replies: 0
    Last Post: 06-19-04, 07:51 AM
  3. php mysql code needed
    By TonyT in forum Programming Forum
    Replies: 14
    Last Post: 06-10-04, 12:26 AM
  4. Router Not Loading Webpage Properly
    By ChuckDees in forum Networking Forum
    Replies: 6
    Last Post: 04-08-04, 08:58 PM
  5. colorgenics!
    By onetrueday in forum General Discussion Board
    Replies: 22
    Last Post: 01-31-02, 12:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •