MySQL INSERT/SELECT subquery syntax

Question

Just can't wrap my head around the proper syntax for this one. Below is my query, with a plain english explanation of my subquery, in the spot where I think I'd want it to execute.

mysql_query("INSERT INTO donations(
            tid,
            email,
            amount,
            ogrequest,
            total
            )
            VALUES (
                '".esc($p->ipn_data['txn_id'])."',
                '".esc($p->ipn_data['pay_email'])."',
                ".(float)$amount.",
                '".esc(http_build_query($_POST))."',

            Here I want to select the row with the max date, get the value of the "total" column in that row, and add $amount to that value to form the new "total" for my newly inserted row.

            )");

Can anyone help a bro out?

schizodactyl · Accepted Answer · 2011-02-02 17:51:18Z

3

The real answer is you should not be storing the total in a column in this table. It isn't really any useful information. What you should be storing is the current date, and then calculating the total via SUM and GROUP BY. If it's something that you need to access often, then cache the value elsewhere.

Why do you need the total in any of the rows before the last one? It is just wasted data, and it can be easily regenerated from the table.

Why do you want to store the total in this column. What value does this data add to your schema? The important thing to note here is that the total is NOT a property of the individual transaction. The total is a property of an aggregated subset of individual transactions.

Also - make sure you are using DECIMAL and not FLOAT for your monetary column types in MySQL if you aren't. FLOAT values could result in rounding errors depending on what you are doing, which is something there is no reason to risk when money is involved.

answered Feb 2, 2011 at 17:51

schizodactyl

1,4551 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jon Over a year ago

You're right on. The only reason I was doing it is because I need to access it often, and was wondering about the overhead of constantly calculating SUM again and again over hundreds of rows. I figured instead of having to run through all those rows, I could just get a single value from the last row, and that would be less strain on the DB. What technique would you recommend for caching it?

schizodactyl Over a year ago

Well it's unlikely you need to cache this at all, really, as MySQL is likely to cache it for you. The normal way this is done in large applications is to use something like memcached ( memcached.org ), but if you don't want to bother to learn this you can get away with storing the value in another mysql table that is updated via a trigger on your donations table.

Jon Over a year ago

I'm going to go this route. Thanks for the advice, and for everyone else's answers... they are much appreciated.

CodeZombie · Accepted Answer · 2011-02-02 17:43:26Z

0

I don't have access to a MySQL server to verify what I created, but try this:

INSERT INTO donations
(
    tid,
    email,
    amount,
    ogrequest,
    total
)
SELECT
    '".esc($p->ipn_data['txn_id'])."',
    '".esc($p->ipn_data['pay_email'])."',
    ".(float)$amount.",
    '".esc(http_build_query($_POST))."',
    total + '".esc($amount)."'
FROM
ORDER BY date DESC
LIMIT 1

Instead of using a direct "INSERT INTO (...) VALUES (...)" I used a "INSERT INTO (...) SELECT ...". The SELECT statement retrieves the row with the highest date (ORDER BY date DESC LIMIT 1), then the total field is accessed and added with the value of $amount.

answered Feb 2, 2011 at 17:43

CodeZombie

5,3753 gold badges32 silver badges37 bronze badges

Comments

diagonalbatman · Accepted Answer · 2011-02-02 17:44:39Z

0

mysql_query("INSERT INTO donations(
            tid,
            email,
            amount,
            ogrequest,
            total
            )
            VALUES (
                '".esc($p->ipn_data['txn_id'])."',
                '".esc($p->ipn_data['pay_email'])."',
                ".(float)$amount.",
                '".esc(http_build_query($_POST))."',
                (select max(total) from donations) + ".(float)$amount."




            )");

answered Feb 2, 2011 at 17:44

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

Comments

Patrick Echterbruch · Accepted Answer · 2011-02-02 18:00:53Z

Your subquery could look like this:

SELECT total
FROM donations 
WHERE tid = <x>
ORDER BY date DESC
LIMIT 1

This of course requires that you have a date column in your table. If you run this one (without the outer query you already have), it should come back with a single row, single column result containing the value of latest total for tid = <x>.

If there's not already a row for txn = <x> in the table, then it will obviously return no row at all. When used as a subquery for your INSERT statement, you should probably check for NULL and replace it with a numeric 0 (zero). This is what IFNULL() can do for you.

Combining this and what you already have:

mysql_query("INSERT INTO donations(
            tid,
            email,
            amount,
            ogrequest,
            total
            )
            VALUES (
                '".esc($p->ipn_data['txn_id'])."',
                '".esc($p->ipn_data['pay_email'])."',
                ".(float)$amount.",
                '".esc(http_build_query($_POST))."',
                IFNULL(SELECT total
                 FROM donations
                 WHERE id = ".esc(p->ipn_data[txn_id']."
                 ORDER BY date DESC 
                 LIMIT 1),0) + ".esc($p->ipn_data['value']
            )");

Collectives™ on Stack Overflow

MySQL INSERT/SELECT subquery syntax

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related