SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making calculations within mysql

    Hi,
    I have a problem here that I want to take a value from one table and add it to a value from another table and then update the value to the table. This would make 3 queries. I was thinking if there is a shorter way of doing things.

    The code is below:

    $result = mysql_query("SELECT money FROM personal WHERE id = '$trimmed' ");
    while($row = mysql_fetch_array($result))
    { $money = $row["money"];
    }
    $result = mysql_query("SELECT sum FROM addmoney WHERE name = '$addmoney' ");
    while($row = mysql_fetch_array($result))
    { $sum = $row["sum"];
    }
    $earnings = "$money" + "$sum";
    mysql_query("UPDATE addmoney SET money='$sum' WHERE id = '$trimmed' ");

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If mysql supported sub-queries and ANSI SQL92 standards then yes you would be able to do that in one query.

    With MySQL you could do it in two queries: one select and one update by using an inner join. More eloquent code, but may not have the performance efficiency of what you already have (which probably is not an issue unless those tables are huge).

    Here is the select query which will return earnings as the sum of money and addmoney:

    SELECT personal.money + addmoney.sum AS earnings
    FROM personal, addmoney
    WHERE personal.id = '$trimmed'
    AND addmoney.name = '$addmoney'

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I would like to stick to what I have, because I wish my site to grow

  4. #4
    SitePoint Member
    Join Date
    Dec 2000
    Location
    Poland
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, FreebieGuy, you should do it in two queries, not in three. Your code add only one value to only one value, so it is equivalent to:

    PHP Code:
    $result mysql_query("SELECT sum FROM addmoney WHERE name = '$addmoney' ");
    $row mysql_fetch_array($result);
    $sum $row["sum"];
    mysql_query("UPDATE personal SET money=money+$sum WHERE id = '$trimmed''"); 
    Chris
    PS. There is no need to put number values in ' '. Is $trimmed a number too?
    Last edited by asdn; Jun 26, 2001 at 08:23.

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good point asdn! For some reason I got confused and thought there were three tables invlolved when there are only two

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much. I thought of that yesterday, but I didn't know how to put the code.


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
  •