Trouble updating particular Records

Hi Forum:

I have a db that therapists use to track their patients. The History table keeps track of the payments, attendance, and a few other details. When the therapist pulls up the post payment page, he selects the Group ID and loads a dropdown box with group member names. Then, as they pay, using the dropdown box to load their information, he posts their payments. This action updates the payment history for each patient indiviaually. It also updates the attendance fields ( attend = "Y" and attend_date = CURDATE() ). By default , the attend field is "N".
My problem is that I have no idea how to extract the patients from the History table that were not present, leaving the attend field to its default while inserting the CURDATE(). I was thinking I cld pull up the last record for each patient in any particular Group and check the last-entered attendance date against CURDATE(), then insert the CURDATE() if it didn't match. And while I think this might be the ticket, I'm really new to MySQL and am not sure how to implement such an idea. I'm not looking for anyone to write my code for me, but I'd really appreciate a little guidance and insight on this. Thanks.
Here is the History schema:

CREATE TABLE `history` (
`historyid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`amount` float NOT NULL,
`subsidy` char(1) NOT NULL,
`last_payment` date NOT NULL,
`amount_paid` float NOT NULL,
`balance` float NOT NULL,
`attend` char(1) NOT NULL DEFAULT 'N',
`attend_date` date NOT NULL,
`attendeeid` int(10) unsigned NOT NULL,
`memberid` int(10) unsigned NOT NULL,
PRIMARY KEY (`historyid`),
KEY `attendeeid` (`attendeeid`),
CONSTRAINT `history_ibfk_10` FOREIGN KEY (`attendeeid`) REFERENCES `attendees` (`attendeeid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm not sure what else I might need to provide. Please advise.

 

Sometimes it just takes walking away from your work and catching a breath of fresh air. I solved my issue over my first cup of coffee. Here's my working solution:

if($_POST['flush']) {
                        try {
                            $groupid = $_SESSION['groupid'];
                            $stmt = $db->prepare('SELECT historyid, amount, subsidy, last_payment, amount_paid, balance, 
                                                                    attend_date, h.memberid, a.groupid, a.attendeeid, h.attendeeid
                                                            FROM history AS h 
                                                            INNER JOIN attendees AS a ON a.attendeeid = h.attendeeid
                                                            WHERE a.groupid = :groupid
                                                            ORDER BY historyid DESC LIMIT 1');
                            $stmt->bindValue(':groupid', $groupid, PDO::PARAM_INT);
                            $stmt->execute();
                            $result = $stmt->fetchAll();
                           
                            foreach($result as $row ) { 
                                $amount = $row[1];           
                                $subsidy = $row[2];
                                $last_pymt = $row[3];
                                $amtpaid = $row[4];
                                $balance = $row[5];                                                           
                                $memid = $row[7];                               
                                $attid = $row[9];
                                if($row[6] <> date('Y-m-d')) {
                                    $stmt = $db->prepare('INSERT INTO history(amount, subsidy, last_payment, amount_paid, balance, 
                                                                    attend_date, attendeeid, memberid)
                                                                VALUES(:amt, :sub, :lastpayment, :amt_paid, :bal, CURDATE(), 
                                                                    :attid, :memid)');
                                    $stmt->bindParam(':amt', $amount, PDO::PARAM_STR);
                                    $stmt->bindParam(':sub', $subsidy, PDO::PARAM_STR);       
                                    $stmt->bindParam(':lastpayment', $last_pymt, PDO::PARAM_STR);
                                    $stmt->bindParam(':amt_paid', $amtpaid, PDO::PARAM_STR);
                                    $stmt->bindParam(':bal', $balance, PDO::PARAM_STR);
                                    $stmt->bindParam(':attid', $attid, PDO::PARAM_STR);
                                    $stmt->bindParam(':memid', $memid, PDO::PARAM_STR);
                                    $stmt->execute();                           
                                }                       
                            }
                        } catch (PDOException $ex) { ... }

Code (markup):

 

Like I said, I'm new to MySQL. Although my above code works, it only inserts on the first record it finds that matches the groupid number. How can I make it cycle through the entire table and INSERT the new record as needed? I'm beginning to think I have a whole lot wrong here.

 

Instead of doing a fetchAll() and looping the resulting array, just do a while ($row = $stmt->fetch()) { and do the inserts here } should work (it will loop through all results, and based on the results, it will update for each result it finds.

 

PoPSiCLe:

I made the change you suggested, but it only writes one insertion then stops before finishing the other needed insertions. Did I do it wrong?

if($_POST['flush']) {
                        try {
                            $groupid = $_SESSION['groupid'];
                            $stmt = $db->prepare('SELECT historyid, amount, subsidy, last_payment, amount_paid, balance,
                                                                    attend_date, h.memberid, a.groupid, a.attendeeid, h.attendeeid
                                                            FROM history AS h
                                                            INNER JOIN attendees AS a ON a.attendeeid = h.attendeeid
                                                            WHERE a.groupid = :groupid
                                                            ORDER BY historyid DESC LIMIT 1');
                            $stmt->bindValue(':groupid', $groupid, PDO::PARAM_INT);
                            $stmt->execute();
                     
                            while($row = $stmt->fetch()) {
                                $amount = $row[1];     
                                $subsidy = $row[2];
                                $last_pymt = $row[3];
                                $amtpaid = $row[4];
                                $balance = $row[5];                                                     
                                $memid = $row[7];                         
                                $attid = $row[9];
                                if($row[6] != date('Y-m-d')) {
                                    $stmt = $db->prepare('INSERT INTO history(amount, subsidy, last_payment, amount_paid, balance,
                                                                    attend_date, attendeeid, memberid)
                                                                VALUES(:amt, :sub, :lastpayment, :amt_paid, :bal, CURDATE(),
                                                                    :attid, :memid)');
                                    $stmt->bindParam(':amt', $amount, PDO::PARAM_STR);
                                    $stmt->bindParam(':sub', $subsidy, PDO::PARAM_STR); 
                                    $stmt->bindParam(':lastpayment', $last_pymt, PDO::PARAM_STR);
                                    $stmt->bindParam(':amt_paid', $amtpaid, PDO::PARAM_STR);
                                    $stmt->bindParam(':bal', $balance, PDO::PARAM_STR);
                                    $stmt->bindParam(':attid', $attid, PDO::PARAM_STR);
                                    $stmt->bindParam(':memid', $memid, PDO::PARAM_STR);
                                    $stmt->execute();                     
                                }                 
                            }
                        } catch (PDOException $ex) { ... }

Code (markup):

Through analysis using Adminer, I see that the problem for the single retrieval is the:

ORDER BY historyid DESC LIMIT 1

Code (markup):

Using that only allows the retrieval of the last record located in the History table. How do I change this so that it retrieves the last record for each attendee with the same groupid, then compares the date

if($row[6] != date('Y-m-d'))

Code (markup):

to see if an insertion is needed?

 

You just remove the limit 1 - if the rest of the query is correct it should do what you want. But if you already know the date, why don't you match against that in the query as well?

 

I cldn't match the date against the query because the History table tracks payment and attendance History for the attendees. There will be many prior payment and attendance records that wldn't match the CURDATE() and wld be falsely updated. As for your other suggestion of removing the limit 1, doing that retrieves every History record in the table for the groupID in question.
What I need this query to do is pull out the last-entered record for each attendee so I can test them to see if they need to be sent on to the INSERT statement or not.