CODEWITHSUNDEEP
phpmysqlsqlpdo
user2766423
user2766423

Reputation: 167

Table rows duplicating - logic may be off

I'm working with a database that contains these tables:

CREATE TABLE required_items(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    required_amount INT NOT NULL
);

CREATE TABLE donations(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    donation_amount INT NOT NULL,
    item_id INT NOT NULL,
    FOREIGN KEY(item_id) REFERENCES required_items(id)
);

On the webpage there is a form which lists the required items and the amount left. The user then can commit to donating a certain amount of whatever item they choose. So far, my code displays all the required items correctly, but it is not updating appropriately.

if(!empty($_REQUEST['donate']))
    {
        $sql = "INSERT INTO donations (name, email, donation_amount, item_id) VALUES (?, ?, ?, ?)";
        $query = $db->prepare($sql);
        $query->execute(array($name, $email, $donation_amount, $item_id));

        echo("Thank you for donating!\n<br>\n<br>");
    }

    $request = "SELECT 
                required_items.id,
                required_items.name, 
                required_items.required_amount - COALESCE(donations.donation_amount, 0) AS Amount_Left
                FROM required_items LEFT JOIN donations ON donations.item_id=required_items.id ORDER BY id ASC";

    $stmt = $db->query($request);
    $item_info = $stmt->fetchAll();

    } catch (PDOException $e) {
        echo "Exception: " . $e->getMessage();
    }

    // Round negative amounts to zero
    if($item_info['Amount_Left'] < 0){
        $item_info['Amount_Left'] = 0;
        }
?>
    <form name="donationForm" action="<?php $pageName ?>" method="POST">
    <fieldset>
    <table border="1">
        <tbody>
            <tr>
                <td width="200">Item Name</th><td width="100">Amount</th><td width="0"></th>
            </tr>
            <?php
                foreach ($item_info as $row):{
                    echo("<tr><td>" . $row['name'] . "</td>");
                    echo("<td>" . $row['Amount_Left'] . "</td>");
                    echo("<td><input type=\"radio\" name=\"radioButtons\" value=\"". $row['id'] ."\"></input></td></tr>\n");
                } endforeach;
            ?>
        <tbody>
    </table>

If I "donate" something once, the table is updated correctly, subtracting the donation from the required amount. However, if I donate the same item again, the table row is duplicated and the new donation is not subtracted from the required amount. I thought INSERT ON DUPLICATE KEY UPDATE may work, but I can't make sense of it. How can I get this table to display correctly?

EDIT: to clarify, the reason I used COALESCE was because the donation table has nothing in it until someone donates something. Therefore the equation doesn't compute as the values in the donations table are null at first.

EDIT 2: I guess the question then becomes, when should I COALESCE in this situation?

Upvotes: 2

Views: 67

Answers (1)

seven77
seven77

Reputation: 380

You would want to use SUM() instead of COALESCE(). All COALESCE() does is return the first not-null value.

Upvotes: 3

Related Questions