使用PHP将字段从mysql视图复制到mysql表

I have a view that calculates the number of appointments scheduled in the last 30 days, 365 days, and the number of missed appointments in the last 30 days.

I need to get these data into a table so that I can populate a reports module (I thought I could do it from the view, but it turns out I can not).

Parent table: client Child table: appointment View: total New table: apt_total

Within mysql I can do an insert or update just fine, but when I put it into php it does not work as planned. There may be 15-20 appointments for a client in a 30 day period, but I don’t want multiple entries in the apt_total table because then the reports module prints out 15-20 different running totals. I would like to keep it where the apt_total table only has as many rows as I have clients.

All of the tables have an “id” field which links back to the client table.

INSERT INTO apt_total (id, name, Last30days, Last365days, Alltime, NoShow) SELECT id, name, Last30days, Last365days, Alltime, NoShow from total3;

UPDATE apt_total, total3 SET apt_total.Last30days = total3.Last30days, apt_total.Last365days = total3.Last365days, apt_total.Alltime = total3.Alltime, apt_total.NoShow = total3.NoShow WHERE apt_total.id = total3.id;

QUESTION: What is the most efficient way of executing this in php?

I’ve been at this for over 24 hours and I feel like I am going to crack. Any help would be greatly appreciated.

PHP code:

function afterSave(&$record)
        {
            $app =& Dataface_Application::getInstance();
            $record = $app->getRecord();
            $sql = "INSERT INTO apt_total (id, name, Last30days, Last365days, Alltime, NoShow) SELECT id, name, Last30days, Last365days, Alltime, NoShow from total3";
            $res = xf_db_query($sql, df_db());
    }