MySQL INSERT/SELECT subquery syntax

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?

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.

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.

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."




            )");

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']
            )");