Jump to content

r4cc00n

New Members
  • Posts

    6
  • Joined

  • Last visited

    Never

Everything posted by r4cc00n

  1. In your function definition, you have: function Set($key, $val, $profile_id) But when you call the function here on 178: $this->Set("<< Time >>", $time); There is no $profile_id passed to the function, and you don't have a default value for it.
  2. Thanks, Wildbug, for the suggestion. There is one problem with this method, however; It does not return cases that do not have comments associated with them.
  3. Line 178? <?php if ($shift) { $this->Set("<< Time >>", $time); ?>
  4. What version of MySQL are you using?
  5. Hello, I've spent a good amount of time trying to solve this problem, and I can't seem to figure it out. From what I've read in the MySQL documentation, it appears that what I'm trying to do is impossible, but I'd like to get it out there anyway to see if someone else has a solution. I have two database tables. One consists of cases, and the other has comments for those cases. The structure is below (With all irrelevant fields removed): CREATE TABLE `cases` ( `case_id` int(11) NOT NULL auto_increment, `case_hours` double NOT NULL default '0', `case_rate` double NOT NULL default '0', `case_paid` double NOT NULL default '0', PRIMARY KEY (`case_id`) ) TYPE=MyISAM ; CREATE TABLE `comments` ( `comment_id` int(11) NOT NULL auto_increment, `comment_case` int(11) NOT NULL default '0', `comment_hours` double NOT NULL default '0', PRIMARY KEY (`comment_id`) ) TYPE=MyISAM ; The case table has 3 values in it used to calculate a balance: The number of hours spent on the case, the rate at which the client is being charged, and the total amount of money the client has paid thus far. You'll notice that the comment field also has a field for hours. The balance is calculated as follows: Balance = ( Hours in Case + Total Number of Comment Hours for Case ) * Rate - Amount Already Paid What I'm trying to accomplish is a MySQL query that will automatically calculate the balance to that it can be easily sorted without having to do a bunch of PHP code. So far, this is the best I could come up with: SELECT cases.case_id , cases.case_hours , cases.case_rate , cases.case_paid , @commenttotal := SUM(IF(comments.comment_case = cases.case_id,comments.comment_hours,0)) AS comment_totalhours , @totalhours := (@commenttotal + cases.case_hours) AS case_totalhours , @cost := (@totalhours * cases.case_rate) AS case_cost , @balance := (@cost - cases.case_paid) AS case_balance FROM cases, comments GROUP BY cases.case_id I've run into two problems with this code: 1) @commenttotal returns a correct value in comment_totalhours, but is not included in @totalhours 2) According to the MySQL documentation: Here's the catch... I'm running MySQL Version 4.0.26, so procedures aren't an option.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.